hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zoltan Haindrich (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-21802) Unexpected change in HiveQL clause order
Date Wed, 29 May 2019 12:04:00 GMT

    [ https://issues.apache.org/jira/browse/HIVE-21802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16850783#comment-16850783
] 

Zoltan Haindrich commented on HIVE-21802:
-----------------------------------------

[~osayankin]: Could you please change the issue summary to describe the issue instead of "Unexpected
change in HiveQL clause order"
and please add a testcase to prevent the same thing from happening in the future


> Unexpected change in HiveQL clause order
> ----------------------------------------
>
>                 Key: HIVE-21802
>                 URL: https://issues.apache.org/jira/browse/HIVE-21802
>             Project: Hive
>          Issue Type: Bug
>          Components: Parser, Query Processor
>            Reporter: Oleksiy Sayankin
>            Assignee: Oleksiy Sayankin
>            Priority: Critical
>         Attachments: HIVE-21802.1.patch
>
>
> This query worked in Hive 1.2 ( ({{ORDER}} clause _before_ {{WINDOW}})):
> {code:java}
> CREATE TABLE ccdp_v02 AS 
> SELECT * from 
> (select 
> cust_xref_id, 
> cstone_last_updatetm, 
> instal_late_pay_ct, 
> ROW_NUMBER() over w1 as RN, 
> a.report_dt 
> from cstonedb3.gam_ccdp_us a where report_dt = '2019-05-01' 
> and cust_xref_id in (1234) 
>         order by cust_xref_id, a.report_dt, cstone_last_updatetm desc 
> WINDOW w1 as (partition by a.cust_xref_id, a.report_dt order by a.cstone_last_updatetm
desc) 
> ) tmp where RN=1 DISTRIBUTE BY report_dt; 
> {code}
> In Hive2.1 it fails with:
> {code:java}
> hive> SELECT id
>     > FROM ( 
>     >     SELECT 
>     >         id, 
>     >         a1,  
>     >         ROW_NUMBER() OVER w1 AS RN, 
>     >         b1 
>     >     FROM i a
>     >     ORDER BY id, b1, a1 DESC
>     >     WINDOW w1 as (PARTITION BY id, b1 ORDER BY a1 DESC)
>     > );
> NoViableAltException(257@[])
> 	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.atomjoinSource(HiveParser_FromClauseParser.java:2269)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:2479)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:1692)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1313)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:42092)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.atomSelectStatement(HiveParser.java:36765)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:37017)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:36663)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:35852)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:35740)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2307)
> 	at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1335)
> 	at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:208)
> 	at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:77)
> 	at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:70)
> 	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:468)
> 	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1317)
> 	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1457)
> 	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1237)
> 	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1227)
> 	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:239)
> 	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:187)
> 	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:409)
> 	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:836)
> 	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:774)
> 	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:697)
> 	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:692)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 	at java.lang.reflect.Method.invoke(Method.java:498)
> 	at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
> 	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
> FAILED: ParseException line 3:4 cannot recognize input near '(' 'SELECT' 'id' in joinSource
> hive> 
> {code}
> *STEPS TO REPRODUCE:*
> 1. Create a table:
> {code:java}
> CREATE TABLE i (id INT, a1 INT, b1 BOOLEAN);
> {code}
> 2. Run the query which was working in Hive-1.2: ({{ORDER}} clause _before_ {{WINDOW}})
> {code:java}
> SELECT id
> FROM ( 
>     SELECT 
>         id, 
>         a1,  
>         ROW_NUMBER() OVER w1 AS rn, 
>         b1 
>     FROM i a
>     ORDER BY id, b1, a1 DESC
>     WINDOW w1 as (PARTITION BY id, b1 ORDER BY a1 DESC)
> ) tmp WHERE rn=1 DISTRIBUTE BY id;
> {code}
> *ACTUAL RESULT:*
> The query fails with an exception you can find above.
> The query from Step 2 which works for Hive-2.3 is ( ({{ORDER}} clause _after_ {{WINDOW}})):
> {code:java}
> SELECT id
> FROM ( 
>     SELECT 
>         id, 
>         a1,  
>         ROW_NUMBER() OVER w1 AS rn, 
>         b1 
>     FROM i a
>     WINDOW w1 as (PARTITION BY id, b1 ORDER BY a1 DESC)
>     ORDER BY id, b1, a1 DESC
> ) tmp WHERE rn=1 DISTRIBUTE BY id;
> {code}
>  Hive-2.3 also fails to parse subquery ( ({{ORDER}} clause _before_ {{WINDOW}})):
> {code:java}
>     SELECT 
>         id, 
>         a1,  
>         ROW_NUMBER() OVER w1 AS rn, 
>         b1 
>     FROM i a
>     ORDER BY id, b1, a1 DESC
>     WINDOW w1 as (PARTITION BY id, b1 ORDER BY a1 DESC)
> {code}
> Customer is facing issue regularly this is occurring after upgrade . the workaround given
by us need lot of code changes which will get them back to squire one they need to follow
process and will cost them a lot. 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message