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

Add support of multilpe ORDER BY clause position

Log workAgile BoardRank to TopRank to BottomVotersWatch issueWatchersCreate sub-taskConvert to sub-taskMoveLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    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

          Activity

          $i18n.getText('security.level.explanation', $currentSelection) Viewable by All Users
          Cancel

            People

            • Assignee:
              osayankin Oleksiy Sayankin Assign to me
              Reporter:
              osayankin Oleksiy Sayankin

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment