Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-21802

Add support of multilpe ORDER BY clause position

    XMLWordPrintableJSON

    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.

        Attachments

        1. HIVE-21802.1.patch
          1.0 kB
          Oleksiy Sayankin
        2. HIVE-21802.2.patch
          12 kB
          Oleksiy Sayankin
        3. sql-2003-2.bnf
          195 kB
          Oleksiy Sayankin

          Issue Links

            Activity

              People

              • Assignee:
                osayankin Oleksiy Sayankin
                Reporter:
                osayankin Oleksiy Sayankin
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: