This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit bfd6d558d3b7b56cc5a8ff13a4e6988f36ace03b Author: Julian Hyde <[email protected]> AuthorDate: Tue Jan 14 16:38:10 2020 -0800 Refactor MaterializationTest to use a fluent API --- .../apache/calcite/test/MaterializationTest.java | 1813 ++++++++++---------- 1 file changed, 946 insertions(+), 867 deletions(-) diff --git a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java index 2935359..23e94b5 100644 --- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java +++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java @@ -50,6 +50,7 @@ import org.apache.calcite.test.JdbcTest.Event; import org.apache.calcite.test.JdbcTest.Location; import org.apache.calcite.tools.RuleSet; import org.apache.calcite.tools.RuleSets; +import org.apache.calcite.util.ImmutableBeans; import org.apache.calcite.util.JsonBuilder; import org.apache.calcite.util.Smalls; import org.apache.calcite.util.TryThreadLocal; @@ -126,6 +127,19 @@ public class MaterializationTest { new RexSimplify(rexBuilder, RelOptPredicateList.EMPTY, RexUtil.EXECUTOR) .withParanoid(true); + private static Sql sql() { + return ImmutableBeans.create(Sql.class) + .withModel(HR_FKUK_MODEL) + .withRuleSet(RuleSets.ofList(ImmutableList.of())) + .withChecker(CONTAINS_M0); + } + + private static Sql sql(String materialize, String query) { + return sql() + .withMaterialize(materialize) + .withQuery(query); + } + @Test public void testScan() { CalciteAssert.that() .withMaterializations( @@ -172,7 +186,7 @@ public class MaterializationTest { + "union all select * from \"emps\" where \"empid\" < 200"; String mv = "select *, \"empid\" * 2 from (" + union + ")"; String query = "select * from (" + union + ") where (\"empid\" * 2) > 3"; - checkMaterialize(mv, query); + sql(mv, query).ok(); } @Test public void testFilterToProject1() { @@ -181,7 +195,7 @@ public class MaterializationTest { + "from \"emps\" group by \"deptno\""; String mv = "select \"c\", \"s\", \"s\" from (" + agg + ")"; String query = "select * from (" + agg + ") where (\"s\" * 0.8) > 10000"; - checkNoMaterialize(mv, query, HR_FKUK_MODEL); + sql(mv, query).noMat(); } @Test public void testFilterQueryOnProjectView() { @@ -202,55 +216,7 @@ public class MaterializationTest { /** Checks that a given query can use a materialized view with a given * definition. */ - private void checkMaterialize(String materialize, String query) { - checkMaterialize(materialize, query, HR_FKUK_MODEL, CONTAINS_M0, - RuleSets.ofList(ImmutableList.of())); - } - - /** Checks that a given query can use a materialized view with a given - * definition. */ - private void checkMaterialize( - String materialize, String query, boolean onlyBySubstitution) { - checkMaterialize(materialize, query, HR_FKUK_MODEL, CONTAINS_M0, - RuleSets.ofList(ImmutableList.of()), onlyBySubstitution); - } - - /** Checks that a given query can use a materialized view with a given - * definition. */ - private void checkMaterialize(String materialize, String query, String model, - Consumer<ResultSet> explainChecker) { - checkMaterialize(materialize, query, model, explainChecker, - RuleSets.ofList(ImmutableList.of())); - } - - /** Checks that a given query can use a materialized view with a given - * definition. */ - private void checkMaterialize(String materialize, String query, String model, - Consumer<ResultSet> explainChecker, final RuleSet rules) { - checkMaterialize(materialize, query, model, explainChecker, rules, false); - } - - /** Checks that a given query can use a materialized view with a given - * definition. */ - private void checkMaterialize(String materialize, String query, String model, - Consumer<ResultSet> explainChecker, final RuleSet rules, - boolean onlyBySubstitution) { - checkThatMaterialize(materialize, query, "m0", false, model, explainChecker, - rules, onlyBySubstitution).sameResultWithMaterializationsDisabled(); - } - - /** Checks that a given query can use a materialized view with a given - * definition. */ - private CalciteAssert.AssertQuery checkThatMaterialize(String materialize, - String query, String name, boolean existing, String model, - Consumer<ResultSet> explainChecker, final RuleSet rules) { - return checkThatMaterialize(materialize, query, name, existing, model, - explainChecker, rules, false); - } - - /** Checks that a given query can use a materialized view with a given - * definition. */ - private CalciteAssert.AssertQuery checkThatMaterialize(String materialize, + static CalciteAssert.AssertQuery checkThatMaterialize_(String materialize, String query, String name, boolean existing, String model, Consumer<ResultSet> explainChecker, final RuleSet rules, boolean onlyBySubstitution) { @@ -281,13 +247,7 @@ public class MaterializationTest { /** Checks that a given query CAN NOT use a materialized view with a given * definition. */ - private void checkNoMaterialize(String materialize, String query, - String model) { - checkNoMaterialize(materialize, query, model, false); - } - /** Checks that a given query CAN NOT use a materialized view with a given - * definition. */ - private void checkNoMaterialize(String materialize, String query, + private static void checkNoMaterialize_(String materialize, String query, String model, boolean onlyBySubstitution) { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); @@ -309,72 +269,68 @@ public class MaterializationTest { /** Runs the same test as {@link #testFilterQueryOnProjectView()} but more * concisely. */ @Test public void testFilterQueryOnProjectView0() { - checkMaterialize( - "select \"deptno\", \"empid\" from \"emps\"", - "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10"); + sql("select \"deptno\", \"empid\" from \"emps\"", + "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10") + .ok(); } /** As {@link #testFilterQueryOnProjectView()} but with extra column in * materialized view. */ @Test public void testFilterQueryOnProjectView1() { - checkMaterialize( - "select \"deptno\", \"empid\", \"name\" from \"emps\"", - "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10"); + sql("select \"deptno\", \"empid\", \"name\" from \"emps\"", + "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10") + .ok(); } /** As {@link #testFilterQueryOnProjectView()} but with extra column in both * materialized view and query. */ @Test public void testFilterQueryOnProjectView2() { - checkMaterialize( - "select \"deptno\", \"empid\", \"name\" from \"emps\"", - "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10"); + sql("select \"deptno\", \"empid\", \"name\" from \"emps\"", + "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10") + .ok(); } @Test public void testFilterQueryOnProjectView3() { - checkMaterialize( - "select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", - "select \"name\" from \"emps\" where \"deptno\" - 10 = 0"); + sql("select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", + "select \"name\" from \"emps\" where \"deptno\" - 10 = 0") + .ok(); } /** As {@link #testFilterQueryOnProjectView3()} but materialized view cannot * be used because it does not contain required expression. */ @Test public void testFilterQueryOnProjectView4() { - checkNoMaterialize( - "select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", - "select \"name\" from \"emps\" where \"deptno\" + 10 = 20", - HR_FKUK_MODEL); + sql("select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", + "select \"name\" from \"emps\" where \"deptno\" + 10 = 20") + .noMat(); } /** As {@link #testFilterQueryOnProjectView3()} but also contains an * expression column. */ @Test public void testFilterQueryOnProjectView5() { - checkMaterialize( - "select \"deptno\" - 10 as \"x\", \"empid\" + 1 as ee, \"name\"\n" + sql("select \"deptno\" - 10 as \"x\", \"empid\" + 1 as ee, \"name\"\n" + "from \"emps\"", "select \"name\", \"empid\" + 1 as e\n" - + "from \"emps\" where \"deptno\" - 10 = 2", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "from \"emps\" where \"deptno\" - 10 = 2") + .withResultContains( "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], " + "expr#4=[=($t0, $t3)], name=[$t2], E=[$t1], $condition=[$t4])\n" - + " EnumerableTableScan(table=[[hr, m0]]")); + + " EnumerableTableScan(table=[[hr, m0]]") + .ok(); } /** Cannot materialize because "name" is not projected in the MV. */ @Test public void testFilterQueryOnProjectView6() { - checkNoMaterialize( - "select \"deptno\" - 10 as \"x\", \"empid\" from \"emps\"", - "select \"name\" from \"emps\" where \"deptno\" - 10 = 0", - HR_FKUK_MODEL); + sql("select \"deptno\" - 10 as \"x\", \"empid\" from \"emps\"", + "select \"name\" from \"emps\" where \"deptno\" - 10 = 0") + .noMat(); } /** As {@link #testFilterQueryOnProjectView3()} but also contains an * expression column. */ @Test public void testFilterQueryOnProjectView7() { - checkNoMaterialize( - "select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", - "select \"name\", \"empid\" + 2 from \"emps\" where \"deptno\" - 10 = 0", - HR_FKUK_MODEL); + sql("select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", + "select \"name\", \"empid\" + 2 from \"emps\" where \"deptno\" - 10 = 0") + .noMat(); } /** Test case for @@ -428,137 +384,140 @@ public class MaterializationTest { @Tag("slow") @Test public void testFilterQueryOnFilterView() { - checkMaterialize( - "select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10", - "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10"); + sql("select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10", + "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10") + .ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. */ @Test public void testFilterQueryOnFilterView2() { - checkMaterialize( - "select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10", - "select \"empid\" + 1 as x, \"name\" from \"emps\" " - + "where \"deptno\" = 10 and \"empid\" < 150"); + final String materialize = "select \"deptno\", \"empid\", \"name\"\n" + + "from \"emps\" where \"deptno\" = 10"; + final String query = "select \"empid\" + 1 as x, \"name\"\n" + + "from \"emps\" where \"deptno\" = 10 and \"empid\" < 150"; + sql(materialize, query).ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * view. */ @Test public void testFilterQueryOnFilterView3() { - checkMaterialize( - "select \"deptno\", \"empid\", \"name\" from \"emps\" " - + "where \"deptno\" = 10 or \"deptno\" = 20 or \"empid\" < 160", - "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + final String materialize = "select \"deptno\", \"empid\", \"name\"\n" + + "from \"emps\"\n" + + "where \"deptno\" = 10 or \"deptno\" = 20 or \"empid\" < 160"; + final String query = "select \"empid\" + 1 as x, \"name\"\n" + + "from \"emps\"\n" + + "where \"deptno\" = 10"; + sql(materialize, query) + .withResultContains( "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t1, $t3)], expr#5=[10], " + "expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[=($t5, $t6)], X=[$t4], " + "name=[$t2], $condition=[$t7])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. */ @Test public void testFilterQueryOnFilterView4() { - checkMaterialize( - "select * from \"emps\" where \"deptno\" > 10", - "select \"name\" from \"emps\" where \"deptno\" > 30"); + sql("select * from \"emps\" where \"deptno\" > 10", + "select \"name\" from \"emps\" where \"deptno\" > 30") + .ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query and columns selected are subset of columns in materialized view. */ @Test public void testFilterQueryOnFilterView5() { - checkMaterialize( - "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", - "select \"name\" from \"emps\" where \"deptno\" > 30"); + sql("select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", + "select \"name\" from \"emps\" where \"deptno\" > 30") + .ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query and columns selected are subset of columns in materialized view. */ @Test public void testFilterQueryOnFilterView6() { - checkMaterialize( - "select \"name\", \"deptno\", \"salary\" from \"emps\" " - + "where \"salary\" > 2000.5", - "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000"); + final String materialize = "select \"name\", \"deptno\", \"salary\"\n" + + "from \"emps\"\n" + + "where \"salary\" > 2000.5"; + final String query = "select \"name\"\n" + + "from \"emps\"\n" + + "where \"deptno\" > 30 and \"salary\" > 3000"; + sql(materialize, query).ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query and columns selected are subset of columns in materialized view. * Condition here is complex. */ @Test public void testFilterQueryOnFilterView7() { - checkMaterialize( - "select * from \"emps\" where " - + "((\"salary\" < 1111.9 and \"deptno\" > 10)" - + "or (\"empid\" > 400 and \"salary\" > 5000) " - + "or \"salary\" > 500)", - "select \"name\" from \"emps\" where (\"salary\" > 1000 " - + "or (\"deptno\" >= 30 and \"salary\" <= 500))"); + final String materialize = "select * from \"emps\"\n" + + "where ((\"salary\" < 1111.9 and \"deptno\" > 10)\n" + + " or (\"empid\" > 400 and \"salary\" > 5000)\n" + + " or \"salary\" > 500)"; + final String query = "select \"name\"\n" + + "from \"emps\"\n" + + "where (\"salary\" > 1000\n" + + " or (\"deptno\" >= 30 and \"salary\" <= 500))"; + sql(materialize, query).ok(); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. However, columns selected are not present in columns of materialized * view, Hence should not use materialized view. */ @Test public void testFilterQueryOnFilterView8() { - checkNoMaterialize( - "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", - "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30", - HR_FKUK_MODEL); + sql("select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", + "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30") + .noMat(); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * query. */ @Test public void testFilterQueryOnFilterView9() { - checkNoMaterialize( - "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", - "select \"name\", \"empid\" from \"emps\" " - + "where \"deptno\" > 30 or \"empid\" > 10", - HR_FKUK_MODEL); + sql("select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", + "select \"name\", \"empid\" from \"emps\"\n" + + "where \"deptno\" > 30 or \"empid\" > 10") + .noMat(); } /** As {@link #testFilterQueryOnFilterView()} but condition currently * has unsupported type being checked on query. */ @Test public void testFilterQueryOnFilterView10() { - checkNoMaterialize( - "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10 " + sql("select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10 " + "and \"name\" = \'calcite\'", "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30 " - + "or \"empid\" > 10", - HR_FKUK_MODEL); + + "or \"empid\" > 10") + .noMat(); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * query and columns selected are subset of columns in materialized view. * Condition here is complex. */ @Test public void testFilterQueryOnFilterView11() { - checkNoMaterialize( - "select \"name\", \"deptno\" from \"emps\" where " + sql("select \"name\", \"deptno\" from \"emps\" where " + "(\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000)", - "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000", - HR_FKUK_MODEL); + "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000") + .noMat(); } /** As {@link #testFilterQueryOnFilterView()} but condition of * query is stronger but is on the column not present in MV (salary). */ @Test public void testFilterQueryOnFilterView12() { - checkNoMaterialize( - "select \"name\", \"deptno\" from \"emps\" where \"salary\" > 2000.5", - "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000", - HR_FKUK_MODEL); + sql("select \"name\", \"deptno\" from \"emps\" where \"salary\" > 2000.5", + "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000") + .noMat(); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * query and columns selected are subset of columns in materialized view. * Condition here is complex. */ @Test public void testFilterQueryOnFilterView13() { - checkNoMaterialize( - "select * from \"emps\" where " + sql("select * from \"emps\" where " + "(\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000)", "select \"name\" from \"emps\" where \"salary\" > 1000 " - + "or (\"deptno\" > 30 and \"salary\" > 3000)", - HR_FKUK_MODEL); + + "or (\"deptno\" > 30 and \"salary\" > 3000)") + .noMat(); } /** As {@link #testFilterQueryOnFilterView7()} but columns in materialized @@ -571,41 +530,41 @@ public class MaterializationTest { + "((\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000) " + "or \"salary\" > 500)"; - checkMaterialize(m, q); + sql(m, q).ok(); } /** As {@link #testFilterQueryOnFilterView13()} but using alias * and condition of query is stronger. */ @Test public void testAlias() { - checkMaterialize( - "select * from \"emps\" as em where " + sql("select * from \"emps\" as em where " + "(em.\"salary\" < 1111.9 and em.\"deptno\" > 10)" + "or (em.\"empid\" > 400 and em.\"salary\" > 5000)", "select \"name\" as n from \"emps\" as e where " - + "(e.\"empid\" > 500 and e.\"salary\" > 6000)"); + + "(e.\"empid\" > 500 and e.\"salary\" > 6000)") + .ok(); } /** Aggregation query at same level of aggregation as aggregation * materialization. */ @Test public void testAggregate0() { - checkMaterialize( - "select count(*) as c from \"emps\" group by \"empid\"", - "select count(*) + 1 as c from \"emps\" group by \"empid\""); + sql("select count(*) as c from \"emps\" group by \"empid\"", + "select count(*) + 1 as c from \"emps\" group by \"empid\"") + .ok(); } /** * Aggregation query at same level of aggregation as aggregation * materialization but with different row types. */ @Test public void testAggregate1() { - checkMaterialize( - "select count(*) as c0 from \"emps\" group by \"empid\"", - "select count(*) as c1 from \"emps\" group by \"empid\""); + sql("select count(*) as c0 from \"emps\" group by \"empid\"", + "select count(*) as c1 from \"emps\" group by \"empid\"") + .ok(); } @Test public void testAggregate2() { - checkMaterialize( - "select \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" group by \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\""); + sql("select \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" group by \"deptno\"", + "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"") + .ok(); } @Test public void testAggregate3() { @@ -625,7 +584,7 @@ public class MaterializationTest { + " (select \"deptno\", \"salary\", 100 as \"k\"\n" + " from " + deduplicated + ")\n" + "group by \"deptno\""; - checkMaterialize(mv, query); + sql(mv, query).ok(); } @Test public void testAggregate4() { @@ -638,7 +597,7 @@ public class MaterializationTest { + "from \"emps\"\n" + "where \"commission\" = 100\n" + "group by \"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testAggregate5() { @@ -651,7 +610,7 @@ public class MaterializationTest { + "from \"emps\"\n" + "where \"commission\" * (\"deptno\" + \"commission\") = 100\n" + "group by \"commission\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } /** @@ -672,7 +631,7 @@ public class MaterializationTest { + "where \"salary\" > 1000\n" + "group by \"deptno\")\n" + "where \"sum_salary\" > 10"; - checkNoMaterialize(mv, query, HR_FKUK_MODEL, true); + sql(mv, query).withOnlyBySubstitution(true).noMat(); } @Test public void testAggregate7() { @@ -710,7 +669,7 @@ public class MaterializationTest { + "from \"emps\"\n" + "group by \"deptno\")\n" + "where \"sum_salary\" > 10"; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } /** @@ -731,7 +690,7 @@ public class MaterializationTest { + "where \"deptno\" >=20\n" + "group by \"deptno\")\n" + "where \"sum_salary\" > 10"; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } /** @@ -753,83 +712,106 @@ public class MaterializationTest { + "where \"deptno\" >= 20\n" + "group by \"deptno\")\n" + "where \"sum_salary\" > 20"; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } /** Aggregation query at same level of aggregation as aggregation * materialization with grouping sets. */ @Test public void testAggregateGroupSets1() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"salary\") as s from \"emps\" group by cube(\"empid\",\"deptno\")", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by cube(\"empid\",\"deptno\")"); + final String materialize = "" + + "select \"empid\", \"deptno\", count(*) as c, sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "group by cube(\"empid\",\"deptno\")"; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by cube(\"empid\",\"deptno\")"; + sql(materialize, query).ok(); } /** Aggregation query with different grouping sets, should not * do materialization. */ @Test public void testAggregateGroupSets2() { - checkNoMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"salary\") as s from \"emps\" group by cube(\"empid\",\"deptno\")", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by rollup(\"empid\",\"deptno\")", - HR_FKUK_MODEL); + final String materialize = "select \"empid\", \"deptno\",\n" + + " count(*) as c, sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "group by cube(\"empid\",\"deptno\")"; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by rollup(\"empid\",\"deptno\")"; + sql(materialize, query).noMat(); } /** Aggregation query at coarser level of aggregation than aggregation * materialization. Requires an additional aggregate to roll up. Note that * COUNT is rolled up using SUM0. */ @Test public void testAggregateRollUp() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" " - + "group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + final String materialize = "select \"empid\", \"deptno\", count(*) as c,\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by \"deptno\""; + sql(materialize, query) + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], " + "expr#3=[+($t1, $t2)], C=[$t3], deptno=[$t0])\n" + " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($2)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } /** Aggregation query with groupSets at coarser level of aggregation than * aggregation materialization. Requires an additional aggregate to roll up. * Note that COUNT is rolled up using SUM0. */ @Test public void testAggregateGroupSetsRollUp() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"salary\") as s from \"emps\" " - + "group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by cube(\"empid\",\"deptno\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " - + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" - + " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]], agg#0=[$SUM0($2)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"empid\", \"deptno\", count(*) as c,\n" + + " sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by cube(\"empid\",\"deptno\")"; + final String expected = "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " + + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" + + " EnumerableAggregate(group=[{0, 1}], " + + "groups=[[{0, 1}, {0}, {1}, {}]], agg#0=[$SUM0($2)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testAggregateGroupSetsRollUp2() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" " - + "group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by cube(\"empid\",\"deptno\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " - + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" - + " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]], agg#0=[$SUM0($2)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"empid\", \"deptno\", count(*) as c,\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by cube(\"empid\",\"deptno\")"; + final String expected = "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " + + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" + + " EnumerableAggregate(group=[{0, 1}], " + + "groups=[[{0, 1}, {0}, {1}, {}]], agg#0=[$SUM0($2)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } /** Aggregation materialization with a project. */ @Test public void testAggregateProject() { // Note that materialization does not start with the GROUP BY columns. // Not a smart way to design a materialization, but people may do it. - checkMaterialize( - "select \"deptno\", count(*) as c, \"empid\" + 2, sum(\"empid\") as s from \"emps\" group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1, $t2)], C=[$t3], deptno=[$t0])\n" - + " EnumerableAggregate(group=[{0}], agg#0=[$SUM0($1)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"deptno\", count(*) as c,\n" + + " \"empid\" + 2, sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by \"deptno\""; + final String expected = "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], " + + "expr#3=[+($t1, $t2)], C=[$t3], deptno=[$t0])\n" + + " EnumerableAggregate(group=[{0}], agg#0=[$SUM0($1)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } /** Test case for @@ -837,49 +819,61 @@ public class MaterializationTest { * AggregateOnProjectToAggregateUnifyRule ignores Project incorrectly when its * Mapping breaks ordering</a>. */ @Test public void testAggregateOnProject1() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" " - + "group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\", \"empid\""); + final String materialize = "select \"empid\", \"deptno\", count(*) as c,\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by \"deptno\", \"empid\""; + sql(materialize, query).ok(); } @Test public void testAggregateOnProject2() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"salary\") as s from \"emps\" " - + "group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by cube(\"deptno\", \"empid\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " - + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" - + " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]], agg#0=[$SUM0($2)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"empid\", \"deptno\", count(*) as c,\n" + + " sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by cube(\"deptno\", \"empid\")"; + final String expected = "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " + + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" + + " EnumerableAggregate(group=[{0, 1}], " + + "groups=[[{0, 1}, {0}, {1}, {}]], agg#0=[$SUM0($2)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testAggregateOnProject3() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"salary\") as s from \"emps\" " - + "group by \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by rollup(\"deptno\", \"empid\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " - + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" - + " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {1}, {}]], agg#0=[$SUM0($2)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"empid\", \"deptno\", count(*) as c,\n" + + " sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by rollup(\"deptno\", \"empid\")"; + final String expected = "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], " + + "expr#4=[+($t2, $t3)], C=[$t4], deptno=[$t1])\n" + + " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {1}, {}]], agg#0=[$SUM0($2)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testAggregateOnProject4() { - checkMaterialize( - "select \"salary\", \"empid\", \"deptno\", count(*) as c, sum(\"commission\") as s from \"emps\" " - + "group by \"salary\", \"empid\", \"deptno\"", - "select count(*) + 1 as c, \"deptno\" from \"emps\" group by rollup(\"empid\", \"deptno\", \"salary\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], " - + "expr#5=[+($t3, $t4)], C=[$t5], deptno=[$t2])\n" - + " EnumerableAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {1, 2}, {1}, {}]], agg#0=[$SUM0($3)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"salary\", \"empid\", \"deptno\",\n" + + " count(*) as c, sum(\"commission\") as s\n" + + "from \"emps\"\n" + + "group by \"salary\", \"empid\", \"deptno\""; + final String query = "select count(*) + 1 as c, \"deptno\"\n" + + "from \"emps\"\n" + + "group by rollup(\"empid\", \"deptno\", \"salary\")"; + final String expected = "EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], " + + "expr#5=[+($t3, $t4)], C=[$t5], deptno=[$t2])\n" + + " EnumerableAggregate(group=[{0, 1, 2}], " + + "groups=[[{0, 1, 2}, {1, 2}, {1}, {}]], agg#0=[$SUM0($3)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } /** Test case for @@ -887,15 +881,14 @@ public class MaterializationTest { * AggregateOnCalcToAggregateUnifyRule ignores Project incorrectly when * there's missing grouping or mapping breaks ordering</a>. */ @Test public void testAggregateOnProject5() { - checkMaterialize( - "select \"empid\", \"deptno\", \"name\", count(*) from \"emps\"\n" + sql("select \"empid\", \"deptno\", \"name\", count(*) from \"emps\"\n" + "group by \"empid\", \"deptno\", \"name\"", - "select \"name\", \"empid\", count(*) from \"emps\" group by \"name\", \"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains("" + "select \"name\", \"empid\", count(*) from \"emps\" group by \"name\", \"empid\"") + .withResultContains("" + "EnumerableCalc(expr#0..2=[{inputs}], name=[$t1], empid=[$t0], EXPR$2=[$t2])\n" + " EnumerableAggregate(group=[{0, 2}], EXPR$2=[$SUM0($3)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateOnProjectAndFilter() { @@ -908,7 +901,7 @@ public class MaterializationTest { + "from \"emps\"\n" + "where \"deptno\" = 10\n" + "group by \"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testProjectOnProject() { @@ -920,16 +913,20 @@ public class MaterializationTest { + "select \"deptno\", sum(\"salary\") + 2\n" + "from \"emps\"\n" + "group by \"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testPermutationError() { - checkMaterialize( - "select min(\"salary\"), count(*), max(\"salary\"), sum(\"salary\"), \"empid\" " - + "from \"emps\" group by \"empid\"", - "select count(*), \"empid\" from \"emps\" group by \"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains("EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select min(\"salary\"), count(*),\n" + + " max(\"salary\"), sum(\"salary\"), \"empid\"\n" + + "from \"emps\"\n" + + "group by \"empid\""; + final String query = "select count(*), \"empid\"\n" + + "from \"emps\"\n" + + "group by \"empid\""; + sql(materialize, query) + .withResultContains("EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinOnLeftProjectToJoin() { @@ -953,7 +950,7 @@ public class MaterializationTest { + " from \"depts\"\n" + " group by \"deptno\") \"B\"\n" + " on \"A\".\"deptno\" = \"B\".\"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testJoinOnRightProjectToJoin() { @@ -977,7 +974,7 @@ public class MaterializationTest { + " from \"depts\"\n" + " group by \"deptno\") \"B\"\n" + " on \"A\".\"deptno\" = \"B\".\"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testJoinOnProjectsToJoin() { @@ -1001,7 +998,7 @@ public class MaterializationTest { + " from \"depts\"\n" + " group by \"deptno\") \"B\"\n" + " on \"A\".\"deptno\" = \"B\".\"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testJoinOnCalcToJoin0() { @@ -1014,7 +1011,7 @@ public class MaterializationTest { + " (select \"empid\", \"deptno\" from \"emps\" where \"deptno\" > 10) A" + " join \"depts\"\n" + "on \"A\".\"deptno\" = \"depts\".\"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testJoinOnCalcToJoin1() { @@ -1027,7 +1024,7 @@ public class MaterializationTest { + "\"emps\" join\n" + "(select \"deptno\" from \"depts\" where \"deptno\" > 10) B\n" + "on \"emps\".\"deptno\" = \"B\".\"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testJoinOnCalcToJoin2() { @@ -1041,7 +1038,7 @@ public class MaterializationTest { + "join\n" + "(select \"deptno\" from \"depts\" where \"deptno\" > 10) B\n" + "on \"A\".\"deptno\" = \"B\".\"deptno\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testJoinOnCalcToJoin3() { @@ -1056,45 +1053,50 @@ public class MaterializationTest { + "(select \"deptno\" from \"depts\" where \"deptno\" > 10) B\n" + "on \"A\".\"deptno\" = \"B\".\"deptno\""; // Match failure because join condition references non-mapping projects. - checkNoMaterialize(mv, query, HR_FKUK_MODEL, true); + sql(mv, query).withOnlyBySubstitution(true).noMat(); } @Test public void testJoinOnCalcToJoin4() { - String mv = "" - + "select \"emps\".\"empid\", \"emps\".\"deptno\", \"depts\".\"deptno\" from\n" - + "\"emps\" join \"depts\"\n" - + "on \"emps\".\"deptno\" = \"depts\".\"deptno\""; - String query = "" - + "select * from\n" + String mv = "select \"emps\".\"empid\", \"emps\".\"deptno\",\n" + + " \"depts\".\"deptno\"\n" + + "from \"emps\"\n" + + "join \"depts\" on \"emps\".\"deptno\" = \"depts\".\"deptno\""; + String query = "select *\n" + + "from\n" + "(select \"empid\", \"deptno\" from \"emps\" where \"empid\" is not null) A\n" + "full join\n" + "(select \"deptno\" from \"depts\" where \"deptno\" is not null) B\n" + "on \"A\".\"deptno\" = \"B\".\"deptno\""; // Match failure because of outer join type but filtering condition in Calc is not empty. - checkNoMaterialize(mv, query, HR_FKUK_MODEL, true); + sql(mv, query).withOnlyBySubstitution(true).noMat(); } @Tag("slow") @Test public void testSwapJoin() { - checkMaterialize( - "select count(*) as c from \"foodmart\".\"sales_fact_1997\" as s join \"foodmart\".\"time_by_day\" as t on s.\"time_id\" = t.\"time_id\"", - "select count(*) as c from \"foodmart\".\"time_by_day\" as t join \"foodmart\".\"sales_fact_1997\" as s on t.\"time_id\" = s.\"time_id\"", - JdbcTest.FOODMART_MODEL, - CalciteAssert.checkResultContains("EnumerableTableScan(table=[[mat, m0]])")); + final String materialize = "select count(*) as c\n" + + "from \"foodmart\".\"sales_fact_1997\" as s\n" + + "join \"foodmart\".\"time_by_day\" as t on s.\"time_id\" = t.\"time_id\""; + final String query = "select count(*) as c\n" + + "from \"foodmart\".\"time_by_day\" as t\n" + + "join \"foodmart\".\"sales_fact_1997\" as s on t.\"time_id\" = s.\"time_id\""; + sql(materialize, query) + .withModel(JdbcTest.FOODMART_MODEL) + .withResultContains("EnumerableTableScan(table=[[mat, m0]])") + .ok(); } @Disabled @Test public void testOrderByQueryOnProjectView() { - checkMaterialize( - "select \"deptno\", \"empid\" from \"emps\"", - "select \"empid\" from \"emps\" order by \"deptno\""); + sql("select \"deptno\", \"empid\" from \"emps\"", + "select \"empid\" from \"emps\" order by \"deptno\"") + .ok(); } @Disabled @Test public void testOrderByQueryOnOrderByView() { - checkMaterialize( - "select \"deptno\", \"empid\" from \"emps\" order by \"deptno\"", - "select \"empid\" from \"emps\" order by \"deptno\""); + sql("select \"deptno\", \"empid\" from \"emps\" order by \"deptno\"", + "select \"empid\" from \"emps\" order by \"deptno\"") + .ok(); } @Disabled @@ -1434,7 +1436,7 @@ public class MaterializationTest { */ @Disabled @Test public void testFilterGroupQueryOnStar() { - checkMaterialize("select p.\"product_name\", t.\"the_year\",\n" + sql("select p.\"product_name\", t.\"the_year\",\n" + " sum(f.\"unit_sales\") as \"sum_unit_sales\", count(*) as \"c\"\n" + "from \"foodmart\".\"sales_fact_1997\" as f\n" + "join (\n" @@ -1458,9 +1460,9 @@ public class MaterializationTest { + "where t.\"the_year\" = 1997\n" + "and t.\"time_id\" = f.\"time_id\"\n" + "group by t.\"the_year\",\n" - + " t.\"the_month\"\n", - JdbcTest.FOODMART_MODEL, - CONTAINS_M0); + + " t.\"the_month\"\n") + .withModel(JdbcTest.FOODMART_MODEL) + .ok(); } /** Simpler than {@link #testFilterGroupQueryOnStar()}, tests a query on a @@ -1472,9 +1474,9 @@ public class MaterializationTest { + "join \"foodmart\".\"time_by_day\" as t on f.\"time_id\" = t.\"time_id\"\n" + "join \"foodmart\".\"product\" as p on f.\"product_id\" = p.\"product_id\"\n" + "join \"foodmart\".\"product_class\" as pc on p.\"product_class_id\" = pc.\"product_class_id\"\n"; - checkMaterialize( - q, q + "where t.\"month_of_year\" = 10", JdbcTest.FOODMART_MODEL, - CONTAINS_M0); + sql(q, q + "where t.\"month_of_year\" = 10") + .withModel(JdbcTest.FOODMART_MODEL) + .ok(); } /** A materialization that is a join of a union cannot at present be converted @@ -1485,14 +1487,14 @@ public class MaterializationTest { String q = "select *\n" + "from (select * from \"emps\" union all select * from \"emps\")\n" + "join \"depts\" using (\"deptno\")"; - checkNoMaterialize(q, q, HR_FKUK_MODEL); + sql(q, q).noMat(); } @Test public void testJoinMaterialization() { String q = "select *\n" + "from (select * from \"emps\" where \"empid\" < 300)\n" + "join \"depts\" using (\"deptno\")"; - checkMaterialize("select * from \"emps\" where \"empid\" < 500", q); + sql("select * from \"emps\" where \"empid\" < 500", q).ok(); } /** Test case for @@ -1505,7 +1507,7 @@ public class MaterializationTest { + "join \"depts\" using (\"deptno\")"; final String m = "select \"deptno\", \"empid\", \"name\",\n" + "\"salary\", \"commission\" from \"emps\""; - checkMaterialize(m, q); + sql(m, q).ok(); } @Test public void testJoinMaterialization3() { @@ -1513,548 +1515,589 @@ public class MaterializationTest { + "join \"depts\" using (\"deptno\") where \"empid\" = 1"; final String m = "select \"empid\" \"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\")"; - checkMaterialize(m, q); + sql(m, q).ok(); } @Test public void testUnionAll() { String q = "select * from \"emps\" where \"empid\" > 300\n" + "union all select * from \"emps\" where \"empid\" < 200"; String m = "select * from \"emps\" where \"empid\" < 500"; - checkMaterialize(m, q, HR_FKUK_MODEL, + sql(m, q).withChecker( CalciteAssert.checkResultContains( - "EnumerableTableScan(table=[[hr, m0]])", 1)); + "EnumerableTableScan(table=[[hr, m0]])", 1)) + .ok(); } @Test public void testAggregateMaterializationNoAggregateFuncs1() { - checkMaterialize( - "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableTableScan(table=[[hr, m0]])")); + sql("select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", + "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"") + .withResultContains( + "EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationNoAggregateFuncs2() { - checkMaterialize( - "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + sql("select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", + "select \"deptno\" from \"emps\" group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationNoAggregateFuncs3() { - checkNoMaterialize( - "select \"deptno\" from \"emps\" group by \"deptno\"", - "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - HR_FKUK_MODEL); + sql("select \"deptno\" from \"emps\" group by \"deptno\"", + "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"") + .noMat(); } @Test public void testAggregateMaterializationNoAggregateFuncs4() { - checkMaterialize( - "select \"empid\", \"deptno\" from \"emps\" where \"deptno\" = 10 group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" where \"deptno\" = 10 group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"empid\", \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" = 10\n" + + "group by \"empid\", \"deptno\""; + final String query = "select \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" = 10\n" + + "group by \"deptno\""; + final String expected = "EnumerableAggregate(group=[{1}])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testAggregateMaterializationNoAggregateFuncs5() { - checkNoMaterialize( - "select \"empid\", \"deptno\" from \"emps\" where \"deptno\" = 5 group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" where \"deptno\" = 10 group by \"deptno\"", - HR_FKUK_MODEL); + final String materialize = "select \"empid\", \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" = 5\n" + + "group by \"empid\", \"deptno\""; + final String query = "select \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" = 10\n" + + "group by \"deptno\""; + sql(materialize, query).noMat(); } @Test public void testAggregateMaterializationNoAggregateFuncs6() { - checkMaterialize( - "select \"empid\", \"deptno\" from \"emps\" where \"deptno\" > 5 group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" where \"deptno\" > 10 group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{1}])\n" - + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[10], expr#3=[<($t2, $t1)], " - + "proj#0..1=[{exprs}], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"empid\", \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" > 5\n" + + "group by \"empid\", \"deptno\""; + final String query = "select \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" > 10\n" + + "group by \"deptno\""; + final String expected = "EnumerableAggregate(group=[{1}])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[10], expr#3=[<($t2, $t1)], " + + "proj#0..1=[{exprs}], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testAggregateMaterializationNoAggregateFuncs7() { - checkNoMaterialize( - "select \"empid\", \"deptno\" from \"emps\" where \"deptno\" > 5 group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" where \"deptno\" < 10 group by \"deptno\"", - HR_FKUK_MODEL); + final String materialize = "select \"empid\", \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" > 5\n" + + "group by \"empid\", \"deptno\""; + final String query = "select \"deptno\"\n" + + "from \"emps\"\n" + + "where \"deptno\" < 10\n" + + "group by \"deptno\""; + sql(materialize, query).noMat(); } @Test public void testAggregateMaterializationNoAggregateFuncs8() { - checkNoMaterialize( - "select \"empid\" from \"emps\" group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL); + sql("select \"empid\" from \"emps\" group by \"empid\", \"deptno\"", + "select \"deptno\" from \"emps\" group by \"deptno\"") + .noMat(); } @Test public void testAggregateMaterializationNoAggregateFuncs9() { - checkNoMaterialize( - "select \"empid\", \"deptno\" from \"emps\"\n" + sql("select \"empid\", \"deptno\" from \"emps\"\n" + "where \"salary\" > 1000 group by \"name\", \"empid\", \"deptno\"", "select \"empid\" from \"emps\"\n" - + "where \"salary\" > 2000 group by \"name\", \"empid\"", - HR_FKUK_MODEL); + + "where \"salary\" > 2000 group by \"name\", \"empid\"") + .noMat(); } @Test public void testAggregateMaterializationAggregateFuncs1() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + "select \"deptno\" from \"emps\" group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs2() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\", count(*) as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "from \"emps\" group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{1}], C=[$SUM0($2)], S=[$SUM0($3)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs3() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\", \"empid\", sum(\"empid\") as s, count(*) as c\n" - + "from \"emps\" group by \"empid\", \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "from \"emps\" group by \"empid\", \"deptno\"") + .withResultContains( "EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], " + "S=[$t3], C=[$t2])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs4() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" where \"deptno\" >= 10 group by \"empid\", \"deptno\"", "select \"deptno\", sum(\"empid\") as s\n" - + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n" + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs5() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" where \"deptno\" >= 10 group by \"empid\", \"deptno\"", "select \"deptno\", sum(\"empid\") + 1 as s\n" - + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1, $t2)]," + " deptno=[$t0], S=[$t3])\n" + " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n" + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs6() { - checkNoMaterialize( - "select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") + 2 as s\n" + sql("select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") + 2 as s\n" + "from \"emps\" where \"deptno\" >= 10 group by \"empid\", \"deptno\"", "select \"deptno\", sum(\"empid\") + 1 as s\n" - + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"", - HR_FKUK_MODEL); + + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"") + .noMat(); } @Test public void testAggregateMaterializationAggregateFuncs7() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" where \"deptno\" >= 10 group by \"empid\", \"deptno\"", "select \"deptno\" + 1, sum(\"empid\") + 1 as s\n" - + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t0, $t2)], " + "expr#4=[+($t1, $t2)], EXPR$0=[$t3], S=[$t4])\n" + " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n" + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Disabled @Test public void testAggregateMaterializationAggregateFuncs8() { // TODO: It should work, but top project in the query is not matched by the planner. // It needs further checking. - checkMaterialize( - "select \"empid\", \"deptno\" + 1, count(*) + 1 as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\" + 1, count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" where \"deptno\" >= 10 group by \"empid\", \"deptno\"", "select \"deptno\" + 1, sum(\"empid\") + 1 as s\n" - + "from \"emps\" where \"deptno\" > 10 group by \"deptno\""); + + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs9() { - checkMaterialize( - "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month)", - "select floor(cast('1997-01-20 12:34:56' as timestamp) to year), sum(\"empid\") as s\n" - + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"); + final String materialize = "select \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month),\n" + + " count(*) + 1 as c, sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month)"; + final String query = "select\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to year),\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs10() { - checkMaterialize( - "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month)", - "select floor(cast('1997-01-20 12:34:56' as timestamp) to year), sum(\"empid\") + 1 as s\n" - + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"); + final String materialize = "select \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month),\n" + + " count(*) + 1 as c, sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month)"; + final String query = "select\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to year),\n" + + " sum(\"empid\") + 1 as s\n" + + "from \"emps\"\n" + + "group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs11() { - checkMaterialize( - "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to second), count(*) + 1 as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to second)", - "select floor(cast('1997-01-20 12:34:56' as timestamp) to minute), sum(\"empid\") as s\n" - + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to minute)"); + final String materialize = "select \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to second),\n" + + " count(*) + 1 as c, sum(\"empid\") as s\nfrom \"emps\"\n" + + "group by \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to second)"; + final String query = "select\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to minute),\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by floor(cast('1997-01-20 12:34:56' as timestamp) to minute)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs12() { - checkMaterialize( - "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to second), count(*) + 1 as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to second)", - "select floor(cast('1997-01-20 12:34:56' as timestamp) to month), sum(\"empid\") as s\n" - + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to month)"); + final String materialize = "select \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to second),\n" + + " count(*) + 1 as c, sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to second)"; + final String query = "select\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month),\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by floor(cast('1997-01-20 12:34:56' as timestamp) to month)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs13() { - checkMaterialize( - "select \"empid\", cast('1997-01-20 12:34:56' as timestamp), count(*) + 1 as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\", cast('1997-01-20 12:34:56' as timestamp)", - "select floor(cast('1997-01-20 12:34:56' as timestamp) to year), sum(\"empid\") as s\n" - + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"); + final String materialize = "select \"empid\",\n" + + " cast('1997-01-20 12:34:56' as timestamp),\n" + + " count(*) + 1 as c, sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\", cast('1997-01-20 12:34:56' as timestamp)"; + final String query = "select\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to year),\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs14() { - checkMaterialize( - "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month)", - "select floor(cast('1997-01-20 12:34:56' as timestamp) to hour), sum(\"empid\") as s\n" - + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to hour)"); + final String materialize = "select \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month),\n" + + " count(*) + 1 as c, sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by \"empid\",\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to month)"; + final String query = "select\n" + + " floor(cast('1997-01-20 12:34:56' as timestamp) to hour),\n" + + " sum(\"empid\") as s\n" + + "from \"emps\"\n" + + "group by floor(cast('1997-01-20 12:34:56' as timestamp) to hour)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs15() { - checkMaterialize( - "select \"eventid\", floor(cast(\"ts\" as timestamp) to second), count(*) + 1 as c, sum(\"eventid\") as s\n" - + "from \"events\" group by \"eventid\", floor(cast(\"ts\" as timestamp) to second)", - "select floor(cast(\"ts\" as timestamp) to minute), sum(\"eventid\") as s\n" - + "from \"events\" group by floor(cast(\"ts\" as timestamp) to minute)"); + final String materialize = "select \"eventid\",\n" + + " floor(cast(\"ts\" as timestamp) to second), count(*) + 1 as c,\n" + + " sum(\"eventid\") as s\n" + + "from \"events\"\n" + + "group by \"eventid\", floor(cast(\"ts\" as timestamp) to second)"; + final String query = "select floor(cast(\"ts\" as timestamp) to minute),\n" + + " sum(\"eventid\") as s\n" + + "from \"events\"\n" + + "group by floor(cast(\"ts\" as timestamp) to minute)"; + sql(materialize, query).ok(); } @Test public void testAggregateMaterializationAggregateFuncs16() { - checkMaterialize( - "select \"eventid\", cast(\"ts\" as timestamp), count(*) + 1 as c, sum(\"eventid\") as s\n" + sql("select \"eventid\", cast(\"ts\" as timestamp), count(*) + 1 as c, sum(\"eventid\") as s\n" + "from \"events\" group by \"eventid\", cast(\"ts\" as timestamp)", "select floor(cast(\"ts\" as timestamp) to year), sum(\"eventid\") as s\n" - + "from \"events\" group by floor(cast(\"ts\" as timestamp) to year)"); + + "from \"events\" group by floor(cast(\"ts\" as timestamp) to year)") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs17() { - checkMaterialize( - "select \"eventid\", floor(cast(\"ts\" as timestamp) to month), count(*) + 1 as c, sum(\"eventid\") as s\n" - + "from \"events\" group by \"eventid\", floor(cast(\"ts\" as timestamp) to month)", - "select floor(cast(\"ts\" as timestamp) to hour), sum(\"eventid\") as s\n" - + "from \"events\" group by floor(cast(\"ts\" as timestamp) to hour)", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableTableScan(table=[[hr, events]])")); + final String materialize = "select \"eventid\",\n" + + " floor(cast(\"ts\" as timestamp) to month), count(*) + 1 as c,\n" + + " sum(\"eventid\") as s\n" + + "from \"events\"\n" + + "group by \"eventid\", floor(cast(\"ts\" as timestamp) to month)"; + final String query = "select floor(cast(\"ts\" as timestamp) to hour),\n" + + " sum(\"eventid\") as s\n" + + "from \"events\"\n" + + "group by floor(cast(\"ts\" as timestamp) to hour)"; + final String expected = "EnumerableTableScan(table=[[hr, events]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testAggregateMaterializationAggregateFuncs18() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"empid\"*\"deptno\", sum(\"empid\") as s\n" - + "from \"emps\" group by \"empid\"*\"deptno\""); + + "from \"emps\" group by \"empid\"*\"deptno\"") + .ok(); } @Test public void testAggregateMaterializationAggregateFuncs19() { - checkMaterialize( - "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + sql("select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"empid\" + 10, count(*) + 1 as c\n" - + "from \"emps\" group by \"empid\" + 10"); + + "from \"emps\" group by \"empid\" + 10") + .ok(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs1() { - checkMaterialize( - "select \"empid\", \"depts\".\"deptno\" from \"emps\"\n" + sql("select \"empid\", \"depts\".\"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 10\n" + "group by \"empid\", \"depts\".\"deptno\"", "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" - + "group by \"empid\", \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"empid\", \"depts\".\"deptno\"") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " + "empid=[$t0], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs2() { - checkMaterialize( - "select \"depts\".\"deptno\", \"empid\" from \"depts\"\n" + sql("select \"depts\".\"deptno\", \"empid\" from \"depts\"\n" + "join \"emps\" using (\"deptno\") where \"depts\".\"deptno\" > 10\n" + "group by \"empid\", \"depts\".\"deptno\"", "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" - + "group by \"empid\", \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"empid\", \"depts\".\"deptno\"") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[<($t2, $t0)], " + "empid=[$t1], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs3() { // It does not match, Project on top of query - checkNoMaterialize( - "select \"empid\" from \"emps\"\n" + sql("select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 10\n" + "group by \"empid\", \"depts\".\"deptno\"", "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" - + "group by \"empid\", \"depts\".\"deptno\"", - HR_FKUK_MODEL); + + "group by \"empid\", \"depts\".\"deptno\"") + .noMat(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs4() { - checkMaterialize( - "select \"empid\", \"depts\".\"deptno\" from \"emps\"\n" + sql("select \"empid\", \"depts\".\"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"emps\".\"deptno\" > 10\n" + "group by \"empid\", \"depts\".\"deptno\"", "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" - + "group by \"empid\", \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"empid\", \"depts\".\"deptno\"") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[<($t2, $t1)], " + "empid=[$t0], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs5() { - checkMaterialize( - "select \"depts\".\"deptno\", \"emps\".\"empid\" from \"depts\"\n" - + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 10\n" - + "group by \"depts\".\"deptno\", \"emps\".\"empid\"", - "select \"depts\".\"deptno\" from \"depts\"\n" - + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 15\n" - + "group by \"depts\".\"deptno\", \"emps\".\"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15], expr#3=[>($t1, $t2)], " - + "deptno=[$t0], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"depts\".\"deptno\", \"emps\".\"empid\" from \"depts\"\n" + + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 10\n" + + "group by \"depts\".\"deptno\", \"emps\".\"empid\""; + final String query = "select \"depts\".\"deptno\" from \"depts\"\n" + + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 15\n" + + "group by \"depts\".\"deptno\", \"emps\".\"empid\""; + final String expected = "" + + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15], expr#3=[>($t1, $t2)], " + + "deptno=[$t0], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs6() { - checkMaterialize( - "select \"depts\".\"deptno\", \"emps\".\"empid\" from \"depts\"\n" - + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 10\n" - + "group by \"depts\".\"deptno\", \"emps\".\"empid\"", - "select \"depts\".\"deptno\" from \"depts\"\n" - + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 15\n" - + "group by \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{0}])\n" - + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15], expr#3=[<($t2, $t1)], " - + "proj#0..1=[{exprs}], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"depts\".\"deptno\", \"emps\".\"empid\" from \"depts\"\n" + + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 10\n" + + "group by \"depts\".\"deptno\", \"emps\".\"empid\""; + final String query = "select \"depts\".\"deptno\" from \"depts\"\n" + + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 15\n" + + "group by \"depts\".\"deptno\""; + final String expected = "EnumerableAggregate(group=[{0}])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15], expr#3=[<($t2, $t1)], " + + "proj#0..1=[{exprs}], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Tag("slow") @Test public void testJoinAggregateMaterializationNoAggregateFuncs7() { - checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 11\n" - + "group by \"depts\".\"deptno\", \"dependents\".\"empid\"", - "select \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{0}])", - "EnumerableUnion(all=[true])", - "EnumerableAggregate(group=[{2}])", - "EnumerableTableScan(table=[[hr, m0]])", - "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[11], expr#8=[>=($t7, $t0)]")); + final String materialize = "select \"depts\".\"deptno\",\n" + + " \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 11\n" + + "group by \"depts\".\"deptno\", \"dependents\".\"empid\""; + final String query = "select \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10\n" + + "group by \"dependents\".\"empid\""; + final String[] expecteds = { + "EnumerableAggregate(group=[{0}])", + "EnumerableUnion(all=[true])", + "EnumerableAggregate(group=[{2}])", + "EnumerableTableScan(table=[[hr, m0]])", + "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[11], expr#8=[>=($t7, $t0)]"}; + sql(materialize, query).withResultContains(expecteds).ok(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs8() { - checkNoMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 20\n" - + "group by \"depts\".\"deptno\", \"dependents\".\"empid\"", - "select \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL); + final String materialize = "select \"depts\".\"deptno\",\n" + + " \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 20\n" + + "group by \"depts\".\"deptno\", \"dependents\".\"empid\""; + final String query = "select \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" + + "group by \"dependents\".\"empid\""; + sql(materialize, query).noMat(); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs9() { - checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 11 and \"depts\".\"deptno\" < 19\n" - + "group by \"depts\".\"deptno\", \"dependents\".\"empid\"", - "select \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{0}])", - "EnumerableUnion(all=[true])", - "EnumerableAggregate(group=[{2}])", - "EnumerableTableScan(table=[[hr, m0]])", - "expr#13=[OR($t10, $t12)], expr#14=[AND($t6, $t8, $t13)]")); + final String materialize = "select \"depts\".\"deptno\",\n" + + " \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 11 and \"depts\".\"deptno\" < 19\n" + + "group by \"depts\".\"deptno\", \"dependents\".\"empid\""; + final String query = "select \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" + + "group by \"dependents\".\"empid\""; + final String[] expecteds = { + "EnumerableAggregate(group=[{0}])", + "EnumerableUnion(all=[true])", + "EnumerableAggregate(group=[{2}])", + "EnumerableTableScan(table=[[hr, m0]])", + "expr#13=[OR($t10, $t12)], expr#14=[AND($t6, $t8, $t13)]"}; + sql(materialize, query).withResultContains(expecteds).ok(); } @Tag("slow") @Test public void testJoinAggregateMaterializationNoAggregateFuncs10() { - checkMaterialize( - "select \"depts\".\"name\", \"dependents\".\"name\" as \"name2\", " - + "\"emps\".\"deptno\", \"depts\".\"deptno\" as \"deptno2\", " - + "\"dependents\".\"empid\"\n" - + "from \"depts\", \"dependents\", \"emps\"\n" - + "where \"depts\".\"deptno\" > 10\n" - + "group by \"depts\".\"name\", \"dependents\".\"name\", " - + "\"emps\".\"deptno\", \"depts\".\"deptno\", " - + "\"dependents\".\"empid\"", - "select \"dependents\".\"empid\"\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{4}])\n" - + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[=($t2, $t3)], " - + "expr#6=[CAST($t1):VARCHAR], " - + "expr#7=[CAST($t0):VARCHAR], " - + "expr#8=[=($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..4=[{exprs}], $condition=[$t9])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"depts\".\"name\", \"dependents\".\"name\" as \"name2\", " + + "\"emps\".\"deptno\", \"depts\".\"deptno\" as \"deptno2\", " + + "\"dependents\".\"empid\"\n" + + "from \"depts\", \"dependents\", \"emps\"\n" + + "where \"depts\".\"deptno\" > 10\n" + + "group by \"depts\".\"name\", \"dependents\".\"name\", " + + "\"emps\".\"deptno\", \"depts\".\"deptno\", " + + "\"dependents\".\"empid\""; + final String query = "select \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10\n" + + "group by \"dependents\".\"empid\""; + final String expected = "EnumerableAggregate(group=[{4}])\n" + + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[=($t2, $t3)], " + + "expr#6=[CAST($t1):VARCHAR], expr#7=[CAST($t0):VARCHAR], " + + "expr#8=[=($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..4=[{exprs}], " + + "$condition=[$t9])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs1() { // This test relies on FK-UK relationship - checkMaterialize( + final String materialize = "select \"empid\", \"depts\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "group by \"empid\", \"depts\".\"deptno\"", - "select \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + "group by \"empid\", \"depts\".\"deptno\""; + final String query = "select \"deptno\" from \"emps\" group by \"deptno\""; + final String expected = "EnumerableAggregate(group=[{1}])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs2() { - checkMaterialize( + final String materialize = "select \"empid\", \"emps\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "group by \"empid\", \"emps\".\"deptno\"", - "select \"depts\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" - + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "group by \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{1}], C=[$SUM0($2)], S=[$SUM0($3)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + "group by \"empid\", \"emps\".\"deptno\""; + final String query = "select \"depts\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" + + "from \"emps\" join \"depts\" using (\"deptno\")\n" + + "group by \"depts\".\"deptno\""; + final String expected = "EnumerableAggregate(group=[{1}], C=[$SUM0($2)], S=[$SUM0($3)])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs3() { // This test relies on FK-UK relationship - checkMaterialize( + final String materialize = "select \"empid\", \"depts\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "group by \"empid\", \"depts\".\"deptno\"", - "select \"deptno\", \"empid\", sum(\"empid\") as s, count(*) as c\n" - + "from \"emps\" group by \"empid\", \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], " - + "S=[$t3], C=[$t2])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + "group by \"empid\", \"depts\".\"deptno\""; + final String query = "select \"deptno\", \"empid\", sum(\"empid\") as s, count(*) as c\n" + + "from \"emps\" group by \"empid\", \"deptno\""; + final String expected = "EnumerableCalc(expr#0..3=[{inputs}], " + + "deptno=[$t1], empid=[$t0], S=[$t3], C=[$t2])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs4() { - checkMaterialize( + final String materialize = "select \"empid\", \"emps\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "where \"emps\".\"deptno\" >= 10 group by \"empid\", \"emps\".\"deptno\"", - "select \"depts\".\"deptno\", sum(\"empid\") as s\n" - + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "where \"emps\".\"deptno\" > 10 group by \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n" - + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + "where \"emps\".\"deptno\" >= 10 group by \"empid\", \"emps\".\"deptno\""; + final String query = "select \"depts\".\"deptno\", sum(\"empid\") as s\n" + + "from \"emps\" join \"depts\" using (\"deptno\")\n" + + "where \"emps\".\"deptno\" > 10 group by \"depts\".\"deptno\""; + final String expected = "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n" + + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs5() { - checkMaterialize( + final String materialize = "select \"empid\", \"depts\".\"deptno\", count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "where \"depts\".\"deptno\" >= 10 group by \"empid\", \"depts\".\"deptno\"", - "select \"depts\".\"deptno\", sum(\"empid\") + 1 as s\n" - + "from \"emps\" join \"depts\" using (\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10 group by \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1, $t2)], " - + "deptno=[$t0], S=[$t3])\n" - + " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n" - + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + "where \"depts\".\"deptno\" >= 10 group by \"empid\", \"depts\".\"deptno\""; + final String query = "select \"depts\".\"deptno\", sum(\"empid\") + 1 as s\n" + + "from \"emps\" join \"depts\" using (\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10 group by \"depts\".\"deptno\""; + final String expected = "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], " + + "expr#3=[+($t1, $t2)], deptno=[$t0], S=[$t3])\n" + + " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n" + + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[<($t4, $t1)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Disabled @@ -2076,97 +2119,100 @@ public class MaterializationTest { + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + "group by \"dependents\".\"empid\""; - checkMaterialize(m, q); + sql(m, q).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs7() { - checkMaterialize( - "select \"dependents\".\"empid\", \"emps\".\"deptno\", sum(\"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - "select \"dependents\".\"empid\", sum(\"salary\") as s\n" - + "from \"emps\"\n" - + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{4}], S=[$SUM0($6)])\n" - + " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[=($t5, $t0)], proj#0..6=[{exprs}], $condition=[$t7])\n" - + " EnumerableNestedLoopJoin(condition=[true], joinType=[inner])\n" - + " EnumerableTableScan(table=[[hr, depts]])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"dependents\".\"empid\",\n" + + " \"emps\".\"deptno\", sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + final String query = "select \"dependents\".\"empid\", sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\""; + final String expected = "EnumerableAggregate(group=[{4}], S=[$SUM0($6)])\n" + + " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[=($t5, $t0)], " + + "proj#0..6=[{exprs}], $condition=[$t7])\n" + + " EnumerableNestedLoopJoin(condition=[true], joinType=[inner])\n" + + " EnumerableTableScan(table=[[hr, depts]])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs8() { - checkMaterialize( - "select \"dependents\".\"empid\", \"emps\".\"deptno\", sum(\"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - "select \"depts\".\"name\", sum(\"salary\") as s\n" - + "from \"emps\"\n" - + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"depts\".\"name\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableAggregate(group=[{1}], S=[$SUM0($6)])\n" - + " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[=($t5, $t0)], proj#0..6=[{exprs}], $condition=[$t7])\n" - + " EnumerableNestedLoopJoin(condition=[true], joinType=[inner])\n" - + " EnumerableTableScan(table=[[hr, depts]])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"dependents\".\"empid\",\n" + + " \"emps\".\"deptno\", sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + final String query = "select \"depts\".\"name\", sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"depts\".\"name\""; + final String expected = "EnumerableAggregate(group=[{1}], S=[$SUM0($6)])\n" + + " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[=($t5, $t0)], " + + "proj#0..6=[{exprs}], $condition=[$t7])\n" + + " EnumerableNestedLoopJoin(condition=[true], joinType=[inner])\n" + + " EnumerableTableScan(table=[[hr, depts]])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs9() { - checkMaterialize( - "select \"dependents\".\"empid\", \"emps\".\"deptno\", count(distinct \"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - "select \"emps\".\"deptno\", count(distinct \"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableCalc(expr#0..2=[{inputs}], deptno=[$t1], S=[$t2])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + final String materialize = "select \"dependents\".\"empid\",\n" + + " \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + final String query = "select \"emps\".\"deptno\",\n" + + " count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + final String expected = "EnumerableCalc(expr#0..2=[{inputs}], " + + "deptno=[$t1], S=[$t2])\n" + + " EnumerableTableScan(table=[[hr, m0]])"; + sql(materialize, query).withResultContains(expected).ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs10() { - checkNoMaterialize( - "select \"dependents\".\"empid\", \"emps\".\"deptno\", count(distinct \"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - "select \"emps\".\"deptno\", count(distinct \"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"emps\".\"deptno\"", - HR_FKUK_MODEL); + final String materialize = "select \"dependents\".\"empid\",\n" + + " \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + final String query = "select \"emps\".\"deptno\",\n" + + " count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"emps\".\"deptno\""; + sql(materialize, query).noMat(); } @Tag("slow") @Test public void testJoinAggregateMaterializationAggregateFuncs11() { - checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\", count(\"emps\".\"salary\") as s\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 11 and \"depts\".\"deptno\" < 19\n" - + "group by \"depts\".\"deptno\", \"dependents\".\"empid\"", - "select \"dependents\".\"empid\", count(\"emps\".\"salary\") + 1\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + final String materialize = "select \"depts\".\"deptno\",\n" + + " \"dependents\".\"empid\", count(\"emps\".\"salary\") as s\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 11 and \"depts\".\"deptno\" < 19\n" + + "group by \"depts\".\"deptno\", \"dependents\".\"empid\""; + final String query = "select \"dependents\".\"empid\",\n" + + " count(\"emps\".\"salary\") + 1\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" + + "group by \"dependents\".\"empid\""; + sql(materialize, query) + .withResultContains( "PLAN=EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1, $t2)], " + "empid=[$t0], EXPR$1=[$t3])\n" + " EnumerableAggregate(group=[{0}], agg#0=[$SUM0($1)])", @@ -2174,44 +2220,46 @@ public class MaterializationTest { "EnumerableAggregate(group=[{2}], agg#0=[COUNT()])", "EnumerableAggregate(group=[{1}], agg#0=[$SUM0($2)])", "EnumerableTableScan(table=[[hr, m0]])", - "expr#13=[OR($t10, $t12)], expr#14=[AND($t6, $t8, $t13)]")); + "expr#13=[OR($t10, $t12)], expr#14=[AND($t6, $t8, $t13)]") + .ok(); } @Test public void testJoinAggregateMaterializationAggregateFuncs12() { - checkNoMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\", count(distinct \"emps\".\"salary\") as s\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 11 and \"depts\".\"deptno\" < 19\n" - + "group by \"depts\".\"deptno\", \"dependents\".\"empid\"", - "select \"dependents\".\"empid\", count(distinct \"emps\".\"salary\") + 1\n" - + "from \"depts\"\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" - + "group by \"dependents\".\"empid\"", - HR_FKUK_MODEL); + final String materialize = "select \"depts\".\"deptno\",\n" + + " \"dependents\".\"empid\",\n" + + " count(distinct \"emps\".\"salary\") as s\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 11 and \"depts\".\"deptno\" < 19\n" + + "group by \"depts\".\"deptno\", \"dependents\".\"empid\""; + final String query = "select \"dependents\".\"empid\",\n" + + " count(distinct \"emps\".\"salary\") + 1\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "where \"depts\".\"deptno\" > 10 and \"depts\".\"deptno\" < 20\n" + + "group by \"dependents\".\"empid\""; + sql(materialize, query).noMat(); } @Test public void testJoinAggregateMaterializationAggregateFuncs13() { - checkNoMaterialize( - "select \"dependents\".\"empid\", \"emps\".\"deptno\", count(distinct \"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - "select \"emps\".\"deptno\", count(\"salary\") as s\n" - + "from \"emps\"\n" - + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" - + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", - HR_FKUK_MODEL); + final String materialize = "select \"dependents\".\"empid\",\n" + + " \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + final String query = "select \"emps\".\"deptno\", count(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\""; + sql(materialize, query).noMat(); } @Test public void testJoinAggregateMaterializationAggregateFuncs14() { - checkMaterialize( - "select \"empid\", \"emps\".\"name\", \"emps\".\"deptno\", \"depts\".\"name\", " + sql("select \"empid\", \"emps\".\"name\", \"emps\".\"deptno\", \"depts\".\"name\", " + "count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" + "where (\"depts\".\"name\" is not null and \"emps\".\"name\" = 'a') or " @@ -2220,105 +2268,96 @@ public class MaterializationTest { "select \"depts\".\"deptno\", sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" + "where \"depts\".\"name\" is not null and \"emps\".\"name\" = 'a'\n" - + "group by \"depts\".\"deptno\"", - HR_FKUK_MODEL, - CONTAINS_M0); + + "group by \"depts\".\"deptno\"") + .ok(); } @Test public void testJoinMaterialization4() { - checkMaterialize( - "select \"empid\" \"deptno\" from \"emps\"\n" + sql("select \"empid\" \"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\")", "select \"empid\" \"deptno\" from \"emps\"\n" - + "join \"depts\" using (\"deptno\") where \"empid\" = 1", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "join \"depts\" using (\"deptno\") where \"empid\" = 1") + .withResultContains( "EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], expr#2=[1], " + "expr#3=[=($t1, $t2)], deptno=[$t0], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterialization5() { - checkMaterialize( - "select cast(\"empid\" as BIGINT) from \"emps\"\n" + sql("select cast(\"empid\" as BIGINT) from \"emps\"\n" + "join \"depts\" using (\"deptno\")", "select \"empid\" \"deptno\" from \"emps\"\n" - + "join \"depts\" using (\"deptno\") where \"empid\" > 1", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "join \"depts\" using (\"deptno\") where \"empid\" > 1") + .withResultContains( "EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):JavaType(int) NOT NULL], " + "expr#2=[1], expr#3=[>($t1, $t2)], EXPR$0=[$t1], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterialization6() { - checkMaterialize( - "select cast(\"empid\" as BIGINT) from \"emps\"\n" + sql("select cast(\"empid\" as BIGINT) from \"emps\"\n" + "join \"depts\" using (\"deptno\")", "select \"empid\" \"deptno\" from \"emps\"\n" - + "join \"depts\" using (\"deptno\") where \"empid\" = 1", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "join \"depts\" using (\"deptno\") where \"empid\" = 1") + .withResultContains( "EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):JavaType(int) NOT NULL], " + "expr#2=[CAST($t1):INTEGER NOT NULL], expr#3=[1], expr#4=[=($t2, $t3)], " + "EXPR$0=[$t1], $condition=[$t4])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterialization7() { - checkMaterialize( - "select \"depts\".\"name\"\n" + sql("select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", "select \"dependents\".\"empid\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")") + .withResultContains( "EnumerableCalc(expr#0..2=[{inputs}], empid=[$t1])\n" + " EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])\n" + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], name00=[$t1])\n" + " EnumerableTableScan(table=[[hr, m0]])\n" + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], empid=[$t0], name0=[$t2])\n" - + " EnumerableTableScan(table=[[hr, dependents]])")); + + " EnumerableTableScan(table=[[hr, dependents]])") + .ok(); } @Test public void testJoinMaterialization8() { - checkMaterialize( - "select \"depts\".\"name\"\n" + sql("select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", "select \"dependents\".\"empid\"\n" + "from \"depts\"\n" + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")") + .withResultContains( "EnumerableCalc(expr#0..2=[{inputs}], empid=[$t0])\n" + " EnumerableNestedLoopJoin(condition=[=(CAST($1):VARCHAR, CAST($2):VARCHAR)], joinType=[inner])\n" + " EnumerableTableScan(table=[[hr, dependents]])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterialization9() { - checkMaterialize( - "select \"depts\".\"name\"\n" + sql("select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", "select \"dependents\".\"empid\"\n" + "from \"depts\"\n" + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", - HR_FKUK_MODEL, - CONTAINS_M0); + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")") + .ok(); } @Tag("slow") @Test public void testJoinMaterialization10() { - checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" + sql("select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" + "from \"depts\"\n" + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" @@ -2327,29 +2366,27 @@ public class MaterializationTest { + "from \"depts\"\n" + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" - + "where \"depts\".\"deptno\" > 10", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "where \"depts\".\"deptno\" > 10") + .withResultContains( "EnumerableUnion(all=[true])", "EnumerableTableScan(table=[[hr, m0]])", - "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[30], expr#8=[>=($t7, $t0)]")); + "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[30], expr#8=[>=($t7, $t0)]") + .ok(); } @Test public void testJoinMaterialization11() { - checkMaterialize( - "select \"empid\" from \"emps\"\n" + sql("select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\")", "select \"empid\" from \"emps\"\n" - + "where \"deptno\" in (select \"deptno\" from \"depts\")", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "PLAN=EnumerableTableScan(table=[[hr, m0]])")); + + "where \"deptno\" in (select \"deptno\" from \"depts\")") + .withResultContains( + "PLAN=EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Tag("slow") @Test public void testJoinMaterialization12() { - checkMaterialize( - "select \"empid\", \"emps\".\"name\", \"emps\".\"deptno\", \"depts\".\"name\"\n" + sql("select \"empid\", \"emps\".\"name\", \"emps\".\"deptno\", \"depts\".\"name\"\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" + "where (\"depts\".\"name\" is not null and \"emps\".\"name\" = 'a') or " + "(\"depts\".\"name\" is not null and \"emps\".\"name\" = 'b') or " @@ -2357,151 +2394,139 @@ public class MaterializationTest { "select \"depts\".\"deptno\", \"depts\".\"name\"\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" + "where (\"depts\".\"name\" is not null and \"emps\".\"name\" = 'a') or " - + "(\"depts\".\"name\" is not null and \"emps\".\"name\" = 'b')", - HR_FKUK_MODEL, - CONTAINS_M0); + + "(\"depts\".\"name\" is not null and \"emps\".\"name\" = 'b')") + .ok(); } @Test public void testJoinMaterializationUKFK1() { - checkMaterialize( - "select \"a\".\"empid\" \"deptno\" from\n" + sql("select \"a\".\"empid\" \"deptno\" from\n" + "(select * from \"emps\" where \"empid\" = 1) \"a\"\n" + "join \"depts\" using (\"deptno\")\n" + "join \"dependents\" using (\"empid\")", "select \"a\".\"empid\" from \n" + "(select * from \"emps\" where \"empid\" = 1) \"a\"\n" - + "join \"dependents\" using (\"empid\")\n", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "PLAN=EnumerableTableScan(table=[[hr, m0]])")); + + "join \"dependents\" using (\"empid\")\n") + .withResultContains( + "PLAN=EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterializationUKFK2() { - checkMaterialize( - "select \"a\".\"empid\", \"a\".\"deptno\" from\n" + sql("select \"a\".\"empid\", \"a\".\"deptno\" from\n" + "(select * from \"emps\" where \"empid\" = 1) \"a\"\n" + "join \"depts\" using (\"deptno\")\n" + "join \"dependents\" using (\"empid\")", "select \"a\".\"empid\" from \n" + "(select * from \"emps\" where \"empid\" = 1) \"a\"\n" - + "join \"dependents\" using (\"empid\")\n", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "join \"dependents\" using (\"empid\")\n") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterializationUKFK3() { - checkNoMaterialize( - "select \"a\".\"empid\", \"a\".\"deptno\" from\n" + sql("select \"a\".\"empid\", \"a\".\"deptno\" from\n" + "(select * from \"emps\" where \"empid\" = 1) \"a\"\n" + "join \"depts\" using (\"deptno\")\n" + "join \"dependents\" using (\"empid\")", "select \"a\".\"name\" from \n" + "(select * from \"emps\" where \"empid\" = 1) \"a\"\n" - + "join \"dependents\" using (\"empid\")\n", - HR_FKUK_MODEL); + + "join \"dependents\" using (\"empid\")\n") + .noMat(); } @Test public void testJoinMaterializationUKFK4() { - checkMaterialize( - "select \"empid\" \"deptno\" from\n" + sql("select \"empid\" \"deptno\" from\n" + "(select * from \"emps\" where \"empid\" = 1)\n" + "join \"depts\" using (\"deptno\")", - "select \"empid\" from \"emps\" where \"empid\" = 1\n", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "PLAN=EnumerableTableScan(table=[[hr, m0]])")); + "select \"empid\" from \"emps\" where \"empid\" = 1\n") + .withResultContains( + "PLAN=EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Tag("slow") @Test public void testJoinMaterializationUKFK5() { - checkMaterialize( - "select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + sql("select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\")\n" + "join \"dependents\" using (\"empid\")" + "where \"emps\".\"empid\" = 1", "select \"emps\".\"empid\" from \"emps\"\n" + "join \"dependents\" using (\"empid\")\n" - + "where \"emps\".\"empid\" = 1", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "where \"emps\".\"empid\" = 1") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Tag("slow") @Test public void testJoinMaterializationUKFK6() { - checkMaterialize( - "select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + sql("select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + "join \"depts\" \"a\" on (\"emps\".\"deptno\"=\"a\".\"deptno\")\n" + "join \"depts\" \"b\" on (\"emps\".\"deptno\"=\"b\".\"deptno\")\n" + "join \"dependents\" using (\"empid\")" + "where \"emps\".\"empid\" = 1", "select \"emps\".\"empid\" from \"emps\"\n" + "join \"dependents\" using (\"empid\")\n" - + "where \"emps\".\"empid\" = 1", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "where \"emps\".\"empid\" = 1") + .withResultContains( "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + + " EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testJoinMaterializationUKFK7() { - checkNoMaterialize( - "select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + sql("select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + "join \"depts\" \"a\" on (\"emps\".\"name\"=\"a\".\"name\")\n" + "join \"depts\" \"b\" on (\"emps\".\"name\"=\"b\".\"name\")\n" + "join \"dependents\" using (\"empid\")" + "where \"emps\".\"empid\" = 1", "select \"emps\".\"empid\" from \"emps\"\n" + "join \"dependents\" using (\"empid\")\n" - + "where \"emps\".\"empid\" = 1", - HR_FKUK_MODEL); + + "where \"emps\".\"empid\" = 1") + .noMat(); } @Test public void testJoinMaterializationUKFK8() { - checkNoMaterialize( - "select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + sql("select \"emps\".\"empid\", \"emps\".\"deptno\" from \"emps\"\n" + "join \"depts\" \"a\" on (\"emps\".\"deptno\"=\"a\".\"deptno\")\n" + "join \"depts\" \"b\" on (\"emps\".\"name\"=\"b\".\"name\")\n" + "join \"dependents\" using (\"empid\")" + "where \"emps\".\"empid\" = 1", "select \"emps\".\"empid\" from \"emps\"\n" + "join \"dependents\" using (\"empid\")\n" - + "where \"emps\".\"empid\" = 1", - HR_FKUK_MODEL); + + "where \"emps\".\"empid\" = 1") + .noMat(); } @Tag("slow") @Test public void testJoinMaterializationUKFK9() { - checkMaterialize( - "select * from \"emps\"\n" + sql("select * from \"emps\"\n" + "join \"dependents\" using (\"empid\")", "select \"emps\".\"empid\", \"dependents\".\"empid\", \"emps\".\"deptno\"\n" + "from \"emps\"\n" + "join \"dependents\" using (\"empid\")" + "join \"depts\" \"a\" on (\"emps\".\"deptno\"=\"a\".\"deptno\")\n" - + "where \"emps\".\"name\" = 'Bill'", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableTableScan(table=[[hr, m0]])")); + + "where \"emps\".\"name\" = 'Bill'") + .withResultContains( + "EnumerableTableScan(table=[[hr, m0]])") + .ok(); } @Test public void testViewMaterialization() { - checkThatMaterialize( - "select \"depts\".\"name\"\n" + sql("select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", "select \"depts\".\"name\"\n" + "from \"depts\"\n" - + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", - "matview", - true, - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableValues(tuples=[[{ 'noname' }]])"), - RuleSets.ofList(ImmutableList.of())) + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")") + .withViewName("matview") + .withExisting(true) + .withResultContains( + "EnumerableValues(tuples=[[{ 'noname' }]])") + .that() .returnsValue("noname"); } @@ -2511,9 +2536,10 @@ public class MaterializationTest { + " select max(\"empid\") from \"emps\"\n" + " where \"deptno\" = e1.\"deptno\")"; final String m = "select \"empid\", \"deptno\" from \"emps\"\n"; - checkMaterialize(m, q, HR_FKUK_MODEL, + sql(m, q).withChecker( CalciteAssert.checkResultContains( - "EnumerableTableScan(table=[[hr, m0]])", 1)); + "EnumerableTableScan(table=[[hr, m0]])", 1)) + .ok(); } @Test public void testTableModify() { @@ -2610,9 +2636,10 @@ public class MaterializationTest { + "from (select * from \"emps\" where \"empid\" < 300)\n" + "join (select * from \"emps\" where \"empid\" < 200) using (\"empid\")"; String m = "select * from \"emps\" where \"empid\" < 500"; - checkMaterialize(m, q, HR_FKUK_MODEL, + sql(m, q).withChecker( CalciteAssert.checkResultContains( - "EnumerableTableScan(table=[[hr, m0]])", 2)); + "EnumerableTableScan(table=[[hr, m0]])", 2)) + .ok(); } @Test public void testMultiMaterializationMultiUsage() { @@ -2672,75 +2699,71 @@ public class MaterializationTest { @Test public void testAggregateMaterializationOnCountDistinctQuery1() { // The column empid is already unique, thus DISTINCT is not // in the COUNT of the resulting rewriting - checkMaterialize( - "select \"deptno\", \"empid\", \"salary\"\n" + sql("select \"deptno\", \"empid\", \"salary\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"empid\", \"salary\"", "select \"deptno\", count(distinct \"empid\") as c from (\n" + "select \"deptno\", \"empid\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"empid\")\n" - + "group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{0}], C=[COUNT($1)])\n" - + " EnumerableTableScan(table=[[hr, m0]]")); + + " EnumerableTableScan(table=[[hr, m0]]") + .ok(); } @Test public void testAggregateMaterializationOnCountDistinctQuery2() { // The column empid is already unique, thus DISTINCT is not // in the COUNT of the resulting rewriting - checkMaterialize( - "select \"deptno\", \"salary\", \"empid\"\n" + sql("select \"deptno\", \"salary\", \"empid\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"salary\", \"empid\"", "select \"deptno\", count(distinct \"empid\") as c from (\n" + "select \"deptno\", \"empid\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"empid\")\n" - + "group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{0}], C=[COUNT($2)])\n" - + " EnumerableTableScan(table=[[hr, m0]]")); + + " EnumerableTableScan(table=[[hr, m0]]") + .ok(); } @Test public void testAggregateMaterializationOnCountDistinctQuery3() { // The column salary is not unique, thus we end up with // a different rewriting - checkMaterialize( - "select \"deptno\", \"empid\", \"salary\"\n" + sql("select \"deptno\", \"empid\", \"salary\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"empid\", \"salary\"", "select \"deptno\", count(distinct \"salary\") from (\n" + "select \"deptno\", \"salary\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"salary\")\n" - + "group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{0}], EXPR$1=[COUNT($1)])\n" + " EnumerableAggregate(group=[{0, 2}])\n" - + " EnumerableTableScan(table=[[hr, m0]]")); + + " EnumerableTableScan(table=[[hr, m0]]") + .ok(); } @Test public void testAggregateMaterializationOnCountDistinctQuery4() { // Although there is no DISTINCT in the COUNT, this is // equivalent to previous query - checkMaterialize( - "select \"deptno\", \"salary\", \"empid\"\n" + sql("select \"deptno\", \"salary\", \"empid\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"salary\", \"empid\"", "select \"deptno\", count(\"salary\") from (\n" + "select \"deptno\", \"salary\"\n" + "from \"emps\"\n" + "group by \"deptno\", \"salary\")\n" - + "group by \"deptno\"", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( + + "group by \"deptno\"") + .withResultContains( "EnumerableAggregate(group=[{0}], EXPR$1=[COUNT()])\n" + " EnumerableAggregate(group=[{0, 1}])\n" - + " EnumerableTableScan(table=[[hr, m0]]")); + + " EnumerableTableScan(table=[[hr, m0]]") + .ok(); } @Test public void testMaterializationSubstitution() { @@ -2825,26 +2848,25 @@ public class MaterializationTest { + " join\n" + " \"depts\" \"y\"\n" + " on \"x\".\"deptno\"=\"y\".\"deptno\"\n"; - checkMaterialize(sql, sql); + sql(sql, sql).ok(); } @Test public void testUnionAllToUnionAll() { String sql0 = "select * from \"emps\" where \"empid\" < 300"; String sql1 = "select * from \"emps\" where \"empid\" > 200"; - checkMaterialize(sql0 + " union all " + sql1, sql1 + " union all " + sql0); + sql(sql0 + " union all " + sql1, sql1 + " union all " + sql0).ok(); } @Test public void testUnionDistinctToUnionDistinct() { String sql0 = "select * from \"emps\" where \"empid\" < 300"; String sql1 = "select * from \"emps\" where \"empid\" > 200"; - checkMaterialize(sql0 + " union " + sql1, sql1 + " union " + sql0); + sql(sql0 + " union " + sql1, sql1 + " union " + sql0).ok(); } @Test public void testUnionDistinctToUnionAll() { String sql0 = "select * from \"emps\" where \"empid\" < 300"; String sql1 = "select * from \"emps\" where \"empid\" > 200"; - checkNoMaterialize(sql0 + " union " + sql1, sql0 + " union all " + sql1, - HR_FKUK_MODEL); + sql(sql0 + " union " + sql1, sql0 + " union all " + sql1).noMat(); } @Test public void testUnionOnCalcsToUnion() { @@ -2864,7 +2886,7 @@ public class MaterializationTest { + "select \"deptno\", \"salary\" * 2\n" + "from \"emps\"\n" + "where \"empid\" < 100 and \"salary\" > 100"; - checkMaterialize(mv, query); + sql(mv, query).ok(); } @Test public void testIntersectToIntersect0() { @@ -2876,7 +2898,7 @@ public class MaterializationTest { + "select \"deptno\" from \"depts\"\n" + "intersect\n" + "select \"deptno\" from \"emps\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testIntersectToIntersect1() { @@ -2888,7 +2910,7 @@ public class MaterializationTest { + "select \"deptno\" from \"depts\"\n" + "intersect all\n" + "select \"deptno\" from \"emps\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } @Test public void testIntersectToCalcOnIntersect() { @@ -2902,7 +2924,7 @@ public class MaterializationTest { + "select \"name\",\"deptno\" from \"depts\"\n" + "intersect all\n" + "select \"name\",\"deptno\" from \"emps\""; - checkMaterialize(mv, query, true); + sql(mv, query).withOnlyBySubstitution(true).ok(); } private static <E> List<List<List<E>>> list3(E[][][] as) { @@ -2996,4 +3018,61 @@ public class MaterializationTest { return Smalls.strView("noname"); } } + + /** Fluent class that contains information necessary to run a test. */ + private interface Sql { + default void ok() { + that().sameResultWithMaterializationsDisabled(); + } + + default CalciteAssert.AssertQuery that() { + return checkThatMaterialize_(getMaterialize(), getQuery(), getViewName(), + isExisting(), getModel(), getChecker(), getRuleSet(), + isOnlyBySubstitution()); + } + + @ImmutableBeans.Property + @ImmutableBeans.BooleanDefault(false) + boolean isExisting(); + Sql withExisting(boolean existing); + + default void noMat() { + checkNoMaterialize_(getMaterialize(), getQuery(), + getModel(), isOnlyBySubstitution()); + } + + default Sql withResultContains(String... expected) { + return withChecker(CalciteAssert.checkResultContains(expected)); + } + + @ImmutableBeans.Property + String getMaterialize(); + Sql withMaterialize(String materialize); + + @ImmutableBeans.Property + String getQuery(); + Sql withQuery(String query); + + @ImmutableBeans.Property + @ImmutableBeans.BooleanDefault(false) + boolean isOnlyBySubstitution(); + Sql withOnlyBySubstitution(boolean onlyBySubstitution); + + @ImmutableBeans.Property + String getModel(); + Sql withModel(String model); + + @ImmutableBeans.Property + Consumer<ResultSet> getChecker(); + Sql withChecker(Consumer<ResultSet> explainChecker); + + @ImmutableBeans.Property + RuleSet getRuleSet(); + Sql withRuleSet(RuleSet ruleSet); + + @ImmutableBeans.Property + @ImmutableBeans.StringDefault("m0") + String getViewName(); + Sql withViewName(String viewName); + } }
