[
https://issues.apache.org/jira/browse/HIVE-21802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16851713#comment-16851713
]
Oleksiy Sayankin commented on HIVE-21802:
-----------------------------------------
I have attached sql-2003-2.bnf, file which contains [Backus-Naur
Form|https://en.wikipedia.org/wiki/Backus%E2%80%93Naur_form] (BNF) for
[SQL:2003|https://en.wikipedia.org/wiki/SQL:2003] (ISO/IEC 9075-2:2003 -
Database Languages - SQL - Part 2: Foundation (SQL/Foundation)) where window
functions where introduced. I downloaded it from
[here|https://github.com/ronsavage/SQL].
According to the ISO/IEC 9075-2:2003:
{code}
<table expression> ::=
<from clause>
[ <where clause> ]
[ <group by clause> ]
[ <having clause> ]
[ <window clause> ]
{code}
{code}
<window clause> ::= WINDOW <window definition list>
{code}
{code}
<window definition list> ::= <window definition> [ { <comma> <window
definition> }... ]
{code}
{code}
<window definition> ::= <new window name> AS <window specification>
{code}
{code}
<window specification> ::= <left paren> <window specification details> <right
paren>
{code}
{code}
<window specification details> ::=
[ <existing window name> ] [ <window partition clause> ] [
<window order clause> ] [ <window frame clause> ]
{code}
{code}
<window order clause> ::= ORDER BY <sort specification list>
{code}
you can clearly see that key word {{ORDER BY}} goes _after_ {{WINDOW}}
(current behaviour of Hive). So I will resolve the issue as "wont do". Guys
sorry for disturbance.
> Add support of multilpe ORDER BY clause position
> ------------------------------------------------
>
> 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, HIVE-21802.2.patch, sql-2003-2.bnf
>
>
> *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)
