You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by da...@apache.org on 2019/10/15 01:37:44 UTC
[calcite] branch master updated: [CALCITE-3410] Simplify
RelOptRulesTest and HepPlannerTest by making test methods fluent
This is an automated email from the ASF dual-hosted git repository.
danny0405 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 8da131c [CALCITE-3410] Simplify RelOptRulesTest and HepPlannerTest by making test methods fluent
8da131c is described below
commit 8da131c08afe67861ced49bc6180a20f66be52fb
Author: yuzhao.cyz <yu...@alibaba-inc.com>
AuthorDate: Mon Oct 14 20:53:23 2019 +0800
[CALCITE-3410] Simplify RelOptRulesTest and HepPlannerTest by making test methods fluent
* Add method 'Sql.withTester'
* In RelOptTestBase, deprecate checkPlanning method
---
.../org/apache/calcite/test/HepPlannerTest.java | 46 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 1679 +++++++++-----------
.../org/apache/calcite/test/RelOptTestBase.java | 123 +-
3 files changed, 777 insertions(+), 1071 deletions(-)
diff --git a/core/src/test/java/org/apache/calcite/test/HepPlannerTest.java b/core/src/test/java/org/apache/calcite/test/HepPlannerTest.java
index 1506cf9..a88a136 100644
--- a/core/src/test/java/org/apache/calcite/test/HepPlannerTest.java
+++ b/core/src/test/java/org/apache/calcite/test/HepPlannerTest.java
@@ -104,9 +104,9 @@ public class HepPlannerTest extends RelOptTestBase {
new CoerceInputsRule(LogicalIntersect.class, false,
RelFactories.LOGICAL_BUILDER));
- checkPlanning(planner,
- "(select name from dept union select ename from emp)"
- + " intersect (select fname from customer.contact)");
+ final String sql = "(select name from dept union select ename from emp)\n"
+ + "intersect (select fname from customer.contact)";
+ sql(sql).with(planner).check();
}
@Test public void testRuleDescription() throws Exception {
@@ -121,9 +121,8 @@ public class HepPlannerTest extends RelOptTestBase {
planner.addRule(FilterToCalcRule.INSTANCE);
- checkPlanning(
- planner,
- "select name from sales.dept where deptno=12");
+ final String sql = "select name from sales.dept where deptno=12";
+ sql(sql).with(planner).check();
}
/**
@@ -177,8 +176,7 @@ public class HepPlannerTest extends RelOptTestBase {
programBuilder.addMatchLimit(1);
programBuilder.addRuleInstance(UnionToDistinctRule.INSTANCE);
- checkPlanning(
- programBuilder.build(), UNION_TREE);
+ sql(UNION_TREE).with(programBuilder.build()).check();
}
@Test public void testMatchLimitOneBottomUp() throws Exception {
@@ -189,8 +187,7 @@ public class HepPlannerTest extends RelOptTestBase {
programBuilder.addMatchOrder(HepMatchOrder.BOTTOM_UP);
programBuilder.addRuleInstance(UnionToDistinctRule.INSTANCE);
- checkPlanning(
- programBuilder.build(), UNION_TREE);
+ sql(UNION_TREE).with(programBuilder.build()).check();
}
@Test public void testMatchUntilFixpoint() throws Exception {
@@ -200,8 +197,7 @@ public class HepPlannerTest extends RelOptTestBase {
programBuilder.addMatchLimit(HepProgram.MATCH_UNTIL_FIXPOINT);
programBuilder.addRuleInstance(UnionToDistinctRule.INSTANCE);
- checkPlanning(
- programBuilder.build(), UNION_TREE);
+ sql(UNION_TREE).with(programBuilder.build()).check();
}
@Test public void testReplaceCommonSubexpression() throws Exception {
@@ -212,10 +208,9 @@ public class HepPlannerTest extends RelOptTestBase {
// rewriting something used as a common sub-expression
// twice by the same parent (the join in this case).
- checkPlanning(
- ProjectRemoveRule.INSTANCE,
- "select d1.deptno from (select * from dept) d1,"
- + " (select * from dept) d2");
+ final String sql = "select d1.deptno from (select * from dept) d1,\n"
+ + "(select * from dept) d2";
+ sql(sql).withRule(ProjectRemoveRule.INSTANCE).check();
}
/** Tests that if two relational expressions are equivalent, the planner
@@ -257,9 +252,9 @@ public class HepPlannerTest extends RelOptTestBase {
HepProgramBuilder programBuilder = HepProgram.builder();
programBuilder.addSubprogram(subprogramBuilder.build());
- checkPlanning(
- programBuilder.build(),
- "select upper(ename) from (select lower(ename) as ename from emp where empno = 100)");
+ final String sql = "select upper(ename) from\n"
+ + "(select lower(ename) as ename from emp where empno = 100)";
+ sql(sql).with(programBuilder.build()).check();
}
@Test public void testGroup() throws Exception {
@@ -273,9 +268,8 @@ public class HepPlannerTest extends RelOptTestBase {
programBuilder.addRuleInstance(FilterToCalcRule.INSTANCE);
programBuilder.addGroupEnd();
- checkPlanning(
- programBuilder.build(),
- "select upper(name) from dept where deptno=20");
+ final String sql = "select upper(name) from dept where deptno=20";
+ sql(sql).with(programBuilder.build()).check();
}
@Test public void testGC() throws Exception {
@@ -302,9 +296,11 @@ public class HepPlannerTest extends RelOptTestBase {
programBuilder.build());
String query = "(select n_nationkey from SALES.CUSTOMER) union all\n"
+ "(select n_name from CUSTOMER_MODIFIABLEVIEW)";
- Tester tester = createDynamicTester()
- .withDecorrelation(true);
- checkPlanning(tester, programBuilder.build(), planner, query, true);
+ sql(query).withTester(t -> createDynamicTester())
+ .withDecorrelation(true)
+ .with(programBuilder.build())
+ .with(planner)
+ .checkUnchanged();
}
@Test public void testRuleApplyCount() {
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 5a852aa..51b671b 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -239,7 +239,9 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql = "select *\n"
+ "from (select (case when sal > 1000 then null else false end) as caseCol from emp)\n"
+ "where NOT(caseCol)";
- checkPlanning(tester, preProgram, hepPlanner, sql, true);
+ sql(sql).withPre(preProgram)
+ .with(hepPlanner)
+ .checkUnchanged();
}
@Test public void testReduceNestedCaseWhen() {
@@ -256,7 +258,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "where case when (sal = 1000) then\n"
+ "(case when sal = 1000 then null else 1 end is null) else\n"
+ "(case when sal = 2000 then null else 1 end is null) end is true";
- checkPlanning(tester, preProgram, hepPlanner, sql);
+ sql(sql).withPre(preProgram)
+ .with(hepPlanner)
+ .check();
}
/** Test case for
@@ -329,7 +333,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "from emp\n"
+ "where case when sal = 1000 then null else 1 end is null\n"
+ "OR case when sal = 2000 then null else 1 end is null";
- checkPlanning(tester, preProgram, hepPlanner, sql);
+ sql(sql).withPre(preProgram)
+ .with(hepPlanner)
+ .check();
}
@Test public void testReduceNullableCase() {
@@ -371,17 +377,19 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " sum(deptno) over(partition by empno order by sal) as sum1,\n"
+ " sum(deptno) over(partition by deptno order by sal) as sum2\n"
+ "from emp";
- checkPlanning(tester, preProgram, hepPlanner, sql);
+ sql(sql).withPre(preProgram)
+ .with(hepPlanner)
+ .check();
}
@Test public void testUnionToDistinctRule() {
- checkPlanning(UnionToDistinctRule.INSTANCE,
- "select * from dept union select * from dept");
+ final String sql = "select * from dept union select * from dept";
+ sql(sql).withRule(UnionToDistinctRule.INSTANCE).check();
}
@Test public void testExtractJoinFilterRule() {
- checkPlanning(JoinExtractFilterRule.INSTANCE,
- "select 1 from emp inner join dept on emp.deptno=dept.deptno");
+ final String sql = "select 1 from emp inner join dept on emp.deptno=dept.deptno";
+ sql(sql).withRule(JoinExtractFilterRule.INSTANCE).check();
}
@Test public void testNotPushExpression() {
@@ -392,8 +400,8 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testAddRedundantSemiJoinRule() {
- checkPlanning(JoinAddRedundantSemiJoinRule.INSTANCE,
- "select 1 from emp inner join dept on emp.deptno = dept.deptno");
+ final String sql = "select 1 from emp inner join dept on emp.deptno = dept.deptno";
+ sql(sql).withRule(JoinAddRedundantSemiJoinRule.INSTANCE).check();
}
@Test public void testStrengthenJoinType() {
@@ -526,38 +534,38 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testFullOuterJoinSimplificationToLeftOuter() {
- checkPlanning(FilterJoinRule.FILTER_ON_JOIN,
- "select 1 from sales.dept d full outer join sales.emp e"
- + " on d.deptno = e.deptno"
- + " where d.name = 'Charlie'");
+ final String sql = "select 1 from sales.dept d full outer join sales.emp e\n"
+ + "on d.deptno = e.deptno\n"
+ + "where d.name = 'Charlie'";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN).check();
}
@Test public void testFullOuterJoinSimplificationToRightOuter() {
- checkPlanning(FilterJoinRule.FILTER_ON_JOIN,
- "select 1 from sales.dept d full outer join sales.emp e"
- + " on d.deptno = e.deptno"
- + " where e.sal > 100");
+ final String sql = "select 1 from sales.dept d full outer join sales.emp e\n"
+ + "on d.deptno = e.deptno\n"
+ + "where e.sal > 100";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN).check();
}
@Test public void testFullOuterJoinSimplificationToInner() {
- checkPlanning(FilterJoinRule.FILTER_ON_JOIN,
- "select 1 from sales.dept d full outer join sales.emp e"
- + " on d.deptno = e.deptno"
- + " where d.name = 'Charlie' and e.sal > 100");
+ final String sql = "select 1 from sales.dept d full outer join sales.emp e\n"
+ + "on d.deptno = e.deptno\n"
+ + "where d.name = 'Charlie' and e.sal > 100";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN).check();
}
@Test public void testLeftOuterJoinSimplificationToInner() {
- checkPlanning(FilterJoinRule.FILTER_ON_JOIN,
- "select 1 from sales.dept d left outer join sales.emp e"
- + " on d.deptno = e.deptno"
- + " where e.sal > 100");
+ final String sql = "select 1 from sales.dept d left outer join sales.emp e\n"
+ + "on d.deptno = e.deptno\n"
+ + "where e.sal > 100";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN).check();
}
@Test public void testRightOuterJoinSimplificationToInner() {
- checkPlanning(FilterJoinRule.FILTER_ON_JOIN,
- "select 1 from sales.dept d right outer join sales.emp e"
- + " on d.deptno = e.deptno"
- + " where d.name = 'Charlie'");
+ final String sql = "select 1 from sales.dept d right outer join sales.emp e\n"
+ + "on d.deptno = e.deptno\n"
+ + "where d.name = 'Charlie'";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN).check();
}
/** Test case for
@@ -641,10 +649,10 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testPushFilterPastAgg() {
- checkPlanning(FilterAggregateTransposeRule.INSTANCE,
- "select dname, c from"
- + " (select name dname, count(*) as c from dept group by name) t"
- + " where dname = 'Charlie'");
+ final String sql = "select dname, c from\n"
+ + "(select name dname, count(*) as c from dept group by name) t\n"
+ + " where dname = 'Charlie'";
+ sql(sql).withRule(FilterAggregateTransposeRule.INSTANCE).check();
}
private void basePushFilterPastAggWithGroupingSets(boolean unchanged)
@@ -658,8 +666,13 @@ public class RelOptRulesTest extends RelOptTestBase {
HepProgram.builder()
.addRuleInstance(FilterAggregateTransposeRule.INSTANCE)
.build();
- checkPlanning(tester, preProgram, new HepPlanner(program), "${sql}",
- unchanged);
+ Sql sql = sql("${sql}").withPre(preProgram)
+ .with(program);
+ if (unchanged) {
+ sql.checkUnchanged();
+ } else {
+ sql.check();
+ }
}
@Test public void testPushFilterPastAggWithGroupingSets1() throws Exception {
@@ -674,24 +687,21 @@ public class RelOptRulesTest extends RelOptTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-434">[CALCITE-434]
* FilterAggregateTransposeRule loses conditions that cannot be pushed</a>. */
@Test public void testPushFilterPastAggTwo() {
- checkPlanning(FilterAggregateTransposeRule.INSTANCE,
- "select dept1.c1 from (\n"
- + " select dept.name as c1, count(*) as c2\n"
- + " from dept where dept.name > 'b' group by dept.name) dept1\n"
- + "where dept1.c1 > 'c' and (dept1.c2 > 30 or dept1.c1 < 'z')");
+ final String sql = "select dept1.c1 from (\n"
+ + "select dept.name as c1, count(*) as c2\n"
+ + "from dept where dept.name > 'b' group by dept.name) dept1\n"
+ + "where dept1.c1 > 'c' and (dept1.c2 > 30 or dept1.c1 < 'z')";
+ sql(sql).withRule(FilterAggregateTransposeRule.INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-799">[CALCITE-799]
* Incorrect result for {@code HAVING count(*) > 1}</a>. */
@Test public void testPushFilterPastAggThree() {
- final HepProgram program =
- HepProgram.builder()
- .addRuleInstance(FilterAggregateTransposeRule.INSTANCE)
- .build();
final String sql = "select deptno from emp\n"
+ "group by deptno having count(*) > 1";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(FilterAggregateTransposeRule.INSTANCE)
+ .checkUnchanged();
}
/** Test case for
@@ -707,9 +717,12 @@ public class RelOptRulesTest extends RelOptTestBase {
HepProgram.builder()
.addRuleInstance(FilterAggregateTransposeRule.INSTANCE)
.build();
- checkPlanning(tester, preProgram, new HepPlanner(program),
- "select emp.deptno, count(*) from emp where emp.sal > '12' "
- + "group by emp.deptno\n", false);
+ final String sql = "select emp.deptno, count(*) from emp where emp.sal > '12'\n"
+ + "group by emp.deptno";
+ sql(sql)
+ .withPre(preProgram)
+ .with(program)
+ .check();
}
/** Test case for
@@ -739,7 +752,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "from dept a\n"
+ "left join dept b on b.deptno > 10\n"
+ "right join dept c on b.deptno > 10\n";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .check();
}
@Test public void testJoinProjectTranspose1() {
@@ -760,7 +775,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "from dept a\n"
+ "left join dept b on b.deptno > 10\n"
+ "right join dept c on b.deptno > 10\n";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .check();
}
/** Test case for
@@ -871,7 +888,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "union all\n"
+ "select b.name from dept b\n"
+ "order by name limit 10";
- checkPlanning(program, sql);
+ sql(sql).with(program).check();
}
/** Test case for
@@ -887,7 +904,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "union all\n"
+ "select b.name from dept b\n"
+ "order by name";
- checkPlanning(program, sql);
+ sql(sql).with(program).check();
}
/** Test case for
@@ -903,7 +920,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "union all\n"
+ "select b.name from dept b\n"
+ "order by name limit 0";
- checkPlanning(program, sql);
+ sql(sql).with(program).check();
}
@Test public void testSortRemovalAllKeysConstant() {
@@ -1142,9 +1159,9 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testReduceAverage() {
- checkPlanning(AggregateReduceFunctionsRule.INSTANCE,
- "select name, max(name), avg(deptno), min(name)"
- + " from sales.dept group by name");
+ final String sql = "select name, max(name), avg(deptno), min(name)\n"
+ + "from sales.dept group by name";
+ sql(sql).withRule(AggregateReduceFunctionsRule.INSTANCE).check();
}
/** Test case for
@@ -1155,8 +1172,8 @@ public class RelOptRulesTest extends RelOptTestBase {
HepProgram.builder()
.addRuleInstance(AggregateReduceFunctionsRule.INSTANCE)
.build();
- final String sql = "select name, stddev_pop(deptno), avg(deptno),"
- + " stddev_samp(deptno),var_pop(deptno), var_samp(deptno)\n"
+ final String sql = "select name, stddev_pop(deptno), avg(deptno),\n"
+ + "stddev_samp(deptno),var_pop(deptno), var_samp(deptno)\n"
+ "from sales.dept group by name";
sql(sql).with(program).check();
}
@@ -1176,9 +1193,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct ename)"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct ename)\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCount2() {
@@ -1186,9 +1203,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct ename), sum(sal)"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct ename), sum(sal)\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
/** Test case for
@@ -1210,10 +1227,10 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.JOIN)
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct ename), count(distinct job, ename),\n"
- + " count(distinct deptno, job), sum(sal)\n"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct ename), count(distinct job, ename),\n"
+ + "count(distinct deptno, job), sum(sal)\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
/** Tests implementing multiple distinct count the new way, using GROUPING
@@ -1223,9 +1240,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct ename), count(distinct job)\n"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct ename), count(distinct job)\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCountMultipleNoGroup() {
@@ -1233,9 +1250,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select count(distinct ename), count(distinct job)\n"
- + " from sales.emp");
+ final String sql = "select count(distinct ename), count(distinct job)\n"
+ + "from sales.emp";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCountMixedJoin() {
@@ -1243,10 +1260,10 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.JOIN)
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct ename), count(distinct job, ename),\n"
- + " count(distinct deptno, job), sum(sal)\n"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct ename), count(distinct job, ename),\n"
+ + "count(distinct deptno, job), sum(sal)\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCountMixed() {
@@ -1254,9 +1271,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct deptno, job) as cddj, sum(sal) as s\n"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct deptno, job) as cddj, sum(sal) as s\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCountMixed2() {
@@ -1265,12 +1282,12 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, count(distinct ename) as cde,\n"
- + " count(distinct job, ename) as cdje,\n"
- + " count(distinct deptno, job) as cddj,\n"
- + " sum(sal) as s\n"
- + " from sales.emp group by deptno");
+ final String sql = "select deptno, count(distinct ename) as cde,\n"
+ + "count(distinct job, ename) as cdje,\n"
+ + "count(distinct deptno, job) as cddj,\n"
+ + "sum(sal) as s\n"
+ + "from sales.emp group by deptno";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCountGroupingSets1() {
@@ -1278,9 +1295,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, job, count(distinct ename)"
- + " from sales.emp group by rollup(deptno,job)");
+ final String sql = "select deptno, job, count(distinct ename)\n"
+ + "from sales.emp group by rollup(deptno,job)";
+ sql(sql).with(program).check();
}
@Test public void testDistinctCountGroupingSets2() {
@@ -1288,9 +1305,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select deptno, job, count(distinct ename), sum(sal)"
- + " from sales.emp group by rollup(deptno,job)");
+ final String sql = "select deptno, job, count(distinct ename), sum(sal)\n"
+ + "from sales.emp group by rollup(deptno,job)";
+ sql(sql).with(program).check();
}
@Test public void testDistinctNonDistinctAggregates() {
@@ -1407,9 +1424,9 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testPushProjectPastFilter() {
- checkPlanning(ProjectFilterTransposeRule.INSTANCE,
- "select empno + deptno from emp where sal = 10 * comm "
- + "and upper(ename) = 'FOO'");
+ final String sql = "select empno + deptno from emp where sal = 10 * comm\n"
+ + "and upper(ename) = 'FOO'";
+ sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
}
/** Test case for
@@ -1424,9 +1441,9 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testPushProjectPastJoin() {
- checkPlanning(ProjectJoinTransposeRule.INSTANCE,
- "select e.sal + b.comm from emp e inner join bonus b "
- + "on e.ename = b.ename and e.deptno = 10");
+ final String sql = "select e.sal + b.comm from emp e inner join bonus b\n"
+ + "on e.ename = b.ename and e.deptno = 10";
+ sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
}
/** Test case for
@@ -1434,9 +1451,9 @@ public class RelOptRulesTest extends RelOptTestBase {
* Should not push over past union but its operands can since setop
* will affect row count</a>. */
@Test public void testProjectSetOpTranspose() {
- checkPlanning(ProjectSetOpTransposeRule.INSTANCE,
- "select job, sum(sal + 100) over (partition by deptno) from\n"
- + "(select * from emp e1 union all select * from emp e2)");
+ final String sql = "select job, sum(sal + 100) over (partition by deptno) from\n"
+ + "(select * from emp e1 union all select * from emp e2)";
+ sql(sql).withRule(ProjectSetOpTransposeRule.INSTANCE).check();
}
@Test public void testProjectCorrelateTransposeDynamic() {
@@ -1449,13 +1466,9 @@ public class RelOptRulesTest extends RelOptTestBase {
String query = "select t1.c_nationkey, t2.a as fake_col2 "
+ "from SALES.CUSTOMER as t1, "
+ "unnest(t1.fake_col) as t2(a)";
-
- checkPlanning(
- createDynamicTester(),
- null,
- new HepPlanner(programBuilder.build()),
- query,
- true);
+ sql(query).withTester(t -> createDynamicTester())
+ .with(programBuilder.build())
+ .checkUnchanged();
}
@Test public void testProjectCorrelateTransposeRuleLeftCorrelate() {
@@ -1554,32 +1567,31 @@ public class RelOptRulesTest extends RelOptTestBase {
ProjectCorrelateTransposeRule customPCTrans =
new ProjectCorrelateTransposeRule(skipItem, RelFactories.LOGICAL_BUILDER);
- checkPlanning(customPCTrans,
- "select t1.name, t2.ename "
- + "from DEPT_NESTED as t1, "
- + "unnest(t1.employees) as t2");
+ final String sql = "select t1.name, t2.ename\n"
+ + "from DEPT_NESTED as t1,\n"
+ + "unnest(t1.employees) as t2";
+ sql(sql).withRule(customPCTrans).check();
}
@Test public void testProjectCorrelateTranspose() {
ProjectCorrelateTransposeRule customPCTrans =
new ProjectCorrelateTransposeRule(expr -> true,
RelFactories.LOGICAL_BUILDER);
-
- checkPlanning(customPCTrans,
- "select t1.name, t2.ename "
- + "from DEPT_NESTED as t1, "
- + "unnest(t1.employees) as t2");
+ final String sql = "select t1.name, t2.ename\n"
+ + "from DEPT_NESTED as t1,\n"
+ + "unnest(t1.employees) as t2";
+ sql(sql).withRule(customPCTrans).check();
}
/** As {@link #testProjectSetOpTranspose()};
* should not push over past correlate but its operands can since correlate
* will affect row count. */
@Test public void testProjectCorrelateTransposeWithOver() {
- checkPlanning(ProjectCorrelateTransposeRule.INSTANCE,
- "select sum(t1.deptno + 1) over (partition by t1.name),\n"
- + "count(t2.empno) over ()\n"
- + "from DEPT_NESTED as t1, "
- + "unnest(t1.employees) as t2");
+ final String sql = "select sum(t1.deptno + 1) over (partition by t1.name),\n"
+ + "count(t2.empno) over ()\n"
+ + "from DEPT_NESTED as t1,\n"
+ + "unnest(t1.employees) as t2";
+ sql(sql).withRule(ProjectCorrelateTransposeRule.INSTANCE).check();
}
/** Tests that the default instance of {@link FilterProjectTransposeRule}
@@ -1723,53 +1735,53 @@ public class RelOptRulesTest extends RelOptTestBase {
* Should not push over whose columns are all from left child past join since
* join will affect row count.</a>. */
@Test public void testPushProjectWithOverPastJoin1() {
- checkPlanning(ProjectJoinTransposeRule.INSTANCE,
- "select e.sal + b.comm,\n"
- + "count(e.empno) over (partition by e.deptno)\n"
- + "from emp e join bonus b\n"
- + "on e.ename = b.ename and e.deptno = 10");
+ final String sql = "select e.sal + b.comm,\n"
+ + "count(e.empno) over (partition by e.deptno)\n"
+ + "from emp e join bonus b\n"
+ + "on e.ename = b.ename and e.deptno = 10";
+ sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
}
/** As {@link #testPushProjectWithOverPastJoin1()};
* should not push over whose columns are all from right child past join since
* join will affect row count. */
@Test public void testPushProjectWithOverPastJoin2() {
- checkPlanning(ProjectJoinTransposeRule.INSTANCE,
- "select e.sal + b.comm,\n"
- + "count(b.sal) over (partition by b.job)\n"
- + "from emp e join bonus b\n"
- + "on e.ename = b.ename and e.deptno = 10");
+ final String sql = "select e.sal + b.comm,\n"
+ + "count(b.sal) over (partition by b.job)\n"
+ + "from emp e join bonus b\n"
+ + "on e.ename = b.ename and e.deptno = 10";
+ sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
}
/** As {@link #testPushProjectWithOverPastJoin2()};
* should not push over past join but should push the operands of over past
* join. */
@Test public void testPushProjectWithOverPastJoin3() {
- checkPlanning(ProjectJoinTransposeRule.INSTANCE,
- "select e.sal + b.comm,\n"
- + "sum(b.sal + b.sal + 100) over (partition by b.job)\n"
- + "from emp e join bonus b\n"
- + "on e.ename = b.ename and e.deptno = 10");
+ final String sql = "select e.sal + b.comm,\n"
+ + "sum(b.sal + b.sal + 100) over (partition by b.job)\n"
+ + "from emp e join bonus b\n"
+ + "on e.ename = b.ename and e.deptno = 10";
+ sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
}
@Test public void testPushProjectPastSetOp() {
- checkPlanning(ProjectSetOpTransposeRule.INSTANCE,
- "select sal from "
- + "(select * from emp e1 union all select * from emp e2)");
+ final String sql = "select sal from\n"
+ + "(select * from emp e1 union all select * from emp e2)";
+ sql(sql).withRule(ProjectSetOpTransposeRule.INSTANCE).check();
}
@Test public void testPushJoinThroughUnionOnLeft() {
- checkPlanning(JoinUnionTransposeRule.LEFT_UNION,
- "select r1.sal from "
- + "(select * from emp e1 union all select * from emp e2) r1, "
- + "emp r2");
+ final String sql = "select r1.sal from\n"
+ + "(select * from emp e1 union all select * from emp e2) r1,\n"
+ + "emp r2";
+ sql(sql).withRule(JoinUnionTransposeRule.LEFT_UNION).check();
}
@Test public void testPushJoinThroughUnionOnRight() {
- checkPlanning(JoinUnionTransposeRule.RIGHT_UNION,
- "select r1.sal from "
- + "emp r1, "
- + "(select * from emp e1 union all select * from emp e2) r2");
+ final String sql = "select r1.sal from\n"
+ + "emp r1,\n"
+ + "(select * from emp e1 union all select * from emp e2) r2";
+ sql(sql).withRule(JoinUnionTransposeRule.RIGHT_UNION).check();
}
@Test public void testPushJoinThroughUnionOnRightDoesNotMatchSemiJoin() {
@@ -1875,11 +1887,11 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(CalcMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select d.name as dname,e.ename as ename"
- + " from emp e inner join dept d"
- + " on e.deptno=d.deptno"
- + " where d.name='Propane'");
+ final String sql = "select d.name as dname,e.ename as ename\n"
+ + " from emp e inner join dept d\n"
+ + " on e.deptno=d.deptno\n"
+ + " where d.name='Propane'";
+ sql(sql).with(program).check();
}
/** Tests that filters are combined if they are identical. */
@@ -1889,12 +1901,12 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(FilterMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select name from (\n"
- + " select *\n"
- + " from dept\n"
- + " where deptno = 10)\n"
- + "where deptno = 10\n");
+ final String sql = "select name from (\n"
+ + " select *\n"
+ + " from dept\n"
+ + " where deptno = 10)\n"
+ + "where deptno = 10\n";
+ sql(sql).with(program).check();
}
/** Tests to see if the final branch of union is missed */
@@ -1906,21 +1918,21 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(UnionMergeRule.INSTANCE)
.build();
- checkPlanning(program,
- "select * from (\n"
- + "select * from (\n"
- + " select name, deptno from dept\n"
- + " union all\n"
- + " select name, deptno from\n"
- + " (\n"
- + " select name, deptno, count(1) from dept group by name, deptno\n"
- + " union all\n"
- + " select name, deptno, count(1) from dept group by name, deptno\n"
- + " ) subq\n"
- + ") a\n"
- + "union all\n"
- + "select name, deptno from dept\n"
- + ") aa\n");
+ final String sql = "select * from (\n"
+ + "select * from (\n"
+ + " select name, deptno from dept\n"
+ + " union all\n"
+ + " select name, deptno from\n"
+ + " (\n"
+ + " select name, deptno, count(1) from dept group by name, deptno\n"
+ + " union all\n"
+ + " select name, deptno, count(1) from dept group by name, deptno\n"
+ + " ) subq\n"
+ + ") a\n"
+ + "union all\n"
+ + "select name, deptno from dept\n"
+ + ") aa\n";
+ sql(sql).with(program).check();
}
@Test
@@ -1931,26 +1943,26 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(UnionMergeRule.MINUS_INSTANCE)
.build();
- checkPlanning(program,
- "select * from (\n"
- + "select * from (\n"
- + " select name, deptno from\n"
- + " (\n"
- + " select name, deptno, count(1) from dept group by name, deptno\n"
- + " except all\n"
- + " select name, deptno, 1 from dept\n"
- + " ) subq\n"
- + " except all\n"
- + " select name, deptno from\n"
- + " (\n"
- + " select name, deptno, 1 from dept\n"
- + " except all\n"
- + " select name, deptno, count(1) from dept group by name, deptno\n"
- + " ) subq2\n"
- + ") a\n"
- + "except all\n"
- + "select name, deptno from dept\n"
- + ") aa\n");
+ final String sql = "select * from (\n"
+ + "select * from (\n"
+ + " select name, deptno from\n"
+ + " (\n"
+ + " select name, deptno, count(1) from dept group by name, deptno\n"
+ + " except all\n"
+ + " select name, deptno, 1 from dept\n"
+ + " ) subq\n"
+ + " except all\n"
+ + " select name, deptno from\n"
+ + " (\n"
+ + " select name, deptno, 1 from dept\n"
+ + " except all\n"
+ + " select name, deptno, count(1) from dept group by name, deptno\n"
+ + " ) subq2\n"
+ + ") a\n"
+ + "except all\n"
+ + "select name, deptno from dept\n"
+ + ") aa\n";
+ sql(sql).with(program).check();
}
/** Tests that a filters is combined are combined if they are identical,
@@ -1962,14 +1974,14 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
.build();
- checkPlanning(program,
- "select * from (\n"
- + " select d.deptno, e.ename\n"
- + " from emp as e\n"
- + " join dept as d\n"
- + " on e.deptno = d.deptno\n"
- + " and d.deptno = 10)\n"
- + "where deptno = 10\n");
+ final String sql = "select * from (\n"
+ + " select d.deptno, e.ename\n"
+ + " from emp as e\n"
+ + " join dept as d\n"
+ + " on e.deptno = d.deptno\n"
+ + " and d.deptno = 10)\n"
+ + "where deptno = 10\n";
+ sql(sql).with(program).check();
}
/** Tests {@link UnionMergeRule}, which merges 2 {@link Union} operators into
@@ -2147,55 +2159,49 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ProjectToCalcRule.INSTANCE)
.build();
- checkPlanning(program,
- "select upper(ename) from emp union all"
- + " select lower(ename) from emp");
+ final String sql = "select upper(ename) from emp union all\n"
+ + "select lower(ename) from emp";
+ sql(sql).with(program).check();
}
@Test public void testPushSemiJoinPastJoinRuleLeft() throws Exception {
// tests the case where the semijoin is pushed to the left
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinJoinTransposeRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e1.ename from emp e1, dept d, emp e2 "
- + "where e1.deptno = d.deptno and e1.empno = e2.empno");
+ final String sql = "select e1.ename from emp e1, dept d, emp e2\n"
+ + "where e1.deptno = d.deptno and e1.empno = e2.empno";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinJoinTransposeRule.INSTANCE)
+ .check();
}
@Test public void testPushSemiJoinPastJoinRuleRight() throws Exception {
// tests the case where the semijoin is pushed to the right
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinJoinTransposeRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e1.ename from emp e1, dept d, emp e2 "
- + "where e1.deptno = d.deptno and d.deptno = e2.deptno");
+ final String sql = "select e1.ename from emp e1, dept d, emp e2\n"
+ + "where e1.deptno = d.deptno and d.deptno = e2.deptno";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinJoinTransposeRule.INSTANCE)
+ .check();
}
@Test public void testPushSemiJoinPastFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinFilterTransposeRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e.ename from emp e, dept d "
- + "where e.deptno = d.deptno and e.ename = 'foo'");
+ final String sql = "select e.ename from emp e, dept d\n"
+ + "where e.deptno = d.deptno and e.ename = 'foo'";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinFilterTransposeRule.INSTANCE)
+ .check();
}
@Test public void testConvertMultiJoinRule() throws Exception {
+ final String sql = "select e1.ename from emp e1, dept d, emp e2\n"
+ + "where e1.deptno = d.deptno and d.deptno = e2.deptno";
HepProgram program = new HepProgramBuilder()
.addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
.addMatchOrder(HepMatchOrder.BOTTOM_UP)
.addRuleInstance(JoinToMultiJoinRule.INSTANCE)
.build();
- checkPlanning(program,
- "select e1.ename from emp e1, dept d, emp e2 "
- + "where e1.deptno = d.deptno and d.deptno = e2.deptno");
+ sql(sql).with(program).check();
}
@Test public void testManyFiltersOnTopOfMultiJoinShouldCollapse() throws Exception {
@@ -2205,9 +2211,10 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleCollection(
Arrays.asList(FilterMultiJoinMergeRule.INSTANCE, ProjectMultiJoinMergeRule.INSTANCE))
.build();
- checkPlanning(program,
- "select * from (select * from emp e1 left outer join dept d on e1.deptno = d.deptno "
- + "where d.deptno > 3) where ename LIKE 'bar'");
+ final String sql = "select * from (select * from emp e1 left outer join dept d\n"
+ + "on e1.deptno = d.deptno\n"
+ + "where d.deptno > 3) where ename LIKE 'bar'";
+ sql(sql).with(program).check();
}
@Test public void testReduceConstants() throws Exception {
@@ -2238,283 +2245,221 @@ public class RelOptRulesTest extends RelOptTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-570">[CALCITE-570]
* ReduceExpressionsRule throws "duplicate key" exception</a>. */
@Test public void testReduceConstantsDup() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select d.deptno"
+ " from dept d"
+ " where d.deptno=7 and d.deptno=8";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-935">[CALCITE-935]
* Improve how ReduceExpressionsRule handles duplicate constraints</a>. */
@Test public void testReduceConstantsDup2() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select *\n"
+ "from emp\n"
+ "where deptno=7 and deptno=8\n"
+ "and empno = 10 and mgr is null and empno = 10";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3198">[CALCITE-3198]
* Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
@Test public void testReduceConstantsDup3() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select d.deptno"
+ " from dept d"
+ " where d.deptno<>7 or d.deptno<>8";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3198">[CALCITE-3198]
* Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
@Test public void testReduceConstantsDup3Null() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select e.empno"
+ " from emp e"
+ " where e.mgr<>7 or e.mgr<>8";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3198">[CALCITE-3198]
* Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
@Test public void testReduceConstantsDupNot() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select d.deptno"
+ " from dept d"
+ " where not(d.deptno=7 and d.deptno=8)";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3198">[CALCITE-3198]
* Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
@Test public void testReduceConstantsDupNotNull() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select e.empno"
+ " from emp e"
+ " where not(e.mgr=7 and e.mgr=8)";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3198">[CALCITE-3198]
* Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
@Test public void testReduceConstantsDupNot2() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select d.deptno"
+ " from dept d"
+ " where not(d.deptno=7 and d.name='foo' and d.deptno=8)";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3198">[CALCITE-3198]
* Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
@Test public void testReduceConstantsDupNot2Null() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select e.empno"
+ " from emp e"
+ " where not(e.mgr=7 and e.deptno=8 and e.mgr=8)";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public void testPullNull() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
-
final String sql = "select *\n"
+ "from emp\n"
+ "where deptno=7\n"
+ "and empno = 10 and mgr is null and empno = 10";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test public void testOrAlwaysTrue() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
final String sql = "select * from EMPNULLABLES_20\n"
+ "where sal is null or sal is not null";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test public void testOrAlwaysTrue2() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
final String sql = "select * from EMPNULLABLES_20\n"
+ "where sal is not null or sal is null";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test public void testReduceConstants2() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
-
- checkPlanUnchanged(new HepPlanner(program),
- "select p1 is not distinct from p0 from (values (2, cast(null as integer))) as t(p0, p1)");
+ final String sql = "select p1 is not distinct from p0 \n"
+ + "from (values (2, cast(null as integer))) as t(p0, p1)";
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .checkUnchanged();
}
@Test public void testReduceConstants3() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
-
final String sql = "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";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-902">[CALCITE-902]
* Match nullability when reducing expressions in a Project</a>. */
@Test public void testReduceConstantsProjectNullable() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
-
- checkPlanning(program, "select mgr from emp where mgr=10");
+ final String sql = "select mgr from emp where mgr=10";
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
// see HIVE-9645
@Test public void testReduceConstantsNullEqualsOne() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
-
- checkPlanning(program,
- "select count(1) from emp where cast(null as integer) = 1");
+ final String sql = "select count(1) from emp where cast(null as integer) = 1";
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
// see HIVE-9644
@Test public void testReduceConstantsCaseEquals() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
-
+ final String sql = "select count(1) from emp\n"
+ + "where case deptno\n"
+ + " when 20 then 2\n"
+ + " when 10 then 1\n"
+ + " else 3 end = 1";
// Equivalent to 'deptno = 10'
- checkPlanning(program,
- "select count(1) from emp\n"
- + "where case deptno\n"
- + " when 20 then 2\n"
- + " when 10 then 1\n"
- + " else 3 end = 1");
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test public void testReduceConstantsCaseEquals2() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
+ final String sql = "select count(1) from emp\n"
+ + "where case deptno\n"
+ + " when 20 then 2\n"
+ + " when 10 then 1\n"
+ + " else cast(null as integer) end = 1";
// Equivalent to 'case when deptno = 20 then false
// when deptno = 10 then true
// else null end'
- checkPlanning(program,
- "select count(1) from emp\n"
- + "where case deptno\n"
- + " when 20 then 2\n"
- + " when 10 then 1\n"
- + " else cast(null as integer) end = 1");
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test public void testReduceConstantsCaseEquals3() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
- .build();
+ final String sql = "select count(1) from emp\n"
+ + "where case deptno\n"
+ + " when 30 then 1\n"
+ + " when 20 then 2\n"
+ + " when 10 then 1\n"
+ + " when 30 then 111\n"
+ + " else 0 end = 1";
// Equivalent to 'deptno = 30 or deptno = 10'
- checkPlanning(program,
- "select count(1) from emp\n"
- + "where case deptno\n"
- + " when 30 then 1\n"
- + " when 20 then 2\n"
- + " when 10 then 1\n"
- + " when 30 then 111\n"
- + " else 0 end = 1");
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.JOIN_INSTANCE)
+ .check();
}
@Test
public void testSkipReduceConstantsCaseEquals() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN)
- .build();
-
- checkPlanning(program,
- "select * from emp e1, emp e2\n"
- + "where coalesce(e1.mgr, -1) = coalesce(e2.mgr, -1)");
+ final String sql = "select * from emp e1, emp e2\n"
+ + "where coalesce(e1.mgr, -1) = coalesce(e2.mgr, -1)";
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE,
+ ReduceExpressionsRule.FILTER_INSTANCE,
+ FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN)
+ .check();
}
@Test public void testReduceConstantsEliminatesFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
+ final String sql = "select * from (values (1,2)) where 1 + 2 > 3 + CAST(NULL AS INTEGER)";
// WHERE NULL is the same as WHERE FALSE, so get empty result
- checkPlanning(program,
- "select * from (values (1,2)) where 1 + 2 > 3 + CAST(NULL AS INTEGER)");
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-1860">[CALCITE-1860]
* Duplicate null predicates cause NullPointerException in RexUtil</a>. */
@Test public void testReduceConstantsNull() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
final String sql = "select * from (\n"
+ " select *\n"
+ " from (\n"
@@ -2522,17 +2467,13 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " from emp)\n"
+ " where n is null and n is null)\n"
+ "where n is null";
- sql(sql).with(program).check();
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-566">[CALCITE-566]
* ReduceExpressionsRule requires planner to have an Executor</a>. */
@Test public void testReduceConstantsRequiresExecutor() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
// Remove the executor
tester.convertSqlToRel("values 1").rel.getCluster().getPlanner()
.setExecutor(null);
@@ -2540,16 +2481,13 @@ public class RelOptRulesTest extends RelOptTestBase {
// Rule should not fire, but there should be no NPE
final String sql =
"select * from (values (1,2)) where 1 + 2 > 3 + CAST(NULL AS INTEGER)";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public void testAlreadyFalseEliminatesFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
+ final String sql = "select * from (values (1,2)) where false";
- checkPlanning(program,
- "select * from (values (1,2)) where false");
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public void testReduceConstantsCalc() throws Exception {
@@ -2578,67 +2516,59 @@ public class RelOptRulesTest extends RelOptTestBase {
// Result should be same as typing
// SELECT * FROM (VALUES ('TABLE ', 'T')) AS T(U, S)
- checkPlanning(program,
- "select * from (\n"
- + " select upper(substring(x FROM 1 FOR 2) || substring(x FROM 3)) as u,\n"
- + " substring(x FROM 1 FOR 1) as s\n"
- + " from (\n"
- + " select 'table' as x from (values (true))\n"
- + " union\n"
- + " select 'view' from (values (true))\n"
- + " union\n"
- + " select 'foreign table' from (values (true))\n"
- + " )\n"
- + ") where u = 'TABLE'");
+ final String sql = "select * from (\n"
+ + " select upper(substring(x FROM 1 FOR 2) || substring(x FROM 3)) as u,\n"
+ + " substring(x FROM 1 FOR 1) as s\n"
+ + " from (\n"
+ + " select 'table' as x from (values (true))\n"
+ + " union\n"
+ + " select 'view' from (values (true))\n"
+ + " union\n"
+ + " select 'foreign table' from (values (true))\n"
+ + " )\n"
+ + ") where u = 'TABLE'";
+ sql(sql).with(program).check();
}
@Test public void testRemoveSemiJoin() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinRemoveRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e.ename from emp e, dept d "
- + "where e.deptno = d.deptno");
+ final String sql = "select e.ename from emp e, dept d\n"
+ + "where e.deptno = d.deptno";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinRemoveRule.INSTANCE)
+ .check();
}
@Test public void testRemoveSemiJoinWithFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinFilterTransposeRule.INSTANCE)
- .addRuleInstance(SemiJoinRemoveRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e.ename from emp e, dept d "
- + "where e.deptno = d.deptno and e.ename = 'foo'");
+ final String sql = "select e.ename from emp e, dept d\n"
+ + "where e.deptno = d.deptno and e.ename = 'foo'";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinFilterTransposeRule.INSTANCE,
+ SemiJoinRemoveRule.INSTANCE)
+ .check();
}
@Test public void testRemoveSemiJoinRight() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinJoinTransposeRule.INSTANCE)
- .addRuleInstance(SemiJoinRemoveRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e1.ename from emp e1, dept d, emp e2 "
- + "where e1.deptno = d.deptno and d.deptno = e2.deptno");
+ final String sql = "select e1.ename from emp e1, dept d, emp e2\n"
+ + "where e1.deptno = d.deptno and d.deptno = e2.deptno";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinJoinTransposeRule.INSTANCE,
+ SemiJoinRemoveRule.INSTANCE)
+ .check();
}
@Test public void testRemoveSemiJoinRightWithFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinJoinTransposeRule.INSTANCE)
- .addRuleInstance(SemiJoinFilterTransposeRule.INSTANCE)
- .addRuleInstance(SemiJoinRemoveRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e1.ename from emp e1, dept d, emp e2 "
- + "where e1.deptno = d.deptno and d.deptno = e2.deptno "
- + "and d.name = 'foo'");
+ final String sql = "select e1.ename from emp e1, dept d, emp e2\n"
+ + "where e1.deptno = d.deptno and d.deptno = e2.deptno\n"
+ + "and d.name = 'foo'";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinJoinTransposeRule.INSTANCE,
+ SemiJoinFilterTransposeRule.INSTANCE,
+ SemiJoinRemoveRule.INSTANCE)
+ .check();
}
private void checkPlanning(String query) throws Exception {
@@ -2668,8 +2598,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ProjectRemoveRule.INSTANCE)
.addRuleInstance(JoinToMultiJoinRule.INSTANCE)
.build();
- checkPlanning(tester1, null,
- new HepPlanner(program), query);
+ sql(query).withTester(t -> tester1)
+ .with(program)
+ .check();
}
@Test public void testConvertMultiJoinRuleOuterJoins() throws Exception {
@@ -2711,81 +2642,63 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testPushSemiJoinPastProject() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinAddRedundantSemiJoinRule.INSTANCE)
- .addRuleInstance(SemiJoinProjectTransposeRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select e.* from "
- + "(select ename, trim(job), sal * 2, deptno from emp) e, dept d "
- + "where e.deptno = d.deptno");
+ final String sql = "select e.* from\n"
+ + "(select ename, trim(job), sal * 2, deptno from emp) e, dept d\n"
+ + "where e.deptno = d.deptno";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinAddRedundantSemiJoinRule.INSTANCE,
+ SemiJoinProjectTransposeRule.INSTANCE)
+ .check();
}
@Test public void testReduceValuesUnderFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterProjectTransposeRule.INSTANCE)
- .addRuleInstance(ValuesReduceRule.FILTER_INSTANCE)
- .build();
-
// Plan should be same as for
// select a, b from (values (10,'x')) as t(a, b)");
- checkPlanning(program,
- "select a, b from (values (10, 'x'), (20, 'y')) as t(a, b) where a < 15");
+ final String sql = "select a, b from (values (10, 'x'), (20, 'y')) as t(a, b) where a < 15";
+ sql(sql).withRule(FilterProjectTransposeRule.INSTANCE,
+ ValuesReduceRule.FILTER_INSTANCE)
+ .check();
}
@Test public void testReduceValuesUnderProject() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectMergeRule.INSTANCE)
- .addRuleInstance(ValuesReduceRule.PROJECT_INSTANCE)
- .build();
-
// Plan should be same as for
// select a, b as x from (values (11), (23)) as t(x)");
- checkPlanning(program,
- "select a + b from (values (10, 1), (20, 3)) as t(a, b)");
+ final String sql = "select a + b from (values (10, 1), (20, 3)) as t(a, b)";
+ sql(sql).withRule(ProjectMergeRule.INSTANCE,
+ ValuesReduceRule.PROJECT_INSTANCE)
+ .check();
}
@Test public void testReduceValuesUnderProjectFilter() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterProjectTransposeRule.INSTANCE)
- .addRuleInstance(ProjectMergeRule.INSTANCE)
- .addRuleInstance(ValuesReduceRule.PROJECT_FILTER_INSTANCE)
- .build();
-
// Plan should be same as for
// select * from (values (11, 1, 10), (23, 3, 20)) as t(x, b, a)");
- checkPlanning(program,
- "select a + b as x, b, a from (values (10, 1), (30, 7), (20, 3)) as t(a, b)"
- + " where a - b < 21");
+ final String sql = "select a + b as x, b, a \n"
+ + "from (values (10, 1), (30, 7), (20, 3)) as t(a, b)\n"
+ + "where a - b < 21";
+ sql(sql).withRule(FilterProjectTransposeRule.INSTANCE,
+ ProjectMergeRule.INSTANCE,
+ ValuesReduceRule.PROJECT_FILTER_INSTANCE)
+ .check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-1439">[CALCITE-1439]
* Handling errors during constant reduction</a>. */
@Test public void testReduceCase() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .build();
-
final String sql = "select\n"
+ " case when false then cast(2.1 as float)\n"
+ " else cast(1 as integer) end as newcol\n"
+ "from emp";
- sql(sql).with(program)
+ sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE)
.withProperty(Hook.REL_BUILDER_SIMPLIFY, false)
.check();
}
private void checkReduceNullableToNotNull(ReduceExpressionsRule rule) {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(rule)
- .build();
-
final String sql = "select\n"
+ " empno + case when 'a' = 'a' then 1 else null end as newcol\n"
+ "from emp";
- sql(sql).with(program)
+ sql(sql).withRule(rule)
.withProperty(Hook.REL_BUILDER_SIMPLIFY, false)
.check();
}
@@ -2806,41 +2719,25 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testReduceConstantsIsNull() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
- checkPlanning(program,
- "select empno from emp where empno=10 and empno is null");
+ final String sql = "select empno from emp where empno=10 and empno is null";
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public void testReduceConstantsIsNotNull() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select empno from emp\n"
+ "where empno=10 and empno is not null";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public void testReduceConstantsNegated() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select empno from emp\n"
+ "where empno=10 and not(empno=10)";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public void testReduceConstantsNegatedInverted() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
final String sql = "select empno from emp where empno>10 and empno<=10";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
/** Test case for
@@ -2907,25 +2804,19 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testCasePushIsAlwaysWorking() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.CALC_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
- .build();
-
final String sql = "select empno from emp"
+ " where case when sal > 1000 then empno else sal end = 1";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE,
+ ReduceExpressionsRule.CALC_INSTANCE,
+ ReduceExpressionsRule.PROJECT_INSTANCE)
+ .check();
}
@Test public void testReduceValuesNull() throws Exception {
// The NULL literal presents pitfalls for value-reduction. Only
// an INSERT statement contains un-CASTed NULL values.
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ValuesReduceRule.PROJECT_INSTANCE)
- .build();
- checkPlanning(program,
- "insert into EMPNULLABLES(EMPNO, ENAME, JOB) (select 0, 'null', NULL)");
+ final String sql = "insert into EMPNULLABLES(EMPNO, ENAME, JOB) (select 0, 'null', NULL)";
+ sql(sql).withRule(ValuesReduceRule.PROJECT_INSTANCE).check();
}
@Test public void testReduceValuesToEmpty() throws Exception {
@@ -2937,9 +2828,9 @@ public class RelOptRulesTest extends RelOptTestBase {
// Plan should be same as for
// select * from (values (11, 1, 10), (23, 3, 20)) as t(x, b, a)");
- checkPlanning(program,
- "select a + b as x, b, a from (values (10, 1), (30, 7)) as t(a, b)"
- + " where a - b < 0");
+ final String sql = "select a + b as x, b, a from (values (10, 1), (30, 7)) as t(a, b)\n"
+ + "where a - b < 0";
+ sql(sql).with(program).check();
}
@Test public void testReduceConstantsWindow() {
@@ -2958,7 +2849,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " sum(sal) over (partition by deptno order by sal) as col3\n"
+ " from emp where sal = 5000)";
- checkPlanning(program, sql);
+ sql(sql).with(program).check();
}
@Test public void testEmptyFilterProjectUnion() throws Exception {
@@ -2973,13 +2864,13 @@ public class RelOptRulesTest extends RelOptTestBase {
// Plan should be same as for
// select * from (values (30, 3)) as t(x, y)");
- checkPlanning(program,
- "select * from (\n"
- + "select * from (values (10, 1), (30, 3)) as t (x, y)\n"
- + "union all\n"
- + "select * from (values (20, 2))\n"
- + ")\n"
- + "where x + y > 30");
+ final String sql = "select * from (\n"
+ + "select * from (values (10, 1), (30, 3)) as t (x, y)\n"
+ + "union all\n"
+ + "select * from (values (20, 2))\n"
+ + ")\n"
+ + "where x + y > 30";
+ sql(sql).with(program).check();
}
/** Test case for
@@ -3116,17 +3007,13 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(PruneEmptyRules.SORT_INSTANCE)
.build();
- checkPlanning(program,
- "select * from emp where false order by deptno");
+ final String sql = "select * from emp where false order by deptno";
+ sql(sql).with(program).check();
}
@Test public void testEmptySortLimitZero() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE)
- .build();
-
- checkPlanning(program,
- "select * from emp order by deptno limit 0");
+ final String sql = "select * from emp order by deptno limit 0";
+ sql(sql).withRule(PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE).check();
}
@Test public void testEmptyAggregate() {
@@ -3142,7 +3029,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select sum(empno) from emp where false group by deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram).with(program).check();
}
@Test public void testEmptyAggregateEmptyKey() {
@@ -3155,8 +3042,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select sum(empno) from emp where false";
- final boolean unchanged = true;
- checkPlanning(tester, preProgram, new HepPlanner(program), sql, unchanged);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .checkUnchanged();
}
@Test public void testEmptyAggregateEmptyKeyWithAggregateValuesRule() {
@@ -3181,11 +3069,12 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
// The resulting plan should have no cast expressions
- checkPlanUnchanged(new HepPlanner(program),
- "select cast(d.name as varchar(128)), cast(e.empno as integer) "
- + "from dept as d inner join emp as e "
- + "on cast(d.deptno as integer) = cast(e.deptno as integer) "
- + "where cast(e.job as varchar(1)) = 'Manager'");
+ final String sql = "select cast(d.name as varchar(128)), cast(e.empno as integer)\n"
+ + "from dept as d inner join emp as e\n"
+ + "on cast(d.deptno as integer) = cast(e.deptno as integer)\n"
+ + "where cast(e.job as varchar(1)) = 'Manager'";
+ sql(sql).with(program)
+ .checkUnchanged();
}
/** Tests that a cast from a TIME to a TIMESTAMP is not reduced. It is not
@@ -3203,15 +3092,11 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testReduceCastAndConsts() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
// Make sure constant expressions inside the cast can be reduced
// in addition to the casts.
- checkPlanning(program,
- "select * from emp "
- + "where cast((empno + (10/2)) as int) = 13");
+ final String sql = "select * from emp\n"
+ + "where cast((empno + (10/2)) as int) = 13";
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE).check();
}
@Test public <T> void testReduceCaseNullabilityChange() throws Exception {
@@ -3220,10 +3105,12 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
.build();
+ final String sql = "select case when empno = 1 then 1\n"
+ + "when 1 IS NOT NULL then 2\n"
+ + "else null end as qx "
+ + "from emp";
try (Hook.Closeable a = Hook.REL_BUILDER_SIMPLIFY.add(Hook.propertyJ(false))) {
- checkPlanning(program,
- "select case when empno = 1 then 1 when 1 IS NOT NULL then 2 else null end as qx "
- + "from emp");
+ sql(sql).with(program).check();
}
}
@@ -3241,9 +3128,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(CalcMergeRule.INSTANCE)
.addRuleInstance(ReduceExpressionsRule.CALC_INSTANCE)
.build();
- checkPlanning(program,
- "insert into sales.dept(deptno, name) "
- + "select empno, cast(job as varchar(128)) from sales.empnullables");
+ final String sql = "insert into sales.dept(deptno, name)\n"
+ + "select empno, cast(job as varchar(128)) from sales.empnullables";
+ sql(sql).with(program).check();
}
private void basePushAggThroughUnion() throws Exception {
@@ -3252,7 +3139,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ProjectMergeRule.INSTANCE)
.addRuleInstance(AggregateUnionTransposeRule.INSTANCE)
.build();
- checkPlanning(program, "${sql}");
+ sql("${sql}").with(program).check();
}
@Test public void testPushSumConstantThroughUnion() throws Exception {
@@ -3356,7 +3243,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " from emp"
+ " where sal > 5000)"
+ "group by ename, sal, deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram).with(program).check();
}
@Test public void testPullFilterThroughAggregateGroupingSets()
@@ -3373,7 +3260,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " from emp"
+ " where sal > 5000)"
+ "group by rollup(ename, sal, deptno)";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram).with(program).check();
}
private void basePullConstantTroughAggregate() throws Exception {
@@ -3382,7 +3269,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectPullUpConstantsRule.INSTANCE)
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
- checkPlanning(program, "${sql}");
+ sql("${sql}").with(program).check();
}
@Test public void testPullConstantThroughConstLast() throws
@@ -3450,7 +3337,7 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql = "select 2, deptno, job from emp as e1\n"
+ "union all\n"
+ "select 1, deptno, job from emp as e2";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).with(program).checkUnchanged();
}
@Test public void testPullConstantThroughUnion3()
@@ -3470,25 +3357,19 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testAggregateProjectMerge() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select x, sum(z), y from (\n"
- + " select deptno as x, empno as y, sal as z, sal * 2 as zz\n"
- + " from emp)\n"
- + "group by x, y");
+ final String sql = "select x, sum(z), y from (\n"
+ + " select deptno as x, empno as y, sal as z, sal * 2 as zz\n"
+ + " from emp)\n"
+ + "group by x, y";
+ sql(sql).withRule(AggregateProjectMergeRule.INSTANCE).check();
}
@Test public void testAggregateGroupingSetsProjectMerge() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select x, sum(z), y from (\n"
- + " select deptno as x, empno as y, sal as z, sal * 2 as zz\n"
- + " from emp)\n"
- + "group by rollup(x, y)");
+ final String sql = "select x, sum(z), y from (\n"
+ + " select deptno as x, empno as y, sal as z, sal * 2 as zz\n"
+ + " from emp)\n"
+ + "group by rollup(x, y)";
+ sql(sql).withRule(AggregateProjectMergeRule.INSTANCE).check();
}
@Test public void testAggregateExtractProjectRule() {
@@ -3932,13 +3813,10 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testProjectWindowTransposeRule() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectToWindowRule.PROJECT)
- .addRuleInstance(ProjectWindowTransposeRule.INSTANCE)
- .build();
-
final String sql = "select count(empno) over(), deptno from emp";
- checkPlanning(program, sql);
+ sql(sql).withRule(ProjectToWindowRule.PROJECT,
+ ProjectWindowTransposeRule.INSTANCE)
+ .check();
}
@Test public void testProjectWindowTransposeRuleWithConstants() {
@@ -3955,54 +3833,48 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " sum(1000) over(partition by deptno order by sal) as col2\n"
+ " from emp)";
- checkPlanning(program, sql);
+ sql(sql).with(program).check();
}
@Test public void testAggregateProjectPullUpConstants() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectPullUpConstantsRule.INSTANCE2)
- .build();
final String sql = "select job, empno, sal, sum(sal) as s\n"
+ "from emp where empno = 10\n"
+ "group by job, empno, sal";
- checkPlanning(program, sql);
+ sql(sql).withRule(AggregateProjectPullUpConstantsRule.INSTANCE2).check();
}
@Test public void testAggregateProjectPullUpConstants2() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectPullUpConstantsRule.INSTANCE2)
- .build();
final String sql = "select ename, sal\n"
+ "from (select '1', ename, sal from emp where ename = 'John') subq\n"
+ "group by ename, sal";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateProjectPullUpConstantsRule.INSTANCE2)
+ .check();
}
@Test public void testPushFilterWithRank() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterProjectTransposeRule.INSTANCE).build();
final String sql = "select e1.ename, r\n"
+ "from (\n"
+ " select ename, "
+ " rank() over(partition by deptno order by sal) as r "
+ " from emp) e1\n"
+ "where r < 2";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(FilterProjectTransposeRule.INSTANCE)
+ .checkUnchanged();
}
@Test public void testPushFilterWithRankExpr() throws Exception {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterProjectTransposeRule.INSTANCE).build();
final String sql = "select e1.ename, r\n"
+ "from (\n"
+ " select ename,\n"
+ " rank() over(partition by deptno order by sal) + 1 as r "
+ " from emp) e1\n"
+ "where r < 2";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(FilterProjectTransposeRule.INSTANCE)
+ .checkUnchanged();
}
/** Test case for
@@ -4087,24 +3959,18 @@ public class RelOptRulesTest extends RelOptTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-750">[CALCITE-750]
* Allow windowed aggregate on top of regular aggregate</a>. */
@Test public void testNestedAggregates() {
- final HepProgram program = HepProgram.builder()
- .addRuleInstance(ProjectToWindowRule.PROJECT)
- .build();
final String sql = "SELECT\n"
+ " avg(sum(sal) + 2 * min(empno) + 3 * avg(empno))\n"
+ " over (partition by deptno)\n"
+ "from emp\n"
+ "group by deptno";
- checkPlanning(program, sql);
+ sql(sql).withRule(ProjectToWindowRule.PROJECT).check();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2078">[CALCITE-2078]
* Aggregate functions in OVER clause</a>. */
@Test public void testWindowFunctionOnAggregations() {
- final HepProgram program = HepProgram.builder()
- .addRuleInstance(ProjectToWindowRule.PROJECT)
- .build();
final String sql = "SELECT\n"
+ " min(empno),\n"
+ " sum(sal),\n"
@@ -4112,21 +3978,20 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " over (partition by min(empno) order by sum(sal))\n"
+ "from emp\n"
+ "group by deptno";
- checkPlanning(program, sql);
+ sql(sql).withRule(ProjectToWindowRule.PROJECT).check();
}
@Test public void testPushAggregateThroughJoin1() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job,d.name\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4135,14 +4000,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.ename\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by e.ename";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4151,14 +4015,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select d.ename\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.emp as d on e.job = d.job\n"
+ "group by d.ename";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4167,14 +4030,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.ename, d.mgr\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.emp as d on e.job = d.job\n"
+ "group by e.ename,d.mgr";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4183,14 +4045,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4199,14 +4060,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select d.name\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4215,14 +4075,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job,d.name\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4231,15 +4090,14 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4248,15 +4106,14 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select d.name\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4265,15 +4122,14 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job, d.name\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job, d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4282,14 +4138,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select count(e.ename)\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.emp as d on e.job = d.job\n"
+ "group by e.ename,d.mgr";
- sql(sql).withPre(preProgram).with(program).checkUnchanged();
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .checkUnchanged();
}
/** Test case for
@@ -4298,14 +4153,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.empno,d.deptno\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "left outer join sales.dept as d on e.empno < d.deptno\n"
+ "group by e.empno,d.deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql, true);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .checkUnchanged();
}
/** Test case for
@@ -4314,14 +4168,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "right outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4330,14 +4183,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "full outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4346,14 +4198,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.mgr, d.mgr\n"
+ "from sales.emp as e\n"
+ "full outer join sales.emp as d on e.mgr = d.mgr\n"
+ "group by d.mgr, e.mgr";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4362,14 +4213,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.ename, d.mgr\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "full outer join sales.emp as d on e.job = d.job\n"
+ "group by e.ename,d.mgr";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4378,44 +4228,41 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "full outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
@Test public void testPushAggregateThroughJoin2() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job,d.name\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job,d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
@Test public void testPushAggregateThroughJoin3() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.empno,d.deptno\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "join sales.dept as d on e.empno < d.deptno\n"
+ "group by e.empno,d.deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql, true);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .checkUnchanged();
}
/** Test case for
@@ -4425,26 +4272,24 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.deptno\n"
+ "from sales.emp as e join sales.dept as d on e.deptno = d.deptno\n"
+ "group by e.deptno";
- sql(sql).withPre(preProgram).with(program).check();
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
@Test public void testPushAggregateThroughJoin5() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.deptno, d.deptno\n"
+ "from sales.emp as e join sales.dept as d on e.deptno = d.deptno\n"
+ "group by e.deptno, d.deptno";
- sql(sql).withPre(preProgram).with(program).check();
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4522,9 +4367,6 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select A.job, B.mgr, A.deptno,\n"
+ "max(B.hiredate1) as hiredate1, sum(B.comm1) as comm1\n"
+ "from sales.emp as A\n"
@@ -4532,7 +4374,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " sum(comm) as comm1 from sales.emp group by mgr, sal) as B\n"
+ "on A.sal=B.sal\n"
+ "group by A.job, B.mgr, A.deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** SUM is the easiest aggregate function to split. */
@@ -4540,14 +4384,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job,sum(sal)\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4558,13 +4401,12 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select sum(sal)\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "join sales.dept as d on e.job = d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4585,7 +4427,9 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql = "select sum(sal)\n"
+ "from (select * from sales.emp where empno = 10) as e\n"
+ "join sales.dept as d on e.job = d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .check();
}
/** Push a variety of aggregate functions. */
@@ -4593,9 +4437,6 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select e.job,\n"
+ " min(sal) as min_sal, min(e.deptno) as min_deptno,\n"
+ " sum(sal) + 1 as sum_sal_plus, max(sal) as max_sal,\n"
@@ -4604,7 +4445,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "from sales.emp as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Push a aggregate functions into a relation that is unique on the join
@@ -4613,16 +4456,15 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql = "select d.name,\n"
+ " sum(sal) as sum_sal, count(*) as c\n"
+ "from sales.emp as e\n"
+ "join (select distinct name from sales.dept) as d\n"
+ " on e.job = d.name\n"
+ "group by d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Push count(*) through join, no GROUP BY. */
@@ -4630,12 +4472,11 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql =
"select count(*) from sales.emp join sales.dept on job = name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4648,16 +4489,14 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ReduceExpressionsRule.FilterReduceExpressionsRule.FILTER_INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
-
final String sql =
"select count(*) volume, sum(C1.sal) C1_sum_sal "
+ "from (select sal, ename from sales.emp where 1=2) C1 "
+ "inner join (select ename from sales.emp) C2 "
+ "on C1.ename = C2.ename ";
- sql(sql).withPre(preProgram).with(program).check();
+ sql(sql).withPre(preProgram)
+ .withRule(AggregateJoinTransposeRule.EXTENDED)
+ .check();
}
/** Test case for
@@ -4665,12 +4504,10 @@ public class RelOptRulesTest extends RelOptTestBase {
* AggregateJoinTransposeRule generates inequivalent nodes if Aggregate relNode contains
* distinct aggregate function.</a>. */
@Test public void testPushDistinctAggregateIntoJoin() throws Exception {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
- .build();
final String sql =
"select count(distinct sal) from sales.emp join sales.dept on job = name";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(AggregateJoinTransposeRule.EXTENDED)
+ .checkUnchanged();
}
/**
@@ -4692,7 +4529,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " min(sal) y, count(hiredate) m, count(mgr) r\n"
+ " from sales.emp group by deptno, ename) t\n"
+ "group by deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .check();
}
/**
@@ -4714,7 +4553,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " from sales.emp\n"
+ " group by deptno, ename, empno) t\n"
+ "group by grouping sets(deptno, empno)";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .check();
}
/**
@@ -4840,7 +4681,9 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select sum(x) x, min(y) z from (\n"
+ " select sum(sal) x, min(sal) y from sales.emp)";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .check();
}
/**
@@ -4855,7 +4698,8 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql = "select empno, sum(sal), min(sal), max(sal), "
+ "bit_and(distinct sal), bit_or(sal), count(distinct sal) "
+ "from sales.emp group by empno, deptno\n";
- checkPlanning(program, sql);
+ sql(sql).with(program)
+ .check();
}
/**
@@ -4868,7 +4712,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
final String sql = "select distinct empno, deptno from sales.emp\n";
- checkPlanning(program, sql);
+ sql(sql).with(program)
+ .check();
}
/**
@@ -4884,7 +4729,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select empno, count(mgr) "
+ "from sales.emp group by empno, deptno\n";
- checkPlanning(program, sql);
+ sql(sql).with(program)
+ .check();
}
/**
@@ -4898,7 +4744,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select empno, max(sal), avg(sal) "
+ "from sales.emp group by empno, deptno\n";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .checkUnchanged();
}
/**
@@ -4912,7 +4759,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select empno, deptno, sum(sal) "
+ "from sales.emp group by cube(empno, deptno)\n";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .checkUnchanged();
}
/**
@@ -4926,7 +4774,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select deptno, max(sal) "
+ "from sales.emp group by deptno\n";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .checkUnchanged();
}
/** Test case for
@@ -4941,7 +4790,8 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql =
"select distinct e.deptno from sales.emp e\n"
+ "left outer join sales.dept d on e.deptno = d.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()} but has aggregate
@@ -4955,7 +4805,8 @@ public class RelOptRulesTest extends RelOptTestBase {
"select e.deptno, count(distinct e.job) from sales.emp e\n"
+ "left outer join sales.dept d on e.deptno = d.deptno\n"
+ "group by e.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()} but should not
@@ -4970,7 +4821,8 @@ public class RelOptRulesTest extends RelOptTestBase {
"select e.deptno, count(distinct d.name) from sales.emp e\n"
+ "left outer join sales.dept d on e.deptno = d.deptno\n"
+ "group by e.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()} but right join. */
@@ -4982,7 +4834,8 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql =
"select distinct d.deptno from sales.emp e\n"
+ "right outer join sales.dept d on e.deptno = d.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove2()} but right join. */
@@ -4995,7 +4848,8 @@ public class RelOptRulesTest extends RelOptTestBase {
"select d.deptno, count(distinct d.name) from sales.emp e\n"
+ "right outer join sales.dept d on e.deptno = d.deptno\n"
+ "group by d.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove3()} but right join. */
@@ -5008,7 +4862,8 @@ public class RelOptRulesTest extends RelOptTestBase {
"select d.deptno, count(distinct e.job) from sales.emp e\n"
+ "right outer join sales.dept d on e.deptno = d.deptno\n"
+ "group by d.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()};
@@ -5023,7 +4878,8 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "FROM sales.emp e\n"
+ "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n"
+ "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
@@ -5039,7 +4895,8 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n"
+ "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno\n"
+ "GROUP BY e.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove7()} but use columns in
@@ -5053,7 +4910,8 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "FROM sales.emp e\n"
+ "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n"
+ "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()};
@@ -5069,127 +4927,110 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n"
+ "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno\n"
+ "GROUP BY e.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).with(program)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should remove the bottom join since the project uses column in the
* right input of bottom join. */
@Test public void testProjectJoinRemove1() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e.deptno, d2.deptno\n"
+ "FROM sales.emp e\n"
+ "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n"
+ "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinJoinRemoveRule.INSTANCE)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should not remove the bottom join since the project uses column in the
* left input of bottom join. */
@Test public void testProjectJoinRemove2() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e.deptno, d1.deptno\n"
+ "FROM sales.emp e\n"
+ "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n"
+ "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinJoinRemoveRule.INSTANCE)
+ .checkUnchanged();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should not remove the bottom join since the right join keys of bottom
* join are not unique. */
@Test public void testProjectJoinRemove3() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e1.deptno, d.deptno\n"
+ "FROM sales.emp e1\n"
+ "LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno\n"
+ "LEFT JOIN sales.dept d ON e1.deptno = d.deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinJoinRemoveRule.INSTANCE)
+ .checkUnchanged();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should remove the left join since the join key of the right input is
* unique. */
@Test public void testProjectJoinRemove4() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e.deptno\n"
+ "FROM sales.emp e\n"
+ "LEFT JOIN sales.dept d ON e.deptno = d.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinRemoveRule.INSTANCE)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should not remove the left join since the join key of the right input is
* not unique. */
@Test public void testProjectJoinRemove5() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e1.deptno\n"
+ "FROM sales.emp e1\n"
+ "LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinRemoveRule.INSTANCE)
+ .checkUnchanged();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should not remove the left join since the project use columns in the right
* input of the join. */
@Test public void testProjectJoinRemove6() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e.deptno, d.name\n"
+ "FROM sales.emp e\n"
+ "LEFT JOIN sales.dept d ON e.deptno = d.deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinRemoveRule.INSTANCE)
+ .checkUnchanged();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should remove the right join since the join key of the left input is
* unique. */
@Test public void testProjectJoinRemove7() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e.deptno\n"
+ "FROM sales.dept d\n"
+ "RIGHT JOIN sales.emp e ON e.deptno = d.deptno";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinRemoveRule.INSTANCE)
+ .check();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should not remove the right join since the join key of the left input is
* not unique. */
@Test public void testProjectJoinRemove8() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e2.deptno\n"
+ "FROM sales.emp e1\n"
+ "RIGHT JOIN sales.emp e2 ON e1.deptno = e2.deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinRemoveRule.INSTANCE)
+ .checkUnchanged();
}
/** Similar to {@link #testAggregateJoinRemove1()};
* Should not remove the right join since the project uses columns in the
* left input of the join. */
@Test public void testProjectJoinRemove9() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ProjectJoinRemoveRule.INSTANCE)
- .build();
final String sql = "SELECT e.deptno, d.name\n"
+ "FROM sales.dept d\n"
+ "RIGHT JOIN sales.emp e ON e.deptno = d.deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(ProjectJoinRemoveRule.INSTANCE)
+ .checkUnchanged();
}
@Test public void testSwapOuterJoin() {
@@ -5197,59 +5038,54 @@ public class RelOptRulesTest extends RelOptTestBase {
.addMatchLimit(1)
.addRuleInstance(JoinCommuteRule.SWAP_OUTER)
.build();
- checkPlanning(program,
- "select 1 from sales.dept d left outer join sales.emp e"
- + " on d.deptno = e.deptno");
+ final String sql = "select 1 from sales.dept d left outer join sales.emp e\n"
+ + " on d.deptno = e.deptno";
+ sql(sql).with(program).check();
}
@Test public void testPushJoinCondDownToProject() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
- .addRuleInstance(JoinPushExpressionsRule.INSTANCE)
- .build();
- checkPlanning(program,
- "select d.deptno, e.deptno from sales.dept d, sales.emp e"
- + " where d.deptno + 10 = e.deptno * 2");
+ final String sql = "select d.deptno, e.deptno from sales.dept d, sales.emp e\n"
+ + " where d.deptno + 10 = e.deptno * 2";
+ sql(sql).withRule(FilterJoinRule.FILTER_ON_JOIN,
+ JoinPushExpressionsRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinTranspose1() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinTransposeRule.INSTANCE)
- .build();
final String sql = "select * from sales.emp e left join (\n"
+ " select * from sales.dept d) d on e.deptno = d.deptno\n"
+ "order by sal limit 10";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinTransposeRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinTranspose2() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinTransposeRule.INSTANCE)
- .build();
final String sql = "select * from sales.emp e right join (\n"
+ " select * from sales.dept d) d on e.deptno = d.deptno\n"
+ "order by name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinTransposeRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinTranspose3() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinTransposeRule.INSTANCE)
- .build();
// This one cannot be pushed down
final String sql = "select * from sales.emp e left join (\n"
+ " select * from sales.dept) d on e.deptno = d.deptno\n"
+ "order by sal, name limit 10";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql, true);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinTransposeRule.INSTANCE)
+ .checkUnchanged();
}
/** Test case for
@@ -5275,13 +5111,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinTransposeRule.INSTANCE)
- .build();
final String sql = "select * from sales.emp e right join (\n"
+ " select * from sales.dept d) d on e.deptno = d.deptno\n"
+ "order by name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withTester(t -> tester)
+ .withPre(preProgram)
+ .withRule(SortJoinTransposeRule.INSTANCE)
+ .check();
}
/** Test case for
@@ -5300,7 +5136,9 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql = "select * from sales.emp e right join (\n"
+ " select * from sales.dept d) d on e.deptno = d.deptno\n"
+ "limit 10";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql, true);
+ sql(sql).withPre(preProgram)
+ .with(program)
+ .checkUnchanged();
}
/** Test case for
@@ -5311,16 +5149,13 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinTransposeRule.INSTANCE)
- .build();
// This one can be pushed down even if it has an OFFSET, since the dept
// table is count-preserving against the join condition.
final String sql = "select d.deptno, empno from sales.dept d\n"
+ "right join sales.emp e using (deptno) limit 10 offset 2";
sql(sql)
.withPre(preProgram)
- .with(program)
+ .withRule(SortJoinTransposeRule.INSTANCE)
.check();
}
@@ -5332,51 +5167,36 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinTransposeRule.INSTANCE)
- .build();
// This one cannot be pushed down
final String sql = "select d.deptno, empno from sales.dept d\n"
+ "left join sales.emp e using (deptno) order by d.deptno offset 1";
sql(sql)
.withPre(preProgram)
- .with(program)
+ .withRule(SortJoinTransposeRule.INSTANCE)
.checkUnchanged();
}
@Test public void testSortProjectTranspose1() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortProjectTransposeRule.INSTANCE)
- .build();
// This one can be pushed down
final String sql = "select d.deptno from sales.dept d\n"
+ "order by cast(d.deptno as integer) offset 1";
- sql(sql)
- .with(program)
+ sql(sql).withRule(SortProjectTransposeRule.INSTANCE)
.check();
}
@Test public void testSortProjectTranspose2() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortProjectTransposeRule.INSTANCE)
- .build();
// This one can be pushed down
final String sql = "select d.deptno from sales.dept d\n"
+ "order by cast(d.deptno as double) offset 1";
- sql(sql)
- .with(program)
+ sql(sql).withRule(SortProjectTransposeRule.INSTANCE)
.check();
}
@Test public void testSortProjectTranspose3() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortProjectTransposeRule.INSTANCE)
- .build();
// This one cannot be pushed down
final String sql = "select d.deptno from sales.dept d\n"
+ "order by cast(d.deptno as varchar(10)) offset 1";
- sql(sql)
- .with(program)
+ sql(sql).withRule(SortJoinTransposeRule.INSTANCE)
.checkUnchanged();
}
@@ -5384,58 +5204,48 @@ public class RelOptRulesTest extends RelOptTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-1023">[CALCITE-1023]
* Planner rule that removes Aggregate keys that are constant</a>. */
@Test public void testAggregateConstantKeyRule() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectPullUpConstantsRule.INSTANCE2)
- .build();
final String sql = "select count(*) as c\n"
+ "from sales.emp\n"
+ "where deptno = 10\n"
+ "group by deptno, sal";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(AggregateProjectPullUpConstantsRule.INSTANCE2)
+ .check();
}
/** Tests {@link AggregateProjectPullUpConstantsRule} where reduction is not
* possible because "deptno" is the only key. */
@Test public void testAggregateConstantKeyRule2() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectPullUpConstantsRule.INSTANCE2)
- .build();
final String sql = "select count(*) as c\n"
+ "from sales.emp\n"
+ "where deptno = 10\n"
+ "group by deptno";
- checkPlanUnchanged(new HepPlanner(program), sql);
+ sql(sql).withRule(AggregateProjectPullUpConstantsRule.INSTANCE2)
+ .checkUnchanged();
}
/** Tests {@link AggregateProjectPullUpConstantsRule} where both keys are
* constants but only one can be removed. */
@Test public void testAggregateConstantKeyRule3() {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateProjectPullUpConstantsRule.INSTANCE2)
- .build();
final String sql = "select job\n"
+ "from sales.emp\n"
+ "where sal is null and job = 'Clerk'\n"
+ "group by sal, job\n"
+ "having count(*) > 3";
- checkPlanning(new HepPlanner(program), sql);
+ sql(sql).withRule(AggregateProjectPullUpConstantsRule.INSTANCE2)
+ .check();
}
@Test public void testReduceExpressionsNot() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
- checkPlanUnchanged(new HepPlanner(program),
- "select * from (values (false),(true)) as q (col1) where not(col1)");
+ final String sql = "select * from (values (false),(true)) as q (col1) where not(col1)";
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE)
+ .checkUnchanged();
}
private Sql checkSubQuery(String sql) {
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SubQueryRemoveRule.PROJECT)
- .addRuleInstance(SubQueryRemoveRule.FILTER)
- .addRuleInstance(SubQueryRemoveRule.JOIN)
- .build();
- return sql(sql).with(new HepPlanner(program)).expand(false);
+ return sql(sql).withRule(SubQueryRemoveRule.PROJECT,
+ SubQueryRemoveRule.FILTER,
+ SubQueryRemoveRule.JOIN)
+ .expand(false);
}
/** Tests expanding a sub-query, specifically an uncorrelated scalar
@@ -5962,12 +5772,11 @@ public class RelOptRulesTest extends RelOptTestBase {
final String sql = "select *\n"
+ "from sales.emp_b as e\n"
+ "where extract(year from birthdate) = 2014";
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(DateRangeRules.FILTER_INSTANCE)
- .build();
final Context context =
Contexts.of(new CalciteConnectionConfigImpl(new Properties()));
- sql(sql).with(program).withContext(context).check();
+ sql(sql).withRule(DateRangeRules.FILTER_INSTANCE)
+ .withContext(context)
+ .check();
}
@Test public void testExtractYearMonthToRange() {
@@ -5975,12 +5784,11 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "from sales.emp_b as e\n"
+ "where extract(year from birthdate) = 2014"
+ "and extract(month from birthdate) = 4";
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(DateRangeRules.FILTER_INSTANCE)
- .build();
final Context context =
Contexts.of(new CalciteConnectionConfigImpl(new Properties()));
- sql(sql).with(program).withContext(context).check();
+ sql(sql).withRule(DateRangeRules.FILTER_INSTANCE)
+ .withContext(context)
+ .check();
}
@Test public void testFilterRemoveIsNotDistinctFromRule() {
@@ -6012,14 +5820,11 @@ public class RelOptRulesTest extends RelOptTestBase {
}
@Test public void testOversimplifiedCaseStatement() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
String sql = "select * from emp "
+ "where MGR > 0 and "
+ "case when MGR > 0 then deptno / MGR else null end > 1";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
/** Test case for
@@ -6027,13 +5832,10 @@ public class RelOptRulesTest extends RelOptTestBase {
* ReduceExpressionRule may oversimplify filter conditions containing nulls</a>.
*/
@Test public void testNoOversimplificationBelowIsNull() {
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .build();
-
String sql =
"select * from emp where ( (empno=1 and mgr=1) or (empno=null and mgr=1) ) is null";
- checkPlanning(program, sql);
+ sql(sql).withRule(ReduceExpressionsRule.FILTER_INSTANCE)
+ .check();
}
@Test public void testExchangeRemoveConstantKeysRule() {
@@ -6071,81 +5873,65 @@ public class RelOptRulesTest extends RelOptTestBase {
@Test public void testReduceAverageWithNoReduceSum() {
final EnumSet<SqlKind> functionsToReduce = EnumSet.of(SqlKind.AVG);
- checkPlanning(
- new AggregateReduceFunctionsRule(
- LogicalAggregate.class, RelFactories.LOGICAL_BUILDER,
- functionsToReduce),
- "select name, max(name), avg(deptno), min(name)"
- + " from sales.dept group by name");
+ final RelOptRule rule = new AggregateReduceFunctionsRule(LogicalAggregate.class,
+ RelFactories.LOGICAL_BUILDER, functionsToReduce);
+ final String sql = "select name, max(name), avg(deptno), min(name)\n"
+ + "from sales.dept group by name";
+ sql(sql).withRule(rule).check();
}
@Test public void testNoReduceAverage() {
final EnumSet<SqlKind> functionsToReduce = EnumSet.noneOf(SqlKind.class);
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(
- new AggregateReduceFunctionsRule(LogicalAggregate.class,
- RelFactories.LOGICAL_BUILDER, functionsToReduce))
- .build();
+ final RelOptRule rule = new AggregateReduceFunctionsRule(LogicalAggregate.class,
+ RelFactories.LOGICAL_BUILDER, functionsToReduce);
String sql = "select name, max(name), avg(deptno), min(name)"
+ " from sales.dept group by name";
- sql(sql).with(program).checkUnchanged();
+ sql(sql).withRule(rule).checkUnchanged();
}
@Test public void testNoReduceSum() {
final EnumSet<SqlKind> functionsToReduce = EnumSet.noneOf(SqlKind.class);
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(
- new AggregateReduceFunctionsRule(LogicalAggregate.class,
- RelFactories.LOGICAL_BUILDER, functionsToReduce))
- .build();
+ final RelOptRule rule = new AggregateReduceFunctionsRule(LogicalAggregate.class,
+ RelFactories.LOGICAL_BUILDER, functionsToReduce);
String sql = "select name, sum(deptno)"
+ " from sales.dept group by name";
- sql(sql).with(program).checkUnchanged();
+ sql(sql).withRule(rule).checkUnchanged();
}
@Test public void testReduceAverageAndVarWithNoReduceStddev() {
// configure rule to reduce AVG and VAR_POP functions
// other functions like SUM, STDDEV won't be reduced
final EnumSet<SqlKind> functionsToReduce = EnumSet.of(SqlKind.AVG, SqlKind.VAR_POP);
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(
- new AggregateReduceFunctionsRule(LogicalAggregate.class,
- RelFactories.LOGICAL_BUILDER, functionsToReduce))
- .build();
+ final RelOptRule rule = new AggregateReduceFunctionsRule(LogicalAggregate.class,
+ RelFactories.LOGICAL_BUILDER, functionsToReduce);
final String sql = "select name, stddev_pop(deptno), avg(deptno),"
+ " var_pop(deptno)\n"
+ "from sales.dept group by name";
- sql(sql).with(program).check();
+ sql(sql).withRule(rule).check();
}
@Test public void testReduceAverageAndSumWithNoReduceStddevAndVar() {
// configure rule to reduce AVG and SUM functions
// other functions like VAR_POP, STDDEV_POP won't be reduced
final EnumSet<SqlKind> functionsToReduce = EnumSet.of(SqlKind.AVG, SqlKind.SUM);
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(
- new AggregateReduceFunctionsRule(LogicalAggregate.class,
- RelFactories.LOGICAL_BUILDER, functionsToReduce))
- .build();
+ final RelOptRule rule = new AggregateReduceFunctionsRule(LogicalAggregate.class,
+ RelFactories.LOGICAL_BUILDER, functionsToReduce);
final String sql = "select name, stddev_pop(deptno), avg(deptno),"
+ " var_pop(deptno)\n"
+ "from sales.dept group by name";
- sql(sql).with(program).check();
+ sql(sql).withRule(rule).check();
}
@Test public void testReduceAllAggregateFunctions() {
// configure rule to reduce all used functions
final EnumSet<SqlKind> functionsToReduce = EnumSet.of(SqlKind.AVG, SqlKind.SUM,
SqlKind.STDDEV_POP, SqlKind.STDDEV_SAMP, SqlKind.VAR_POP, SqlKind.VAR_SAMP);
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(
- new AggregateReduceFunctionsRule(LogicalAggregate.class,
- RelFactories.LOGICAL_BUILDER, functionsToReduce))
- .build();
+ final RelOptRule rule = new AggregateReduceFunctionsRule(LogicalAggregate.class,
+ RelFactories.LOGICAL_BUILDER, functionsToReduce);
final String sql = "select name, stddev_pop(deptno), avg(deptno),"
+ " stddev_samp(deptno), var_pop(deptno), var_samp(deptno)\n"
+ "from sales.dept group by name";
- sql(sql).with(program).check();
+ sql(sql).withRule(rule).check();
}
/** Test case for
@@ -6153,9 +5939,9 @@ public class RelOptRulesTest extends RelOptTestBase {
* Identify expanded IS NOT DISTINCT FROM expression when pushing project past join</a>.
*/
@Test public void testPushProjectWithIsNotDistinctFromPastJoin() {
- checkPlanning(ProjectJoinTransposeRule.INSTANCE,
- "select e.sal + b.comm from emp e inner join bonus b "
- + "on (e.ename || e.job) IS NOT DISTINCT FROM (b.ename || b.job) and e.deptno = 10");
+ final String sql = "select e.sal + b.comm from emp e inner join bonus b\n"
+ + "on (e.ename || e.job) IS NOT DISTINCT FROM (b.ename || b.job) and e.deptno = 10";
+ sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
}
@Test public void testDynamicStarWithUnion() {
@@ -6268,7 +6054,7 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "emp t2 "
+ "ON t1.deptno = t2.deptno "
+ "WHERE t1.ename is not distinct from t2.ename";
- checkPlanning(FilterJoinRule.FILTER_ON_JOIN, query);
+ sql(query).withRule(FilterJoinRule.FILTER_ON_JOIN).check();
}
/**
@@ -6364,26 +6150,23 @@ public class RelOptRulesTest extends RelOptTestBase {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinCopyRule.INSTANCE)
- .build();
final String sql = "select * from sales.emp join sales.dept on\n"
+ "sales.emp.deptno = sales.dept.deptno order by sal";
- final HepPlanner hepPlanner = new HepPlanner(program);
- checkPlanning(tester, preProgram, hepPlanner, sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinCopyRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinCopyInnerJoinOrderByLimit() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SortProjectTransposeRule.INSTANCE)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinCopyRule.INSTANCE)
- .build();
final String sql = "select * from sales.emp e join (\n"
+ " select * from sales.dept d) d on e.deptno = d.deptno\n"
+ "order by sal limit 10";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinCopyRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinCopyInnerJoinOrderByTwoFields() {
@@ -6395,45 +6178,44 @@ public class RelOptRulesTest extends RelOptTestBase {
.build();
final String sql = "select * from sales.emp e join sales.dept d on\n"
+ " e.deptno = d.deptno order by e.sal,d.name";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinCopyRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinCopySemiJoinOrderBy() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SemiJoinRule.PROJECT)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinCopyRule.INSTANCE)
- .build();
final String sql = "select * from sales.dept d where d.deptno in\n"
+ " (select e.deptno from sales.emp e) order by d.deptno";
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinCopyRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinCopySemiJoinOrderByLimitOffset() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SemiJoinRule.PROJECT)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinCopyRule.INSTANCE)
- .build();
final String sql = "select * from sales.dept d where d.deptno in\n"
+ " (select e.deptno from sales.emp e) order by d.deptno limit 10 offset 2";
// Do not copy the limit and offset
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinCopyRule.INSTANCE)
+ .check();
}
@Test public void testSortJoinCopySemiJoinOrderByOffset() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(SemiJoinRule.PROJECT)
.build();
- final HepProgram program = new HepProgramBuilder()
- .addRuleInstance(SortJoinCopyRule.INSTANCE)
- .build();
final String sql = "select * from sales.dept d where d.deptno in"
+ " (select e.deptno from sales.emp e) order by d.deptno offset 2";
// Do not copy the offset
- checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ sql(sql).withPre(preProgram)
+ .withRule(SortJoinCopyRule.INSTANCE)
+ .check();
}
/** Test case for
@@ -6448,7 +6230,9 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)\n"
+ "order by e1.empno";
- sql(sql).with(HepProgram.builder().build()).withDecorrelation(true).checkUnchanged();
+ sql(sql).with(HepProgram.builder().build())
+ .withDecorrelation(true)
+ .checkUnchanged();
}
/**
@@ -6461,7 +6245,8 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(FilterToCalcRule.INSTANCE)
.addRuleInstance(ReduceDecimalsRule.INSTANCE)
.build();
- checkPlanning(program, "select ename from emp where sal > cast (100.0 as decimal(4, 1))");
+ final String sql = "select ename from emp where sal > cast (100.0 as decimal(4, 1))";
+ sql(sql).with(program).check();
}
@Test public void testEnumerableCalcRule() {
@@ -6508,41 +6293,25 @@ public class RelOptRulesTest extends RelOptTestBase {
@Test public void testMaxReuseDistinctAttrWithMixedOptionality() {
final String sql = "select sum(distinct deptno), count(distinct deptno), "
+ "max(deptno) from emp";
-
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
- .build();
- sql(sql).with(program).check();
+ sql(sql).withRule(AggregateExpandDistinctAggregatesRule.INSTANCE).check();
}
@Test public void testMinReuseDistinctAttrWithMixedOptionality() {
final String sql = "select sum(distinct deptno), count(distinct deptno), "
+ "min(deptno) from emp";
-
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
- .build();
- sql(sql).with(program).check();
+ sql(sql).withRule(AggregateExpandDistinctAggregatesRule.INSTANCE).check();
}
@Test public void testBitAndReuseDistinctAttrWithMixedOptionality() {
final String sql = "select sum(distinct deptno), count(distinct deptno), "
+ "bit_and(deptno) from emp";
-
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
- .build();
- sql(sql).with(program).check();
+ sql(sql).withRule(AggregateExpandDistinctAggregatesRule.INSTANCE).check();
}
@Test public void testBitOrReuseDistinctAttrWithMixedOptionality() {
final String sql = "select sum(distinct deptno), count(distinct deptno), "
+ "bit_or(deptno) from emp";
-
- HepProgram program = new HepProgramBuilder()
- .addRuleInstance(AggregateExpandDistinctAggregatesRule.INSTANCE)
- .build();
- sql(sql).with(program).check();
+ sql(sql).withRule(AggregateExpandDistinctAggregatesRule.INSTANCE).check();
}
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
index be68d40..235a53f 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
@@ -44,11 +44,12 @@ import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Function;
+import java.util.function.UnaryOperator;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.notNullValue;
+import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertThat;
-import static org.junit.Assert.assertTrue;
/**
* RelOptTestBase is an abstract base for tests which exercise a planner and/or
@@ -64,12 +65,8 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
protected Tester createDynamicTester() {
return getTesterWithDynamicTable();
}
- /**
- * Checks the plan for a SQL statement before/after executing a given rule.
- *
- * @param rule Planner rule
- * @param sql SQL query
- */
+
+ @Deprecated // to be removed before 1.23
protected void checkPlanning(
RelOptRule rule,
String sql) {
@@ -81,93 +78,22 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
sql);
}
- /**
- * Checks the plan for a SQL statement before/after executing a given rule.
- *
- * @param rule Planner rule
- * @param sql SQL query
- */
- protected void checkPlanningDynamic(
- RelOptRule rule,
- String sql) {
- HepProgramBuilder programBuilder = HepProgram.builder();
- programBuilder.addRuleInstance(rule);
-
- checkPlanning(
- createDynamicTester(),
- null,
- new HepPlanner(programBuilder.build()),
- sql);
- }
- /**
- * Checks the plan for a SQL statement before/after executing a given rule.
- *
- * @param sql SQL query
- */
- protected void checkPlanningDynamic(
- String sql) {
- checkPlanning(
- createDynamicTester(),
- null,
- new HepPlanner(HepProgram.builder().build()),
- sql);
- }
-
- /**
- * Checks the plan for a SQL statement before/after executing a given
- * program.
- *
- * @param program Planner program
- * @param sql SQL query
- */
+ @Deprecated // to be removed before 1.23
protected void checkPlanning(HepProgram program, String sql) {
checkPlanning(new HepPlanner(program), sql);
}
- /**
- * Checks the plan for a SQL statement before/after executing a given
- * program, but with specified implicit type coercion flag.
- *
- * @param program Planner program
- * @param sql SQL query
- * @param typeCoercion Whether to open up the implicit type coercion
- */
- protected void checkPlanning(HepProgram program, String sql, boolean typeCoercion) {
- final Tester tester = typeCoercion ? this.tester : this.strictTester;
- checkPlanning(tester, null, new HepPlanner(program), sql);
- }
-
- /**
- * Checks the plan for a SQL statement before/after executing a given
- * planner.
- *
- * @param planner Planner
- * @param sql SQL query
- */
+ @Deprecated // to be removed before 1.23
protected void checkPlanning(RelOptPlanner planner, String sql) {
checkPlanning(tester, null, planner, sql);
}
- /**
- * Checks that the plan is the same before and after executing a given
- * planner. Useful for checking circumstances where rules should not fire.
- *
- * @param planner Planner
- * @param sql SQL query
- */
+ @Deprecated // to be removed before 1.23
protected void checkPlanUnchanged(RelOptPlanner planner, String sql) {
checkPlanning(tester, null, planner, sql, true);
}
- /**
- * Checks the plan for a SQL statement before/after executing a given rule,
- * with a pre-program to prepare the tree.
- *
- * @param tester Tester
- * @param preProgram Program to execute before comparing before state
- * @param planner Planner
- * @param sql SQL query
- */
+ @Deprecated // to be removed before 1.23
protected void checkPlanning(Tester tester, HepProgram preProgram,
RelOptPlanner planner, String sql) {
checkPlanning(tester, preProgram, planner, sql, false);
@@ -183,14 +109,14 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
* @param sql SQL query
* @param unchanged Whether the rule is to have no effect
*/
- protected void checkPlanning(Tester tester, HepProgram preProgram,
+ private void checkPlanning(Tester tester, HepProgram preProgram,
RelOptPlanner planner, String sql, boolean unchanged) {
final DiffRepository diffRepos = getDiffRepos();
String sql2 = diffRepos.expand("sql", sql);
final RelRoot root = tester.convertSqlToRel(sql2);
final RelNode relInitial = root.rel;
- assertTrue(relInitial != null);
+ assertNotNull(relInitial);
List<RelMetadataProvider> list = new ArrayList<>();
list.add(DefaultRelMetadataProvider.INSTANCE);
@@ -240,21 +166,23 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
/** Sets the SQL statement for a test. */
Sql sql(String sql) {
- return new Sql(sql, null, null,
+ return new Sql(tester, sql, null, null,
ImmutableMap.of(), ImmutableList.of());
}
/** Allows fluent testing. */
class Sql {
+ private final Tester tester;
private final String sql;
private HepProgram preProgram;
private final HepPlanner hepPlanner;
private final ImmutableMap<Hook, Consumer> hooks;
private ImmutableList<Function<Tester, Tester>> transforms;
- Sql(String sql, HepProgram preProgram, HepPlanner hepPlanner,
+ Sql(Tester tester, String sql, HepProgram preProgram, HepPlanner hepPlanner,
ImmutableMap<Hook, Consumer> hooks,
ImmutableList<Function<Tester, Tester>> transforms) {
+ this.tester = tester;
this.sql = sql;
this.preProgram = preProgram;
this.hepPlanner = hepPlanner;
@@ -262,16 +190,20 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
this.transforms = transforms;
}
+ public Sql withTester(UnaryOperator<Tester> transform) {
+ return new Sql(transform.apply(tester), sql, preProgram, hepPlanner, hooks, transforms);
+ }
+
public Sql withPre(HepProgram preProgram) {
- return new Sql(sql, preProgram, hepPlanner, hooks, transforms);
+ return new Sql(tester, sql, preProgram, hepPlanner, hooks, transforms);
}
public Sql with(HepPlanner hepPlanner) {
- return new Sql(sql, preProgram, hepPlanner, hooks, transforms);
+ return new Sql(tester, sql, preProgram, hepPlanner, hooks, transforms);
}
public Sql with(HepProgram program) {
- return new Sql(sql, preProgram, new HepPlanner(program), hooks,
+ return new Sql(tester, sql, preProgram, new HepPlanner(program), hooks,
transforms);
}
@@ -286,7 +218,7 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
/** Adds a transform that will be applied to {@link #tester}
* just before running the query. */
private Sql withTransform(Function<Tester, Tester> transform) {
- return new Sql(sql, preProgram, hepPlanner, hooks,
+ return new Sql(tester, sql, preProgram, hepPlanner, hooks,
FlatLists.append(transforms, transform));
}
@@ -294,7 +226,7 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
* hook (by calling {@link Hook#addThread(Consumer)})
* just before running the query, and remove the hook afterwards. */
public <T> Sql withHook(Hook hook, Consumer<T> handler) {
- return new Sql(sql, preProgram, hepPlanner,
+ return new Sql(tester, sql, preProgram, hepPlanner,
FlatLists.append(hooks, hook, handler), transforms);
}
@@ -330,10 +262,19 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
return withTransform(tester -> tester.withContext(context));
}
+ /**
+ * Checks the plan for a SQL statement before/after executing a given rule,
+ * with a optional pre-program specified by {@link #withPre(HepProgram)}
+ * to prepare the tree.
+ */
public void check() {
check(false);
}
+ /**
+ * Checks that the plan is the same before and after executing a given
+ * planner. Useful for checking circumstances where rules should not fire.
+ */
public void checkUnchanged() {
check(true);
}