Random Neurons

11.2.0.2 Bug – Outer Joins and Literals

Here are some more details about a bug that Laurent Schneider wrote about in a recent blog post.

Let's start with an example of the bug.

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 7 07:32:32 2011
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> SELECT * FROM
  2    (SELECT 2 B FROM dual WHERE dummy = 'X'),
  3    (SELECT 3 C FROM dual WHERE dummy = 'X')
  4  WHERE C = B(+)
  5  ;
 
         B          C
---------- ----------
                    3
 
SQL> SELECT * FROM
  2    (SELECT 2 B FROM dual WHERE dummy LIKE '%'),
  3    (SELECT 3 C FROM dual WHERE dummy = 'X')
  4  WHERE C = B(+)
  5  ;
 
         B          C
---------- ----------
         2          3
 
SQL> SELECT 2 B FROM dual WHERE dummy = 'X';
 
         B
----------
         2
 
SQL> SELECT 2 B FROM dual WHERE dummy LIKE '%';
 
         B
----------
         2

A workaround is to use the NO_MERGE hint or alter session set “_complex_view_merging”=false

SQL> SELECT /*+ NO_MERGE(t1) */ * FROM
  2    (SELECT 2 B FROM dual WHERE dummy LIKE '%') t1,
  3    (SELECT 3 C FROM dual WHERE dummy = 'X') t2
  4  WHERE t2.c = t1.b (+)
  5  ;
 
         B          C
---------- ----------
                    3

This is even a problem if the outer joined subquery returns no rows

SQL> SELECT 2 B FROM dual WHERE dummy = 'Y';
 
no rows selected
 
SQL> SELECT * FROM
  2    (SELECT 2 B FROM dual WHERE dummy = 'Y') t1,
  3    (SELECT 3 C FROM dual WHERE dummy LIKE 'X') t2
  4  WHERE t2.c = t1.b (+)
  5  ;

         B          C
---------- ----------
         2          3

It only seems to be a problem when the outer-joined column is a constant though

SQL> SELECT * FROM
  2    (SELECT dummy B FROM dual WHERE dummy = 'X') t1,
  3    (SELECT '3' C FROM dual WHERE dummy LIKE 'X') t2
  4  WHERE t2.c = t1.b (+)
  5  ;
 
B C
- -
  3
 
SQL> SELECT * FROM
  2    (SELECT '2' B FROM dual WHERE dummy = 'X') t1,
  3    (SELECT dummy C FROM dual WHERE dummy LIKE 'X') t2
  4  WHERE t2.c = t1.b (+)
  5  ;
 
B C
- -
2 X

Ultimately it seems to be an issue with Oracle moving the constants around when re-writing the query as you can see from the following where the column that is selected from the table in the sub-query remains NULL in the results but the constant is brought through.

SQL> SELECT t1.b, t1.d, t2.c FROM
  2    (SELECT 2 B, dummy D FROM dual WHERE dummy = 'X') t1,
  3    (SELECT 3 C FROM dual WHERE dummy LIKE 'X') t2
  4  WHERE t2.c = t1.b (+)
  5  ;
 
         B D          C
---------- - ----------
         2            3