[PHOENIX-3469] Incorrect sort order for DESC primary key for NULLS LAST/NULLS FIRST

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.8.0
• Fix Version/s: 4.9.0, 4.8.2
• Assignee: chenglei
• Reporter: chenglei
• Created: 10/Nov/16 05:30
• Updated: 17/Nov/16 07:15
• Resolved: 17/Nov/16 06:49

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.