[
https://issues.apache.org/jira/browse/PHOENIX-3491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15676508#comment-15676508
]
chenglei edited comment on PHOENIX-3491 at 11/18/16 12:07 PM:
--------------------------------------------------------------
The OrderBy could not be compiled out because the following code in
OrderPreservingTracker :
{code:borderStyle=solid}
123 /*
124 * When a GROUP BY is not order preserving, we
cannot do a reverse
125 * scan to eliminate the ORDER BY since our
server-side scan is not
126 * ordered in that case.
127 */
128 if (!groupBy.isEmpty() &&
!groupBy.isOrderPreserving()) {
129 isOrderPreserving = false;
130 isReverse = false;
131 return;
132 }
{code}
The above code and its comment seems very strange, in fact,if GroupBy is not
orderPreserving, AggregatePlan would sort the aggregated Keys after geting
results from RegionServer at the client side, but AggregatePlan always uses ASC
order to sort the aggregated Keys, just as the following code,so if we just
remove above code, the query result will be error when OrderBy is
OrderBy.REV_ROW_KEY_ORDER_BY :
{code:borderStyle=solid}
137 public PeekingResultIterator newIterator(StatementContext context,
ResultIterator scanner, Scan scan, String tableName, QueryPlan plan) throws
SQLException {
138 Expression expression = RowKeyExpression.INSTANCE;
139 OrderByExpression orderByExpression = new
OrderByExpression(expression, false, true);
140 int threshold =
services.getProps().getInt(QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB,
QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES);
141 return new OrderedResultIterator(scanner,
Collections.<OrderByExpression>singletonList(orderByExpression), threshold);
142 }
{code}
Therefore,we can modify the AggregatePlan to make it can sort the aggregated
Keys ASC or DESC:
{code:borderStyle=solid}
public PeekingResultIterator newIterator(StatementContext context,
ResultIterator scanner, Scan scan, String tableName, QueryPlan plan) throws
SQLException {
Expression expression = RowKeyExpression.INSTANCE;
boolean isNullsLast=false;
boolean isAscending=true;
if(this.orderBy==OrderBy.REV_ROW_KEY_ORDER_BY) {
isNullsLast=true; //which is needed for the whole rowKey.
isAscending=false;
}
OrderByExpression orderByExpression = new
OrderByExpression(expression, isNullsLast, isAscending);
int threshold =
services.getProps().getInt(QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB,
QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES);
return new OrderedResultIterator(scanner,
Collections.<OrderByExpression>singletonList(orderByExpression), threshold);
}
{code}
After modifying AggregatePlan ,It seems we can remove the code in
OrderPreservingTracker,
I include a lot of test cases in my patch to prove it, such as ASC/DESC, Salted
Table and NULLS FIRST/LAST.
was (Author: comnetwork):
The OrderBy could not be compiled out because the following code in
OrderPreservingTracker :
{code:borderStyle=solid}
123 /*
124 * When a GROUP BY is not order preserving, we
cannot do a reverse
125 * scan to eliminate the ORDER BY since our
server-side scan is not
126 * ordered in that case.
127 */
128 if (!groupBy.isEmpty() &&
!groupBy.isOrderPreserving()) {
129 isOrderPreserving = false;
130 isReverse = false;
131 return;
132 }
{code}
The above code and its comment seems very strange, in fact,if GroupBy is not
orderPreserving, AggregatePlan would sort the aggregated Keys after geting
results from RegionServer at the client side, but AggregatePlan always uses ASC
order to sort the aggregated Keys, just as the following code,so if we just
remove above code, the query result will be error when OrderBy is
OrderBy.REV_ROW_KEY_ORDER_BY :
{code:borderStyle=solid}
137 public PeekingResultIterator newIterator(StatementContext context,
ResultIterator scanner, Scan scan, String tableName, QueryPlan plan) throws
SQLException {
138 Expression expression = RowKeyExpression.INSTANCE;
139 OrderByExpression orderByExpression = new
OrderByExpression(expression, false, true);
140 int threshold =
services.getProps().getInt(QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB,
QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES);
141 return new OrderedResultIterator(scanner,
Collections.<OrderByExpression>singletonList(orderByExpression), threshold);
142 }
{code}
Therefore,we can modify the AggregatePlan to make it can sort the aggregated
Keys ASC or DESC:
{code:borderStyle=solid}
public PeekingResultIterator newIterator(StatementContext context,
ResultIterator scanner, Scan scan, String tableName, QueryPlan plan) throws
SQLException {
Expression expression = RowKeyExpression.INSTANCE;
boolean isNullsLast=false;
boolean isAscending=true;
if(this.orderBy==OrderBy.REV_ROW_KEY_ORDER_BY) {
isNullsLast=true; //which is needed for the whole rowKey.
isAscending=false;
}
OrderByExpression orderByExpression = new
OrderByExpression(expression, isNullsLast, isAscending);
int threshold =
services.getProps().getInt(QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB,
QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES);
return new OrderedResultIterator(scanner,
Collections.<OrderByExpression>singletonList(orderByExpression), threshold);
}
{code}
After modifying AggregatePlan ,It seems we can remove the code in
OrderPreservingTracker,
I include a lot of test cases to prove it, such as ASC/DESC, Salted Table and
NULLS FIRST/LAST.
> OrderBy should be compiled out when GroupBy is not orderPreserving but
> OrderBy is reverse
> -----------------------------------------------------------------------------------------
>
> Key: PHOENIX-3491
> URL: https://issues.apache.org/jira/browse/PHOENIX-3491
> Project: Phoenix
> Issue Type: Improvement
> Affects Versions: 4.8.0
> Reporter: chenglei
> Assignee: chenglei
> Attachments: PHOENIX-3491_v1.patch
>
>
> for the following table:
> {code:borderStyle=solid}
> CREATE TABLE ORDERBY_TEST (
> ORGANIZATION_ID INTEGER NOT NULL,
> CONTAINER_ID INTEGER NOT NULL,
> SCORE INTEGER NOT NULL,
> ENTITY_ID INTEGER NOT NULL,
> CONSTRAINT TEST_PK PRIMARY KEY (
> ORGANIZATION_ID,
> CONTAINER_ID,
> SCORE,
> ENTITY_ID
> ));
> {code}
>
> If we execute explain on the following sql:
> {code:borderStyle=solid}
> SELECT ORGANIZATION_ID,SCORE FROM ORDERBY_TEST GROUP BY ORGANIZATION_ID,
> SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC
> {code}
> the result is :
> {code:borderStyle=solid}
> ----------------------------------------------------------------------+
> | PLAN |
> +----------------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER ORDERBY_TEST |
> | SERVER FILTER BY FIRST KEY ONLY |
> | SERVER AGGREGATE INTO DISTINCT ROWS BY [ORGANIZATION_ID, SCORE] |
> | CLIENT MERGE SORT |
> | CLIENT SORTED BY [ORGANIZATION_ID DESC, SCORE DESC] |
> +----------------------------------------------------------------------+
> {code}
> from the above explain result, we can see that the ORDER BY ORGANIZATION_ID
> DESC, SCORE DESC is not compiled out,but obviously it should be compiled out
> as OrderBy.REV_ROW_KEY_ORDER_BY.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
