Query With LEFT JOIN and NVL Condition Returns Wrong Results After Upgrade from 10.2 to 11.2 (Doc ID 2359738.1)

Last updated on APRIL 02, 2025

Applies to:

Symptoms

• After upgrade from 10.2 to 11.2, select statement with LEFT JOIN of ANSI SQL Syntax and NVL condition in WHERE clause returns wrong results
• Following is example testcase: 

  1.prepare test data
  SQL> create table test1(aaa number,bbb varchar2(20));
  SQL> create table test2(aaa number,ccc varchar2(20));
  SQL> insert into test1 values(1,'N');
  SQL> insert into test1 values(2,'N');
  SQL> insert into test1 values(3,'N');
  SQL> insert into test1 values(4,'N');
  SQL> insert into test1 values(5,'Y');
  SQL> insert into test1 values(6,'Y');
  SQL> insert into test1 values(7,'Y');
  SQL> insert into test1 values(8,'Y');
  SQL> insert into test1 values(9,'Y');
  SQL> insert into test2 values(2,'A');
  SQL> insert into test2 values(3,'B');
  SQL> insert into test2 values(4,'C');
  SQL> insert into test2 values(5,'D');
  SQL> insert into test2 values(6,'E');
  SQL> insert into test2 values(7,'F');
  SQL> commit;
  

  
  
  -- 2. Following SQL returns wrong results missing some rows.
  
  SQL> select * from test1 left join test2
  2  on test1.aaa=test2.aaa
  3  and test1.bbb=nvl('Y',test2.aaa)
  4  order by test1.aaa;
  
  Wrong Results:
  
  AAA BBB    AAA CCC
  ---- ----- ---- -----
   5 Y        5 D
   6 Y        6 E
   7 Y        7 F
   8 Y
   9 Y
  
  

• Because the condition of "test1.bbb=nvl('Y',test2.aaa)" is just a condition of left outer join in ANSI SQL Syntax, not in the WHERE clause that filters out rows from TEST1, all rows of TEST1 should be return:
  
  Correct Results:
  
  AAA BBB    AAA CCC
  ---- ----- ---- -----
   1 N
   2 N
   3 N
   4 N
   5 Y        5 D
   6 Y        6 E
   7 Y        7 F
   8 Y
   9 Y
  
  

•  In 10.2 or 11.1 environment, the select statement will return correct results.
• From information of dbms_xplan.display_cursor, the predicate of "filter("TEST1"."BBB"='Y')" is applied to the row source operation of "TABLE ACCESS FULL TEST1", that cause some rows are filtered out:
  
  ---------------------------------------+-----------------------------------+
  | Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
  ---------------------------------------+-----------------------------------+
  | 0   | SELECT STATEMENT     |         |       |       |     8 |           |
  | 1   |  SORT ORDER BY       |         |     5 |    50 |     8 |  00:00:01 |
  |*2   |   HASH JOIN OUTER    |         |     5 |    50 |     7 |  00:00:01 |
  |*3   |    TABLE ACCESS FULL | TEST1   |     5 |    25 |     3 |  00:00:01 | <<<<<----------------------
  | 4   |    TABLE ACCESS FULL | TEST2   |     6 |    30 |     3 |  00:00:01 |
  ---------------------------------------+-----------------------------------+
  Predicate Information:
  ----------------------
  2 - access("TEST1"."AAA"="TEST2"."AAA")
  3 - filter("TEST1"."BBB"='Y')   <<<<<----------------------

Changes

Upgrade from 10.2 to 11.2.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.