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 87c5bd2 [CALCITE-3346] Re-enable some ignored tests in
RelOptRuleTests (Wang Yanlin)
87c5bd2 is described below
commit 87c5bd29364a23ab3c06f289ffce1300e26e8c46
Author: yanlin-Lynn <[email protected]>
AuthorDate: Sun Sep 29 11:45:05 2019 +0800
[CALCITE-3346] Re-enable some ignored tests in RelOptRuleTests (Wang Yanlin)
---
.../org/apache/calcite/test/RelOptRulesTest.java | 238 ++++++++++++--------
.../org/apache/calcite/test/RelOptRulesTest.xml | 242 ++++++++++++++-------
2 files changed, 309 insertions(+), 171 deletions(-)
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index de55728..4d7a4b9 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -58,10 +58,6 @@ import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalTableModify;
import org.apache.calcite.rel.logical.LogicalTableScan;
-import org.apache.calcite.rel.metadata.CachingRelMetadataProvider;
-import org.apache.calcite.rel.metadata.ChainedRelMetadataProvider;
-import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider;
-import org.apache.calcite.rel.metadata.RelMetadataProvider;
import org.apache.calcite.rel.rules.AggregateCaseToFilterRule;
import org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule;
import org.apache.calcite.rel.rules.AggregateExtractProjectRule;
@@ -166,7 +162,6 @@ import org.junit.Assert;
import org.junit.Ignore;
import org.junit.Test;
-import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.EnumSet;
@@ -179,9 +174,7 @@ import static org.apache.calcite.plan.RelOptRule.none;
import static org.apache.calcite.plan.RelOptRule.operand;
import static org.apache.calcite.plan.RelOptRule.operandJ;
-import static org.hamcrest.CoreMatchers.notNullValue;
import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertThat;
/**
* Unit test for rules in {@code org.apache.calcite.rel} and subpackages.
@@ -1873,14 +1866,13 @@ public class RelOptRulesTest extends RelOptTestBase {
SqlToRelTestBase.assertValid(output);
}
- @Ignore("cycles")
@Test public void testMergeFilterWithJoinCondition() throws Exception {
HepProgram program = new HepProgramBuilder()
.addRuleInstance(TableScanRule.INSTANCE)
.addRuleInstance(JoinExtractFilterRule.INSTANCE)
.addRuleInstance(FilterToCalcRule.INSTANCE)
- .addRuleInstance(CalcMergeRule.INSTANCE)
.addRuleInstance(ProjectToCalcRule.INSTANCE)
+ .addRuleInstance(CalcMergeRule.INSTANCE)
.build();
checkPlanning(program,
@@ -2140,27 +2132,19 @@ public class RelOptRulesTest extends RelOptTestBase {
sql(sql).with(program).checkUnchanged();
}
- @Ignore("cycles")
@Test public void testHeterogeneousConversion() throws Exception {
// This one tests the planner's ability to correctly
// apply different converters on top of a common
// sub-expression. The common sub-expression is the
// reference to the table sales.emps. On top of that
// are two projections, unioned at the top. For one
- // of the projections, we force a Fennel implementation.
- // For the other, we force a Java implementation.
- // Then, we request conversion from Fennel to Java,
- // and verify that it only applies to one usage of the
- // table, not both (which would be incorrect).
+ // of the projections, transfer it to calc, for the other,
+ // keep it unchanged.
HepProgram program = new HepProgramBuilder()
.addRuleInstance(TableScanRule.INSTANCE)
- .addRuleInstance(ProjectToCalcRule.INSTANCE)
-
- // Control the calc conversion.
+ // Control the calc conversion.
.addMatchLimit(1)
-
- // Let the converter rule fire to its heart's content.
- .addMatchLimit(HepProgram.MATCH_UNTIL_FIXPOINT)
+ .addRuleInstance(ProjectToCalcRule.INSTANCE)
.build();
checkPlanning(program,
@@ -2934,7 +2918,6 @@ public class RelOptRulesTest extends RelOptTestBase {
checkPlanning(program, sql);
}
- @Ignore // Calcite does not support INSERT yet
@Test public void testReduceValuesNull() throws Exception {
// The NULL literal presents pitfalls for value-reduction. Only
// an INSERT statement contains un-CASTed NULL values.
@@ -2942,7 +2925,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ValuesReduceRule.PROJECT_INSTANCE)
.build();
checkPlanning(program,
- "insert into sales.depts(deptno,name) values (NULL, 'null')");
+ "insert into EMPNULLABLES(EMPNO, ENAME, JOB) (select 0, 'null',
NULL)");
}
@Test public void testReduceValuesToEmpty() throws Exception {
@@ -3244,7 +3227,6 @@ public class RelOptRulesTest extends RelOptTestBase {
}
}
- @Ignore // Calcite does not support INSERT yet
@Test public void testReduceCastsNullable() throws Exception {
HepProgram program = new HepProgramBuilder()
@@ -3260,8 +3242,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ReduceExpressionsRule.CALC_INSTANCE)
.build();
checkPlanning(program,
- "insert into sales.depts(name) "
- + "select cast(gender as varchar(128)) from sales.emps");
+ "insert into sales.dept(deptno, name) "
+ + "select empno, cast(job as varchar(128)) from
sales.empnullables");
}
private void basePushAggThroughUnion() throws Exception {
@@ -3660,138 +3642,192 @@ public class RelOptRulesTest extends RelOptTestBase {
sql(sql).with(program).check();
}
- private void transitiveInference(RelOptRule... extraRules) throws Exception {
- final DiffRepository diffRepos = getDiffRepos();
- final String sql = diffRepos.expand(null, "${sql}");
-
+ /**
+ * Create a {@link HepProgram} with common transitive rules.
+ */
+ private HepProgram getTransitiveProgram() {
final HepProgram program = new HepProgramBuilder()
.addRuleInstance(FilterJoinRule.DUMB_FILTER_ON_JOIN)
.addRuleInstance(FilterJoinRule.JOIN)
.addRuleInstance(FilterProjectTransposeRule.INSTANCE)
.addRuleInstance(FilterSetOpTransposeRule.INSTANCE)
.build();
- final HepPlanner planner = new HepPlanner(program);
-
- final RelRoot root = tester.convertSqlToRel(sql);
- final RelNode relInitial = root.rel;
-
- assertThat(relInitial, notNullValue());
-
- List<RelMetadataProvider> list = new ArrayList<>();
- list.add(DefaultRelMetadataProvider.INSTANCE);
- planner.registerMetadataProviders(list);
- RelMetadataProvider plannerChain = ChainedRelMetadataProvider.of(list);
- relInitial.getCluster().setMetadataProvider(
- new CachingRelMetadataProvider(plannerChain, planner));
-
- planner.setRoot(relInitial);
- RelNode relBefore = planner.findBestExp();
-
- String planBefore = NL + RelOptUtil.toString(relBefore);
- diffRepos.assertEquals("planBefore", "${planBefore}", planBefore);
-
- HepProgram program2 = new HepProgramBuilder()
- .addMatchOrder(HepMatchOrder.BOTTOM_UP)
- .addRuleInstance(FilterJoinRule.DUMB_FILTER_ON_JOIN)
- .addRuleInstance(FilterJoinRule.JOIN)
- .addRuleInstance(FilterProjectTransposeRule.INSTANCE)
- .addRuleInstance(FilterSetOpTransposeRule.INSTANCE)
- .addRuleInstance(JoinPushTransitivePredicatesRule.INSTANCE)
- .addRuleCollection(Arrays.asList(extraRules))
- .build();
- final HepPlanner planner2 = new HepPlanner(program2);
- planner.registerMetadataProviders(list);
- planner2.setRoot(relBefore);
- RelNode relAfter = planner2.findBestExp();
-
- String planAfter = NL + RelOptUtil.toString(relAfter);
- diffRepos.assertEquals("planAfter", "${planAfter}", planAfter);
+ return program;
}
@Test public void testTransitiveInferenceJoin() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno where e.deptno > 7";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceProject() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from (select * from sales.emp where deptno >
7) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceAggregate() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from (select deptno, count(*) from sales.emp
where deptno > 7\n"
+ + "group by deptno) d inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceUnion() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from\n"
+ + "(select deptno from sales.emp where deptno > 7\n"
+ + "union all select deptno from sales.emp where deptno > 10) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceJoin3way() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno\n"
+ + "inner join sales.emp f on e.deptno = f.deptno\n"
+ + "where d.deptno > 7";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceJoin3wayAgg() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from\n"
+ + "(select deptno, count(*) from sales.emp where deptno > 7 group by
deptno) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno\n"
+ + "inner join sales.emp f on e.deptno = f.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceLeftOuterJoin() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "left outer join sales.emp e on d.deptno = e.deptno\n"
+ + "where d.deptno > 7 and e.deptno > 9";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceRightOuterJoin() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "right outer join sales.emp e on d.deptno = e.deptno\n"
+ + "where d.deptno > 7 and e.deptno > 9";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceFullOuterJoin() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d full outer join sales.emp
e\n"
+ + "on d.deptno = e.deptno where d.deptno > 7 and e.deptno > 9";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).checkUnchanged();
}
@Test public void testTransitiveInferencePreventProjectPullUp()
throws Exception {
- transitiveInference();
+ final String sql = "select 1 from (select comm as deptno from sales.emp
where deptno > 7) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).checkUnchanged();
}
@Test public void testTransitiveInferencePullUpThruAlias() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from (select comm as deptno from sales.emp
where comm > 7) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceConjunctInPullUp() throws Exception
{
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno\n"
+ + "where d.deptno in (7, 9) or d.deptno > 10";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceNoPullUpExprs() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno\n"
+ + "where d.deptno in (7, 9) or d.comm > 10";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).checkUnchanged();
}
@Test public void testTransitiveInferenceUnion3way() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from\n"
+ + "(select deptno from sales.emp where deptno > 7\n"
+ + "union all\n"
+ + "select deptno from sales.emp where deptno > 10\n"
+ + "union all\n"
+ + "select deptno from sales.emp where deptno > 1) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
- @Ignore("not working")
@Test public void testTransitiveInferenceUnion3wayOr() throws Exception {
- transitiveInference();
+ final String sql = "select 1 from\n"
+ + "(select empno, deptno from sales.emp where deptno > 7 or empno <
10\n"
+ + "union all\n"
+ + "select empno, deptno from sales.emp where deptno > 10 or empno <
deptno\n"
+ + "union all\n"
+ + "select empno, deptno from sales.emp where deptno > 1) d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).checkUnchanged();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-443";>[CALCITE-443]
* getPredicates from a union is not correct</a>. */
@Test public void testTransitiveInferenceUnionAlwaysTrue() throws Exception {
- transitiveInference();
+ final String sql = "select d.deptno, e.deptno from\n"
+ + "(select deptno from sales.emp where deptno < 4) d\n"
+ + "inner join\n"
+ + "(select deptno from sales.emp where deptno > 7\n"
+ + "union all select deptno from sales.emp) e\n"
+ + "on d.deptno = e.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testTransitiveInferenceConstantEquiPredicate()
throws Exception {
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno where 1 = 1";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).checkUnchanged();
}
@Test public void testTransitiveInferenceComplexPredicate() throws Exception
{
- transitiveInference();
+ final String sql = "select 1 from sales.emp d\n"
+ + "inner join sales.emp e on d.deptno = e.deptno\n"
+ + "where d.deptno > 7 and e.sal = e.deptno and d.comm = d.deptno\n"
+ + "and d.comm + d.deptno > d.comm/2";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE).check();
}
@Test public void testPullConstantIntoProject() throws Exception {
- transitiveInference(ReduceExpressionsRule.PROJECT_INSTANCE);
+ final String sql = "select deptno, deptno + 1, empno + deptno\n"
+ + "from sales.emp where deptno = 10";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE,
+ ReduceExpressionsRule.PROJECT_INSTANCE)
+ .check();
}
@Test public void testPullConstantIntoFilter() throws Exception {
- transitiveInference(ReduceExpressionsRule.FILTER_INSTANCE);
+ final String sql = "select * from (select * from sales.emp where deptno =
10)\n"
+ + "where deptno + 5 > empno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
/** Test case for
@@ -3799,17 +3835,35 @@ public class RelOptRulesTest extends RelOptTestBase {
* Remove predicates from Filter if they can be proved to be always true or
* false</a>. */
@Test public void testSimplifyFilter() throws Exception {
- transitiveInference(ReduceExpressionsRule.FILTER_INSTANCE);
+ final String sql = "select * from (select * from sales.emp where deptno >
10)\n"
+ + "where empno > 3 and deptno > 5";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
@Test public void testPullConstantIntoJoin() throws Exception {
- transitiveInference(ReduceExpressionsRule.JOIN_INSTANCE);
+ final String sql = "select * from (select * from sales.emp where empno =
10) as e\n"
+ + "left join sales.dept as d on e.empno = d.deptno";
+ sql(sql).withPre(getTransitiveProgram())
+ .withRule(JoinPushTransitivePredicatesRule.INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test public void testPullConstantIntoJoin2() throws Exception {
- transitiveInference(ReduceExpressionsRule.JOIN_INSTANCE,
- ReduceExpressionsRule.PROJECT_INSTANCE,
- FilterProjectTransposeRule.INSTANCE);
+ final String sql = "select * from (select * from sales.emp where empno =
10) as e\n"
+ + "join sales.dept as d on e.empno = d.deptno and e.deptno + e.empno =
d.deptno + 5";
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(JoinPushTransitivePredicatesRule.INSTANCE)
+ .addRuleCollection(
+ ImmutableList.of(
+ ReduceExpressionsRule.PROJECT_INSTANCE,
+ FilterProjectTransposeRule.INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE))
+ .build();
+ sql(sql).withPre(getTransitiveProgram()).with(program).check();
}
/** Test case for
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 4389093..3ecd662 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2485,6 +2485,24 @@ LogicalValues(tuples=[[{ 11, 1, 10 }, { 23, 3, 20 }]])
]]>
</Resource>
</TestCase>
+ <TestCase name="testReduceValuesNull">
+ <Resource name="sql">
+ <![CDATA[insert into EMPNULLABLES(EMPNO, ENAME, JOB) (select 0,
'null', NULL)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT],
flattened=[false])
+ LogicalProject(EMPNO=[0], ENAME=['null'], JOB=[null:VARCHAR(10)],
MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[null:INTEGER],
COMM=[null:INTEGER], DEPTNO=[null:INTEGER], SLACKER=[null:BOOLEAN])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT],
flattened=[false])
+ LogicalValues(tuples=[[{ 0, 'null', null, null, null, null, null, null, null
}]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testReduceValuesToEmpty">
<Resource name="sql">
<![CDATA[select a + b as x, b, a from (values (10, 1), (30, 7)) as
t(a, b) where a - b < 0]]>
@@ -4781,11 +4799,19 @@ LogicalProject(ENAME=[$1])
</Resource>
<Resource name="planBefore">
<![CDATA[
-ProjectRel(DNAME=[$10], ENAME=[$1])
- FilterRel(condition=[=($10, 'Propane')])
- JoinRel(condition=[=($7, $9)], joinType=[inner])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+LogicalProject(DNAME=[$10], ENAME=[$1])
+ LogicalFilter(condition=[=($10, 'Propane')])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalCalc(expr#0..10=[{inputs}], expr#11=[=($t7, $t9)],
expr#12=['Propane':VARCHAR(10)], expr#13=[=($t10, $t12)], expr#14=[AND($t11,
$t13)], DNAME=[$t10], ENAME=[$t1], $condition=[$t14])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -5014,11 +5040,20 @@ LogicalProject(ENAME=[$1])
</Resource>
<Resource name="planBefore">
<![CDATA[
-UnionRel(all=[true])
- ProjectRel(EXPR$0=[UPPER($1)])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- ProjectRel(EXPR$0=[LOWER($1)])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
+LogicalUnion(all=[true])
+ LogicalProject(EXPR$0=[UPPER($1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EXPR$0=[LOWER($1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalCalc(expr#0..8=[{inputs}], expr#9=[UPPER($t1)], EXPR$0=[$t9])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EXPR$0=[LOWER($1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -5070,7 +5105,8 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
</TestCase>
<TestCase name="testTransitiveInferenceJoin">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d inner join sales.emp e on
d.deptno = e.deptno where e.deptno > 7]]>
+ <![CDATA[select 1 from sales.emp d
+inner join sales.emp e on d.deptno = e.deptno where e.deptno > 7]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5094,7 +5130,8 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceProject">
<Resource name="sql">
- <![CDATA[select 1 from (select * from sales.emp where deptno > 7)
d inner join sales.emp e on d.deptno = e.deptno ]]>
+ <![CDATA[select 1 from (select * from sales.emp where deptno > 7) d
+inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5120,7 +5157,8 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceAggregate">
<Resource name="sql">
- <![CDATA[select 1 from (select deptno, count(*) from sales.emp
where deptno > 7 group by deptno) d inner join sales.emp e on d.deptno =
e.deptno ]]>
+ <![CDATA[select 1 from (select deptno, count(*) from sales.emp
where deptno > 7
+group by deptno) d inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5148,7 +5186,10 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceUnion">
<Resource name="sql">
- <![CDATA[select 1 from (select deptno from sales.emp where deptno
> 7 union all select deptno from sales.emp where deptno > 10) d inner join
sales.emp e on d.deptno = e.deptno ]]>
+ <![CDATA[select 1 from
+(select deptno from sales.emp where deptno > 7
+union all select deptno from sales.emp where deptno > 10) d
+inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5182,7 +5223,10 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceJoin3way">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d inner join sales.emp e on
d.deptno = e.deptno inner join sales.emp f on e.deptno = f.deptno where
d.deptno > 7]]>
+ <![CDATA[select 1 from sales.emp d
+inner join sales.emp e on d.deptno = e.deptno
+inner join sales.emp f on e.deptno = f.deptno
+where d.deptno > 7]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5212,12 +5256,11 @@ LogicalProject(EXPR$0=[1])
<TestCase name="testTransitiveInferenceUnionAlwaysTrue">
<Resource name="sql">
<![CDATA[select d.deptno, e.deptno from
- (select deptno from sales.emp where deptno < 4) d
- inner join
- (select deptno from sales.emp where deptno > 7
- union all
- select deptno from sales.emp) e
- on d.deptno = e.deptno ]]>
+(select deptno from sales.emp where deptno < 4) d
+inner join
+(select deptno from sales.emp where deptno > 7
+union all select deptno from sales.emp) e
+on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5253,8 +5296,10 @@ LogicalProject(DEPTNO=[$0], DEPTNO0=[$1])
</TestCase>
<TestCase name="testTransitiveInferenceJoin3wayAgg">
<Resource name="sql">
- <![CDATA[select 1 from (select deptno, count(*) from sales.emp
where deptno > 7 group by deptno) d inner join
- sales.emp e on d.deptno = e.deptno inner join sales.emp f on
e.deptno = f.deptno ]]>
+ <![CDATA[select 1 from
+(select deptno, count(*) from sales.emp where deptno > 7 group by deptno) d
+inner join sales.emp e on d.deptno = e.deptno
+inner join sales.emp f on e.deptno = f.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5287,7 +5332,9 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceLeftOuterJoin">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d left outer join sales.emp e on
d.deptno = e.deptno where d.deptno > 7 and e.deptno > 9]]>
+ <![CDATA[select 1 from sales.emp d
+left outer join sales.emp e on d.deptno = e.deptno
+where d.deptno > 7 and e.deptno > 9]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5313,7 +5360,9 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceRightOuterJoin">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d right outer join sales.emp e on
d.deptno = e.deptno where d.deptno > 7 and e.deptno > 9]]>
+ <![CDATA[select 1 from sales.emp d
+right outer join sales.emp e on d.deptno = e.deptno
+where d.deptno > 7 and e.deptno > 9]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5339,7 +5388,8 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceFullOuterJoin">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d full outer join sales.emp e on
d.deptno = e.deptno where d.deptno > 7 and e.deptno > 9]]>
+ <![CDATA[select 1 from sales.emp d full outer join sales.emp e
+on d.deptno = e.deptno where d.deptno > 7 and e.deptno > 9]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5362,7 +5412,8 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferencePreventProjectPullUp">
<Resource name="sql">
- <![CDATA[select 1 from (select comm as deptno from sales.emp where
deptno > 7) d inner join sales.emp e on d.deptno = e.deptno ]]>
+ <![CDATA[select 1 from (select comm as deptno from sales.emp where
deptno > 7) d
+inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5387,7 +5438,8 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferencePullUpThruAlias">
<Resource name="sql">
- <![CDATA[select 1 from (select comm as deptno from sales.emp where
comm > 7) d inner join sales.emp e on d.deptno = e.deptno ]]>
+ <![CDATA[select 1 from (select comm as deptno from sales.emp where
comm > 7) d
+inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5413,7 +5465,9 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceConjunctInPullUp">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d inner join sales.emp e on
d.deptno = e.deptno where d.deptno in (7, 9) or d.deptno > 10]]>
+ <![CDATA[select 1 from sales.emp d
+inner join sales.emp e on d.deptno = e.deptno
+where d.deptno in (7, 9) or d.deptno > 10]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5437,7 +5491,9 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceNoPullUpExprs">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d inner join sales.emp e on
d.deptno = e.deptno where d.deptno in (7, 9) or d.comm > 10]]>
+ <![CDATA[select 1 from sales.emp d
+inner join sales.emp e on d.deptno = e.deptno
+where d.deptno in (7, 9) or d.comm > 10]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5460,7 +5516,13 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceUnion3way">
<Resource name="sql">
- <![CDATA[select 1 from (select deptno from sales.emp where deptno
> 7 union all select deptno from sales.emp where deptno > 10 union all select
deptno from sales.emp where deptno > 1) d inner join sales.emp e on d.deptno =
e.deptno ]]>
+ <![CDATA[select 1 from
+(select deptno from sales.emp where deptno > 7
+union all
+select deptno from sales.emp where deptno > 10
+union all
+select deptno from sales.emp where deptno > 1) d
+inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5502,55 +5564,55 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceUnion3wayOr">
<Resource name="sql">
- <![CDATA[
-select 1 from (
- select empno, deptno from sales.emp where deptno > 7 or empno < 10
- union all
- select empno, deptno from sales.emp where deptno > 10 or empno < deptno
- union all
- select empno, deptno from sales.emp where deptno > 1) d
-inner join sales.emp e on d.deptno = e.deptno ]]>
+ <![CDATA[select 1 from
+(select empno, deptno from sales.emp where deptno > 7 or empno < 10
+union all
+select empno, deptno from sales.emp where deptno > 10 or empno < deptno
+union all
+select empno, deptno from sales.emp where deptno > 1) d
+inner join sales.emp e on d.deptno = e.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
-ProjectRel(EXPR$0=[1])
- JoinRel(condition=[=($1, $9)], joinType=[inner])
- UnionRel(all=[true])
- UnionRel(all=[true])
- ProjectRel(EMPNO=[$0], DEPTNO=[$7])
- FilterRel(condition=[OR(>($7, 7), <($0, 10))])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- ProjectRel(EMPNO=[$0], DEPTNO=[$7])
- FilterRel(condition=[OR(>($7, 10), <($0, $7))])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- ProjectRel(EMPNO=[$0], DEPTNO=[$7])
- FilterRel(condition=[>($7, 1)])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[1])
+ LogicalJoin(condition=[=($1, $9)], joinType=[inner])
+ LogicalUnion(all=[true])
+ LogicalUnion(all=[true])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[OR(>($7, 7), <($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[OR(>($7, 10), <($0, $7))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[>($7, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
-ProjectRel(EXPR$0=[1])
- JoinRel(condition=[=($1, $9)], joinType=[inner])
- UnionRel(all=[true])
- UnionRel(all=[true])
- ProjectRel(EMPNO=[$0], DEPTNO=[$7])
- FilterRel(condition=[OR(>($7, 7), <($0, 10))])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- ProjectRel(EMPNO=[$0], DEPTNO=[$7])
- FilterRel(condition=[OR(>($7, 10), <($0, $7))])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- ProjectRel(EMPNO=[$0], DEPTNO=[$7])
- FilterRel(condition=[>($7, 1)])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
- TableAccessRel(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[1])
+ LogicalJoin(condition=[=($1, $9)], joinType=[inner])
+ LogicalUnion(all=[true])
+ LogicalUnion(all=[true])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[OR(>($7, 7), <($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[OR(>($7, 10), <($0, $7))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[>($7, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
<TestCase name="testTransitiveInferenceConstantEquiPredicate">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d inner join sales.emp e on
d.deptno = e.deptno where 1 = 1]]>
+ <![CDATA[select 1 from sales.emp d
+inner join sales.emp e on d.deptno = e.deptno where 1 = 1]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5594,7 +5656,10 @@ ProjectRel(EXPR$0=[1])
</TestCase>
<TestCase name="testTransitiveInferenceComplexPredicate">
<Resource name="sql">
- <![CDATA[select 1 from sales.emp d inner join sales.emp e on
d.deptno = e.deptno where d.deptno > 7 and e.sal = e.deptno and d.comm =
d.deptno and d.comm + d.deptno > d.comm/2]]>
+ <![CDATA[select 1 from sales.emp d
+inner join sales.emp e on d.deptno = e.deptno
+where d.deptno > 7 and e.sal = e.deptno and d.comm = d.deptno
+and d.comm + d.deptno > d.comm/2]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5620,7 +5685,8 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testPullConstantIntoProject">
<Resource name="sql">
- <![CDATA[select deptno, deptno + 1, empno + deptno from sales.emp
where deptno = 10]]>
+ <![CDATA[select deptno, deptno + 1, empno + deptno
+from sales.emp where deptno = 10]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5639,7 +5705,8 @@ LogicalProject(DEPTNO=[10], EXPR$1=[11], EXPR$2=[+($0,
10)])
</TestCase>
<TestCase name="testPullConstantIntoFilter">
<Resource name="sql">
- <![CDATA[select * from (select * from sales.emp where deptno = 10)
where deptno + 5 > empno]]>
+ <![CDATA[select * from (select * from sales.emp where deptno = 10)
+where deptno + 5 > empno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -6130,8 +6197,7 @@ LogicalAggregate(group=[{0, 2}])
</TestCase>
<TestCase name="testSimplifyFilter">
<Resource name="sql">
- <![CDATA[select *
-from (select * from sales.emp where deptno > 10)
+ <![CDATA[select * from (select * from sales.emp where deptno > 10)
where empno > 3 and deptno > 5]]>
</Resource>
<Resource name="planBefore">
@@ -6155,9 +6221,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
</TestCase>
<TestCase name="testPullConstantIntoJoin">
<Resource name="sql">
- <![CDATA[select *
- from (select * from sales.emp where empno = 10) as e
- left join sales.dept as d on e.empno = d.deptno]]>
+ <![CDATA[select * from (select * from sales.emp where empno = 10)
as e
+left join sales.dept as d on e.empno = d.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -6183,9 +6248,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
</TestCase>
<TestCase name="testPullConstantIntoJoin2">
<Resource name="sql">
- <![CDATA[select *
- from (select * from sales.emp where empno = 10) as e
- join sales.dept as d on e.empno = d.deptno and e.deptno + e.empno =
d.deptno + 5]]>
+ <![CDATA[select * from (select * from sales.emp where empno = 10)
as e
+join sales.dept as d on e.empno = d.deptno and e.deptno + e.empno = d.deptno +
5]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -7571,6 +7635,26 @@ LogicalProject(QX=[CAST(CASE(=($0, 1), 1, 2)):INTEGER])
]]>
</Resource>
</TestCase>
+ <TestCase name="testReduceCastsNullable">
+ <Resource name="sql">
+ <![CDATA[insert into sales.dept(deptno, name)
+select empno, cast(job as varchar(128)) from sales.EMPNULLABLES]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, DEPT]], operation=[INSERT],
flattened=[false])
+ LogicalProject(DEPTNO=[$0], NAME=[CAST($2):VARCHAR(128)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, DEPT]], operation=[INSERT],
flattened=[false])
+ LogicalCalc(expr#0..8=[{inputs}], expr#9=[CAST($t2):VARCHAR(128)],
expr#10=[CAST($t9):VARCHAR(10) NOT NULL], DEPTNO=[$t0], NAME=[$t10])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testReduceConstants3">
<Resource name="sql">
<![CDATA[select e.mgr is not distinct from f.mgr from emp e join
emp f on (e.mgr=f.mgr) where e.mgr is null]]>
