[jira] [Created] (PHOENIX-3469) Once a column in primary key or index is DESC, the corresponding order by NULLS LAST/NULLS FIRST may work incorrectly

[chenglei (JIRA)]

chenglei created PHOENIX-3469:
---------------------------------

             Summary: Once a column in primary key or index is DESC, the 
corresponding order by  NULLS LAST/NULLS FIRST may work incorrectly
                 Key: PHOENIX-3469
                 URL: https://issues.apache.org/jira/browse/PHOENIX-3469
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.8.0
            Reporter: chenglei


This problem can be reproduced as following:

{code:borderStyle=solid} 
    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')")
{code} 

For the following sql:
{code:borderStyle=solid}
      SELECT CONTAINER_ID,ORGANIZATION_ID FROM DESC_TEST  order by CONTAINER_ID 
ASC NULLS LAST
{code} 
the expecting result is:
{code:borderStyle=solid}
     2,   null 
     3,    c   
    null,  a
{code} 
but the actual result is:
{code:borderStyle=solid}
      null,  a     
      2,   null 
      3,    c     
{code} 

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

{code:borderStyle=solid}
 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());

{code} 

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

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





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)