title: Oracle Partition Pruning not working when left outer join is used
body: <p>I have one table that is list partitioned on a numeric column (row_id), </p>
  
  <pre><code>TABLEA (ROW_ID NUMERIC(38), TB_KEY NUMERIC(38), ROW_DATA VARCHAR(20));
  </code></pre>
  
  <p>Partition pruning <strong>works</strong> when i query from table with no joins:</p>
  
  <pre><code>SELECT A.* FROM TABLEA A
  WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
  </code></pre>
  
  <p>Partition Pruning <strong>fails</strong> when I do left outer join to TableB </p>
  
  <pre><code>SELECT A.* FROM TABLEA A
  LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
  WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
  </code></pre>
  
  <p>Partition Pruning <strong>works</strong> when I change left outer join to inner join</p>
  
  <pre><code>SELECT A.* FROM TABLEA A
  INNER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
  WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
  </code></pre>
  
  <p>Partition Pruning <strong>works</strong> when I do left outer join to TableB and do not use IN clause</p>
  
  <pre><code>SELECT A.* FROM TABLEA A
  LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
  WHERE ROW_ID = 123;
  </code></pre>
  
  <p>Partition Pruning <strong>works</strong> when I do left outer join to TableB and use static values for IN clause</p>
  
  <pre><code>SELECT A.* FROM TABLEA A
  LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
  WHERE ROW_ID IN (123, 345);
  </code></pre>
  
  <p>Can someone explain me why left outer join will cause partition pruning to fail, when i query on column that table is partitioned on using IN clause with result from subquery? </p>