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:
ORA-00904: "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
----------

Full article...