[HIVE-21802] Add support of multilpe ORDER BY clause position - ASF JIRA

Log work

Agile Board

Rank to Top

Rank to Bottom

Bulk Copy Attachments

Bulk Move Attachments

Voters

Watch issue

Watchers

Create sub-task

Convert to sub-task

Move

Link

Clone

Labels

Update Comment Author

Replace String in Comment

Update Comment Visibility

Delete Comments

XML

Word

Printable

JSON

Details

Type: Bug
Status: Resolved
Priority: Critical
Resolution: Won't Fix
Affects Version/s: None
Fix Version/s: None
Component/s: Parser, Query Processor
Labels:
None

Description

STEPS TO REPRODUCE:

1. Create a table:

CREATE TABLE i (id INT, a1 INT, b1 BOOLEAN);

2. Run the query which was working in Hive-1.2: (ORDER clause before WINDOW)

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;

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)):

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;

Hive-2.3 also fails to parse subquery ( (ORDER clause before WINDOW)):

    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)

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.