This is an automated email from the ASF dual-hosted git repository.
hyuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 0af3fd1 [CALCITE-4011] Support trait propagation for
EnumerableProject and EnumerableFilter (Rui Wang)
0af3fd1 is described below
commit 0af3fd17a293d37125c7cca58257e5f6cbc1a76c
Author: amaliujia <[email protected]>
AuthorDate: Tue May 19 23:52:40 2020 -0700
[CALCITE-4011] Support trait propagation for EnumerableProject and
EnumerableFilter (Rui Wang)
Trait propgation includes trait passthrough and trait derivation.
Trait passthrough could happen when Project satisfies the ordering
requirement that is defined by collations. Project will not satisfy the
ordering requirement when a requested collation is defined on a
non-trivial expression. Usually a RexCall is considered as non-trivial,
unless it is a CAST that perserves monotonicity,
Here is an example to demonstrate why trait cannot pass through when
collations are defined on non-trival expr:
select a, b*-1 as b
from foo
order by a, b;
which generates the logical plan:
LogicalSort
LogicalProject
LogicalTableScan
We cannot move the top sort down through the project. Because b*-1 will
change ordering to the opposite. The sort has to remain on top of
project for correness.
Trait derivaiton does something simlar to trait pass through, except for
one difference:
trait derivation can return parital collations that are derived from
child. For example, if [a, b, c, d] is derived from child, and if c is
defined on a non-trival expr, then [a, b] will be returned cause it
might be useful for parents.
Another example to show why trait derivation could be useful to reutrn
partial collations:
select a, b
from (
select a, b, c*-1, d
from foo
order by a, b, c, d
)
order by a, b;
In this example, even though the inner project does not preserve the
total ordering for inner sort, but the outer sort only want to sort on
[a, b], thus if inner project can derive [a, b], the outer project will
not need the top sort or enforce a sort for its input.
After top-down optimization is enabled, trait propagation for
EnumerableProject
and EnumerableFilter is supposed to replace ProjectSortTransposeRule and
SortProjectTransposeRule.
Close #1985
---
.../adapter/enumerable/EnumerableConvention.java | 7 +-
.../adapter/enumerable/EnumerableFilter.java | 27 ++
.../adapter/enumerable/EnumerableMergeJoin.java | 2 +-
.../adapter/enumerable/EnumerableProject.java | 114 ++++++
.../calcite/config/CalciteConnectionConfig.java | 2 +
.../config/CalciteConnectionConfigImpl.java | 4 +
.../calcite/config/CalciteConnectionProperty.java | 5 +-
.../calcite/config/CalciteSystemProperty.java | 3 +-
.../apache/calcite/plan/volcano/OptimizeTask.java | 3 +-
.../apache/calcite/prepare/CalcitePrepareImpl.java | 1 +
.../org/apache/calcite/test/TopDownOptTest.java | 293 ++++++++++++--
.../java/org/apache/calcite/tools/PlannerTest.java | 31 +-
.../org/apache/calcite/test/TopDownOptTest.xml | 429 +++++++++++++++++++++
core/src/test/resources/saffron.properties | 2 +-
.../apache/calcite/adapter/tpcds/TpcdsTest.java | 50 ++-
15 files changed, 877 insertions(+), 96 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableConvention.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableConvention.java
index 48ae551..6acc25d 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableConvention.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableConvention.java
@@ -24,7 +24,7 @@ import org.apache.calcite.plan.RelTrait;
import org.apache.calcite.plan.RelTraitDef;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.rel.RelCollation;
-import org.apache.calcite.rel.RelCollationTraitDef;
+import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.RelFactories;
@@ -60,9 +60,8 @@ public enum EnumerableConvention implements Convention {
input.getCluster().getPlanner(),
input, INSTANCE, true);
}
- RelCollation collation = required.getTrait(RelCollationTraitDef.INSTANCE);
- if (collation != null) {
- assert !collation.getFieldCollations().isEmpty();
+ RelCollation collation = required.getCollation();
+ if (collation != null && collation != RelCollations.EMPTY) {
rel = EnumerableSort.create(rel, collation, null, null);
}
return rel;
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableFilter.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableFilter.java
index f8256e6..5af195c 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableFilter.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableFilter.java
@@ -18,7 +18,9 @@ package org.apache.calcite.adapter.enumerable;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollationTraitDef;
+import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelDistributionTraitDef;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Filter;
@@ -26,6 +28,11 @@ import org.apache.calcite.rel.metadata.RelMdCollation;
import org.apache.calcite.rel.metadata.RelMdDistribution;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.util.Pair;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
/** Implementation of {@link org.apache.calcite.rel.core.Filter} in
* {@link org.apache.calcite.adapter.enumerable.EnumerableConvention
enumerable calling convention}. */
@@ -68,4 +75,24 @@ public class EnumerableFilter
// EnumerableCalc is always better
throw new UnsupportedOperationException();
}
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
+ RelTraitSet required) {
+ RelCollation collation = required.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+ RelTraitSet traits = traitSet.replace(collation);
+ return Pair.of(traits, ImmutableList.of(traits));
+ }
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> deriveTraits(
+ final RelTraitSet childTraits, final int childId) {
+ RelCollation collation = childTraits.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+ RelTraitSet traits = traitSet.replace(collation);
+ return Pair.of(traits, ImmutableList.of(traits));
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
index 4668fbf..5908212 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
@@ -139,7 +139,7 @@ public class EnumerableMergeJoin extends Join implements
EnumerableRel {
@Override public Pair<RelTraitSet, List<RelTraitSet>> deriveTraits(
final RelTraitSet childTraits, final int childId) {
final int keyCount = joinInfo.leftKeys.size();
- RelCollation collation =
childTraits.getTrait(RelCollationTraitDef.INSTANCE);
+ RelCollation collation = childTraits.getCollation();
final int colCount = collation.getFieldCollations().size();
if (colCount < keyCount || keyCount == 0) {
return null;
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableProject.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableProject.java
index 8712f20..f38e348 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableProject.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableProject.java
@@ -16,20 +16,37 @@
*/
package org.apache.calcite.adapter.enumerable;
+import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollationTraitDef;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.metadata.RelMdCollation;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexCallBinding;
+import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.validate.SqlMonotonicity;
+import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
+import org.apache.calcite.util.mapping.MappingType;
+import org.apache.calcite.util.mapping.Mappings;
import com.google.common.collect.ImmutableList;
+import java.util.ArrayList;
import java.util.List;
+import java.util.Objects;
+
/** Implementation of {@link org.apache.calcite.rel.core.Project} in
* {@link org.apache.calcite.adapter.enumerable.EnumerableConvention
enumerable calling convention}. */
@@ -86,4 +103,101 @@ public class EnumerableProject extends Project implements
EnumerableRel {
// EnumerableCalcRel is always better
throw new UnsupportedOperationException();
}
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
+ RelTraitSet required) {
+ RelCollation collation = required.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+
+ final Mappings.TargetMapping map =
+ RelOptUtil.permutationIgnoreCast(
+ getProjects(), getInput().getRowType());
+
+ for (RelFieldCollation rc : collation.getFieldCollations()) {
+ if (!isCollationOnTrivialExpr(map, rc, true)) {
+ return null;
+ }
+ }
+
+ final RelCollation newCollation = collation.apply(map);
+ return Pair.of(traitSet.replace(collation),
+ ImmutableList.of(traitSet.replace(newCollation)));
+ }
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> deriveTraits(
+ final RelTraitSet childTraits, final int childId) {
+ RelCollation collation = childTraits.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+
+ int maxField = Math.max(getProjects().size(),
+ getInput().getRowType().getFieldCount());
+ Mappings.TargetMapping mapping = Mappings
+ .create(MappingType.FUNCTION, maxField, maxField);
+ for (Ord<RexNode> node : Ord.zip(getProjects())) {
+ if (node.e instanceof RexInputRef) {
+ mapping.set(((RexInputRef) node.e).getIndex(), node.i);
+ } else if (node.e.isA(SqlKind.CAST)) {
+ final RexNode operand = ((RexCall) node.e).getOperands().get(0);
+ if (operand instanceof RexInputRef) {
+ mapping.set(((RexInputRef) operand).getIndex(), node.i);
+ }
+ }
+ }
+
+ List<RelFieldCollation> collationFieldsToDerive = new ArrayList<>();
+ for (RelFieldCollation rc : collation.getFieldCollations()) {
+ if (isCollationOnTrivialExpr(mapping, rc, false)) {
+ collationFieldsToDerive.add(rc);
+ } else {
+ break;
+ }
+ }
+
+ if (collationFieldsToDerive.size() > 0) {
+ final RelCollation newCollation = RelCollations
+ .of(collationFieldsToDerive).apply(mapping);
+ return Pair.of(traitSet.replace(newCollation),
+ ImmutableList.of(traitSet.replace(collation)));
+ } else {
+ return null;
+ }
+ }
+
+ /**
+ * Determine whether there is mapping between project input and output
fields.
+ * Bail out if sort relies on non-trivial expressions.
+ */
+ private boolean isCollationOnTrivialExpr(
+ Mappings.TargetMapping map, RelFieldCollation fc, boolean passdown) {
+ int target = map.getTargetOpt(fc.getFieldIndex());
+ if (target < 0) {
+ return false;
+ }
+
+ final RexNode node;
+ if (passdown) {
+ node = getProjects().get(fc.getFieldIndex());
+ } else {
+ node = getProjects().get(target);
+ }
+
+ if (node.isA(SqlKind.CAST)) {
+ // Check whether it is a monotonic preserving cast
+ final RexCall cast = (RexCall) node;
+ RelFieldCollation newFc = Objects.requireNonNull(RexUtil.apply(map, fc));
+ final RexCallBinding binding =
+ RexCallBinding.create(getCluster().getTypeFactory(), cast,
+ ImmutableList.of(RelCollations.of(newFc)));
+ if (cast.getOperator().getMonotonicity(binding)
+ == SqlMonotonicity.NOT_MONOTONIC) {
+ return false;
+ }
+ }
+
+ return true;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
index d495a21..d952e33 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
@@ -78,4 +78,6 @@ public interface CalciteConnectionConfig extends
ConnectionConfig {
boolean typeCoercion();
/** @see CalciteConnectionProperty#LENIENT_OPERATOR_LOOKUP */
boolean lenientOperatorLookup();
+ /** @see CalciteConnectionProperty#TOPDOWN_OPT */
+ boolean topDownOpt();
}
diff --git
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
index b3c9d91..93f6ea3 100644
---
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
+++
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
@@ -199,4 +199,8 @@ public class CalciteConnectionConfigImpl extends
ConnectionConfigImpl
return CalciteConnectionProperty.LENIENT_OPERATOR_LOOKUP.wrap(properties)
.getBoolean();
}
+
+ public boolean topDownOpt() {
+ return CalciteConnectionProperty.TOPDOWN_OPT.wrap(properties).getBoolean();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
index d3b9b53..a061fb7 100644
---
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
+++
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
@@ -159,7 +159,10 @@ public enum CalciteConnectionProperty implements
ConnectionProperty {
/** Whether to make create implicit functions if functions do not exist
* in the operator table, default false. */
- LENIENT_OPERATOR_LOOKUP("lenientOperatorLookup", Type.BOOLEAN, false, false);
+ LENIENT_OPERATOR_LOOKUP("lenientOperatorLookup", Type.BOOLEAN, false, false),
+
+ /** Whether to enable top-down optimization in Volcano planner. */
+ TOPDOWN_OPT("topDownOpt", Type.BOOLEAN,
CalciteSystemProperty.TOPDOWN_OPT.value(), false);
private final String camelName;
private final Type type;
diff --git
a/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
b/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
index 37e36ac..3e93a5b 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
@@ -122,7 +122,8 @@ public final class CalciteSystemProperty<T> {
booleanProperty("calcite.volcano.dump.sets", true);
/**
- * Whether to enable top-down optimization.
+ * Whether to enable top-down optimization. This config can be overridden
+ * by {@link CalciteConnectionProperty#TOPDOWN_OPT}.
*
* <p>Note: Enabling top-down optimization will automatically disable
* the use of AbstractConverter and related rules.</p>
diff --git
a/core/src/main/java/org/apache/calcite/plan/volcano/OptimizeTask.java
b/core/src/main/java/org/apache/calcite/plan/volcano/OptimizeTask.java
index af651c8..48558c7 100644
--- a/core/src/main/java/org/apache/calcite/plan/volcano/OptimizeTask.java
+++ b/core/src/main/java/org/apache/calcite/plan/volcano/OptimizeTask.java
@@ -228,7 +228,8 @@ abstract class OptimizeTask {
RelNode newRel = rel.derive(subset.getTraitSet(), childId);
if (newRel != null && !planner.isRegistered(newRel)) {
RelSubset relSubset = planner.register(newRel, node);
- assert relSubset.set == planner.getSubset(node).set;
+ // TODO: CALCITE-4030
+ // assert relSubset.set == planner.getSubset(node).set;
}
}
}
diff --git
a/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
b/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
index 8d3f9b9..4b270ca 100644
--- a/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
+++ b/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
@@ -431,6 +431,7 @@ public class CalcitePrepareImpl implements CalcitePrepare {
if (CalciteSystemProperty.ENABLE_COLLATION_TRAIT.value()) {
planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
}
+ planner.setTopDownOpt(prepareContext.config().topDownOpt());
RelOptUtil.registerDefaultRules(planner,
prepareContext.config().materializationsEnabled(),
enableBindable);
diff --git a/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
b/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
index d188c7b..592661a 100644
--- a/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
@@ -19,17 +19,22 @@ package org.apache.calcite.test;
import org.apache.calcite.adapter.enumerable.EnumerableRules;
import org.apache.calcite.plan.ConventionTraitDef;
import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptRule;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.volcano.VolcanoPlanner;
import org.apache.calcite.rel.RelCollationTraitDef;
import org.apache.calcite.rel.rules.JoinCommuteRule;
import org.apache.calcite.rel.rules.JoinPushThroughJoinRule;
+import org.apache.calcite.rel.rules.ProjectSortTransposeRule;
+import org.apache.calcite.rel.rules.SortProjectTransposeRule;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import org.junit.jupiter.api.Test;
+import java.util.List;
+
/**
* Unit test for top-down optimization.
*
@@ -65,92 +70,296 @@ import org.junit.jupiter.api.Test;
* </ol>
*/
class TopDownOptTest extends RelOptTestBase {
-
- protected DiffRepository getDiffRepos() {
- return DiffRepository.lookup(TopDownOptTest.class);
- }
-
- Sql sql(String sql) {
- VolcanoPlanner planner = new VolcanoPlanner();
- // Always use top-down optimization
- planner.setTopDownOpt(true);
- planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
- planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
-
- RelOptUtil.registerDefaultRules(planner, false, false);
-
- // Keep deterministic join order
- planner.removeRule(JoinCommuteRule.INSTANCE);
- planner.removeRule(JoinPushThroughJoinRule.LEFT);
- planner.removeRule(JoinPushThroughJoinRule.RIGHT);
-
- // Always use merge join
- planner.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
- planner.removeRule(EnumerableRules.ENUMERABLE_CALC_RULE);
-
- // Always use sorted agg
- planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
- planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
-
- Tester tester = createTester().withDecorrelation(true)
- .withClusterFactory(cluster -> RelOptCluster.create(planner,
cluster.getRexBuilder()));
-
- return new Sql(tester, sql, null, planner,
- ImmutableMap.of(), ImmutableList.of());
- }
-
@Test void testSortAgg() {
final String sql = "select mgr, count(*) from sales.emp\n"
+ "group by mgr order by mgr desc nulls last limit 5";
- sql(sql).check();
+ Query.create(sql).check();
}
@Test void testSortAggPartialKey() {
final String sql = "select mgr,deptno,comm,count(*) from sales.emp\n"
+ "group by mgr,deptno,comm\n"
+ "order by comm desc nulls last, deptno nulls first";
- sql(sql).check();
+ Query.create(sql).check();
}
@Test void testSortMergeJoin() {
final String sql = "select * from\n"
+ "sales.emp r join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+ "order by r.job desc nulls last, r.ename nulls first";
- sql(sql).check();
+ Query.create(sql).check();
}
@Test void testSortMergeJoinRight() {
final String sql = "select * from\n"
+ "sales.emp r join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+ "order by s.job desc nulls last, s.ename nulls first";
- sql(sql).check();
+ Query.create(sql).check();
}
@Test void testMergeJoinDeriveLeft1() {
final String sql = "select * from\n"
+ "(select ename, job, max(sal) from sales.emp group by ename, job)
r\n"
+ "join sales.bonus s on r.job=s.job and r.ename=s.ename";
- sql(sql).check();
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
}
@Test void testMergeJoinDeriveLeft2() {
final String sql = "select * from\n"
+ "(select ename, job, mgr, max(sal) from sales.emp group by ename,
job, mgr) r\n"
+ "join sales.bonus s on r.job=s.job and r.ename=s.ename";
- sql(sql).check();
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
}
@Test void testMergeJoinDeriveRight1() {
final String sql = "select * from sales.bonus s join\n"
+ "(select ename, job, max(sal) from sales.emp group by ename, job)
r\n"
+ "on r.job=s.job and r.ename=s.ename";
- sql(sql).check();
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
}
@Test void testMergeJoinDeriveRight2() {
final String sql = "select * from sales.bonus s join\n"
+ "(select ename, job, mgr, max(sal) from sales.emp group by ename,
job, mgr) r\n"
+ "on r.job=s.job and r.ename=s.ename";
- sql(sql).check();
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // test if "order by mgr desc nulls last" can be pushed through the
projection ("select mgr").
+ @Test void testSortProject() {
+ final String sql = "select mgr from sales.emp order by mgr desc nulls
last";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // test that Sort cannot push through projection because of non-trival call
+ // (e.g. RexCall(sal * -1)). In this example, the reason is that "sal * -1"
+ // creates opposite ordering if Sort is pushed down.
+ @Test void testSortProjectOnRexCall() {
+ final String sql = "select ename, sal * -1 as sal, mgr from\n"
+ + "sales.emp order by ename desc, sal desc, mgr desc nulls last";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // test that Sort can push through projection when cast is monotonic.
+ @Test void testSortProjectWhenCastLeadingToMonotonic() {
+ final String sql = "select deptno from sales.emp order by cast(deptno as
float) desc";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // test that Sort cannot push through projection when cast is not monotonic.
+ @Test void testSortProjectWhenCastLeadingToNonMonotonic() {
+ final String sql = "select deptno from sales.emp order by cast(deptno as
varchar) desc";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // No sort on left join input.
+ @Test void testSortProjectDeriveWhenCastLeadingToMonotonic() {
+ final String sql = "select * from\n"
+ + "(select ename, cast(job as varchar) as job, max_sal + 1 from\n"
+ + "(select ename, job, max(sal) as max_sal from sales.emp group by
ename, job) t) r\n"
+ + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // need sort on left join input.
+ @Test void testSortProjectDeriveOnRexCall() {
+ final String sql = "select * from\n"
+ + "(select ename, sal * -1 as sal, max_job from\n"
+ + "(select ename, sal, max(job) as max_job from sales.emp group by
ename, sal) t) r\n"
+ + "join sales.bonus s on r.sal=s.sal and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // need sort on left join input.
+ @Test void testSortProjectDeriveWhenCastLeadingToNonMonotonic() {
+ final String sql = "select * from\n"
+ + "(select ename, cast(job as numeric) as job, max_sal + 1 from\n"
+ + "(select ename, job, max(sal) as max_sal from sales.emp group by
ename, job) t) r\n"
+ + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // no Sort need for left join input.
+ @Test void testSortProjectDerive3() {
+ final String sql = "select * from\n"
+ + "(select ename, cast(job as varchar) as job, sal + 1 from\n"
+ + "(select ename, job, sal from sales.emp limit 100) t) r\n"
+ + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // need Sort on left join input.
+ @Test void testSortProjectDerive4() {
+ final String sql = "select * from\n"
+ + "(select ename, cast(job as bigint) as job, sal + 1 from\n"
+ + "(select ename, job, sal from sales.emp limit 100) t) r\n"
+ + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // test if top projection can enforce sort when inner sort cannot produce
satisfying ordering.
+ @Test void testSortProjectDerive5() {
+ final String sql = "select ename, empno*-1, job from\n"
+ + "(select * from sales.emp order by ename, empno, job limit 10) order
by ename, job";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ @Test void testSortProjectDerive() {
+ final String sql = "select * from\n"
+ + "(select ename, job, max_sal + 1 from\n"
+ + "(select ename, job, max(sal) as max_sal from sales.emp group by
ename, job) t) r\n"
+ + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // need Sort on projection.
+ @Test void testSortProjectDerive2() {
+ final String sql = "select distinct ename, sal*-2, mgr\n"
+ + "from (select ename, mgr, sal from sales.emp order by ename, mgr,
sal limit 100) t";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ @Test void testSortProjectDerive6() {
+ final String sql = "select comm, deptno, slacker from\n"
+ + "(select * from sales.emp order by comm, deptno, slacker limit 10)
t\n"
+ + "order by comm, slacker";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // test traits push through filter.
+ @Test void testSortFilter() {
+ final String sql = "select ename, job, mgr, max_sal from\n"
+ + "(select ename, job, mgr, max(sal) as max_sal from sales.emp group
by ename, job, mgr) as t\n"
+ + "where max_sal > 1000\n"
+ + "order by mgr desc, ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // test traits derivation in filter.
+ @Test void testSortFilterDerive() {
+ final String sql = "select * from\n"
+ + "(select ename, job, max_sal from\n"
+ + "(select ename, job, max(sal) as max_sal from sales.emp group by
ename, job) t where job > 1000) r\n"
+ + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+}
+
+/**
+ * A helper class that creates Volcano planner with top-down optimization
enabled. This class
+ * allows easy-to-add and easy-to-remove rules from the planner.
+ */
+class Query extends RelOptTestBase {
+ protected DiffRepository getDiffRepos() {
+ return DiffRepository.lookup(TopDownOptTest.class);
+ }
+
+ private String sql;
+ private VolcanoPlanner planner;
+
+ private Query(String sql) {
+ this.sql = sql;
+
+ planner = new VolcanoPlanner();
+ // Always use top-down optimization
+ planner.setTopDownOpt(true);
+ planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
+ planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
+
+ RelOptUtil.registerDefaultRules(planner, false, false);
+
+ // Remove to Keep deterministic join order.
+ planner.removeRule(JoinCommuteRule.INSTANCE);
+ planner.removeRule(JoinPushThroughJoinRule.LEFT);
+ planner.removeRule(JoinPushThroughJoinRule.RIGHT);
+
+ // Always use sorted agg.
+ planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
+ planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
+
+ // pushing down sort should be handled by top-down optimization.
+ planner.removeRule(SortProjectTransposeRule.INSTANCE);
+ planner.removeRule(ProjectSortTransposeRule.INSTANCE);
+ }
+
+ public static Query create(String sql) {
+ return new Query(sql);
+ }
+
+ public Query addRule(RelOptRule ruleToAdd) {
+ planner.addRule(ruleToAdd);
+ return this;
+ }
+
+ public Query addRules(List<RelOptRule> rulesToAdd) {
+ for (RelOptRule ruleToAdd : rulesToAdd) {
+ planner.addRule(ruleToAdd);
+ }
+ return this;
+ }
+
+ public Query removeRule(RelOptRule ruleToRemove) {
+ planner.removeRule(ruleToRemove);
+ return this;
+ }
+
+ public Query removeRules(List<RelOptRule> rulesToRemove) {
+ for (RelOptRule ruleToRemove : rulesToRemove) {
+ planner.removeRule(ruleToRemove);
+ }
+ return this;
+ }
+
+ public void check() {
+ SqlToRelTestBase.Tester tester = createTester().withDecorrelation(true)
+ .withClusterFactory(cluster -> RelOptCluster.create(planner,
cluster.getRexBuilder()));
+
+ new Sql(tester, sql, null, planner,
+ ImmutableMap.of(), ImmutableList.of()).check();
}
}
diff --git a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
index ed71050..e6ce8a0 100644
--- a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
+++ b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
@@ -969,7 +969,7 @@ public class PlannerTest {
RelNode transform = planner.transform(0, traitSet, convert);
assertThat(toString(transform),
containsString(
- "EnumerableMergeJoin(condition=[=($0, $5)], joinType=[inner])"));
+ "EnumerableHashJoin(condition=[=($0, $5)], joinType=[inner])"));
}
/** Test case for
@@ -1030,12 +1030,10 @@ public class PlannerTest {
+ "EnumerableProject(empid=[$2], deptno=[$3], name=[$4], salary=[$5],
commission=[$6], deptno0=[$7], name0=[$8], employees=[$9], location=[ROW($10,
$11)], empid0=[$0], name1=[$1])\n"
+ " EnumerableHashJoin(condition=[=($0, $2)], joinType=[left])\n"
+ " EnumerableTableScan(table=[[hr, dependents]])\n"
- + " EnumerableMergeJoin(condition=[=($1, $5)], joinType=[inner])\n"
- + " EnumerableSort(sort0=[$1], dir0=[ASC])\n"
- + " EnumerableTableScan(table=[[hr, emps]])\n"
- + " EnumerableSort(sort0=[$0], dir0=[ASC])\n"
- + " EnumerableProject(deptno=[$0], name=[$1], employees=[$2],
x=[$3.x], y=[$3.y])\n"
- + " EnumerableTableScan(table=[[hr, depts]])";
+ + " EnumerableHashJoin(condition=[=($1, $5)], joinType=[inner])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n"
+ + " EnumerableProject(deptno=[$0], name=[$1], employees=[$2],
x=[$3.x], y=[$3.y])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])";
checkHeuristic(sql, expected);
}
@@ -1100,11 +1098,10 @@ public class PlannerTest {
+ " EnumerableFilter(condition=[=($9, 'San Francisco')])\n"
+ " EnumerableTableScan(table=[[foodmart2, customer]])\n"
+ " EnumerableHashJoin(condition=[=($6, $20)],
joinType=[inner])\n"
- + " EnumerableMergeJoin(condition=[=($0, $5)],
joinType=[inner])\n"
+ + " EnumerableHashJoin(condition=[=($0, $5)],
joinType=[inner])\n"
+ " EnumerableTableScan(table=[[foodmart2, product_class]])\n"
- + " EnumerableSort(sort0=[$0], dir0=[ASC])\n"
- + " EnumerableFilter(condition=[=($2, 'Washington')])\n"
- + " EnumerableTableScan(table=[[foodmart2, product]])\n"
+ + " EnumerableFilter(condition=[=($2, 'Washington')])\n"
+ + " EnumerableTableScan(table=[[foodmart2, product]])\n"
+ " EnumerableTableScan(table=[[foodmart2,
sales_fact_1997]])\n";
checkBushy(sql, expected);
}
@@ -1131,13 +1128,11 @@ public class PlannerTest {
+ " EnumerableHashJoin(condition=[=($0, $51)],
joinType=[inner])\n"
+ " EnumerableFilter(condition=[=($9, 'San Francisco')])\n"
+ " EnumerableTableScan(table=[[foodmart2, customer]])\n"
- + " EnumerableMergeJoin(condition=[=($6, $20)],
joinType=[inner])\n"
- + " EnumerableSort(sort0=[$6], dir0=[ASC])\n"
- + " EnumerableHashJoin(condition=[=($0, $5)],
joinType=[inner])\n"
- + " EnumerableTableScan(table=[[foodmart2,
product_class]])\n"
- + " EnumerableTableScan(table=[[foodmart2, product]])\n"
- + " EnumerableSort(sort0=[$0], dir0=[ASC])\n"
- + " EnumerableTableScan(table=[[foodmart2,
sales_fact_1997]])\n";
+ + " EnumerableHashJoin(condition=[=($6, $20)],
joinType=[inner])\n"
+ + " EnumerableHashJoin(condition=[=($0, $5)],
joinType=[inner])\n"
+ + " EnumerableTableScan(table=[[foodmart2,
product_class]])\n"
+ + " EnumerableTableScan(table=[[foodmart2, product]])\n"
+ + " EnumerableTableScan(table=[[foodmart2,
sales_fact_1997]])\n";
checkBushy(sql, expected);
}
diff --git a/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
b/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
index 2a38a02..f1ee19b 100644
--- a/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
@@ -38,6 +38,435 @@ EnumerableLimit(fetch=[5])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSortProject">
+ <Resource name="sql">
+ <![CDATA[select mgr from sales.emp order by mgr desc nulls last]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[DESC-nulls-last])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(MGR=[$3])
+ EnumerableSort(sort0=[$3], dir0=[DESC-nulls-last])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDerive2">
+ <Resource name="sql">
+ <![CDATA[
+select distinct ename, sal*-2, mgr
+from (select ename, mgr, sal from sales.emp order by ename, mgr, sal limit
100) t
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1, 2}])
+ LogicalProject(ENAME=[$0], EXPR$1=[*($2, -2)], MGR=[$1])
+ LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC],
dir2=[ASC], fetch=[100])
+ LogicalProject(ENAME=[$1], MGR=[$3], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSortedAggregate(group=[{0, 1, 2}])
+ EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC],
dir2=[ASC])
+ EnumerableProject(ENAME=[$0], EXPR$1=[*($2, -2)], MGR=[$1])
+ EnumerableLimit(fetch=[100])
+ EnumerableProject(ENAME=[$1], MGR=[$3], SAL=[$5])
+ EnumerableSort(sort0=[$1], sort1=[$3], sort2=[$5], dir0=[ASC],
dir1=[ASC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectOnRexCall">
+ <Resource name="sql">
+ <![CDATA[
+select ename, sal * -1 as sal, mgr from
+sales.emp order by ename desc, sal desc, mgr desc nulls last
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[DESC], dir1=[DESC],
dir2=[DESC-nulls-last])
+ LogicalProject(ENAME=[$1], SAL=[*($5, -1)], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[DESC], dir1=[DESC],
dir2=[DESC-nulls-last])
+ EnumerableProject(ENAME=[$1], SAL=[*($5, -1)], MGR=[$3])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDeriveOnRexCall">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, sal * -1 as sal, max_job from
+(select ename, sal, max(job) as max_job from sales.emp group by ename, sal) t)
r
+join sales.bonus s on r.sal=s.sal and r.ename=s.ename
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], SAL=[$1], MAX_JOB=[$2], ENAME0=[$3], JOB=[$4],
SAL0=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($1, $5), =($0, $3))], joinType=[inner])
+ LogicalProject(ENAME=[$0], SAL=[*($1, -1)], MAX_JOB=[$2])
+ LogicalAggregate(group=[{0, 1}], MAX_JOB=[MAX($2)])
+ LogicalProject(ENAME=[$1], SAL=[$5], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableMergeJoin(condition=[AND(=($1, $5), =($0, $3))], joinType=[inner])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableProject(ENAME=[$0], SAL=[*($1, -1)], MAX_JOB=[$2])
+ EnumerableSortedAggregate(group=[{1, 5}], MAX_JOB=[MAX($2)])
+ EnumerableSort(sort0=[$1], sort1=[$5], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectWhenCastLeadingToMonotonic">
+ <Resource name="sql">
+ <![CDATA[select deptno from sales.emp order by cast(deptno as
float) desc]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$1], dir0=[DESC])
+ LogicalProject(DEPTNO=[$7], EXPR$1=[CAST($7):FLOAT NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(DEPTNO=[$7], EXPR$1=[CAST($7):FLOAT NOT NULL])
+ EnumerableSort(sort0=[$7], dir0=[DESC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectWhenCastLeadingToNonMonotonic">
+ <Resource name="sql">
+ <![CDATA[select deptno from sales.emp order by cast(deptno as
varchar) desc]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$1], dir0=[DESC])
+ LogicalProject(DEPTNO=[$7], EXPR$1=[CAST($7):VARCHAR NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSort(sort0=[$1], dir0=[DESC])
+ EnumerableProject(DEPTNO=[$7], EXPR$1=[CAST($7):VARCHAR NOT NULL])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDeriveWhenCastLeadingToNonMonotonic">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, cast(job as numeric) as job, max_sal + 1 from
+(select ename, job, max(sal) as max_sal from sales.emp group by ename, job) t)
r
+join sales.bonus s on r.job=s.job and r.ename=s.ename"
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$4], JOB0=[$5],
SAL=[$6], COMM=[$7])
+ LogicalJoin(condition=[AND(=($3, $8), =($0, $4))], joinType=[inner])
+ LogicalProject(ENAME=[$0], JOB=[CAST($1):DECIMAL(19, 0) NOT NULL],
EXPR$2=[+($2, 1)], JOB0=[CAST(CAST($1):DECIMAL(19, 0) NOT NULL):DECIMAL(19, 19)
NOT NULL])
+ LogicalAggregate(group=[{0, 1}], MAX_SAL=[MAX($2)])
+ LogicalProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):DECIMAL(19, 19) NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$4], JOB0=[$5],
SAL=[$6], COMM=[$7])
+ EnumerableMergeJoin(condition=[AND(=($3, $8), =($0, $4))], joinType=[inner])
+ EnumerableSort(sort0=[$3], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableProject(ENAME=[$0], JOB=[CAST($1):DECIMAL(19, 0) NOT NULL],
EXPR$2=[+($2, 1)], JOB0=[CAST(CAST($1):DECIMAL(19, 0) NOT NULL):DECIMAL(19, 19)
NOT NULL])
+ EnumerableSortedAggregate(group=[{1, 2}], MAX_SAL=[MAX($5)])
+ EnumerableSort(sort0=[$1], sort1=[$2], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableSort(sort0=[$4], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):DECIMAL(19, 19) NOT NULL])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDeriveWhenCastLeadingToMonotonic">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, cast(job as varchar) as job, max_sal + 1 from
+(select ename, job, max(sal) as max_sal from sales.emp group by ename, job) t)
r
+join sales.bonus s on r.job=s.job and r.ename=s.ename"
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ LogicalProject(ENAME=[$0], JOB=[CAST($1):VARCHAR NOT NULL], EXPR$2=[+($2,
1)])
+ LogicalAggregate(group=[{0, 1}], MAX_SAL=[MAX($2)])
+ LogicalProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):VARCHAR NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ EnumerableMergeJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ EnumerableProject(ENAME=[$0], JOB=[CAST($1):VARCHAR NOT NULL],
EXPR$2=[+($2, 1)])
+ EnumerableSortedAggregate(group=[{1, 2}], MAX_SAL=[MAX($5)])
+ EnumerableSort(sort0=[$2], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):VARCHAR NOT NULL])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDerive3">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, cast(job as varchar) as job, sal + 1 from
+(select ename, job, sal from sales.emp limit 100) t) r
+join sales.bonus s on r.job=s.job and r.ename=s.ename
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ LogicalProject(ENAME=[$0], JOB=[CAST($1):VARCHAR NOT NULL], EXPR$2=[+($2,
1)])
+ LogicalSort(fetch=[100])
+ LogicalProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):VARCHAR NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ EnumerableMergeJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ EnumerableProject(ENAME=[$0], JOB=[CAST($1):VARCHAR NOT NULL],
EXPR$2=[+($2, 1)])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableLimit(fetch=[100])
+ EnumerableProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):VARCHAR NOT NULL])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDerive4">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, cast(job as bigint) as job, sal + 1 from
+(select ename, job, sal from sales.emp limit 100) t) r
+join sales.bonus s on r.job=s.job and r.ename=s.ename
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ LogicalProject(ENAME=[$0], JOB=[CAST($1):BIGINT NOT NULL], EXPR$2=[+($2,
1)])
+ LogicalSort(fetch=[100])
+ LogicalProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):BIGINT NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ EnumerableMergeJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableProject(ENAME=[$0], JOB=[CAST($1):BIGINT NOT NULL],
EXPR$2=[+($2, 1)])
+ EnumerableLimit(fetch=[100])
+ EnumerableProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableSort(sort0=[$4], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3],
JOB0=[CAST($1):BIGINT NOT NULL])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDerive5">
+ <Resource name="sql">
+ <![CDATA[
+select ename, empno*-1, job from
+(select * from sales.emp order by ename, empno, job limit 10) order by ename,
job
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$2], dir0=[ASC], dir1=[ASC])
+ LogicalProject(ENAME=[$1], EXPR$1=[*($0, -1)], JOB=[$2])
+ LogicalSort(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC],
dir2=[ASC], fetch=[10])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(ENAME=[$1], EXPR$1=[*($0, -1)], JOB=[$2])
+ EnumerableSort(sort0=[$1], sort1=[$2], dir0=[ASC], dir1=[ASC])
+ EnumerableLimit(fetch=[10])
+ EnumerableSort(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC],
dir1=[ASC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDerive">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, max_sal + 1 from
+(select ename, job, max(sal) as max_sal from sales.emp group by ename, job) t)
r
+join sales.bonus s on r.job=s.job and r.ename=s.ename
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], JOB=[$1], EXPR$2=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner])
+ LogicalProject(ENAME=[$0], JOB=[$1], EXPR$2=[+($2, 1)])
+ LogicalAggregate(group=[{0, 1}], MAX_SAL=[MAX($2)])
+ LogicalProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableMergeJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner])
+ EnumerableProject(ENAME=[$0], JOB=[$1], EXPR$2=[+($2, 1)])
+ EnumerableSortedAggregate(group=[{1, 2}], MAX_SAL=[MAX($5)])
+ EnumerableSort(sort0=[$2], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortProjectDerive6">
+ <Resource name="sql">
+ <![CDATA[
+select comm, deptno, slacker from
+(select * from sales.emp order by comm, deptno, slacker limit 10) t
+order by comm, slacker
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$2], dir0=[ASC], dir1=[ASC])
+ LogicalProject(COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalSort(sort0=[$6], sort1=[$7], sort2=[$8], dir0=[ASC], dir1=[ASC],
dir2=[ASC], fetch=[10])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ EnumerableSort(sort0=[$6], sort1=[$8], dir0=[ASC], dir1=[ASC])
+ EnumerableLimit(fetch=[10])
+ EnumerableSort(sort0=[$6], sort1=[$7], sort2=[$8], dir0=[ASC],
dir1=[ASC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortFilter">
+ <Resource name="sql">
+ <![CDATA[
+select ename, job, mgr, max_sal from
+(select ename, job, mgr, max(sal) as max_sal from sales.emp group by ename,
job, mgr) as t
+where max_sal > 1000
+order by mgr desc, ename
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], sort1=[$0], dir0=[DESC], dir1=[ASC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], MAX_SAL=[$3])
+ LogicalFilter(condition=[>($3, 1000)])
+ LogicalAggregate(group=[{0, 1, 2}], MAX_SAL=[MAX($3)])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableFilter(condition=[>($3, 1000)])
+ EnumerableSortedAggregate(group=[{1, 2, 3}], MAX_SAL=[MAX($5)])
+ EnumerableSort(sort0=[$3], sort1=[$1], sort2=[$2], dir0=[DESC],
dir1=[ASC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortFilterDerive">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, max_sal from
+(select ename, job, max(sal) as max_sal from sales.emp group by ename, job) t
where job > 1000) r
+join sales.bonus s on r.job=s.job and r.ename=s.ename";
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$0], JOB=[$1], MAX_SAL=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner])
+ LogicalProject(ENAME=[$0], JOB=[$1], MAX_SAL=[$2])
+ LogicalFilter(condition=[>(CAST($1):INTEGER NOT NULL, 1000)])
+ LogicalAggregate(group=[{0, 1}], MAX_SAL=[MAX($2)])
+ LogicalProject(ENAME=[$1], JOB=[$2], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableMergeJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner])
+ EnumerableSortedAggregate(group=[{1, 2}], MAX_SAL=[MAX($5)])
+ EnumerableSort(sort0=[$2], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableFilter(condition=[>(CAST($2):INTEGER NOT NULL, 1000)])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSortAggPartialKey">
<Resource name="sql">
<![CDATA[select mgr,deptno,comm,count(*) from sales.emp
diff --git a/core/src/test/resources/saffron.properties
b/core/src/test/resources/saffron.properties
index 1e2802f..523409c 100644
--- a/core/src/test/resources/saffron.properties
+++ b/core/src/test/resources/saffron.properties
@@ -14,4 +14,4 @@
# See the License for the specific language governing permissions and
# limitations under the License.
#
-calcite.planner.topdown.opt=true
+calcite.planner.topdown.opt=false
diff --git a/plus/src/test/java/org/apache/calcite/adapter/tpcds/TpcdsTest.java
b/plus/src/test/java/org/apache/calcite/adapter/tpcds/TpcdsTest.java
index 32bc89a..dec8206 100644
--- a/plus/src/test/java/org/apache/calcite/adapter/tpcds/TpcdsTest.java
+++ b/plus/src/test/java/org/apache/calcite/adapter/tpcds/TpcdsTest.java
@@ -220,33 +220,29 @@ class TpcdsTest {
.withHook(Hook.PROGRAM, handler(true, 2))
.explainMatches("including all attributes ",
CalciteAssert.checkMaskedResultContains(""
- + "EnumerableCalc(expr#0..9=[{inputs}], expr#10=[/($t4, $t3)],
expr#11=[CAST($t10):INTEGER NOT NULL], expr#12=[*($t4, $t4)], expr#13=[/($t12,
$t3)], expr#14=[-($t5, $t13)], expr#15=[1], expr#16=[=($t3, $t15)],
expr#17=[null:BIGINT], expr#18=[-($t3, $t15)], expr#19=[CASE($t16, $t17,
$t18)], expr#20=[/($t14, $t19)], expr#21=[0.5:DECIMAL(2, 1)],
expr#22=[POWER($t20, $t21)], expr#23=[CAST($t22):INTEGER NOT NULL],
expr#24=[/($t23, $t11)], expr#25=[/($t6, $t3)], expr#26=[CAST($ [...]
- + " EnumerableLimit(fetch=[100]): rowcount = 100.0,
cumulative cost = {1.2430341380834431E28 rows, 2.555295487103789E30 cpu, 0.0
io}\n"
- + " EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2],
dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.434029018852197E26,
cumulative cost = {1.2430341380834431E28 rows, 2.555295487103789E30 cpu, 0.0
io}\n"
- + " EnumerableAggregate(group=[{0, 1, 2}],
STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[$SUM0($3)], agg#2=[$SUM0($6)],
agg#3=[$SUM0($4)], agg#4=[$SUM0($7)], agg#5=[$SUM0($5)], agg#6=[$SUM0($8)]):
rowcount = 5.434029018852197E26, cumulative cost = {1.1886938478949211E28 rows,
1.2172225014174444E30 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..211=[{inputs}],
expr#212=[*($t89, $t89)], expr#213=[*($t140, $t140)], expr#214=[*($t196,
$t196)], I_ITEM_ID=[$t58], I_ITEM_DESC=[$t61], S_STATE=[$t24],
SS_QUANTITY=[$t89], SR_RETURN_QUANTITY=[$t140], CS_QUANTITY=[$t196],
$f6=[$t212], $f7=[$t213], $f8=[$t214]): rowcount = 5.434029018852197E27,
cumulative cost = {1.0868058037914423E28 rows, 1.2172225014174444E30 cpu, 0.0
io}\n"
- + " EnumerableMergeJoin(condition=[AND(=($82, $133),
=($81, $132), =($88, $139))], joinType=[inner]): rowcount =
5.434029018852197E27, cumulative cost = {5.434029019062226E27 rows,
1.1945521881363807E21 cpu, 0.0 io}\n"
- + " EnumerableSort(sort0=[$82], sort1=[$81],
sort2=[$88], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount =
2.3008402586892598E13, cumulative cost = {7.159725725974736E13 rows,
2.8882188423696301E17 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $86)],
joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost =
{4.8588854672854766E13 rows, 7281360.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
STORE]]): rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $50)],
joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost =
{1.279800620431234E13 rows, 7281347.0 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], proj#0..27=[{exprs}],
$condition=[$t29]): rowcount = 10957.35, cumulative cost = {84006.35 rows,
4382941.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0,
$24)], joinType=[inner]): rowcount = 7.7770908E9, cumulative cost =
{7.783045975286664E9 rows, 2898406.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0
io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
2880405.0 cpu, 0.0 io}\n"
- + " EnumerableSort(sort0=[$31], sort1=[$30],
sort2=[$37], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount =
6.9978029381741304E16, cumulative cost = {1.3995607297693488E17 rows,
1.1942633662521438E21 cpu, 0.0 io}\n"
- + " EnumerableMergeJoin(condition=[AND(=($31,
$79), =($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16,
cumulative cost = {6.9978043595193584E16 rows, 2.473747714463278E13 cpu, 0.0
io}\n"
- + " EnumerableSort(sort0=[$31], sort1=[$30],
dir0=[ASC], dir1=[ASC]): rowcount = 7.87597881975E8, cumulative cost =
{1.5760413031966867E9 rows, 3.097646138009654E12 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0,
$28)], joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost =
{7.884434212216867E8 rows, 5035701.0 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows,
287515.0 cpu, 0.0 io}\n"
- + " EnumerableSort(sort0=[$31], sort1=[$43],
dir0=[ASC], dir1=[ASC]): rowcount = 3.94888649445E9, cumulative cost =
{7.900926597146687E9 rows, 2.163983100662313E13 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0,
$28)], joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost =
{3.9520401026966867E9 rows, 6189735.0 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
CATALOG_SALES]]): rowcount = 1441548.0, cumulative cost = {1441548.0 rows,
1441549.0 cpu, 0.0 io}\n"));
+ + "EnumerableCalc(expr#0..9=[{inputs}], expr#10=[/($t4, $t3)],
expr#11=[CAST($t10):INTEGER NOT NULL], expr#12=[*($t4, $t4)], expr#13=[/($t12,
$t3)], expr#14=[-($t5, $t13)], expr#15=[1], expr#16=[=($t3, $t15)],
expr#17=[null:BIGINT], expr#18=[-($t3, $t15)], expr#19=[CASE($t16, $t17,
$t18)], expr#20=[/($t14, $t19)], expr#21=[0.5:DECIMAL(2, 1)],
expr#22=[POWER($t20, $t21)], expr#23=[CAST($t22):INTEGER NOT NULL],
expr#24=[/($t23, $t11)], expr#25=[/($t6, $t3)], expr#26=[CAST($ [...]
+ + " EnumerableLimit(fetch=[100]): rowcount = 100.0,
cumulative cost = {1.2435775409784036E28 rows, 2.555295485909236E30 cpu, 0.0
io}\n"
+ + " EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2],
dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.434029018852197E26,
cumulative cost = {1.2435775409784036E28 rows, 2.555295485909236E30 cpu, 0.0
io}\n"
+ + " EnumerableAggregate(group=[{0, 1, 2}],
STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[$SUM0($3)], agg#2=[$SUM0($6)],
agg#3=[$SUM0($4)], agg#4=[$SUM0($7)], agg#5=[$SUM0($5)], agg#6=[$SUM0($8)]):
rowcount = 5.434029018852197E26, cumulative cost = {1.1892372507898816E28 rows,
1.2172225002228922E30 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..211=[{inputs}],
expr#212=[*($t89, $t89)], expr#213=[*($t140, $t140)], expr#214=[*($t196,
$t196)], I_ITEM_ID=[$t58], I_ITEM_DESC=[$t61], S_STATE=[$t24],
SS_QUANTITY=[$t89], SR_RETURN_QUANTITY=[$t140], CS_QUANTITY=[$t196],
$f6=[$t212], $f7=[$t213], $f8=[$t214]): rowcount = 5.434029018852197E27,
cumulative cost = {1.0873492066864028E28 rows, 1.2172225002228922E30 cpu, 0.0
io}\n"
+ + " EnumerableHashJoin(condition=[AND(=($82, $133),
=($81, $132), =($88, $139))], joinType=[inner]): rowcount =
5.434029018852197E27, cumulative cost = {5.439463048011832E27 rows, 1.8506796E7
cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $86)],
joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost =
{4.8588854672854766E13 rows, 7281360.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS, STORE]]):
rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $50)],
joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost =
{1.279800620431234E13 rows, 7281347.0 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], proj#0..27=[{exprs}],
$condition=[$t29]): rowcount = 10957.35, cumulative cost = {84006.35 rows,
4382941.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $24)],
joinType=[inner]): rowcount = 7.7770908E9, cumulative cost =
{7.783045975286664E9 rows, 2898406.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0
io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
2880405.0 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[AND(=($31, $79),
=($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16, cumulative
cost = {7.0048032234040472E16 rows, 1.1225436E7 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $28)],
joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost =
{7.884434212216867E8 rows, 5035701.0 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows,
287515.0 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $28)],
joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost =
{3.9520401026966867E9 rows, 6189735.0 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
CATALOG_SALES]]): rowcount = 1441548.0, cumulative cost = {1441548.0 rows,
1441549.0 cpu, 0.0 io}\n"));
}
@Disabled("throws 'RuntimeException: Cannot convert null to long'")
