 

Oracle's ANSI Join Syntax Can Result In Wrong Results By: David Fitzjarrell
Ever since Oracle provided their version of ANSI syntax it's been improving with each release. Unfortunately some steps forward also take steps backwards. A case in point is the following example, brought to my attention by Jonathan Lewis. Let's look at the queries and see where Oracle has gone afoul of things. The queries seem simple enough: with table1 as ( select 1 my_number from dual ), table2 as ( select 1 my_number from dual ) select * from ( select sum(table3.table2.my_number) the_answer from table1 left join table2 on table1.my_number = table2.my_number group by table1.my_number ); with table1 as ( select 1 my_number from dual ), table2 as ( select 1 my_number from dual ) select sum(table3.table2.my_number) the_answer from table1 left join table2 on table1.my_number = table2.my_number group by table1.my_number; Looking very carefully, notice that there's a 'table3' that is unaccounted for in the setup. Interestingly enough 11.2.0.4 executes these queries with nary a whimper or complaint: SQL> with 2 table1 as ( select 1 my_number from dual ), 3 table2 as ( select 1 my_number from dual ) 4 select * 5 from ( 6 select sum(table3.table2.my_number) the_answer 7 from table1 8 left join table2 on table1.my_number = table2.my_number 9 group by table1.my_number 10 ); THE_ANSWER  1 SQL> SQL> SQL> with 2 table1 as ( select 1 my_number from dual ), 3 table2 as ( select 1 my_number from dual ) 4 select sum(table3.table2.my_number) the_answer 5 from table1 6 left join table2 on table1.my_number = table2.my_number 7 group by table1.my_number; THE_ANSWER  1 SQL> Try to run them on 12.1.0.2 and you see a different result: SQL> with 2 table1 as ( select 1 my_number from dual ), 3 table2 as ( select 1 my_number from dual ) 4 select * 5 from ( 6 select sum(table3.table2.my_number) the_answer 7 from table1 8 left join table2 on table1.my_number = table2.my_number 9 group by table1.my_number 10 ); select sum(table3.table2.my_number) the_answer * ERROR at line 6: ORA00904: "TABLE3"."TABLE2"."MY_NUMBER": invalid identifier SQL> SQL> SQL> with 2 table1 as ( select 1 my_number from dual ), 3 table2 as ( select 1 my_number from dual ) 4 select sum(table3.table2.my_number) the_answer 5 from table1 6 left join table2 on table1.my_number = table2.my_number 7 group by table1.my_number; THE_ANSWER 
Other Related Articles ... to read more DBA articles, visit http://dba.fyicenter.com/article/ 
