title: Oracle ROWNUM with multiple joins
body: <p>I'm trying to execute a query on an Oracle database.</p>
  
  <p>My table structure is:</p>
  
  <ul>
  <li>d--o one-to-one</li>
  <li>o--r one-to-many</li>
  <li>o--m many-to-many (via mtm)</li>
  </ul>
  
  <p>I'm using the following query:</p>
  
  <pre><code>SELECT o.id ID, ROWNUM rn                                               /*row 1*/
  FROM TABLE_D d
  LEFT JOIN TABLE_O o ON d.O_ID=o.ID 
  RIGHT JOIN TABLE_R r ON r.O_ID = o.ID AND r.TYPE = 'SOME_TYPE'          /*row 4*/
  RIGHT JOIN TABLE_MTM mtm ON o.ID = mtm.ORD_ID                           /*row 5-6*/
  RIGHT JOIN TABLE_M m ON mtm.M_ID = m.ID AND m.TYPE = 'SOMETHING_ELSE'   /*row 5-6*/
  
  WHERE o.ACTIVE=0
  
  ORDER BY o.SOME_COL ASC; 
  </code></pre>
  
  <p>(This is just a small part of my full query; yes I do need the ROWNUM, and the 'd' table, and everything)</p>
  
  <ul>
  <li>If I run this query it returns no results.</li>
  <li>If I remove the ROWNUM from the SELECT, it returns the result</li>
  <li>If I remove row 4, it returns results</li>
  <li>If I remove row 5 and row 6, it returns results.</li>
  </ul>
  
  <p>Can anyone tell me what I'm missing? </p>
  
  <p>Update:
    All works well on Oracle 12c. 
    Doesn't work correctly on 10g.</p>