You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by ch...@apache.org on 2020/06/12 08:31:42 UTC
[calcite] branch master updated: [CALCITE-4016] Support trait
propagation for EnumerableCalc
This is an automated email from the ASF dual-hosted git repository.
chunwei 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 3c317b6 [CALCITE-4016] Support trait propagation for EnumerableCalc
3c317b6 is described below
commit 3c317b608344ab2ebd6d65b67f647bddf87376b3
Author: Chunwei Lei <ch...@gmail.com>
AuthorDate: Tue Jun 9 11:32:28 2020 +0800
[CALCITE-4016] Support trait propagation for EnumerableCalc
---
.../adapter/enumerable/EnumTraitsUtils.java | 151 ++++++++++++++++
.../calcite/adapter/enumerable/EnumerableCalc.java | 20 +++
.../adapter/enumerable/EnumerableProject.java | 108 +-----------
.../org/apache/calcite/test/TopDownOptTest.java | 108 ++++++++++++
.../org/apache/calcite/test/TopDownOptTest.xml | 193 +++++++++++++++++++++
5 files changed, 476 insertions(+), 104 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumTraitsUtils.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumTraitsUtils.java
new file mode 100644
index 0000000..2be158a
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumTraitsUtils.java
@@ -0,0 +1,151 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.enumerable;
+
+import org.apache.calcite.linq4j.Ord;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+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.mapping.MappingType;
+import org.apache.calcite.util.mapping.Mappings;
+
+import com.google.common.collect.ImmutableList;
+
+import org.apiguardian.api.API;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Objects;
+
+/**
+ * Utilities for traits propagation.
+ */
+@API(since = "1.24", status = API.Status.INTERNAL)
+class EnumTraitsUtils {
+
+ private EnumTraitsUtils() {}
+
+ /**
+ * Determine whether there is mapping between project input and output fields.
+ * Bail out if sort relies on non-trivial expressions.
+ */
+ private static boolean isCollationOnTrivialExpr(
+ List<RexNode> projects, RelDataTypeFactory typeFactory,
+ Mappings.TargetMapping map, RelFieldCollation fc, boolean passDown) {
+ final int index = fc.getFieldIndex();
+ int target = map.getTargetOpt(index);
+ if (target < 0) {
+ return false;
+ }
+
+ final RexNode node = passDown ? projects.get(index) : projects.get(target);
+ if (node.isA(SqlKind.CAST)) {
+ // Check whether it is a monotonic preserving cast
+ final RexCall cast = (RexCall) node;
+ RelFieldCollation newFieldCollation = Objects.requireNonNull(RexUtil.apply(map, fc));
+ final RexCallBinding binding =
+ RexCallBinding.create(typeFactory, cast,
+ ImmutableList.of(RelCollations.of(newFieldCollation)));
+ if (cast.getOperator().getMonotonicity(binding)
+ == SqlMonotonicity.NOT_MONOTONIC) {
+ return false;
+ }
+ }
+
+ return true;
+ }
+
+ static Pair<RelTraitSet, List<RelTraitSet>> passThroughTraitsForProject(
+ RelTraitSet required,
+ List<RexNode> exps,
+ RelDataType inputRowType,
+ RelDataTypeFactory typeFactory,
+ RelTraitSet currentTraits) {
+ final RelCollation collation = required.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+
+ final Mappings.TargetMapping map =
+ RelOptUtil.permutationIgnoreCast(
+ exps, inputRowType);
+
+ if (collation.getFieldCollations().stream().anyMatch(
+ rc -> !isCollationOnTrivialExpr(exps, typeFactory,
+ map, rc, true))) {
+ return null;
+ }
+
+ final RelCollation newCollation = collation.apply(map);
+ return Pair.of(currentTraits.replace(collation),
+ ImmutableList.of(currentTraits.replace(newCollation)));
+ }
+
+ static Pair<RelTraitSet, List<RelTraitSet>> deriveTraitsForProject(
+ RelTraitSet childTraits, int childId, List<RexNode> exps,
+ RelDataType inputRowType, RelDataTypeFactory typeFactory, RelTraitSet currentTraits) {
+ final RelCollation collation = childTraits.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+
+ final int maxField = Math.max(exps.size(),
+ inputRowType.getFieldCount());
+ Mappings.TargetMapping mapping = Mappings
+ .create(MappingType.FUNCTION, maxField, maxField);
+ for (Ord<RexNode> node : Ord.zip(exps)) {
+ 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(exps, typeFactory, mapping, rc, false)) {
+ collationFieldsToDerive.add(rc);
+ } else {
+ break;
+ }
+ }
+
+ if (collationFieldsToDerive.size() > 0) {
+ final RelCollation newCollation = RelCollations
+ .of(collationFieldsToDerive).apply(mapping);
+ return Pair.of(currentTraits.replace(newCollation),
+ ImmutableList.of(currentTraits.replace(collation)));
+ } else {
+ return null;
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableCalc.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableCalc.java
index 4a392ff..f899624 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableCalc.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableCalc.java
@@ -39,6 +39,7 @@ 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.RexBuilder;
+import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.rex.RexSimplify;
import org.apache.calcite.rex.RexUtil;
@@ -49,6 +50,7 @@ import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
@@ -265,6 +267,24 @@ public class EnumerableCalc extends Calc implements EnumerableRel {
return implementor.result(physType, builder.toBlock());
}
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
+ final RelTraitSet required) {
+ final List<RexNode> exps = Lists.transform(program.getProjectList(),
+ program::expandLocalRef);
+
+ return EnumTraitsUtils.passThroughTraitsForProject(required, exps,
+ input.getRowType(), input.getCluster().getTypeFactory(), traitSet);
+ }
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> deriveTraits(
+ final RelTraitSet childTraits, final int childId) {
+ final List<RexNode> exps = Lists.transform(program.getProjectList(),
+ program::expandLocalRef);
+
+ return EnumTraitsUtils.deriveTraitsForProject(childTraits, childId, exps,
+ input.getRowType(), input.getCluster().getTypeFactory(), traitSet);
+ }
+
public RexProgram getProgram() {
return program;
}
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 f38e348..d940d97 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,36 +16,21 @@
*/
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
@@ -106,98 +91,13 @@ public class EnumerableProject extends Project implements EnumerableRel {
@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)));
+ return EnumTraitsUtils.passThroughTraitsForProject(required, exps,
+ input.getRowType(), input.getCluster().getTypeFactory(), traitSet);
}
@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;
+ return EnumTraitsUtils.deriveTraitsForProject(childTraits, childId, exps,
+ input.getRowType(), input.getCluster().getTypeFactory(), traitSet);
}
}
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 28950fd..bac5e4d 100644
--- a/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
@@ -23,9 +23,11 @@ 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.FilterToCalcRule;
import org.apache.calcite.rel.rules.JoinCommuteRule;
import org.apache.calcite.rel.rules.JoinPushThroughJoinRule;
import org.apache.calcite.rel.rules.JoinToCorrelateRule;
+import org.apache.calcite.rel.rules.ProjectToCalcRule;
import org.apache.calcite.rel.rules.SemiJoinRule;
import org.apache.calcite.rel.rules.SortJoinCopyRule;
import org.apache.calcite.rel.rules.SortJoinTransposeRule;
@@ -585,6 +587,112 @@ class TopDownOptTest extends RelOptTestBase {
.removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
.check();
}
+
+ // test if "order by mgr desc nulls last" can be pushed through the calc ("select mgr").
+ @Test void testSortCalc() {
+ final String sql = "select mgr from sales.emp order by mgr desc nulls last";
+ Query.create(sql)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .check();
+ }
+
+ // test that Sort cannot push through calc 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 testSortCalcOnRexCall() {
+ 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)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .check();
+ }
+
+ // test that Sort can push through calc when cast is monotonic.
+ @Test void testSortCalcWhenCastLeadingToMonotonic() {
+ final String sql = "select cast(deptno as float) from sales.emp order by deptno desc";
+ Query.create(sql)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .check();
+ }
+
+ // test that Sort cannot push through calc when cast is not monotonic.
+ @Test void testSortCalcWhenCastLeadingToNonMonotonic() {
+ final String sql = "select deptno from sales.emp order by cast(deptno as varchar) desc";
+ Query.create(sql)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .check();
+ }
+
+ // test traits push through calc with filter.
+ @Test void testSortCalcWithFilter() {
+ 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)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(FilterToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_FILTER_RULE)
+ .check();
+ }
+
+ // Do not need Sort for calc.
+ @Test void testSortCalcDerive1() {
+ 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)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
+
+ // Need Sort for calc.
+ @Test void testSortCalcDerive2() {
+ 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)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .check();
+ }
+
+ // Do not need Sort for left join input.
+ @Test void testSortCalcDerive3() {
+ 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)
+ .addRule(ProjectToCalcRule.INSTANCE)
+ .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
+ .check();
+ }
}
/**
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 eb266ba..2f4fa70 100644
--- a/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
@@ -1229,4 +1229,197 @@ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSortCalc">
+ <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[
+EnumerableCalc(expr#0..8=[{inputs}], MGR=[$t3])
+ EnumerableSort(sort0=[$3], dir0=[DESC-nulls-last])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortCalcDerive1">
+ <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])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t2, $t3)], proj#0..1=[{exprs}], EXPR$2=[$t4])
+ 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="testSortCalcDerive2">
+ <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])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[-2], expr#4=[*($t2, $t3)], ENAME=[$t0], EXPR$1=[$t4], MGR=[$t1])
+ EnumerableLimit(fetch=[100])
+ EnumerableCalc(expr#0..8=[{inputs}], ENAME=[$t1], MGR=[$t3], SAL=[$t5])
+ EnumerableSort(sort0=[$1], sort1=[$3], sort2=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortCalcDerive3">
+ <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[
+EnumerableCalc(expr#0..7=[{inputs}], proj#0..6=[{exprs}])
+ EnumerableMergeJoin(condition=[AND(=($1, $7), =($0, $3))], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t1):VARCHAR NOT NULL], expr#4=[1], expr#5=[+($t2, $t4)], ENAME=[$t0], JOB=[$t3], EXPR$2=[$t5])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableLimit(fetch=[100])
+ EnumerableCalc(expr#0..8=[{inputs}], ENAME=[$t1], JOB=[$t2], SAL=[$t5])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):VARCHAR NOT NULL], proj#0..4=[{exprs}])
+ EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortCalcOnRexCall">
+ <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])
+ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[-1], expr#10=[*($t5, $t9)], ENAME=[$t1], SAL=[$t10], MGR=[$t3])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortCalcWhenCastLeadingToNonMonotonic">
+ <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])
+ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[CAST($t7):VARCHAR NOT NULL], DEPTNO=[$t7], EXPR$1=[$t9])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortCalcWhenCastLeadingToMonotonic">
+ <Resource name="sql">
+ <![CDATA[select cast(deptno as float) from sales.emp order by deptno desc]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$1], dir0=[DESC])
+ LogicalProject(EXPR$0=[CAST($7):FLOAT NOT NULL], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableCalc(expr#0..8=[{inputs}], expr#9=[CAST($t7):FLOAT NOT NULL], EXPR$0=[$t9], DEPTNO=[$t7])
+ EnumerableSort(sort0=[$7], dir0=[DESC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortCalcWithFilter">
+ <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[
+EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1000], expr#5=[>($t3, $t4)], proj#0..3=[{exprs}], $condition=[$t5])
+ 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>
</Root>