Is this a bug in Oracle when joining a table to a view that depends on the table

Question

I've found what I think is a bug in Oracle, but I'm wondering if there is something documented that I've missed.

Fiddles: Oracle: http://sqlfiddle.com/#!4/43c19/2 SQL Server: http://sqlfiddle.com/#!3/ddc49/1 MySql: http://sqlfiddle.com/#!2/43c195/1

Basically I have a main table that I left join onto a secondary table. Then I left join onto a view. If I specify in the join to the view that I only want to join when a column in the secondary table is not null, I get unexpected results. This is best explained by showing the query:

SELECT
  1,
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithoutSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithoutSecondary.KeyColumn)
UNION ALL
SELECT
  2,
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn)

See below for the creation scripts to test it yourself. In SQL Server and MySql I get the same results, however Oracle is different. There are three tables and two views in the schema. The views are defined as follows:

CREATE VIEW ViewWithoutSecondary
AS
SELECT
  TertiaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  TertiaryTable

CREATE VIEW ViewWithSecondary
AS
SELECT
  SecondaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  SecondaryTable
  LEFT JOIN TertiaryTable ON SecondaryTable.KeyColumn = TertiaryTable.KeyColumn;

In Oracle, I've found that if the view contains a reference to SecondaryTable, then I only get rows from MainTable that have a match in Secondary table. It looks to me like Oracle is inlining the view code in some way so that one of the rows is omitted.

I think if MainTable has three rows then doing two left joins of it should always return the three rows at least, plus any results from the join. However in the example given that is not the case.

I know that the SecondaryTable.KeyValue IS NOT NULL is redundant since the second half of the clause would not be true if the value is null, but I've been trying to rework a query to help the optimizer come up with a better plan.

The full creation script to run the example is:

CREATE TABLE MainTable
(
  KeyColumn varchar(32),
  ValueColumn varchar(32)
);

INSERT INTO MainTable VALUES ('123', 'abc');
INSERT INTO MainTable VALUES ('456', 'def');
INSERT INTO MainTable VALUES ('789', 'ghi');

CREATE TABLE SecondaryTable
(
  KeyColumn varchar(32),
  SecondaryValue integer  
);

INSERT INTO SecondaryTable VALUES ('123', 1);
INSERT INTO SecondaryTable VALUES ('456', 2);

CREATE TABLE TertiaryTable
(
  KeyColumn varchar(32),
  TertiaryValue integer  
);

INSERT INTO TertiaryTable VALUES ('123', 1);

CREATE VIEW ViewWithoutSecondary
AS
SELECT
  TertiaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  TertiaryTable;

CREATE VIEW ViewWithSecondary
AS
SELECT
  SecondaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  SecondaryTable
  LEFT JOIN TertiaryTable ON SecondaryTable.KeyColumn = TertiaryTable.KeyColumn;

Answer 1

If you run an explain plan on the query, you can see that Oracle is transforming the query by inlining the view, and, for some reason, it is performing an inner join at line 2, rather than a left-outer.

explain plan 
SET statement_id = 'no-hint' FOR  
SELECT 
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn);

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'no-hint','TYPICAL'));


----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     2 |   108 |    20  (10)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER   |                |     2 |   108 |    20  (10)| 00:00:01 |
|*  2 |   HASH JOIN           |                |     2 |   108 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | SECONDARYTABLE |     2 |    36 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | MAINTABLE      |     3 |   108 |     3   (0)| 00:00:01 |
|   5 |   VIEW                |                |     1 |       |     7  (15)| 00:00:01 |
|*  6 |    FILTER             |                |       |       |            |          |
|*  7 |     HASH JOIN OUTER   |                |     1 |    36 |     7  (15)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL| SECONDARYTABLE |     1 |    18 |     3   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL| TERTIARYTABLE  |     1 |    18 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MAINTABLE"."KEYCOLUMN"="SECONDARYTABLE"."KEYCOLUMN")
   6 - filter("SECONDARYTABLE"."KEYCOLUMN" IS NOT NULL)
   7 - access("SECONDARYTABLE"."KEYCOLUMN"="TERTIARYTABLE"."KEYCOLUMN"(+))
   8 - filter("SECONDARYTABLE"."KEYCOLUMN"="SECONDARYTABLE"."KEYCOLUMN")

A work-around for this issue is to use the NO_MERGE hint.

SELECT /*+ NO_MERGE(ViewWithSecondary) */
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn);

This produces the expected result:

KEYCOLUMN                        VALUECOLUMN                    
-------------------------------- --------------------------------
123                              abc                              
456                              def                              
789                              ghi  

Compare the query plan for the hinted query. Here we see a left-outer join at line 2.

explain plan
SET statement_id = 'with-hint' FOR
SELECT /*+ NO_MERGE(ViewWithSecondary) */
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn);

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'with-hint','TYPICAL'));  

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     6 |   324 |    26   (8)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER    |                   |     6 |   324 |    26   (8)| 00:00:01 |
|*  2 |   HASH JOIN OUTER      |                   |     3 |   162 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | MAINTABLE         |     3 |   108 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | SECONDARYTABLE    |     2 |    36 |     3   (0)| 00:00:01 |
|   5 |   VIEW                 |                   |     2 |       |     7  (15)| 00:00:01 |
|*  6 |    FILTER              |                   |       |       |            |          |
|*  7 |     VIEW               | VIEWWITHSECONDARY |     2 |    36 |     7  (15)| 00:00:01 |
|*  8 |      HASH JOIN OUTER   |                   |     2 |    72 |     7  (15)| 00:00:01 |
|   9 |       TABLE ACCESS FULL| SECONDARYTABLE    |     2 |    36 |     3   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL| TERTIARYTABLE     |     1 |    18 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MAINTABLE"."KEYCOLUMN"="SECONDARYTABLE"."KEYCOLUMN"(+))
   6 - filter("SECONDARYTABLE"."KEYCOLUMN" IS NOT NULL)
   7 - filter("SECONDARYTABLE"."KEYCOLUMN"="VIEWWITHSECONDARY"."KEYCOLUMN")
   8 - access("SECONDARYTABLE"."KEYCOLUMN"="TERTIARYTABLE"."KEYCOLUMN"(+))