[HIVE-27649] Support ORDER BY clause in subqueries with set operators

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.2, 4.0.0
• Fix Version/s: 4.0.0
• Component/s: Parser
• Labels: pull-request-available
• Assignee: Nicolas Richard
• Reporter: Nicolas Richard
• Created: 24/Aug/23 18:40
• Updated: 29/Mar/24 17:49
• Resolved: 29/Sep/23 15:50

Description

Consider the following query:

select key from ((select key from src order by key) union (select key from src))subq 

Up until 3.1.2, Hive would parse this query without any problems. However, if you try it on the latest versions, you'll get the following exception:

org.apache.hadoop.hive.ql.parse.ParseException: line 1:60 cannot recognize input near 'union' '(' 'select' in subquery source
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:125)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:97) 

With the inner exception stack trace being:

NoViableAltException(367@[])
    at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:14006)
    at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:45086)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.subQuerySource(HiveParser_FromClauseParser.java:5411)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.atomjoinSource(HiveParser_FromClauseParser.java:1921)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:2175)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.atomjoinSource(HiveParser_FromClauseParser.java:2110)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:2175)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:1750)
    at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1593)
    at org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:45094)
    at org.apache.hadoop.hive.ql.parse.HiveParser.atomSelectStatement(HiveParser.java:38538)
    at org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:38831)
    at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:38424)
    at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:37686)
    at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:37574)
    at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2757)
    at org.apache.hadoop.hive.ql.parse.HiveParser.explainStatement(HiveParser.java:1751)
    at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1614)
    at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:123)
    at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:97) 

Note that this behavior also happens if the subquery contains a SORT BY, CLUSTER BY, DISTRIBUTE BY or LIMIT clause.

 

Issue Links

• is caused by: HIVE-21980 Parsing time can be high in case of deeply nested subqueries
• links to: GitHub Pull Request #4628