[
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,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,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,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)
