Oracle’s ANSI Join Syntax Can Result In Wrong Results

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
----------
         1

SQL>

Running a 10053 trace in 11.2.0.4 reveals this:


SELECT SUM(CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE NULL END ) "THE_ANSWER" 
FROM "SYS"."DUAL" "DUAL","SYS"."DUAL" "DUAL" 
WHERE CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =CASE  
WHEN ("DUAL".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END  
GROUP BY 1

The errant ‘table3’ has disappeared! Oracle has done a merge and in the process eliminated the problem table reference, as shown in this excerpt from the 10053 trace file:


CVM:   Merging complex view SEL$5E38693D (#0) into SEL$3 (#0).
qbcp:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_003"."THE_ANSWER" "THE_ANSWER" FROM  (SELECT SUM("TABLE2"."MY_NUMBER") "THE_ANSWER" FROM "SYS"."DUAL" "DUAL", (SELECT 1 "MY_NUMBER" FROM "SYS"."DUAL" "DUAL") "TABLE2" WHERE CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END ="TABLE2"."MY_NUMBER"(+) GROUP BY 1) "from$_subquery$_003"
vqbcp:******* UNPARSED QUERY IS *******
SELECT SUM("TABLE2"."MY_NUMBER") "THE_ANSWER" FROM "SYS"."DUAL" "DUAL", (SELECT 1 "MY_NUMBER" FROM "SYS"."DUAL" "DUAL") "TABLE2" WHERE CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END ="TABLE2"."MY_NUMBER"(+) GROUP BY 1
CVM: result SEL$3 (#0)
******* UNPARSED QUERY IS *******
SELECT SUM("TABLE2"."MY_NUMBER") "THE_ANSWER" FROM "SYS"."DUAL" "DUAL", (SELECT 1 "MY_NUMBER" FROM "SYS"."DUAL" "DUAL") "TABLE2" WHERE CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END ="TABLE2"."MY_NUMBER"(+) GROUP BY 1
Registered qb: SEL$0A2D7B19 0xf9641f8 (VIEW MERGE SEL$3; SEL$5E38693D)

Oracle 12.1.0.2 doesn’t do this on the first query, so it returns the error that would be expected. The second query in both systems executes, even though it also has the errant ‘table3’ specified. The trace file reports:


FPD: Considering simple filter push in query block SEL$F146EED8 (#1)
CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =CASE  WHEN ("DUAL".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END 
try to generate transitive predicate from check constraints for query block SEL$F146EED8 (#1)
finally: CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =CASE  WHEN ("DUAL".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END 

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SUM(CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE NULL END ) "THE_ANSWER" FROM "SYS"."DUAL" "DUAL","SYS"."DUAL" "DUAL" WHERE CASE  WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =CASE  WHEN ("DUAL".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END  GROUP BY 1
kkoqbc: optimizing query block SEL$F146EED8 (#1)

It appears that the second query is transformed using a simple filter push, which in both releases eliminates the troublesome ‘table3’ from the query text, allowing Oracle to successfully execute the query. This would indicate to me that there are two code paths involved and that only one has been modified between 11.2.0.4 and 12.1.0.2. To me, both queries should produce the same error, but at least Oracle is making progress since the first query, as written, throws the expected error in the latest release of the database.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles