title: Subquery containing aggreagate function in Hiveql
body: <p>Why doesn't this work in Hiveql?</p>
  
  <pre><code>create table hist (
     id int,
     state int,
     create_date string);
  
  insert into hist values (1, 1, '2017-01-01');
  insert into hist values (1, 1, '2017-02-01');
  insert into hist values (1, 1, '2017-03-01');
  insert into hist values (1, 2, '2017-02-01');
  insert into hist values (1, 2, '2017-03-01');
  insert into hist values (1, 2, '2017-04-01');
  
  WITH MonthEnd as
  (select to_date(trunc(add_months(current_date, -1), 'MM'))
   MonthEndDate)
  SELECT date_add(MAX(a.create_date), 0)
    FROM hist a, monthend me
   where a.create_date &lt;=
   (select max(b.create_date) from hist b
    where a.id=b.id and b.state=2
      and b.create_date &lt; me.MonthEndDate) x
     and a.state=1 and a.create_date &lt; me.MonthEndDate;
  </code></pre>
  
  <p>I would expect an answer of '2017-03-01', but I get this instead:</p>
  
  <pre><code>Error while compiling statement: FAILED: ParseException line 20:24 cannot recognize input near 'select' 'max' '(' in expression specification
  </code></pre>
  
  <p>Side note:  I'm highlighting the bottom select statement in Hue before executing it.  The select statement is in lines 13-17.  There is other commented code after the select query.  Therefore, I have no idea why it's saying the compilation error is on Line 20.</p>
  
  <p>The only StackOverflow question I found close to this recommended I name the subquery.  That's why I added the 'x' after it.  It didn't help.  Same error.</p>
  
  <p>The subquery by itself works:</p>
  
  <pre><code>WITH MonthEnd as
  (select to_date(trunc(add_months(current_date, -1), 'MM'))
   MonthEndDate)
  select max(b.create_date) from hist b, monthend me
   where b.state=2 and b.create_date &lt; me.MonthEndDate;
  
  2017-04-01
  </code></pre>
  
  <p>Thanks in advance for any insight anyone can give here!</p>