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

Incorrect sort order for DESC primary key for NULLS LAST/NULLS FIRST

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 4.8.0
    • 4.9.0, 4.8.2
    • None
    • None

    Description

      This problem can be reproduced as following:

       
             CREATE TABLE  DESC_TEST (
      	  ORGANIZATION_ID VARCHAR,
      	  CONTAINER_ID VARCHAR,
      	  ENTITY_ID VARCHAR NOT NULL,
      	  CONSTRAINT TEST_PK PRIMARY KEY ( 
      			ORGANIZATION_ID DESC,
      			CONTAINER_ID DESC,
      			ENTITY_ID
      			))
      
            UPSERT INTO DESC_TEST VALUES ('a',null,'11')
            UPSERT INTO DESC_TEST VALUES (null,'2','22')
            UPSERT INTO DESC_TEST VALUES ('c','3','33')
      

      For the following sql:

            SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESC_TEST  order by CONTAINER_ID ASC NULLS LAST
      

      the expecting result is:

           2,   null 
           3,    c   
          null,  a
      

      but the actual result is:

            null,  a     
            2,   null 
            3,    c     
      

      By debuging the source code,I found the ScanPlan passes the OrderByExpression to both the ScanRegionObserver and MergeSortTopNResultIterator in line 100 and line 232,but the OrderByExpression 's "isNullsLast" property is false, while the sql is "order by CONTAINER_ID ASC NULLS LAST", the "isNullsLast" property should be true.

       90    private ScanPlan(StatementContext context, FilterableStatement statement, TableRef table, RowProjector projector, Integer limit, Integer offset, OrderBy orderBy, ParallelIteratorFactory parallelIteratorFactory, boolean allowPageFilter, Expression dynamicFilter) throws SQLException {
           ......   
      95      boolean isOrdered = !orderBy.getOrderByExpressions().isEmpty();
      96     if (isOrdered) { // TopN
      97           int thresholdBytes = context.getConnection().getQueryServices().getProps().getInt(
      98                   QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB, QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES);    
      99           ScanRegionObserver.serializeIntoScan(context.getScan(), thresholdBytes, 
      100                  limit == null ? -1 : QueryUtil.getOffsetLimit(limit, offset),  orderBy.getOrderByExpressions(),
      101                  projector.getEstimatedRowByteSize());
      102   }
      
      ......
      
      231        } else if (isOrdered) {
      232            scanner = new MergeSortTopNResultIterator(iterators, limit, offset, orderBy.getOrderByExpressions());
      
      

      so the problem is caused by the OrderByCompiler, in line 144, it should not negative the "isNullsLast",because the "isNullsLast" should not be influenced by the SortOrder,no matter it is DESC or ASC:

      142              if (expression.getSortOrder() == SortOrder.DESC) {
      143                     isAscending = !isAscending;
      144                     isNullsLast = !isNullsLast;
      145             }
      

      I include more IT test cases in my patch.

      Attachments

        1. PHOENIX-3469_v2.patch
          37 kB
          chenglei
        2. PHOENIX-3469_v3.patch
          38 kB
          James R. Taylor

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: