title: Query featuring outer joins behaves differently in Oracle 12c
body: <p>I had a problem come through concerning missing data on Oracle 12c.</p>
  
  <p>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. </p>
  
  <p>I have generalized the table structure and query somewhat and reproduced the issue.</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>Running the query:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>on Oracle 11g gives (here's a <a href="http://sqlfiddle.com/#!4/4b9ed/3" rel="noreferrer">SQL Fiddle</a>):</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Yet the same schema, data, and query on Oracle 12c:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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..</p>
  
  <p>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.</p>
  
  <p>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.?</p>
  
  <p>EDIT: Attached plans.</p>
  
  <p>Oracle 11g:</p>
  
  <p><img src="https://i.stack.imgur.com/RyP7s.png" alt="enter image description here"></p>
  
  <p>Oracle 12c:</p>
  
  <p><img src="https://i.stack.imgur.com/WGUyl.png" alt="enter image description here"></p>