[ https://issues.apache.org/jira/browse/PHOENIX-4820?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
chenglei updated PHOENIX-4820: ------------------------------ Description: Given a table {code} create table test ( pk1 varchar not null , pk2 varchar not null, pk3 varchar not null, v1 varchar, v2 varchar, CONSTRAINT TEST_PK PRIMARY KEY ( pk1, pk2, pk3 )) {code} for following sql : {code} select a.ak3 from (select substr(pk1,1,1) ak1,substr(pk2,1,1) ak2,substr(pk3,1,1) ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from test order by pk2,pk3 limit 10) a group by a.ak3,a.av1 order by a.ak3,a.av1 {code} Intuitively, the above OrderBy statement {{order by a.ak3,a.av1}} should be compiled out because it match the group by statement, but in fact it is not. The problem is caused by the {{QueryCompiler.compileSingleQuery}} and {{QueryCompiler.compileSingleFlatQuery}},for {{QueryCompiler.compileSingleQuery}} method,because the inner query has order by, so in line 520, local variable {{isInRowKeyOrder}} is false: {code} 519 context.setCurrentTable(tableRef); 520 boolean isInRowKeyOrder = innerPlan.getGroupBy() == GroupBy.EMPTY_GROUP_BY && innerPlan.getOrderBy() == OrderBy.EMPTY_ORDER_BY; {code} In {{QueryCompiler.compileSingleFlatQuery}},when {{OrderByCompiler.compile}} method is invoked, the last parameter {{isInRowKeyOrder}} is false: {code} 562 OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit, offset, projector, 563 groupBy == GroupBy.EMPTY_GROUP_BY ? innerPlanTupleProjector : null, isInRowKeyOrder); {code} So in following line 156 for {{OrderByCompiler.compile}},even though the {{tracker.isOrderPreserving}} is true, the OrderBy statement could not be compiled out. {code} 156 if (isInRowKeyOrder && tracker.isOrderPreserving()) { {code} In my opinion, with GroupBy, in following line 563 for {{QueryCompiler.compileSingleFlatQuery}} method, when we call {{OrderByCompiler.compile}} method, we no need to conside the {{isInRowKeyOrder}}, just like the previous parameter {{tupleProjector}} does. {code} 562 OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit, offset, projector, 563 groupBy == GroupBy.EMPTY_GROUP_BY ? innerPlanTupleProjector : null, isInRowKeyOrder); {code} was: Given a table {code} create table test ( pk1 varchar not null , pk2 varchar not null, pk3 varchar not null, v1 varchar, v2 varchar, CONSTRAINT TEST_PK PRIMARY KEY ( pk1, pk2, pk3 )) {code} for following sql : {code} select a.ak3 from (select substr(pk1,1,1) ak1,substr(pk2,1,1) ak2,substr(pk3,1,1) ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from test order by pk2,pk3 limit 10) a group by a.ak3,a.av1 order by a.ak3,a.av1 {code} Intuitively, the above OrderBy statement {{order by a.ak3,a.av1}} should be compiled out because it match the group by statement, but in fact it is not. The problem is caused by the {{QueryCompiler.compileSingleQuery}} and {{QueryCompiler.compileSingleFlatQuery}},for {{QueryCompiler.compileSingleQuery}} method,because the inner query has order by, so in line 520, local variable {{isInRowKeyOrder}} is false: {code} 519 context.setCurrentTable(tableRef); 520 boolean isInRowKeyOrder = innerPlan.getGroupBy() == GroupBy.EMPTY_GROUP_BY && innerPlan.getOrderBy() == OrderBy.EMPTY_ORDER_BY; {code} In {{QueryCompiler.compileSingleFlatQuery}},when {{OrderByCompiler.compile}} method is invoked, the last parameter {{isInRowKeyOrder}} is false: {code} 562 OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit, offset, projector, 563 groupBy == GroupBy.EMPTY_GROUP_BY ? innerPlanTupleProjector : null, isInRowKeyOrder); {code} So in following line 156 for {{OrderByCompiler.compile}},even though the {{tracker.isOrderPreserving}} is true, the OrderBy statement could not be compiled out. {code} 156 if (isInRowKeyOrder && tracker.isOrderPreserving()) { {code} In my opinion, with GroupBy, In following line 563 for {{QueryCompiler.compileSingleFlatQuery}} method, when we call {{OrderByCompiler.compile}} method, we no need to conside the {{isInRowKeyOrder}}, just like the previous parameter {{tupleProjector}} does. {code} 562 OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit, offset, projector, 563 groupBy == GroupBy.EMPTY_GROUP_BY ? innerPlanTupleProjector : null, isInRowKeyOrder); {code} > Optimize OrderBy for ClientAggregatePlan > ---------------------------------------- > > Key: PHOENIX-4820 > URL: https://issues.apache.org/jira/browse/PHOENIX-4820 > Project: Phoenix > Issue Type: Improvement > Affects Versions: 4.14.0 > Reporter: chenglei > Priority: Major > > Given a table > {code} > create table test ( > pk1 varchar not null , > pk2 varchar not null, > pk3 varchar not null, > v1 varchar, > v2 varchar, > CONSTRAINT TEST_PK PRIMARY KEY ( > pk1, > pk2, > pk3 )) > {code} > for following sql : > {code} > select a.ak3 > from (select substr(pk1,1,1) ak1,substr(pk2,1,1) ak2,substr(pk3,1,1) > ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from test order by pk2,pk3 limit > 10) a group by a.ak3,a.av1 order by a.ak3,a.av1 > {code} > Intuitively, the above OrderBy statement {{order by a.ak3,a.av1}} should be > compiled out because it match the group by statement, but in fact it is not. > The problem is caused by the {{QueryCompiler.compileSingleQuery}} and > {{QueryCompiler.compileSingleFlatQuery}},for > {{QueryCompiler.compileSingleQuery}} method,because the inner query has order > by, so in line 520, local variable {{isInRowKeyOrder}} is false: > {code} > 519 context.setCurrentTable(tableRef); > 520 boolean isInRowKeyOrder = innerPlan.getGroupBy() == > GroupBy.EMPTY_GROUP_BY && innerPlan.getOrderBy() == OrderBy.EMPTY_ORDER_BY; > {code} > In {{QueryCompiler.compileSingleFlatQuery}},when {{OrderByCompiler.compile}} > method is invoked, the last parameter {{isInRowKeyOrder}} is false: > {code} > 562 OrderBy orderBy = OrderByCompiler.compile(context, select, > groupBy, limit, offset, projector, > 563 groupBy == GroupBy.EMPTY_GROUP_BY ? > innerPlanTupleProjector : null, isInRowKeyOrder); > {code} > So in following line 156 for {{OrderByCompiler.compile}},even though the > {{tracker.isOrderPreserving}} is true, the OrderBy statement could not be > compiled out. > {code} > 156 if (isInRowKeyOrder && tracker.isOrderPreserving()) { > {code} > In my opinion, with GroupBy, in following line 563 for > {{QueryCompiler.compileSingleFlatQuery}} method, when we call > {{OrderByCompiler.compile}} method, we no need to conside the > {{isInRowKeyOrder}}, just like the previous parameter {{tupleProjector}} > does. > {code} > 562 OrderBy orderBy = OrderByCompiler.compile(context, select, > groupBy, limit, offset, projector, > 563 groupBy == GroupBy.EMPTY_GROUP_BY ? > innerPlanTupleProjector : null, isInRowKeyOrder); > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)