Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3491

OrderBy can not be compiled out if GroupBy is not orderPreserving and OrderBy is reverse

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.8.0
    • Fix Version/s: 4.9.0
    • Labels:
      None

      Description

      for the following table:

                  CREATE TABLE ORDERBY_TEST ( 
                          ORGANIZATION_ID INTEGER NOT NULL,
                          CONTAINER_ID INTEGER NOT NULL,
                          SCORE INTEGER NOT NULL,
                          ENTITY_ID INTEGER NOT NULL,     
                         CONSTRAINT TEST_PK PRIMARY KEY ( 
                          ORGANIZATION_ID,
                          CONTAINER_ID,
                          SCORE,
                          ENTITY_ID
                          ));
       

      If we execute explain on the following sql:

                 
          SELECT ORGANIZATION_ID,SCORE FROM ORDERBY_TEST  GROUP BY ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC         
      

      the result is :

       
          ----------------------------------------------------------------------+
      |                                 PLAN                                 |
      +----------------------------------------------------------------------+
      | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER ORDERBY_TEST        |
      |     SERVER FILTER BY FIRST KEY ONLY                                  |
      |     SERVER AGGREGATE INTO DISTINCT ROWS BY [ORGANIZATION_ID, SCORE]  |
      | CLIENT MERGE SORT                                                    |
      | CLIENT SORTED BY [ORGANIZATION_ID DESC, SCORE DESC]                  |
      +----------------------------------------------------------------------+
      

      from the above explain result, we can see that the ORDER BY ORGANIZATION_ID DESC, SCORE DESC is not compiled out,but obviously it should be compiled out as OrderBy.REV_ROW_KEY_ORDER_BY.

        Attachments

        1. PHOENIX-3491_v2.patch
          57 kB
          James R. Taylor
        2. PHOENIX-3491_v1.patch
          57 kB
          chenglei

          Activity

            People

            • Assignee:
              comnetwork chenglei
              Reporter:
              comnetwork chenglei
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: