title: HIVE SubQuery expression refers to both Parent and SubQuery expressions
body: <p>So I have this HIVE Query:</p>
  
  <pre><code>SELECT p.id 
  FROM posts p JOIN
  comments c ON c.postid = p.id JOIN
  users u ON u.id = p.owneruserid JOIN
  badges b ON b.userid = u.id CROSS JOIN
  postlinks l 
  WHERE l.relatedpostid &gt; p.id
  AND u.upvotes IN (SELECT MAX(upvotes) AS uv FROM users uu WHERE uu.creationdate &gt; p.creationdate);
  </code></pre>
  
  <p>and it gives me this Error:</p>
  
  <blockquote>
    <p>Error while compiling statement: FAILED: SemanticException [Error
    10249]: line 21:58 Unsupported SubQuery Expression 'creationdate':
    SubQuery expression refers to both Parent and SubQuery expressions and
    is not a valid join condition.</p>
  </blockquote>
  
  <p>But I dont see how my SubQuery expression refers to both parent and SubQuery?!?</p>
  
  <p>I specifically use <code>user uu</code> to distinguish from <code>user u</code> from the parent query, and p.creationdate <strong>has to be</strong> related to the parent query in my case.</p>
  
  <p>As stated in this <a href="https://stackoverflow.com/questions/34920692/nested-subquery-not-supported-in-hive">post</a> already
  HIVE comes with some restrictions when it comes to Subqueries:</p>
  
  <ol>
  <li>These subqueries are only supported on the right-hand side of an expression.</li>
  <li>IN/NOT IN subqueries may only select a single column.</li>
  <li>EXISTS/NOT EXISTS must have one or more correlated predicates.</li>
  <li>References to the parent query are only supported in the WHERE clause of the subquery.</li>
  </ol>
  
  <p>But I dont see how I am breaking any of these restrictions.</p>
  
  <p>What am I doing wrong?</p>
  
  <p>Any help is appreciated.</p>
  
  <p>EDIT: </p>
  
  <p>The data used, is an excerpt of stackexchange data. 
  So post referrs to a stackexchange post etc..</p>
  
  <p>Tables: </p>
  
  <p><strong>badges</strong>(id,class,date,name,tagbased,userid)</p>
  
  <p><strong>comments</strong>(id,creationdate,postid,score,text,userdisplayname,userid)</p>
  
  <p><strong>postlinks</strong>(id,creationdate,linktypeid,postid,relatedpostid)</p>
  
  <p><strong>posts</strong>(id,acceptedanswerid,answercount,body,closeddate,commentcount,communityowneddate,creationdate,favoritecount,lastactivitydate,lasteditdate,lasteditordisplayname,lasteditoruserid,ownerdisplayname,owneruserid,parentid,posttypeid,score,tags,title,viewcount)</p>
  
  <p><strong>tags</strong>(id,count,excerptpostid,tagname,wikipostid)</p>
  
  <p><strong>users</strong>(id,aboutme,accountid,creationdate,displayname,downvotes,lastaccessdate,location,profileimageurl,reputation,upvotes,views,websiteurl)</p>
  
  <p><strong>votes</strong>(id,bountyamount,creationdate,postid,userid,votetypeid)</p>
  
  <p>I am mainly interested in how I could write </p>
  
  <pre><code>SELECT p.id FROM posts p JOIN users u ON u.id = p.owneruserid
  WHERE u.upvotes IN 
  (SELECT MAX(upvotes) FROM users uu WHERE uu.creationdate &gt; p.creationdate) 
  </code></pre>
  
  <p>so that it does not give me this error.</p>