The query does not give the same results in oracle 10g and oracle 11g:
with table1 as
(select 1 as id, 'A' as col1, 'X' as col2
from dual
union all
select 2 as id, 'Y' as col1, 'A' as col2
from dual
union all
select 3 as id, 'Z' as col1, 'Z' as col2
from dual)
select *
from table1
where (1 = 1 and (ID=2 or ID=3))
or EXISTS (SELECT 0 FROM dual WHERE col1 = 'A')
or EXISTS (SELECT 0 FROM dual WHERE col2 = 'A')
lead to oracle 11g:
ID COL1 COL2
1 A X
2 Y A
result in oracle 10g:
ID COL1 COL2
1 A X
2 Y A
3 Z Z
why???
source
share