Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 8DC45200BCB for ; Thu, 10 Nov 2016 06:39:00 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 8C59A160AFD; Thu, 10 Nov 2016 05:39:00 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id AB6F2160AFA for ; Thu, 10 Nov 2016 06:38:59 +0100 (CET) Received: (qmail 87793 invoked by uid 500); 10 Nov 2016 05:38:58 -0000 Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list dev@phoenix.apache.org Received: (qmail 87766 invoked by uid 99); 10 Nov 2016 05:38:58 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 10 Nov 2016 05:38:58 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 8547B2C0005 for ; Thu, 10 Nov 2016 05:38:58 +0000 (UTC) Date: Thu, 10 Nov 2016 05:38:58 +0000 (UTC) From: "chenglei (JIRA)" To: dev@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (PHOENIX-3469) Once a column in primary key or index is DESC, the corresponding order by NULLS LAST/NULLS FIRST may work incorrectly MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Thu, 10 Nov 2016 05:39:00 -0000 [ https://issues.apache.org/jira/browse/PHOENIX-3469?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] chenglei updated PHOENIX-3469: ------------------------------ Description: 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 in line 100 and line 232,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} was: 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 in line 100 and line 232,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} > 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 in line 100 and line 232,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)