title: Hive: LEFT JOIN vs JOIN gives different results with filter in ON clause
body: <p>Suppose two tables:</p>
  
  <pre><code>    table1.c1   table1.c2
  1   1           A
  2   1           B
  3   1           C
  4   2           A
  5   2           B
  </code></pre>
  
  <p>and </p>
  
  <pre><code>    table2.c1   table2.c2
  1   2           A
  2   2           D
  3   3           A
  4   3           B
  </code></pre>
  
  <p>When I do:</p>
  
  <pre><code>select distinct t1.c1, t2.c2
  from
  schema.table1 t1
  join
  schema.table2 t2
  on (t1.c2 = t2.c2 
      and t1.c1 = t2.c1
      and t1.c1 = 2)
  </code></pre>
  
  <p>in Hive, I get:</p>
  
  <pre><code>    t1.c1   t2.c2
  1   2   A
  </code></pre>
  
  <p>This is the expected result, no problem. But, when I do:</p>
  
  <pre><code>select distinct t1.c1, t2.c2
  from
  schema.table1 t1
  left join
  schema.table2 t2
  on (t1.c2 = t2.c2 
      and t1.c1 = t2.c1
      and t1.c1 = 2)
  </code></pre>
  
  <p>I get: </p>
  
  <pre><code>    t1.c1   t2.c2
  1   1       NULL
  2   2       NULL
  3   2       A
  </code></pre>
  
  <p>So, filter in ON clause seems not to work like I had expected. The filters <code>t1.c1 = t2.c1</code> and <code>t1.c1 = 2</code> hasn't been applied when, in the LEFT JOIN, it doesn't find the key on the second table so <code>t2.c2</code> is <code>NULL</code>.</p>
  
  <p>I suppose that the answer must be in <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins" rel="nofollow">doc</a> (May be in the 'Joins occur BEFORE WHERE CLAUSES' section?) But still I don't understand the difference.</p>
  
  <p>How is the process to give different results? </p>