In almost all cases the SQL you write using the ANSI standard syntax is tranformed into a statement using Oracle’s original syntax before being optimised – and there are still odd cases where the translation is not ideal. This can result in poor performance, it can result in wrong results. The following examples arrived in my in-tray a couple of weeks ago:
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;
Notice the reference to table3.table2.my_number in the select list of both queries – where does the “table3” bit come from ? These queries should result in Oracle error ORA-00904: “TABLE3″.”TABLE2″.”MY_NUMBER”: invalid identifier.
If you’re running 11.2.0.4 (and, probably, earlier versions) both queries produce the following result:
THE_ANSWER ---------- 1 1 row selected.
If you’re running 12.1.0.2 the first query produces the ORA-00904 error that it should do, but the second query still survives to produce the same result as 11.2.0.4.
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 14 weeks ago
2 years 35 weeks ago
3 years 4 weeks ago
3 years 33 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago