10g vs 11g CBO UNNEST different results

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 27 Jun 2011 14:32:27 -0700

create table tests  (id number);



insert into tests values(1);

insert into tests values(2);

commit;



/* in 10g this gives 1 row, in 11g no rows */

SELECT *

  FROM tests a

 WHERE id = NVL ( (SELECT  MAX (b.id)

                     FROM tests b

                    WHERE b.id > 2  AND a.id = b.id),

                 1);



/* the hint will make 11g have same result as 10g */

SELECT *

  FROM tests a

 WHERE id = NVL ( (SELECT /*+ NO_UNNEST */ MAX (b.id)

                     FROM tests b

                    WHERE b.id > 2  AND a.id = b.id),

                 1);


I got this example from the oracle-plsql group.

Is this an oracle bug?


Regards,

Mike

Other related posts: