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:
