unexpected results !

It makes you hurl! It makes you claim you have found a bug! but it is working as specified!

1) subquery refers to a column of the main query

select * from emp where ename in (select ename from dept where deptno=10);

the query does not complain that column does not exist in dept. It is perfectly legal to specify a non-prefixed column of the main query in the subquery. This could be like

select * from tab where 'foo' in (select 'foo' from dict);

so the "in" clause is always true

2) "not in" does not deliver result

select sysdate from dual where 400 not in (select comm from emp);

this is because 400!=null is UNKOWN. Check the 3 state booleans operations!

it could be rewritten with a not exists, or in 10g with LNNVL

select sysdate from dual where lnnvl(400 in (select comm from emp));

3) rows are not delivered in the correct order
it is a wrong assumption to think that the rows will be delivered in a specific order if you do not specify order by.

it depends mainly on the execution plan.

SQL> select ename from emp where ename<'C';
ENAME
----------
BLAKE
ALLEN
ADAMS

even group by does not help
SQL> select ename, max(job) from emp where ename<'C' group by ename;
ENAME MAX(JOB)
---------- ---------
BLAKE MANAGER
ALLEN SALESMAN
ADAMS CLERK

Why? I cheated the emp table and add an index using descending order!

If your table is partitioned, the order and the degree of parallelism depend on the load, and may deliver different results.

Even if I did not find an example for DISTINCT, I guess it is not safe to assume the way the rows are sorted does not depend on the execution plan.

4) avg does not deliver sum/count(*)
select sum(comm),count(*),avg(comm) from emp;
SUM(COMM) COUNT(*) AVG(COMM)
---------- ---------- ----------
2200 14 550

avg does ignore null. in fact it delivers sum(comm)/count(comm).

5) table does not exist

SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GreatTable TABLE

SQL> desc GreatTable
ERROR:
ORA-04043: object GreatTable does not exist

Whoever it is, someone created a case sensitive table name.

SQL> desc "GreatTable"
Name Null? Type
----------------------- -------- ----------------
MagicColumn NUMBER(38,2)