This is an automated email from the ASF dual-hosted git repository.
chenglei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new e2479cc541 PHOENIX-7397 Optimize ClientAggregatePlan/ClientScanPlan
when inner q… (#1966)
e2479cc541 is described below
commit e2479cc54175b11e486845e30fbc258fa15f163f
Author: chenglei <[email protected]>
AuthorDate: Wed Sep 11 17:22:57 2024 +0800
PHOENIX-7397 Optimize ClientAggregatePlan/ClientScanPlan when inner q…
(#1966)
---
.../org/apache/phoenix/compile/CompiledOffset.java | 2 +
.../org/apache/phoenix/compile/OffsetCompiler.java | 4 +-
.../apache/phoenix/compile/OrderByCompiler.java | 18 +-
.../org/apache/phoenix/compile/QueryCompiler.java | 16 +-
.../org/apache/phoenix/compile/UnionCompiler.java | 122 ++++++-
.../phoenix/execute/ClientAggregatePlan.java | 5 +-
.../java/org/apache/phoenix/execute/UnionPlan.java | 86 ++++-
.../phoenix/expression/OrderByExpression.java | 9 +-
.../java/org/apache/phoenix/parse/OrderByNode.java | 13 +
.../org/apache/phoenix/parse/SelectStatement.java | 12 +
.../org/apache/phoenix/util/ExpressionUtil.java | 4 +-
.../org/apache/phoenix/end2end/DerivedTableIT.java | 4 +-
.../phoenix/end2end/SortMergeJoinMoreIT.java | 4 +-
.../org/apache/phoenix/end2end/UnionAllIT.java | 327 ++++++++++++++++++
.../apache/phoenix/compile/QueryCompilerTest.java | 366 ++++++++++++++++++++-
15 files changed, 957 insertions(+), 35 deletions(-)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
index 027265ba64..aa97f0760a 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
@@ -23,6 +23,8 @@ import
org.apache.phoenix.thirdparty.com.google.common.base.Optional;
* CompiledOffset represents the result of the Compiler on the OFFSET clause.
*/
public class CompiledOffset {
+ public static final CompiledOffset EMPTY_COMPILED_OFFSET =
+ new CompiledOffset(Optional.<Integer>absent(),
Optional.<byte[]>absent());
private final Optional<Integer> integerOffset;
private final Optional<byte[]> byteOffset;
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
index ae522ca698..f83aa30d39 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
@@ -80,7 +80,9 @@ public class OffsetCompiler {
public CompiledOffset compile(StatementContext context,
FilterableStatement statement, boolean inJoin, boolean inUnion) throws
SQLException {
OffsetNode offsetNode = statement.getOffset();
- if (offsetNode == null) { return new
CompiledOffset(Optional.<Integer>absent(), Optional.<byte[]>absent()); }
+ if (offsetNode == null) {
+ return CompiledOffset.EMPTY_COMPILED_OFFSET;
+ }
if (offsetNode.isIntegerOffset()) {
OffsetParseNodeVisitor visitor = new
OffsetParseNodeVisitor(context);
offsetNode.getOffsetParseNode().accept(visitor);
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
index 1f8a819ba8..7faf1dbcbd 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
@@ -23,6 +23,7 @@ import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
+import java.util.Objects;
import org.apache.phoenix.compile.GroupByCompiler.GroupBy;
import org.apache.phoenix.compile.OrderPreservingTracker.Ordering;
@@ -32,16 +33,13 @@ import org.apache.phoenix.expression.Expression;
import org.apache.phoenix.expression.OrderByExpression;
import org.apache.phoenix.iterate.OrderedResultIterator;
import org.apache.phoenix.parse.HintNode.Hint;
-import org.apache.phoenix.parse.LiteralParseNode;
import org.apache.phoenix.parse.OrderByNode;
-import org.apache.phoenix.parse.ParseNode;
import org.apache.phoenix.parse.SelectStatement;
import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.query.QueryServicesOptions;
import org.apache.phoenix.schema.PTableType;
import
org.apache.phoenix.schema.RowValueConstructorOffsetNotAllowedInQueryException;
import
org.apache.phoenix.schema.RowValueConstructorOffsetNotCoercibleException;
-import org.apache.phoenix.schema.types.PInteger;
import org.apache.phoenix.thirdparty.com.google.common.collect.ImmutableList;
import org.apache.phoenix.thirdparty.com.google.common.collect.Lists;
@@ -105,6 +103,10 @@ public class OrderByCompiler {
}
return new OrderBy(newOrderByExpressions);
}
+
+ public static boolean equalsForOutputOrderBy(OrderBy orderBy1, OrderBy
orderBy2) {
+ return Objects.equals(orderBy1.orderByExpressions,
orderBy2.orderByExpressions);
+ }
}
/**
* Gets a list of columns in the ORDER BY clause
@@ -150,10 +152,14 @@ public class OrderByCompiler {
LinkedHashSet<OrderByExpression> orderByExpressions =
Sets.newLinkedHashSetWithExpectedSize(orderByNodes.size());
for (OrderByNode node : orderByNodes) {
- ParseNode parseNode = node.getNode();
Expression expression = null;
- if (parseNode instanceof LiteralParseNode &&
((LiteralParseNode)parseNode).getType() == PInteger.INSTANCE){
- Integer index =
(Integer)((LiteralParseNode)parseNode).getValue();
+ if (node.isIntegerLiteral()) {
+ if (rowProjector == null) {
+ throw new IllegalStateException(
+ "rowProjector is null when there is
LiteralParseNode in orderByNodes");
+ }
+ Integer index = node.getValueIfIntegerLiteral();
+ assert index != null;
int size = rowProjector.getColumnProjectors().size();
if (index > size || index <= 0 ) {
throw new
SQLExceptionInfo.Builder(SQLExceptionCode.PARAM_INDEX_OUT_OF_BOUND)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
index 9444241931..0c40b2ca5d 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
@@ -42,7 +42,6 @@ import org.apache.phoenix.compile.JoinCompiler.JoinSpec;
import org.apache.phoenix.compile.JoinCompiler.JoinTable;
import org.apache.phoenix.compile.JoinCompiler.Table;
import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
-import org.apache.phoenix.coprocessorclient.BaseScannerRegionObserverConstants;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.exception.SQLExceptionInfo;
import org.apache.phoenix.execute.AggregatePlan;
@@ -237,10 +236,12 @@ public class QueryCompiler {
QueryPlan subPlan = compileSubquery(subSelect, true);
plans.add(subPlan);
}
+
TableRef tableRef = UnionCompiler.contructSchemaTable(statement, plans,
select.hasWildcard() ? null : select.getSelect());
ColumnResolver resolver = FromCompiler.getResolver(tableRef);
StatementContext context = new StatementContext(statement, resolver,
bindManager, scan, sequenceManager);
+ plans = UnionCompiler.convertToTupleProjectionPlan(plans, tableRef,
context);
QueryPlan plan = compileSingleFlatQuery(
context,
select,
@@ -267,7 +268,7 @@ public class QueryCompiler {
}
public QueryPlan compileSelect(SelectStatement select) throws SQLException{
- StatementContext context = new StatementContext(statement, resolver,
bindManager, scan, sequenceManager);
+ StatementContext context = createStatementContext();
QueryPlan dataPlanForCDC = getExistingDataPlanForCDC();
if (dataPlanForCDC != null) {
TableRef cdcTableRef = dataPlanForCDC.getTableRef();
@@ -298,6 +299,10 @@ public class QueryCompiler {
}
}
+ private StatementContext createStatementContext() {
+ return new StatementContext(statement, resolver, bindManager, scan,
sequenceManager);
+ }
+
/**
* Call compileJoinQuery() for join queries recursively down to the leaf
JoinTable nodes.
* If it is a leaf node, call compileSingleFlatQuery() or
compileSubquery(), otherwise:
@@ -721,6 +726,13 @@ public class QueryCompiler {
}
QueryPlan innerPlan = compileSubquery(innerSelect, false);
+ if (innerPlan instanceof UnionPlan) {
+ UnionCompiler.optimizeUnionOrderByIfPossible(
+ (UnionPlan) innerPlan,
+ select,
+ this::createStatementContext);
+ }
+
RowProjector innerQueryPlanRowProjector = innerPlan.getProjector();
TupleProjector tupleProjector = new
TupleProjector(innerQueryPlanRowProjector);
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/UnionCompiler.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/UnionCompiler.java
index 6277affcda..97597aaf8b 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/UnionCompiler.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/UnionCompiler.java
@@ -23,17 +23,23 @@ import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
+import java.util.function.Supplier;
import org.apache.phoenix.thirdparty.com.google.common.collect.ImmutableList;
import org.apache.hadoop.hbase.HConstants;
+import org.apache.phoenix.compile.GroupByCompiler.GroupBy;
+import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.exception.SQLExceptionInfo;
import org.apache.phoenix.execute.TupleProjectionPlan;
import org.apache.phoenix.execute.TupleProjector;
+import org.apache.phoenix.execute.UnionPlan;
import org.apache.phoenix.expression.CoerceExpression;
import org.apache.phoenix.expression.Expression;
import org.apache.phoenix.jdbc.PhoenixStatement;
import org.apache.phoenix.parse.AliasedNode;
+import org.apache.phoenix.parse.OrderByNode;
+import org.apache.phoenix.parse.SelectStatement;
import org.apache.phoenix.schema.PColumn;
import org.apache.phoenix.schema.PColumnImpl;
import org.apache.phoenix.schema.PName;
@@ -80,12 +86,6 @@ public class UnionCompiler {
public static TableRef contructSchemaTable(PhoenixStatement statement,
List<QueryPlan> plans,
List<AliasedNode> selectNodes) throws SQLException {
List<TargetDataExpression> targetTypes =
checkProjectionNumAndExpressions(plans);
- for (int i = 0; i < plans.size(); i++) {
- QueryPlan subPlan = plans.get(i);
- TupleProjector projector =
getTupleProjector(subPlan.getProjector(), targetTypes);
- subPlan = new TupleProjectionPlan(subPlan, projector, null, null);
- plans.set(i, subPlan);
- }
QueryPlan plan = plans.get(0);
List<PColumn> projectedColumns = new ArrayList<PColumn>();
for (int i = 0; i < plan.getProjector().getColumnCount(); i++) {
@@ -161,13 +161,13 @@ public class UnionCompiler {
}
private static TupleProjector getTupleProjector(RowProjector rowProj,
- List<TargetDataExpression> targetTypes) throws SQLException {
- Expression[] exprs = new Expression[targetTypes.size()];
+ List<PColumn> columns) throws SQLException {
+ Expression[] exprs = new Expression[columns.size()];
int i = 0;
for (ColumnProjector colProj : rowProj.getColumnProjectors()) {
exprs[i] = CoerceExpression.create(colProj.getExpression(),
- targetTypes.get(i).getType(),
targetTypes.get(i).getSortOrder(),
- targetTypes.get(i).getMaxLength());
+ columns.get(i).getDataType(),
columns.get(i).getSortOrder(),
+ columns.get(i).getMaxLength());
i++;
}
return new TupleProjector(exprs);
@@ -218,4 +218,106 @@ public class UnionCompiler {
this.sortOrder = sortOrder;
}
}
+
+ static List<QueryPlan> convertToTupleProjectionPlan(
+ List<QueryPlan> plans,
+ TableRef tableRef,
+ StatementContext statementContext) throws SQLException {
+ List<PColumn> columns = tableRef.getTable().getColumns();
+ for (int i = 0; i < plans.size(); i++) {
+ QueryPlan subPlan = plans.get(i);
+ TupleProjector projector =
getTupleProjector(subPlan.getProjector(), columns);
+ subPlan = new TupleProjectionPlan(subPlan, projector,
statementContext, null);
+ plans.set(i, subPlan);
+ }
+ return plans;
+ }
+
+ /**
+ * If every subquery in {@link UnionPlan} is ordered, and {@link
QueryPlan#getOutputOrderBys}
+ * of each subquery are equal(absolute equality or the same column name is
unnecessary, just
+ * column types are compatible and columns count is same), and at the same
time the outer
+ * query of {@link UnionPlan} has group by or order by, we would further
examine whether
+ * maintaining this order for the entire {@link UnionPlan} can compile out
the outer query's
+ * group by or order by. If it is sure, then {@link UnionPlan} just to
perform a simple
+ * merge on the output of each subquery to ensure the overall order of the
union all;
+ * otherwise, {@link UnionPlan} would not perform any special processing
on the output
+ * of the subqueries.
+ */
+ static void optimizeUnionOrderByIfPossible(
+ UnionPlan innerUnionPlan,
+ SelectStatement outerSelectStatement,
+ Supplier<StatementContext> statementContextCreator) throws
SQLException {
+ innerUnionPlan.enableCheckSupportOrderByOptimize();
+ if (!innerUnionPlan.isSupportOrderByOptimize()) {
+ return;
+ }
+
+ if (!isOptimizeUnionOrderByDeserved(
+ innerUnionPlan, outerSelectStatement,
statementContextCreator)) {
+ // If maintain the order for the entire UnionPlan(by merge on the
output of each
+ // subquery) could not compile out the outer query's group by or
order by, we would
+ // not perform any special processing on the output of the
subqueries.
+ innerUnionPlan.disableSupportOrderByOptimize();
+ }
+ }
+
+ /**
+ * If group by or order by in outerSelectStatement could be compiled out,
+ * this optimization is deserved.
+ */
+ private static boolean isOptimizeUnionOrderByDeserved(
+ UnionPlan innerUnionPlan,
+ SelectStatement outerSelectStatement,
+ Supplier<StatementContext> statementContextCreator) throws
SQLException {
+ if (!outerSelectStatement.haveGroupBy() &&
!outerSelectStatement.haveOrderBy()) {
+ return false;
+ }
+
+ // Just to avoid additional ProjectionCompiler.compile, make the
compilation of order by
+ // as simple as possible.
+ if (!outerSelectStatement.haveGroupBy()
+ &&
outerSelectStatement.getOrderBy().stream().anyMatch(OrderByNode::isIntegerLiteral))
{
+ return false;
+ }
+ StatementContext statementContext = statementContextCreator.get();
+ ColumnResolver columResover =
innerUnionPlan.getContext().getResolver();
+ TableRef tableRef = innerUnionPlan.getTableRef();
+ statementContext.setResolver(columResover);
+ statementContext.setCurrentTable(tableRef);
+
+ if (outerSelectStatement.haveGroupBy()) {
+ // For outer query has group by, we check whether
groupBy.isOrderPreserving is true.
+ GroupBy groupBy = GroupByCompiler.compile(statementContext,
outerSelectStatement);
+ outerSelectStatement =
+ HavingCompiler.rewrite(statementContext,
outerSelectStatement, groupBy);
+ Expression where = WhereCompiler.compile(
+ statementContext,
+ outerSelectStatement,
+ null,
+ null,
+ CompiledOffset.EMPTY_COMPILED_OFFSET.getByteOffset());
+ groupBy = groupBy.compile(statementContext, innerUnionPlan, where);
+ return groupBy.isOrderPreserving();
+ }
+
+ assert outerSelectStatement.haveOrderBy();
+ Expression where = WhereCompiler.compile(
+ statementContext,
+ outerSelectStatement,
+ null,
+ null,
+ CompiledOffset.EMPTY_COMPILED_OFFSET.getByteOffset());
+ // For outer query has order by, we check whether orderBy is
OrderBy.FWD_ROW_KEY_ORDER_BY.
+ OrderBy orderBy = OrderByCompiler.compile(
+ statementContext,
+ outerSelectStatement,
+ GroupBy.EMPTY_GROUP_BY,
+ null,
+ CompiledOffset.EMPTY_COMPILED_OFFSET,
+ null,
+ innerUnionPlan,
+ where);
+ return orderBy == OrderBy.FWD_ROW_KEY_ORDER_BY;
+ }
}
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/ClientAggregatePlan.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/ClientAggregatePlan.java
index 1b472a0fe3..b9a34c217e 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/ClientAggregatePlan.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/ClientAggregatePlan.java
@@ -236,8 +236,9 @@ public class ClientAggregatePlan extends
ClientProcessingPlan {
planSteps.add("CLIENT AGGREGATE INTO SINGLE ROW");
newBuilder.setClientAggregate("CLIENT AGGREGATE INTO SINGLE ROW");
} else if (groupBy.isOrderPreserving()) {
- planSteps.add("CLIENT AGGREGATE INTO DISTINCT ROWS BY " +
groupBy.getExpressions().toString());
- newBuilder.setClientAggregate("CLIENT AGGREGATE INTO DISTINCT ROWS
BY "
+ planSteps.add("CLIENT AGGREGATE INTO ORDERED DISTINCT ROWS BY "
+ + groupBy.getExpressions().toString());
+ newBuilder.setClientAggregate("CLIENT AGGREGATE INTO ORDERED
DISTINCT ROWS BY "
+ groupBy.getExpressions().toString());
} else if (useHashAgg) {
planSteps.add("CLIENT HASH AGGREGATE INTO DISTINCT ROWS BY " +
groupBy.getExpressions().toString());
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
index 649fd766ae..e1f0885886 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
@@ -26,6 +26,7 @@ import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Set;
+import java.util.stream.Collectors;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.phoenix.compile.ExplainPlan;
@@ -38,6 +39,7 @@ import org.apache.phoenix.compile.RowProjector;
import org.apache.phoenix.compile.ScanRanges;
import org.apache.phoenix.compile.StatementContext;
import org.apache.phoenix.execute.visitor.QueryPlanVisitor;
+import org.apache.phoenix.expression.OrderByExpression;
import org.apache.phoenix.iterate.ConcatResultIterator;
import org.apache.phoenix.iterate.DefaultParallelScanGrouper;
import org.apache.phoenix.iterate.LimitingResultIterator;
@@ -51,7 +53,6 @@ import org.apache.phoenix.optimize.Cost;
import org.apache.phoenix.parse.FilterableStatement;
import org.apache.phoenix.query.KeyRange;
import org.apache.phoenix.schema.TableRef;
-import org.apache.phoenix.util.ExpressionUtil;
import org.apache.phoenix.thirdparty.com.google.common.collect.Sets;
@@ -75,6 +76,8 @@ public class UnionPlan implements QueryPlan {
private Long estimatedBytes;
private Long estimateInfoTs;
private boolean getEstimatesCalled;
+ private boolean supportOrderByOptimize = false;
+ private List<OrderBy> outputOrderBys = null;
public UnionPlan(StatementContext context, FilterableStatement statement,
TableRef table, RowProjector projector,
Integer limit, Integer offset, OrderBy orderBy, GroupBy groupBy,
List<QueryPlan> plans, ParameterMetaData paramMetaData) throws SQLException {
@@ -95,7 +98,53 @@ public class UnionPlan implements QueryPlan {
break;
}
}
- this.isDegenerate = isDegen;
+ this.isDegenerate = isDegen;
+ }
+
+ /**
+ * If every subquery in {@link UnionPlan} is ordered, and {@link
QueryPlan#getOutputOrderBys}
+ * of each subquery are equal(absolute equality or the same column name is
unnecessary, just
+ * column types are compatible and columns count is same), then it just
needs to perform a
+ * simple merge on the subquery results to ensure the overall order of the
union all, see
+ * comments on {@link QueryCompiler#optimizeUnionOrderByIfPossible}.
+ */
+ private boolean checkIfSupportOrderByOptimize() {
+ if (!this.orderBy.isEmpty()) {
+ return false;
+ }
+ if (plans.isEmpty()) {
+ return false;
+ }
+ OrderBy prevOrderBy = null;
+ for (QueryPlan queryPlan : plans) {
+ List<OrderBy> orderBys = queryPlan.getOutputOrderBys();
+ if (orderBys.isEmpty() || orderBys.size() > 1) {
+ return false;
+ }
+ OrderBy orderBy = orderBys.get(0);
+ if (prevOrderBy != null &&
!OrderBy.equalsForOutputOrderBy(prevOrderBy, orderBy)) {
+ return false;
+ }
+ prevOrderBy = orderBy;
+ }
+ return true;
+ }
+
+ public boolean isSupportOrderByOptimize() {
+ return this.supportOrderByOptimize;
+ }
+
+ public void enableCheckSupportOrderByOptimize() {
+ this.supportOrderByOptimize = checkIfSupportOrderByOptimize();
+ this.outputOrderBys = null;
+ }
+
+ public void disableSupportOrderByOptimize() {
+ if (!this.supportOrderByOptimize) {
+ return;
+ }
+ this.outputOrderBys = null;
+ this.supportOrderByOptimize = false;
}
@Override
@@ -165,10 +214,13 @@ public class UnionPlan implements QueryPlan {
public final ResultIterator iterator(ParallelScanGrouper scanGrouper, Scan
scan) throws SQLException {
this.iterators = new UnionResultIterators(plans, parentContext);
ResultIterator scanner;
- boolean isOrdered = !orderBy.getOrderByExpressions().isEmpty();
- if (isOrdered) { // TopN
+ if (!orderBy.isEmpty()) { // TopN
scanner = new MergeSortTopNResultIterator(iterators, limit,
offset, orderBy.getOrderByExpressions());
+ } else if (this.supportOrderByOptimize) {
+ //Every subquery is ordered
+ scanner = new MergeSortTopNResultIterator(
+ iterators, limit, offset,
getOrderByExpressionsWhenSupportOrderByOptimize());
} else {
scanner = new ConcatResultIterator(iterators);
if (offset != null) {
@@ -308,17 +360,43 @@ public class UnionPlan implements QueryPlan {
}
}
+ @edu.umd.cs.findbugs.annotations.SuppressWarnings(
+ value = "EI_EXPOSE_REP",
+ justification = "getOutputOrderBys designed to work this way.")
@Override
public List<OrderBy> getOutputOrderBys() {
+ if (this.outputOrderBys != null) {
+ return this.outputOrderBys;
+ }
+ return this.outputOrderBys = convertToOutputOrderBys();
+ }
+
+ private List<OrderBy> convertToOutputOrderBys() {
assert this.groupBy == GroupBy.EMPTY_GROUP_BY;
assert this.orderBy != OrderBy.FWD_ROW_KEY_ORDER_BY && this.orderBy !=
OrderBy.REV_ROW_KEY_ORDER_BY;
if(!this.orderBy.isEmpty()) {
return Collections.<OrderBy> singletonList(
OrderBy.convertCompiledOrderByToOutputOrderBy(this.orderBy));
}
+ if (this.supportOrderByOptimize) {
+ assert this.plans.size() > 0;
+ return this.plans.get(0).getOutputOrderBys();
+ }
return Collections.<OrderBy> emptyList();
}
+ private List<OrderByExpression>
getOrderByExpressionsWhenSupportOrderByOptimize() {
+ assert this.supportOrderByOptimize;
+ assert this.plans.size() > 0;
+ assert this.orderBy.isEmpty();
+ List<OrderBy> outputOrderBys = this.plans.get(0).getOutputOrderBys();
+ assert outputOrderBys != null && outputOrderBys.size() == 1;
+ List<OrderByExpression> orderByExpressions =
outputOrderBys.get(0).getOrderByExpressions();
+ assert orderByExpressions != null && orderByExpressions.size() > 0;
+ return
orderByExpressions.stream().map(OrderByExpression::convertIfExpressionSortOrderDesc)
+ .collect(Collectors.toList());
+ }
+
@Override
public boolean isApplicable() {
return true;
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/OrderByExpression.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/OrderByExpression.java
index 8f71de9c4f..0ea8ef4eb0 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/OrderByExpression.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/OrderByExpression.java
@@ -50,15 +50,18 @@ public class OrderByExpression implements Writable {
}
/**
- * If {@link Expression#getSortOrder()} is {@link SortOrder#DESC},the
isAscending of returned new is reversed,but isNullsLast is untouched.
+ * If {@link Expression#getSortOrder()} is {@link SortOrder#DESC}, reverse
the isAscending, but isNullsLast is untouched.
* @param orderByExpression
* @return
*/
public static OrderByExpression
convertIfExpressionSortOrderDesc(OrderByExpression orderByExpression) {
- return createByCheckIfExpressionSortOrderDesc(
+ if (orderByExpression.getExpression().getSortOrder() !=
SortOrder.DESC) {
+ return orderByExpression;
+ }
+ return new OrderByExpression(
orderByExpression.getExpression(),
orderByExpression.isNullsLast(),
- orderByExpression.isAscending());
+ !orderByExpression.isAscending());
}
/**
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/parse/OrderByNode.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/parse/OrderByNode.java
index 2451c4b4c9..39ddc8dbe1 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/parse/OrderByNode.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/parse/OrderByNode.java
@@ -18,6 +18,7 @@
package org.apache.phoenix.parse;
import org.apache.phoenix.compile.ColumnResolver;
+import org.apache.phoenix.schema.types.PInteger;
/**
@@ -84,4 +85,16 @@ public final class OrderByNode {
if (!orderAscending) buf.append(" DESC");
if (nullsLast) buf.append(" NULLS LAST ");
}
+
+ public boolean isIntegerLiteral() {
+ return child instanceof LiteralParseNode
+ && ((LiteralParseNode) child).getType() == PInteger.INSTANCE;
+ }
+
+ public Integer getValueIfIntegerLiteral() {
+ if (!isIntegerLiteral()) {
+ return null;
+ }
+ return (Integer) ((LiteralParseNode) child).getValue();
+ }
}
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/parse/SelectStatement.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/parse/SelectStatement.java
index 53e82639f8..3446eb73bc 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/parse/SelectStatement.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/parse/SelectStatement.java
@@ -384,4 +384,16 @@ public class SelectStatement implements
FilterableStatement {
return offset;
}
+ public boolean haveGroupBy() {
+ return this.getGroupBy() != null && this.getGroupBy().size() > 0
+ || !this.isAggregate()
+ && this.isDistinct()
+ && this.getSelect() != null
+ && this.getSelect().size() > 0;
+ }
+
+ public boolean haveOrderBy() {
+ return this.getOrderBy() != null && this.getOrderBy().size() > 0;
+ }
+
}
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
index 64e6885c0a..bf84a4c413 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
@@ -244,7 +244,7 @@ public class ExpressionUtil {
/**
* <pre>
- * Infer OrderBys from the rowkey columns of {@link PTable},for projected
table may be there is no rowkey columns,
+ * Infer OrderBys from the rowkey columns of {@link PTable}, for projected
table may be no rowkey columns,
* so we should move forward to inspect {@link ProjectedColumn} by {@link
#getOrderByFromProjectedTable}.
* The second part of the return pair is the rowkey column offset we must
skip when we create OrderBys, because for table with
salted/multiTenant/viewIndexId,
* some leading rowkey columns should be skipped.
@@ -302,7 +302,7 @@ public class ExpressionUtil {
}
/**
- * For projected table may be there is no rowkey columns,
+ * For projected table may be no rowkey columns,
* so we should move forward to inspect {@link ProjectedColumn} to check
if the source column is rowkey column.
* The second part of the return pair is the rowkey column offset we must
skip when we create OrderBys, because for table with
salted/multiTenant/viewIndexId,
* some leading rowkey columns should be skipped.
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
index 597e0fc6cb..b86b75cee1 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
@@ -129,7 +129,7 @@ public class DerivedTableIT extends ParallelStatsDisabledIT
{
"CLIENT PARALLEL 1-WAY FULL SCAN OVER
"+dynamicTableName+"_DERIVED_IDX \n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY
[\"A_STRING\", \"B_STRING\"]\n" +
"CLIENT MERGE SORT\n" +
- "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
+ "CLIENT AGGREGATE INTO ORDERED DISTINCT ROWS BY [A]\n"
+
"CLIENT DISTINCT ON [COLLECTDISTINCT(B)]\n" +
"CLIENT SORTED BY [A DESC]"}});
testCases.add(new String[][] {
@@ -145,7 +145,7 @@ public class DerivedTableIT extends ParallelStatsDisabledIT
{
"CLIENT PARALLEL 4-WAY FULL SCAN OVER "+dynamicTableName+" \n"
+
" SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING,
B_STRING]\n" +
"CLIENT MERGE SORT\n" +
- "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
+ "CLIENT AGGREGATE INTO ORDERED DISTINCT ROWS BY [A]\n"
+
"CLIENT DISTINCT ON [COLLECTDISTINCT(B)]\n" +
"CLIENT SORTED BY [A DESC]"}});
return testCases;
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
index b7e52859bb..fe64d7dfc6 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
@@ -443,7 +443,7 @@ public class SortMergeJoinMoreIT extends
ParallelStatsDisabledIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS
BY [BUCKET, \"TIMESTAMP\", SRC_LOCATION, DST_LOCATION]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY [BUCKET, \"TIMESTAMP\"]\n" +
- "CLIENT AGGREGATE INTO DISTINCT ROWS BY [E.BUCKET,
E.TIMESTAMP]"
+ "CLIENT AGGREGATE INTO ORDERED DISTINCT ROWS BY
[E.BUCKET, E.TIMESTAMP]"
:
"SORT-MERGE-JOIN (INNER) TABLES\n" +
" CLIENT PARALLEL 2-WAY SKIP SCAN ON 2 RANGES OVER
" + eventCountTableName + " [X'00','5SEC',~1462993520000000000,'Tr/Bal'] -
[X'01','5SEC',~1462993420000000000,'Tr/Bal']\n" +
@@ -458,7 +458,7 @@ public class SortMergeJoinMoreIT extends
ParallelStatsDisabledIT {
" SERVER DISTINCT PREFIX FILTER OVER [BUCKET,
\"TIMESTAMP\", SRC_LOCATION, DST_LOCATION]\n" +
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS
BY [BUCKET, \"TIMESTAMP\", SRC_LOCATION, DST_LOCATION]\n" +
" CLIENT MERGE SORT\n" +
- "CLIENT AGGREGATE INTO DISTINCT ROWS BY [E.BUCKET,
E.TIMESTAMP]";
+ "CLIENT AGGREGATE INTO ORDERED DISTINCT ROWS BY
[E.BUCKET, E.TIMESTAMP]";
ResultSet rs = conn.createStatement().executeQuery("explain "
+ q);
assertEquals(p, QueryUtil.getExplainPlan(rs));
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
index 9b76b319db..b15f8c66c2 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
@@ -37,8 +37,10 @@ import org.apache.phoenix.compile.ExplainPlan;
import org.apache.phoenix.compile.ExplainPlanAttributes;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
+import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.TestUtil;
import org.junit.Test;
import org.junit.experimental.categories.Category;
@@ -1134,4 +1136,329 @@ public class UnionAllIT extends ParallelStatsDisabledIT
{
conn.close();
}
}
+
+ @Test
+ public void testOrderByOptimizeBug7397() throws Exception {
+ String tableName1 = generateUniqueName();
+ String tableName2 = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ props.setProperty(QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB,
Boolean.toString(false));
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+
+ try {
+ String ddl = "create table " + tableName1 + "( "+
+ " fuid UNSIGNED_LONG not null , " +
+ " fstatsdate UNSIGNED_LONG not null, " +
+ " fversion UNSIGNED_LONG not null," +
+ " faid_1 UNSIGNED_LONG not null," +
+ " clk_pv_1 UNSIGNED_LONG, " +
+ " activation_pv_1 UNSIGNED_LONG, " +
+ " CONSTRAINT TEST_PK PRIMARY KEY ( " +
+ " fuid , " +
+ " fstatsdate, " +
+ " fversion, " +
+ " faid_1 " +
+ " ))";
+ createTestTable(getUrl(), ddl);
+ String dml = "UPSERT INTO " + tableName1 + " VALUES(?,?,?,?,?,?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ setValues(stmt, 1, 20240711, 1, 11, 1, 1);
+ stmt.execute();
+ setValues(stmt, 1, 20240712, 1, 22, 3, 3);
+ stmt.execute();
+ setValues(stmt, 1, 20240713, 1, 33, 7, 7);
+ stmt.execute();
+ conn.commit();
+
+ ddl = "create table " + tableName2 + "( "+
+ " fuid UNSIGNED_LONG not null, " +
+ " fstatsdate UNSIGNED_LONG not null, " +
+ " fversion UNSIGNED_LONG not null," +
+ " faid_2 UNSIGNED_LONG not null," +
+ " clk_pv_2 UNSIGNED_LONG, " +
+ " activation_pv_2 UNSIGNED_LONG, " +
+ " CONSTRAINT TEST_PK PRIMARY KEY ( " +
+ " fuid , " +
+ " fstatsdate, " +
+ " fversion," +
+ " faid_2 " +
+ " ))";
+ createTestTable(getUrl(), ddl);
+ dml = "UPSERT INTO " + tableName2 + " VALUES(?,?,?,?,?,?)";
+ stmt = conn.prepareStatement(dml);
+ setValues(stmt, 1, 20240711, 1, 11, 6, 6);
+ stmt.execute();
+ setValues(stmt, 1, 20240712, 1, 22, 2, 2);
+ stmt.execute();
+ setValues(stmt, 1, 20240713, 1, 33, 4, 4);
+ stmt.execute();
+ setValues(stmt, 1, 20240710, 1, 22, 5, 5);
+ stmt.execute();
+ conn.commit();
+
+ String orderedUnionSql = "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_1) AS valid_click_count,"
+ + " SUM(activation_pv_1) AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_1, FStatsDate"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_2) AS valid_click_count,"
+ + " SUM(activation_pv_2) AS activated_count"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_2, FStatsDate"
+ + ")";
+
+ //Test group by orderPreserving
+ String sql = "SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ ResultSet rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 11L, 20240711L, 7L, 7L},
+ {1L, 22L, 20240710L, 5L, 5L},
+ {1L, 22L, 20240712L, 5L, 5L},
+ {1L, 33L, 20240713L, 11L, 11L}});
+
+ //Test group by not orderPreserving
+ sql = "SELECT ADVERTISER_ID AS i_advertiser_id,"
+ + "ADGROUP_ID AS i_adgroup_id,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "GROUP BY I_ADVERTISER_ID, ADGROUP_ID "
+ + "ORDER BY i_adgroup_id "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 11L, 7L, 7L},
+ {1L, 22L, 10L, 10L},
+ {1L, 33L, 11L, 11L}});
+
+ //Test group by not orderPreserving
+ sql = "SELECT ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "GROUP BY ADGROUP_ID, I_DATE "
+ + "ORDER BY adgroup_id, i_date "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {11L, 20240711L, 7L, 7L},
+ {22L, 20240710L, 5L, 5L},
+ {22L, 20240712L, 5L, 5L},
+ {33L, 20240713L, 11L, 11L}});
+
+ //Test group by orderPreserving with where
+ sql = "SELECT ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + " where advertiser_id = 1 "
+ + "GROUP BY ADGROUP_ID, I_DATE "
+ + "ORDER BY adgroup_id, i_date "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {11L, 20240711L, 7L, 7L},
+ {22L, 20240710L, 5L, 5L},
+ {22L, 20240712L, 5L, 5L},
+ {33L, 20240713L, 11L, 11L}});
+
+ //Test order by orderPreserving
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "VALID_CLICK_COUNT AS valid_click_count,"
+ + "ACTIVATED_COUNT AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + " where valid_click_count in (1, 5, 2, 4) "
+ + "ORDER BY advertiser_id, i_date, adgroup_id "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 22L, 20240710L, 5L, 5L},
+ {1L, 11L, 20240711L, 1L, 1L},
+ {1L, 22L, 20240712L, 2L, 2L},
+ {1L, 33L, 20240713L, 4L, 4L}});
+
+ //Test order by not orderPreserving
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS i_adgroup_id,"
+ + "DATE AS date,"
+ + "VALID_CLICK_COUNT AS valid_click_count,"
+ + "ACTIVATED_COUNT AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "ORDER BY advertiser_id, i_adgroup_id, date,
valid_click_count "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 11L, 20240711L, 1L, 1L},
+ {1L, 11L, 20240711L, 6L, 6L},
+ {1L, 22L, 20240710L, 5L, 5L},
+ {1L, 22L, 20240712L, 2L, 2L},
+ {1L, 22L, 20240712L, 3L, 3L},
+ {1L, 33L, 20240713L, 4L, 4L},
+ {1L, 33L, 20240713L, 7L, 7L}});
+
+ //Test there is no order in union
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " clk_pv_1 AS valid_click_count,"
+ + " activation_pv_1 AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " clk_pv_2 AS valid_click_count,"
+ + " activation_pv_2 AS activated_count"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + ")"
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 11L, 20240711L, 7L, 7L},
+ {1L, 22L, 20240710L, 5L, 5L},
+ {1L, 22L, 20240712L, 5L, 5L},
+ {1L, 33L, 20240713L, 11L, 11L}});
+
+ //Test alias not inconsistent in union
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID_1 AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id_1,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_1) AS valid_click_count,"
+ + " SUM(activation_pv_1) AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_1, FStatsDate"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_2),"
+ + " SUM(activation_pv_2)"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_2, FStatsDate"
+ + ")"
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID_1, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 11L, 20240711L, 7L, 7L},
+ {1L, 22L, 20240710L, 5L, 5L},
+ {1L, 22L, 20240712L, 5L, 5L},
+ {1L, 33L, 20240713L, 11L, 11L}});
+
+ //Test order by column not equals in union
+ sql = "SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_1) AS valid_click_count,"
+ + " SUM(activation_pv_1) AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_1, FStatsDate"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2 AS adgroup_id,"
+ + " cast (0 as UNSIGNED_LONG) AS date,"
+ + " SUM(clk_pv_2) AS valid_click_count,"
+ + " SUM(activation_pv_2) AS activated_count"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_2"
+ + ")"
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 11L, 0L, 6L, 6L},
+ {1L, 11L, 20240711L, 1L, 1L},
+ {1L, 22L, 0L, 7L, 7L},
+ {1L, 22L, 20240712L, 3L, 3L},
+ {1L, 33L, 0L, 4L, 4L},
+ {1L, 33L, 20240713L, 7L, 7L}});
+
+ //Test only union and order by not match
+ sql = orderedUnionSql.substring(1, orderedUnionSql.length()-1) +
+ " order by advertiser_id, date, adgroup_id,
valid_click_count";
+ rs = conn.createStatement().executeQuery(sql);
+ TestUtil.assertResultSet(rs, new Long[][]{
+ {1L, 22L, 20240710L, 5L, 5L},
+ {1L, 11L, 20240711L, 1L, 1L},
+ {1L, 11L, 20240711L, 6L, 6L},
+ {1L, 22L, 20240712L, 2L, 2L},
+ {1L, 22L, 20240712L, 3L, 3L},
+ {1L, 33L, 20240713L, 4L, 4L},
+ {1L, 33L, 20240713L, 7L, 7L}});
+ } finally {
+ conn.close();
+ }
+ }
+
+ private static void setValues(PreparedStatement stmt, int... values)
throws SQLException {
+ for(int i = 0; i < values.length; i++) {
+ stmt.setLong(i + 1, values[i]);
+ }
+ }
}
diff --git
a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index 0e32be630f..bb838b9956 100644
---
a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++
b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -80,6 +80,7 @@ import
org.apache.phoenix.expression.aggregator.CountAggregator;
import org.apache.phoenix.expression.aggregator.ServerAggregators;
import org.apache.phoenix.expression.function.TimeUnit;
import org.apache.phoenix.filter.EncodedQualifiersColumnProjectionFilter;
+import org.apache.phoenix.iterate.MergeSortTopNResultIterator;
import org.apache.phoenix.jdbc.PhoenixConnection;
import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
import org.apache.phoenix.jdbc.PhoenixStatement;
@@ -6863,7 +6864,6 @@ public class QueryCompilerTest extends
BaseConnectionlessQueryTest {
"(SELECT 1 FROM " + orderTableName + " o where o.price >
8) ORDER BY name";
queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
assertTrue(queryPlan instanceof HashJoinPlan);
- System.out.println(queryPlan.getStatement());
TestUtil.assertSelectStatement(
queryPlan.getStatement(),
"SELECT ITEM_ID,NAME FROM ITEM_TABLE I WHERE EXISTS
(SELECT 1 FROM ORDER_TABLE O WHERE O.PRICE > 8 LIMIT 1) ORDER BY NAME");
@@ -7410,4 +7410,368 @@ public class QueryCompilerTest extends
BaseConnectionlessQueryTest {
assertEquals(isNullsLast, orderByExpression.isNullsLast());
assertEquals(isAscending, orderByExpression.isAscending());
}
+
+ @Test
+ public void testUnionAllOrderByOptimizeBug7397() throws Exception {
+ Properties props = new Properties();
+ props.setProperty(QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB,
Boolean.toString(false));
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ String tableName1 = generateUniqueName();
+ String sql1 = "create table " + tableName1 + "( "+
+ " fuid UNSIGNED_LONG not null , " +
+ " fstatsdate UNSIGNED_LONG not null, " +
+ " fversion UNSIGNED_LONG not null," +
+ " faid_1 UNSIGNED_LONG not null," +
+ " clk_pv_1 UNSIGNED_LONG, " +
+ " activation_pv_1 UNSIGNED_LONG, " +
+ " CONSTRAINT TEST_PK PRIMARY KEY ( " +
+ " fuid , " +
+ " fstatsdate, " +
+ " fversion, " +
+ " faid_1 " +
+ " ))";
+ conn.createStatement().execute(sql1);
+
+ String tableName2= generateUniqueName();
+ String sql2 = "create table " + tableName2 + "( "+
+ " fuid UNSIGNED_LONG not null , " +
+ " fstatsdate UNSIGNED_LONG not null, " +
+ " fversion UNSIGNED_LONG not null," +
+ " faid_2 UNSIGNED_LONG not null," +
+ " clk_pv_2 UNSIGNED_LONG, " +
+ " activation_pv_2 UNSIGNED_LONG, " +
+ " CONSTRAINT TEST_PK PRIMARY KEY ( " +
+ " fuid , " +
+ " fstatsdate, " +
+ " fversion," +
+ " faid_2 " +
+ " ))";
+ conn.createStatement().execute(sql2);
+
+ String orderedUnionSql =
+ "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_1) AS valid_click_count,"
+ + " SUM(activation_pv_1) AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_1, FStatsDate"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_2) AS valid_click_count,"
+ + " SUM(activation_pv_2) AS activated_count"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_2, FStatsDate"
+ + ")";
+
+ //Test group by orderPreserving
+ String sql = "SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ ClientAggregatePlan plan
=(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn, sql);
+ assertTrue(plan.getGroupBy().isOrderPreserving());
+ UnionPlan unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ List<OrderBy> orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ OrderBy orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 3);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID"));
+
+ //Test group by orderPreserving for distinct
+ sql = "SELECT distinct ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date "
+ + "FROM "
+ + orderedUnionSql
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(plan.getGroupBy().isOrderPreserving());
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 3);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID"));
+
+ //Test group by not orderPreserving
+ sql = "SELECT ADVERTISER_ID AS i_advertiser_id,"
+ + "ADGROUP_ID AS i_adgroup_id,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "GROUP BY I_ADVERTISER_ID, ADGROUP_ID "
+ + "ORDER BY i_adgroup_id "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(!plan.getGroupBy().isOrderPreserving());
+ assertTrue(plan.getOrderBy() != OrderBy.FWD_ROW_KEY_ORDER_BY);
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(!(unionPlan.iterator() instanceof
MergeSortTopNResultIterator));
+ assertTrue(unionPlan.getOutputOrderBys().isEmpty());
+
+ //Test group by not orderPreserving
+ sql = "SELECT ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "GROUP BY ADGROUP_ID, I_DATE "
+ + "ORDER BY adgroup_id, i_date "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(!plan.getGroupBy().isOrderPreserving());
+ assertTrue(plan.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY);
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(!(unionPlan.iterator() instanceof
MergeSortTopNResultIterator));
+ assertTrue(unionPlan.getOutputOrderBys().isEmpty());
+
+ //Test group by orderPreserving with where
+ sql = "SELECT ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + " where advertiser_id = 1 "
+ + "GROUP BY ADGROUP_ID, I_DATE "
+ + "ORDER BY adgroup_id, i_date "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(plan.getGroupBy().isOrderPreserving());
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 3);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID"));
+
+ //Test order by orderPreserving
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "VALID_CLICK_COUNT AS valid_click_count,"
+ + "ACTIVATED_COUNT AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "ORDER BY advertiser_id, i_date, adgroup_id "
+ + "limit 10";
+ ClientScanPlan scanPlan
=(ClientScanPlan)TestUtil.getOptimizeQueryPlan(conn, sql);
+ assertTrue(scanPlan.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY);
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(scanPlan.getDelegate())).getDelegate();
+ assertTrue(unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 3);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID"));
+
+ //Test order by not orderPreserving
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS i_adgroup_id,"
+ + "DATE AS date,"
+ + "VALID_CLICK_COUNT AS valid_click_count,"
+ + "ACTIVATED_COUNT AS activated_count "
+ + "FROM "
+ + orderedUnionSql
+ + "ORDER BY advertiser_id, i_adgroup_id, date,
valid_click_count "
+ + "limit 10";
+ scanPlan =(ClientScanPlan)TestUtil.getOptimizeQueryPlan(conn, sql);
+ assertTrue(!scanPlan.getOrderBy().isEmpty());
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(scanPlan.getDelegate())).getDelegate();
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(!(unionPlan.iterator() instanceof
MergeSortTopNResultIterator));
+ assertTrue(unionPlan.getOutputOrderBys().isEmpty());
+
+ //Test there is no order in union
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " clk_pv_1 AS valid_click_count,"
+ + " activation_pv_1 AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " clk_pv_2 AS valid_click_count,"
+ + " activation_pv_2 AS activated_count"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + ")"
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(!plan.getGroupBy().isOrderPreserving());
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(!(unionPlan.iterator() instanceof
MergeSortTopNResultIterator));
+ assertTrue(unionPlan.getOutputOrderBys().isEmpty());
+
+ //Test alias not inconsistent in union
+ sql ="SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID_1 AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id_1,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_1) AS valid_click_count,"
+ + " SUM(activation_pv_1) AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_1, FStatsDate"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_2),"
+ + " SUM(activation_pv_2)"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_2, FStatsDate"
+ + ")"
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID_1, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(plan.getGroupBy().isOrderPreserving());
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 3);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID_1"));
+
+ //Test order by column not equals in union
+ sql = "SELECT ADVERTISER_ID AS advertiser_id,"
+ + "ADGROUP_ID AS adgroup_id,"
+ + "DATE AS i_date,"
+ + "SUM(VALID_CLICK_COUNT) AS valid_click_count,"
+ + "SUM(ACTIVATED_COUNT) AS activated_count "
+ + "FROM "
+ + "(SELECT FUId AS advertiser_id,"
+ + " FAId_1 AS adgroup_id,"
+ + " FStatsDate AS date,"
+ + " SUM(clk_pv_1) AS valid_click_count,"
+ + " SUM(activation_pv_1) AS activated_count"
+ + " FROM " + tableName1
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_1 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_1, FStatsDate"
+ + " UNION ALL "
+ + " SELECT "
+ + " FUId AS advertiser_id,"
+ + " FAId_2 AS adgroup_id,"
+ + " cast (0 as UNSIGNED_LONG) AS date,"
+ + " SUM(clk_pv_2) AS valid_click_count,"
+ + " SUM(activation_pv_2) AS activated_count"
+ + " FROM " + tableName2
+ + " WHERE (FVersion = 1) AND (FUId IN (1)) AND (FAId_2 IN
(11, 22, 33, 10))"
+ + " AND (FStatsDate >= 20240710) AND (FStatsDate <=
20240718)"
+ + " GROUP BY FUId, FAId_2"
+ + ")"
+ + "GROUP BY ADVERTISER_ID, ADGROUP_ID, I_DATE "
+ + "ORDER BY advertiser_id, adgroup_id, i_date "
+ + "limit 10";
+ plan =(ClientAggregatePlan)TestUtil.getOptimizeQueryPlan(conn,
sql);
+ assertTrue(!plan.getGroupBy().isOrderPreserving());
+ unionPlan =
(UnionPlan)((TupleProjectionPlan)(plan.getDelegate())).getDelegate();
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(!(unionPlan.iterator() instanceof
MergeSortTopNResultIterator));
+ assertTrue(unionPlan.getOutputOrderBys().isEmpty());
+
+ //Test only union
+ sql = orderedUnionSql.substring(1, orderedUnionSql.length()-1);
+ unionPlan =(UnionPlan)TestUtil.getOptimizeQueryPlan(conn, sql);
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(!(unionPlan.iterator() instanceof
MergeSortTopNResultIterator));
+ assertTrue(unionPlan.getOutputOrderBys().isEmpty());
+
+ //Test only union and order by match
+ sql = orderedUnionSql.substring(1, orderedUnionSql.length()-1) + "
order by advertiser_id, date, adgroup_id";
+ unionPlan =(UnionPlan)TestUtil.getOptimizeQueryPlan(conn, sql);
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ assertTrue(unionPlan.getSubPlans().stream().allMatch(
+ p -> p.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY));
+ orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 3);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID"));
+
+ //Test only union and order by not match
+ sql = orderedUnionSql.substring(1, orderedUnionSql.length()-1) +
+ " order by advertiser_id, date, adgroup_id,
valid_click_count";
+ unionPlan =(UnionPlan)TestUtil.getOptimizeQueryPlan(conn, sql);
+ assertTrue(!unionPlan.isSupportOrderByOptimize());
+ assertTrue(unionPlan.iterator() instanceof
MergeSortTopNResultIterator);
+ assertTrue(unionPlan.getSubPlans().stream().noneMatch(
+ p -> p.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY));
+ orderBys = unionPlan.getOutputOrderBys();
+ assertTrue(orderBys.size() == 1);
+ orderBy = orderBys.get(0);
+ assertTrue(orderBy.getOrderByExpressions().size() == 4);
+
assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("ADVERTISER_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("DATE"));
+
assertTrue(orderBy.getOrderByExpressions().get(2).toString().equals("ADGROUP_ID"));
+
assertTrue(orderBy.getOrderByExpressions().get(3).toString().equals("VALID_CLICK_COUNT"));
+ }
+ }
}
