[ https://issues.apache.org/jira/browse/HIVE-18724?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vineet Garg updated HIVE-18724: ------------------------------- Description: Currently Hive doesn't allow correlated subqueries which refer to columns of grand parent query e.g. {code:sql} select t1.a from t t1 where t1.b IN (select t2.c from t t2 where t2.a IN (select max(t3.b) from t t3, t t4 where t3.a=t2.a and t3.b=t1.b)) {code} In this example inner most query has column reference {{t3.b=t1.b}} where {{t1.b}} references outer most query. The error which Hive compiler throw is {noformat} FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression 'a': Nested SubQuery expressions are not supported. {noformat} This is very misleading since Hive does support Nested subqueries. was: Currently Hive doesn't allow correlated subqueries which refer to columns of grand parent query e.g. {code:sql} select t1.a from t t1 where t1.b IN (select t2.c from t t2 where t2.a IN (select max(t3.b) from t t3, t t4 where t3.a=t2.a and t3.b=t1.b)) {code} In this example inner most query has column reference {{t3.b=t1.b}} where {{t1.b}} references outer most query. The error which Hive compiler throw is {noformat} FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression 'a': Nested SubQuery expressions are not supported. {format} This is very misleading since Hive does support Nested subqueries. > Improve error handling for subqueries referencing columns(correlated) of it's > grand-parent query > ------------------------------------------------------------------------------------------------ > > Key: HIVE-18724 > URL: https://issues.apache.org/jira/browse/HIVE-18724 > Project: Hive > Issue Type: Improvement > Components: Query Planning > Reporter: Vineet Garg > Priority: Major > > Currently Hive doesn't allow correlated subqueries which refer to columns of > grand parent query e.g. > {code:sql} > select t1.a from t t1 where t1.b IN (select t2.c from t t2 where t2.a IN > (select max(t3.b) from t t3, t t4 where t3.a=t2.a and t3.b=t1.b)) > {code} > In this example inner most query has column reference {{t3.b=t1.b}} where > {{t1.b}} references outer most query. > The error which Hive compiler throw is > {noformat} > FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression 'a': > Nested SubQuery expressions are not supported. > {noformat} > This is very misleading since Hive does support Nested subqueries. -- This message was sent by Atlassian JIRA (v7.6.3#76005)