Query featuring outer joins behaves differently in Oracle 12c

Question

I had a problem come through concerning missing data on Oracle 12c.

I took a look at the code and found a query that works on mysql, mssql, oracle 11g, but has different behaviour in oracle 12c.

I have generalized the table structure and query somewhat and reproduced the issue.

create table thing (thing_id number, display_name varchar2(500));
create table thing_related (related_id number, thing_id number, thing_type varchar2(500));
create table type_a_status (related_id number, status varchar2(500));
create table type_b_status (related_id number, status varchar2(500));

insert into thing values (1, 'first');
insert into thing values (2, 'second');
insert into thing values (3, 'third');
insert into thing values (4, 'fourth');
insert into thing values (5, 'fifth');
insert into thing_related values (101, 1, 'TypeA');
insert into thing_related values (102, 2, 'TypeB');
insert into thing_related values (103, 3, 'TypeB');
insert into thing_related (related_id, thing_id) values (104, 4);

insert into type_a_status values (101, 'OK');
insert into type_b_status values (102, 'OK');
insert into type_b_status values (103, 'NOT OK');

Running the query:

SELECT t.thing_id AS id, t.display_name as name,
       tas.status as type_a_status,
       tbs.status as type_b_status
FROM thing t LEFT JOIN thing_related tr 
  ON t.thing_id = tr.thing_id
LEFT JOIN type_a_status tas 
  ON (tr.related_id IS NOT NULL 
      AND tr.thing_type = 'TypeA' 
      AND tr.related_id = tas.related_id)
LEFT JOIN type_b_status tbs 
  ON (tr.related_id IS NOT NULL 
      AND tr.thing_type = 'TypeB' 
      AND tr.related_id = tbs.related_id)

on Oracle 11g gives (here's a SQL Fiddle):

ID | NAME   | TYPE_A_STATUS | TYPE_B_STATUS
 1 | first  |            OK | (null)
 2 | second |        (null) | OK
 3 | third  |        (null) | NOT OK
 4 | fourth |        (null) | (null)
 5 | fifth  |        (null) | (null)

Yet the same schema, data, and query on Oracle 12c:

ID | NAME   | TYPE_A_STATUS | TYPE_B_STATUS
 1 | first  |            OK | (null)
 2 | second |        (null) | OK
 3 | third  |        (null) | NOT OK
 4 | fourth |        (null) | (null)

It seems that the second two outer joins are failing to bring back anything because there is no row in 'thing_related' to join by. However I don't understand why the outer join does not return nulls in this case as it does in Oracle 11g, Mysql, etc..

I've been researching and found documentation the Oracle 12c had a number of enhancements for outer joins, but nothing that highlighted a change that would affect this.

Does anyone know why this is happening only for Oracle 12c, and how best would I rewrite this to work in 12c and maintain compatibility with 11g, mysql, etc.?

EDIT: Attached plans.

Oracle 11g:

Oracle 12c:

Answer 1

UPDATE: This is fixed in 12.1.0.2.

---

This definitely looks like a bug in 12.1.0.1. I would encourage you to create a service request through Oracle support. They might be able to find a fix or a better work around. And hopefully Oracle can fix it in a future version for everyone. Normally the worst part about working with support is reproducing the issue. But since you already have a very good test case this issue may be easy to resolve.

There are probably many ways to work around this bug. But it's difficult to tell which method will always work. Your query re-write may work now, but if optimizer statistics change perhaps the plan will change back in the future.

Another option that works for me on 12.1.0.1.0 is:

ALTER SESSION SET optimizer_features_enable='11.2.0.3';

But you'd need to remember to always change this setting before the query is run, and then change it back to '12.1.0.1' after. There are ways to embed that within a query hint, such as /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */. But for some reason that does not work here. Or perhaps you can temporarily set that for the entire system and change it back after a fix or better work around is available.

Whichever solution you use, remember to document it. If a query looks odd the next developer may try to "fix" it and hit the same problem.

Answer 2

Refer to:

ANSI Outer Join Query Returns Wrong Results After Upgrade to 12.1.0.1 (Doc ID 1957943.1)

Unpublished bug 16726638

Fixed in 12.1.0.2 (I have tested this)

Workaround (I tested in 12.1.0.1):

alter session set "_optimizer_ansi_rearchitecture"=false;          

Note 1957943.1 recommends as an alternative:

optimizer_features_enable = '11.2.0.4';

but that does not work.