title: SQL / PostgreSQL left join ignores "on = constant" predicate, on left table
body: <p>An SQL <em>left join</em> query ignores an <code>ON column = constant</code> predicate on the <em>left</em> table.<br>
  However, it cares about another <code>ON column = constant</code> predicate on the <em>right</em> table.</p>
  
  <p>If I move the left table's <code>ON column = constant</code> to the WHERE clause instead, the query works as intended.</p>
  
  <p>Why does it matter, for the left table, if I place the <code>column = constant</code> in the WHERE part of the query, or in the JOIN ... ON part of the query?</p>
  
  <p>(What happens, is that the left table <code>ON column = constant</code> condition gets pushed up to a "JOIN Filter" step, where it is seemingly being ignored.)</p>
  
  <p><strong>Details:</strong></p>
  
  <pre><code>EXPLAIN ANALYZE
  select * from DW1_PAGE_PATHS t left join DW1_PAGES g
     on t.TENANT = g.TENANT
    and t.PAGE_ID = g.GUID
    and g.GUID = 'abcdefg'  -- works
    and t.CANONICAL = 'C'  -- "ignored", unless moved to `where` clause
  where t.TENANT = '72'
    and PARENT_FOLDER like '/%';
  </code></pre>
  
  <p>Here (below) is the exec plan. Note that <code>t.CANONICAL = 'C'</code> has been pushed up to the "JOIN Filter" step, whereas the <code>g.GUID = 'abcdefg'</code> filter happens directly when the right table is being scanned.</p>
  
  <pre><code> Nested Loop Left Join  (cost=... actual time=...)
     Join Filter: (((t.canonical)::text = 'C'::text)
               AND ((t.tenant)::text = (g.tenant)::text)
               AND ((t.page_id)::text = (g.guid)::text))
     -&gt;  Seq Scan on dw1_page_paths t
           Filter: (((parent_folder)::text ~~ '/%'::text)
                AND ((tenant)::text = '72'::text))
     -&gt;  Seq Scan on dw1_pages g
           Filter: (((tenant)::text = '72'::text)
                AND ((guid)::text = 'abcdefg'::text))
  </code></pre>
  
  <p>(Another question: Why won't the "Join Filter" with <em>t.canonical = 'C'</em> filter out rows for which <em>canonical</em> is not 'C'?  It does not.)</p>
  
  <p>(PostgreSQL version <code>psql (9.1.6, server 9.1.1)</code>.)</p>
  
  <p>Here is a link to a similar query, but the answers don't explain <em>why</em> it works if you move the left table <code>ON column = constant</code> to a <code>where</code> clause instead: 
  <a href="https://stackoverflow.com/questions/7147943/add-condition-while-using-left-outer-join">Add condition while using LEFT OUTER JOIN</a></p>