title: Hive does not MapJoin a small table against the Union of two large tables
body: <p>I have 3 small tables: <code>smallTable0</code>, <code>smallTable1</code>, and <code>smallTable3</code>. They all have fewer than 100 rows and identical schemas. I also have 3 large tables: <code>largeTable0</code>, <code>largeTable1</code>, and <code>largeTable3</code>. The all have more than 1M rows, have identical schemas, share the <code>id</code> column with the small tables, are are partitioned on something other than <code>id</code> (in case partitioning matters, I suspect it does not).</p>
  
  <p>After setting <code>hive.auto.convert.join=true</code>, the following cases result in a MapJoin, as expected:</p>
  
  <ul>
  <li>Joining <code>smallTable0</code> against <code>smallTable1</code></li>
  <li>Joining <code>smallTable0</code> against <code>largeTable0</code></li>
  <li>Joining <code>smallTable0</code> against the <code>smallTable1 UNION ALL smallTable2</code></li>
  </ul>
  
  <p>The following cases do not result in a MapJoin, as expected:</p>
  
  <ul>
  <li>Joining <code>largeTable0</code> against anything.</li>
  <li>Joining <code>smallTable0</code> against anything with <code>hive.auto.convert.join=false</code></li>
  </ul>
  
  <p>Unexpectedly however, the following case also does not result in a MapJoin:</p>
  
  <ul>
  <li>Joining <code>smallTable0</code> against <code>largeTable0 UNION ALL largeTable1</code></li>
  </ul>
  
  <p>The exact query is as follows:</p>
  
  <pre><code>SELECT * FROM smallTable0 s
  JOIN (
    SELECT * FROM (
      SELECT * FROM largeTable0
      UNION ALL
      SELECT * FROM largeTable1 
    ) x
  ) l
    ON s.id = l.id;
  </code></pre>
  
  <p>It runs fine, but with a Common Join instead of a MapJoin and it is causing a performance hit. Creating a view which represents <code>largeTable0 UNION ALL largeTable1</code> does not resolve the issue. I'm sure creating a table which is <code>largetTable0 UNION ALL largeTable1</code> would resolve the issue, but duplicating so much data and then keeping them in sync is undesirable.</p>
  
  <p>The source code for the Union operator (<a href="https://github.com/apache/hive/blob/398c9c6737b905cf1ae64b9bc52b8f919879bd82/ql/src/java/org/apache/hadoop/hive/ql/exec/UnionOperator.java" rel="nofollow">here</a>) has a comment which I find somewhat cryptic.</p>
  
  <pre><code>/**
  * Union operators are not allowed either before or after a explicit mapjoin hint.
  * Note that, the same query would just work without the mapjoin hint (by setting
  * hive.auto.convert.join to true).
  **/
  @Override
  public boolean opAllowedBeforeMapJoin() {
    return false;
  }
  
  @Override
  public boolean opAllowedAfterMapJoin() {
    return false;
  }
  </code></pre>
  
  <p>It seems to suggest that the UNION operator is not allowed with an explicit MapJoin hint, but that the UNION operator is allowed with MapJoins initiated as a result of <code>hive.auto.convert.join</code>. However I do not understand why one would be allowed the other disallowed. Unless "just work" means that the query will "work", just not with a MapJoin. If this were the case, however, I would have expected joining <code>smallTable0</code> to <code>smallTable1 UNION ALL smallTable2</code> to result in a Common Join. </p>
  
  <p>Is the odd behavior a result of a bug in Hive, a bug in my code, a missing feature in Hive, or a misunderstanding on my part?</p>