In almost all cases the SQL you write using the ANSI (SQL-92) 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

*bit come from ? These queries should result in Oracle error ORA-00904: “TABLE3″.”TABLE2″.”MY_NUMBER”: invalid identifier.*

**“table3”**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.

### Update (8th July 2016)

I’ve just seen a reference to this blog note on the OTN database forum that prompted me to point out that the problem doesn’t occur if you switch to traditional Oracle syntax – the expected Oracle error message appears in 11.2.0.4:

SQL> ed temp 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 */ from table1, table2 where table2.my_number(+) = table1.my_number group by table1.my_number ) 15 ; select sum(table3.table2.my_number) the_answer * ERROR at line 6: ORA-00904: "TABLE3"."TABLE2"."MY_NUMBER": invalid identifier SQL> ed temp3 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 from table1, table2 where table2.my_number(+) = table1.my_number group by table1.my_number ) 13 ; select sum(table3.table2.my_number) the_answer * ERROR at line 6: ORA-00904: "TABLE3"."TABLE2"."MY_NUMBER": invalid identifier