title: Hive LATERAL VIEW and WHERE Clause using Sub query
body: <p>I'm looking for a way to optimize my query.</p>
  
  <p>We have a table with events called <code>lea</code>, with a column <code>app_properties</code>, which are tags, stored as a comma separated string.</p>
  
  <p>I would like to select all the events that match the result of a query that select the desired tags.</p>
  
  <p><strong>My first try:</strong></p>
  
  <pre><code>SELECT uuid, app_properties, tag
  FROM events
  LATERAL VIEW explode(split(app_properties, '(, |,)')) tag_table AS tag
  WHERE tag IN (SELECT source_value FROM mapping WHERE indicator = 'Bandwidth Usage')
  </code></pre>
  
  <p>But Hive will not allow this...</p>
  
  <p><code>FAILED: SemanticException [Error 10249]: Line 4:6 Unsupported SubQuery Expression 'tag': Correlating expression cannot contain unqualified column references.</code></p>
  
  <p>Gave it another try by replacing <code>WHERE tag IN</code> by <code>WHERE tag_table.tag IN</code> but not luck...</p>
  
  <p><code>FAILED: SemanticException Line 4:6 Invalid table alias tag_table' in definition of SubQuery sq_1 [tag_table.tag IN (SELECT source_value FROM mapping WHERE indicator = 'Bandwidth Usage')] used as sq_1 at Line 4:20</code>.</p>
  
  <p>In the end... The query below gives the desired result, but I've a feeling that this is not the most optimized way of solving this use case. Has anyone ran into the same use case where you need the select from a <code>LATERAL VIEW</code> using a <code>Sub query</code>?</p>
  
  <pre><code>SELECT to_date(substring(events.time, 0, 10)) as date, t2.code, t2.indicator, count(1) as total
  FROM events
  LEFT JOIN (
      SELECT distinct t.uuid, im.code, im.indicator
      FROM mapping im
      RIGHT JOIN (
        SELECT tag, uuid
        FROM events
        LATERAL VIEW explode(split(app_properties, '(, |,)')) tag_table AS tag
        ) t
      ON im.source_value = t.tag AND im.indicator = 'Bandwidth Usage'
      WHERE im.source_value IS NOT NULL
  ) t2 ON (events.uuid = t2.uuid)
  WHERE t2.code IS NOT NULL
  GROUP BY to_date(substring(events.time, 0, 10)), t2.code, t2.indicator;
  </code></pre>