Different results with the same query in oracle 11g and 10g

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

enter image description here result in oracle 10g:

ID  COL1    COL2
1   A       X
2   Y       A
3   Z       Z

enter image description here why???

+4
source share

Source: https://habr.com/ru/post/1616025/


All Articles