"Too smart" optimizer


Part of the features of the optimizer is its ability to re-arrange SQL to attempt to attain a result more efficiently. Of course, sometimes the optimizer might get things wrong and perform a simplification where it was not appropriate. Consider the following little example from 9.2.0.5

SQL> select deptno, job, min(sal) from scott.emp group by deptno, job
  2  /

    DEPTNO JOB         MIN(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 CLERK            800
        20 ANALYST         3000
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        1250

9 rows selected.

SQL> select distinct deptno
  2  from
  3  ( select deptno, job, min(sal)
  4    from scott.emp group by deptno, job )
  5  where deptno = 20
  6  /

    DEPTNO
----------
        20

1 row selected.

No problems there. But when I wrap the above SQL within another layer...

SQL> select * from ( select distinct deptno from ( select deptno, job,
  2  min(sal) from scott.emp group by deptno, job ) where deptno = 20 )
  3  /

    DEPTNO
----------
        20
        20
        20

3 rows selected.