http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/MaterializationTest.java ---------------------------------------------------------------------- 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 a5ffc58..fb96721 100644 --- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java +++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java @@ -89,12 +89,10 @@ public class MaterializationTest { CalciteAssert.checkResultContains( "EnumerableTableScan(table=[[hr, locations]])"); - private static final Ordering<Iterable<String>> - CASE_INSENSITIVE_LIST_COMPARATOR = + private static final Ordering<Iterable<String>> CASE_INSENSITIVE_LIST_COMPARATOR = Ordering.from(String.CASE_INSENSITIVE_ORDER).lexicographical(); - private static final Ordering<Iterable<List<String>>> - CASE_INSENSITIVE_LIST_LIST_COMPARATOR = + private static final Ordering<Iterable<List<String>>> CASE_INSENSITIVE_LIST_LIST_COMPARATOR = CASE_INSENSITIVE_LIST_COMPARATOR.lexicographical(); private static final String HR_FKUK_SCHEMA = "{\n" @@ -285,9 +283,9 @@ public class MaterializationTest { @Test public void testFilterQueryOnProjectView5() { checkMaterialize( "select \"deptno\" - 10 as \"x\", \"empid\" + 1 as ee, \"name\"\n" - + "from \"emps\"", + + "from \"emps\"", "select \"name\", \"empid\" + 1 as e\n" - + "from \"emps\" where \"deptno\" - 10 = 2", + + "from \"emps\" where \"deptno\" - 10 = 2", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], " @@ -396,25 +394,25 @@ public class MaterializationTest { * query. */ @Test public void testFilterQueryOnFilterView4() { checkMaterialize( - "select * from \"emps\" where \"deptno\" > 10", - "select \"name\" from \"emps\" where \"deptno\" > 30"); + "select * from \"emps\" where \"deptno\" > 10", + "select \"name\" from \"emps\" where \"deptno\" > 30"); } /** 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"); + "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", + "select \"name\" from \"emps\" where \"deptno\" > 30"); } /** 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"); + "select \"name\", \"deptno\", \"salary\" from \"emps\" " + + "where \"salary\" > 2000.5", + "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000"); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in @@ -422,12 +420,12 @@ public class MaterializationTest { * 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))"); + "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))"); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in @@ -435,30 +433,30 @@ public class MaterializationTest { * 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); + "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", + "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30", + HR_FKUK_MODEL); } /** 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); + "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", + "select \"name\", \"empid\" from \"emps\" " + + "where \"deptno\" > 30 or \"empid\" > 10", + HR_FKUK_MODEL); } /** 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 " - + "and \"name\" = \'calcite\'", - "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30 " - + "or \"empid\" > 10", - HR_FKUK_MODEL); + "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); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in @@ -466,11 +464,11 @@ public class MaterializationTest { * Condition here is complex. */ @Test public void testFilterQueryOnFilterView11() { checkNoMaterialize( - "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\", \"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); } /** As {@link #testFilterQueryOnFilterView()} but condition of @@ -478,9 +476,9 @@ public class MaterializationTest { */ @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); + "select \"name\", \"deptno\" from \"emps\" where \"salary\" > 2000.5", + "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000", + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in @@ -488,12 +486,12 @@ public class MaterializationTest { * Condition here is complex. */ @Test public void testFilterQueryOnFilterView13() { checkNoMaterialize( - "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); + "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); } /** As {@link #testFilterQueryOnFilterView7()} but columns in materialized @@ -513,11 +511,11 @@ public class MaterializationTest { * and condition of query is stronger. */ @Test public void testAlias() { checkMaterialize( - "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)"); + "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)"); } /** Aggregation query at same level of aggregation as aggregation @@ -910,21 +908,21 @@ public class MaterializationTest { @Ignore @Test public void testFilterGroupQueryOnStar() { checkMaterialize("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" - + " select \"time_id\", \"the_year\", \"the_month\"\n" - + " from \"foodmart\".\"time_by_day\") as t\n" - + " on f.\"time_id\" = t.\"time_id\"\n" - + "join \"foodmart\".\"product\" as p\n" - + " on f.\"product_id\" = p.\"product_id\"\n" - + "join \"foodmart\".\"product_class\" as pc" - + " on p.\"product_class_id\" = pc.\"product_class_id\"\n" - + "group by t.\"the_year\",\n" - + " t.\"the_month\",\n" - + " pc.\"product_department\",\n" - + " pc.\"product_category\",\n" - + " p.\"product_name\"", + + " sum(f.\"unit_sales\") as \"sum_unit_sales\", count(*) as \"c\"\n" + + "from \"foodmart\".\"sales_fact_1997\" as f\n" + + "join (\n" + + " select \"time_id\", \"the_year\", \"the_month\"\n" + + " from \"foodmart\".\"time_by_day\") as t\n" + + " on f.\"time_id\" = t.\"time_id\"\n" + + "join \"foodmart\".\"product\" as p\n" + + " on f.\"product_id\" = p.\"product_id\"\n" + + "join \"foodmart\".\"product_class\" as pc" + + " on p.\"product_class_id\" = pc.\"product_class_id\"\n" + + "group by t.\"the_year\",\n" + + " t.\"the_month\",\n" + + " pc.\"product_department\",\n" + + " pc.\"product_category\",\n" + + " p.\"product_name\"", "select t.\"the_month\", count(*) as x\n" + "from (\n" + " select \"time_id\", \"the_year\", \"the_month\"\n" @@ -965,8 +963,8 @@ public class MaterializationTest { @Test public void testJoinMaterialization() { String q = "select *\n" - + "from (select * from \"emps\" where \"empid\" < 300)\n" - + "join \"depts\" using (\"deptno\")"; + + "from (select * from \"emps\" where \"empid\" < 300)\n" + + "join \"depts\" using (\"deptno\")"; checkMaterialize("select * from \"emps\" where \"empid\" < 500", q); } @@ -1002,171 +1000,171 @@ public class MaterializationTest { @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]])")); + "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]])")); } @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( - "EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", + "select \"deptno\" from \"emps\" group by \"deptno\"", + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableAggregate(group=[{1}])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testAggregateMaterializationNoAggregateFuncs3() { checkNoMaterialize( - "select \"deptno\" from \"emps\" group by \"deptno\"", - "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - HR_FKUK_MODEL); + "select \"deptno\" from \"emps\" group by \"deptno\"", + "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", + HR_FKUK_MODEL); } @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]])")); + "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]])")); } @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); + "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); } @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=[>($t1, $t2)], " - + "proj#0..1=[{exprs}], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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=[>($t1, $t2)], " + + "proj#0..1=[{exprs}], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @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); + "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); } @Test public void testAggregateMaterializationNoAggregateFuncs8() { checkNoMaterialize( - "select \"empid\" from \"emps\" group by \"empid\", \"deptno\"", - "select \"deptno\" from \"emps\" group by \"deptno\"", - HR_FKUK_MODEL); + "select \"empid\" from \"emps\" group by \"empid\", \"deptno\"", + "select \"deptno\" from \"emps\" group by \"deptno\"", + HR_FKUK_MODEL); } @Test public void testAggregateMaterializationNoAggregateFuncs9() { checkNoMaterialize( - "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); + "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); } @Test public void testAggregateMaterializationAggregateFuncs1() { checkMaterialize( - "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( - "EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableAggregate(group=[{1}])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testAggregateMaterializationAggregateFuncs2() { checkMaterialize( - "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( - "EnumerableAggregate(group=[{1}], C=[$SUM0($2)], S=[$SUM0($3)])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableAggregate(group=[{1}], C=[$SUM0($2)], S=[$SUM0($3)])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testAggregateMaterializationAggregateFuncs3() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], " - + "S=[$t3], C=[$t2])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], " + + "S=[$t3], C=[$t2])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testAggregateMaterializationAggregateFuncs4() { checkMaterialize( - "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( - "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n" - + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[>($t1, $t4)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n" + + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[>($t1, $t4)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testAggregateMaterializationAggregateFuncs5() { checkMaterialize( - "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( - "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=[>($t1, $t4)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "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=[>($t1, $t4)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testAggregateMaterializationAggregateFuncs6() { checkNoMaterialize( - "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); + "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); } @Test public void testAggregateMaterializationAggregateFuncs7() { checkMaterialize( - "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( - "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=[>($t1, $t4)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "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=[>($t1, $t4)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Ignore @@ -1174,172 +1172,172 @@ public class MaterializationTest { // 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" - + "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\""); + "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\""); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs1() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " - + "empid=[$t0], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " + + "empid=[$t0], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs2() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t0, $t2)], " - + "empid=[$t1], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t0, $t2)], " + + "empid=[$t1], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs3() { // It does not match, Project on top of query checkNoMaterialize( - "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); + "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); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs4() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " - + "empid=[$t0], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " + + "empid=[$t0], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @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]])")); + "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]])")); } @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=[>($t1, $t2)], " - + "proj#0..1=[{exprs}], $condition=[$t3])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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=[>($t1, $t2)], " + + "proj#0..1=[{exprs}], $condition=[$t3])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs7() { checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" + "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" + "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=[<=($t0, $t7)]")); + 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=[<=($t0, $t7)]")); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs8() { checkNoMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" + "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" + "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); + HR_FKUK_MODEL); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs9() { checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" + "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" + "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)]")); + 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)]")); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs10() { checkMaterialize( - "select \"depts\".\"name\", \"dependents\".\"name\" as \"name2\", " + "select \"depts\".\"name\", \"dependents\".\"name\" as \"name2\", " + "\"emps\".\"deptno\", \"depts\".\"deptno\" as \"deptno2\", " + "\"dependents\".\"empid\"\n" + "from \"depts\", \"dependents\", \"emps\"\n" @@ -1347,96 +1345,96 @@ public class MaterializationTest { + "group by \"depts\".\"name\", \"dependents\".\"name\", " + "\"emps\".\"deptno\", \"depts\".\"deptno\", " + "\"dependents\".\"empid\"", - "select \"dependents\".\"empid\"\n" + "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($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\"], " - + "expr#7=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\"], " - + "expr#8=[=($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..4=[{exprs}], $condition=[$t9])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableAggregate(group=[{4}])\n" + + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[=($t2, $t3)], " + + "expr#6=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\"], " + + "expr#7=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\"], " + + "expr#8=[=($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..4=[{exprs}], $condition=[$t9])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinAggregateMaterializationAggregateFuncs1() { // This test relies on FK-UK relationship checkMaterialize( - "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]])")); + "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]])")); } @Test public void testJoinAggregateMaterializationAggregateFuncs2() { checkMaterialize( - "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]])")); + "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]])")); } @Test public void testJoinAggregateMaterializationAggregateFuncs3() { // This test relies on FK-UK relationship checkMaterialize( - "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]])")); + "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]])")); } @Test public void testJoinAggregateMaterializationAggregateFuncs4() { checkMaterialize( - "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=[>($t1, $t4)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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=[>($t1, $t4)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinAggregateMaterializationAggregateFuncs5() { checkMaterialize( - "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=[>($t1, $t4)], " - + "proj#0..3=[{exprs}], $condition=[$t5])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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=[>($t1, $t4)], " + + "proj#0..3=[{exprs}], $condition=[$t5])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Ignore @@ -1530,273 +1528,273 @@ public class MaterializationTest { @Test public void testJoinAggregateMaterializationAggregateFuncs11() { checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\", count(\"emps\".\"salary\") as s\n" + "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" + "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( - "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)])", - "EnumerableUnion(all=[true])", - "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)]")); + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "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)])", + "EnumerableUnion(all=[true])", + "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)]")); } @Test public void testJoinAggregateMaterializationAggregateFuncs12() { checkNoMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\", count(distinct \"emps\".\"salary\") as s\n" + "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" + "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); + HR_FKUK_MODEL); } @Test public void testJoinMaterialization4() { checkMaterialize( - "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( - "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]])")); + "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( + "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]])")); } @Test public void testJoinMaterialization5() { checkMaterialize( - "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( - "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]])")); + "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( + "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]])")); } @Test public void testJoinMaterialization6() { checkMaterialize( - "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( - "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]])")); + "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( + "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]])")); } @Test public void testJoinMaterialization7() { checkMaterialize( - "select \"depts\".\"name\"\n" + "select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", - "select \"dependents\".\"empid\"\n" + "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( - "EnumerableCalc(expr#0..2=[{inputs}], empid0=[$t1])\n" - + " EnumerableJoin(condition=[=($0, $2)], joinType=[inner])\n" - + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" " - + "COLLATE \"ISO-8859-1$en_US$primary\"], name=[$t1])\n" - + " EnumerableTableScan(table=[[hr, m0]])\n" - + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" " - + "COLLATE \"ISO-8859-1$en_US$primary\"], empid=[$t0], name0=[$t2])\n" - + " EnumerableTableScan(table=[[hr, dependents]])")); + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableCalc(expr#0..2=[{inputs}], empid0=[$t1])\n" + + " EnumerableJoin(condition=[=($0, $2)], joinType=[inner])\n" + + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], name=[$t1])\n" + + " EnumerableTableScan(table=[[hr, m0]])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], empid=[$t0], name0=[$t2])\n" + + " EnumerableTableScan(table=[[hr, dependents]])")); } @Test public void testJoinMaterialization8() { checkMaterialize( - "select \"depts\".\"name\"\n" + "select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", - "select \"dependents\".\"empid\"\n" + "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( - "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t2])\n" - + " EnumerableJoin(condition=[=($1, $4)], joinType=[inner])\n" - + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" " - + "COLLATE \"ISO-8859-1$en_US$primary\"], proj#0..1=[{exprs}])\n" - + " EnumerableTableScan(table=[[hr, m0]])\n" - + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" " - + "COLLATE \"ISO-8859-1$en_US$primary\"], proj#0..2=[{exprs}])\n" - + " EnumerableTableScan(table=[[hr, dependents]])")); + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t2])\n" + + " EnumerableJoin(condition=[=($1, $4)], joinType=[inner])\n" + + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], proj#0..1=[{exprs}])\n" + + " EnumerableTableScan(table=[[hr, m0]])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], proj#0..2=[{exprs}])\n" + + " EnumerableTableScan(table=[[hr, dependents]])")); } @Test public void testJoinMaterialization9() { checkMaterialize( - "select \"depts\".\"name\"\n" + "select \"depts\".\"name\"\n" + "from \"emps\"\n" + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", - "select \"dependents\".\"empid\"\n" + "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); + HR_FKUK_MODEL, + CONTAINS_M0); } @Test public void testJoinMaterialization10() { checkMaterialize( - "select \"depts\".\"deptno\", \"dependents\".\"empid\"\n" + "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" + "where \"depts\".\"deptno\" > 30", - "select \"dependents\".\"empid\"\n" + "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", - HR_FKUK_MODEL, - CalciteAssert.checkResultContains( - "EnumerableUnion(all=[true])", - "EnumerableTableScan(table=[[hr, m0]])", - "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[30], expr#8=[<=($t0, $t7)]")); + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableUnion(all=[true])", + "EnumerableTableScan(table=[[hr, m0]])", + "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[30], expr#8=[<=($t0, $t7)]")); } @Test public void testJoinMaterializationUKFK1() { checkMaterialize( - "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]])")); + "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]])")); } @Test public void testJoinMaterializationUKFK2() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinMaterializationUKFK3() { checkNoMaterialize( - "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); + "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); } @Test public void testJoinMaterializationUKFK4() { checkMaterialize( - "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\" \"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]])")); } @Test public void testJoinMaterializationUKFK5() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinMaterializationUKFK6() { checkMaterialize( - "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( - "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" - + " EnumerableTableScan(table=[[hr, m0]])")); + "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( + "EnumerableCalc(expr#0..1=[{inputs}], empid=[$t0])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); } @Test public void testJoinMaterializationUKFK7() { checkNoMaterialize( - "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); + "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); } @Test public void testJoinMaterializationUKFK8() { checkNoMaterialize( - "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); + "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); } @Test public void testSubQuery() { @@ -1854,7 +1852,7 @@ public class MaterializationTest { final Map<String, Object> map = builder.map(); map.put("table", "locations"); String sql = "select `deptno` as `empid`, '' as `name`\n" - + "from `emps`"; + + "from `emps`"; final String sql2 = sql.replaceAll("`", "\""); map.put("sql", sql2); return ImmutableList.<Object>of(map); @@ -1977,14 +1975,14 @@ public class MaterializationTest { + "join (select * from \"emps\" where \"empid\" < 200) using (\"empid\")"; final String[][][] expectedNames = { - {{"hr", "emps"}, {"hr", "m0"}}, - {{"hr", "emps"}, {"hr", "m1"}}, - {{"hr", "m0"}, {"hr", "emps"}}, - {{"hr", "m0"}, {"hr", "m0"}}, - {{"hr", "m0"}, {"hr", "m1"}}, - {{"hr", "m1"}, {"hr", "emps"}}, - {{"hr", "m1"}, {"hr", "m0"}}, - {{"hr", "m1"}, {"hr", "m1"}}}; + {{"hr", "emps"}, {"hr", "m0"}}, + {{"hr", "emps"}, {"hr", "m1"}}, + {{"hr", "m0"}, {"hr", "emps"}}, + {{"hr", "m0"}, {"hr", "m0"}}, + {{"hr", "m0"}, {"hr", "m1"}}, + {{"hr", "m1"}, {"hr", "emps"}}, + {{"hr", "m1"}, {"hr", "m0"}}, + {{"hr", "m1"}, {"hr", "m1"}}}; try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); @@ -2014,21 +2012,21 @@ public class MaterializationTest { + "join (select * from \"emps\" where \"empid\" < 200) using (\"empid\")"; final String[][][] expectedNames = { - {{"hr", "emps"}, {"hr", "m0"}}, - {{"hr", "emps"}, {"hr", "m1"}}, - {{"hr", "emps"}, {"hr", "m2"}}, - {{"hr", "m0"}, {"hr", "emps"}}, - {{"hr", "m0"}, {"hr", "m0"}}, - {{"hr", "m0"}, {"hr", "m1"}}, - {{"hr", "m0"}, {"hr", "m2"}}, - {{"hr", "m1"}, {"hr", "emps"}}, - {{"hr", "m1"}, {"hr", "m0"}}, - {{"hr", "m1"}, {"hr", "m1"}}, - {{"hr", "m1"}, {"hr", "m2"}}, - {{"hr", "m2"}, {"hr", "emps"}}, - {{"hr", "m2"}, {"hr", "m0"}}, - {{"hr", "m2"}, {"hr", "m1"}}, - {{"hr", "m2"}, {"hr", "m2"}}}; + {{"hr", "emps"}, {"hr", "m0"}}, + {{"hr", "emps"}, {"hr", "m1"}}, + {{"hr", "emps"}, {"hr", "m2"}}, + {{"hr", "m0"}, {"hr", "emps"}}, + {{"hr", "m0"}, {"hr", "m0"}}, + {{"hr", "m0"}, {"hr", "m1"}}, + {{"hr", "m0"}, {"hr", "m2"}}, + {{"hr", "m1"}, {"hr", "emps"}}, + {{"hr", "m1"}, {"hr", "m0"}}, + {{"hr", "m1"}, {"hr", "m1"}}, + {{"hr", "m1"}, {"hr", "m2"}}, + {{"hr", "m2"}, {"hr", "emps"}}, + {{"hr", "m2"}, {"hr", "m0"}}, + {{"hr", "m2"}, {"hr", "m1"}}, + {{"hr", "m2"}, {"hr", "m2"}}}; try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); @@ -2100,25 +2098,25 @@ public class MaterializationTest { } public final Employee[] emps = { - new Employee(100, 10, "Bill", 10000, 1000), - new Employee(200, 20, "Eric", 8000, 500), - new Employee(150, 10, "Sebastian", 7000, null), - new Employee(110, 10, "Theodore", 11500, 250), + new Employee(100, 10, "Bill", 10000, 1000), + new Employee(200, 20, "Eric", 8000, 500), + new Employee(150, 10, "Sebastian", 7000, null), + new Employee(110, 10, "Theodore", 11500, 250), }; public final Department[] depts = { - new Department(10, "Sales", Arrays.asList(emps[0], emps[2], emps[3]), - new Location(-122, 38)), - new Department(30, "Marketing", Collections.<Employee>emptyList(), - new Location(0, 52)), - new Department(20, "HR", Collections.singletonList(emps[1]), null), + new Department(10, "Sales", Arrays.asList(emps[0], emps[2], emps[3]), + new Location(-122, 38)), + new Department(30, "Marketing", Collections.<Employee>emptyList(), + new Location(0, 52)), + new Department(20, "HR", Collections.singletonList(emps[1]), null), }; public final Dependent[] dependents = { - new Dependent(10, "Michael"), - new Dependent(10, "Jane"), + new Dependent(10, "Michael"), + new Dependent(10, "Jane"), }; public final Dependent[] locations = { - new Dependent(10, "San Francisco"), - new Dependent(20, "San Diego"), + new Dependent(10, "San Francisco"), + new Dependent(20, "San Diego"), }; public final RelReferentialConstraint rcs0 =
http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java index 060e434..ed1f952 100644 --- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java +++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java @@ -1167,7 +1167,7 @@ public class MockCatalogReader extends CalciteCatalogReader { * CustomColumnResolvingTable. */ private class ModifiableViewWithCustomColumnResolving - extends ModifiableView implements CustomColumnResolvingTable, Wrapper { + extends ModifiableView implements CustomColumnResolvingTable, Wrapper { @Override public List<Pair<RelDataTypeField, List<String>>> resolveColumn( RelDataType rowType, RelDataTypeFactory typeFactory, List<String> names) { http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java b/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java index 801e401..b5a256b 100644 --- a/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java +++ b/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java @@ -880,17 +880,17 @@ public class ReflectiveSchemaTest { public final BitSet bitSet = new BitSet(1); public final EveryType[] everyTypes = { - new EveryType( - false, (byte) 0, (char) 0, (short) 0, 0, 0L, 0F, 0D, - false, (byte) 0, (char) 0, (short) 0, 0, 0L, 0F, 0D, - new java.sql.Date(0), new Time(0), new Timestamp(0), - new Date(0), "1"), - new EveryType( - true, Byte.MAX_VALUE, Character.MAX_VALUE, Short.MAX_VALUE, - Integer.MAX_VALUE, Long.MAX_VALUE, Float.MAX_VALUE, - Double.MAX_VALUE, - null, null, null, null, null, null, null, null, - null, null, null, null, null), + new EveryType( + false, (byte) 0, (char) 0, (short) 0, 0, 0L, 0F, 0D, + false, (byte) 0, (char) 0, (short) 0, 0, 0L, 0F, 0D, + new java.sql.Date(0), new Time(0), new Timestamp(0), + new Date(0), "1"), + new EveryType( + true, Byte.MAX_VALUE, Character.MAX_VALUE, Short.MAX_VALUE, + Integer.MAX_VALUE, Long.MAX_VALUE, Float.MAX_VALUE, + Double.MAX_VALUE, + null, null, null, null, null, null, null, null, + null, null, null, null, null), }; public final AllPrivate[] allPrivates = { new AllPrivate() }; @@ -898,10 +898,10 @@ public class ReflectiveSchemaTest { public final BadType[] badTypes = { new BadType() }; public final Employee[] prefixEmps = { - new Employee(1, 10, "A", 0f, null), - new Employee(2, 10, "Ab", 0f, null), - new Employee(3, 10, "Abc", 0f, null), - new Employee(4, 10, "Abd", 0f, null), + new Employee(1, 10, "A", 0f, null), + new Employee(2, 10, "Ab", 0f, null), + new Employee(3, 10, "Abc", 0f, null), + new Employee(4, 10, "Abd", 0f, null), }; public final Integer[] primesBoxed = new Integer[]{1, 3, 5}; @@ -912,11 +912,11 @@ public class ReflectiveSchemaTest { new IntHolder[]{new IntHolder(1), new IntHolder(3), new IntHolder(5)}; public final IntAndString[] nullables = new IntAndString[] { - new IntAndString(1, "A"), new IntAndString(2, "B"), new IntAndString(2, "C"), - new IntAndString(3, null)}; + new IntAndString(1, "A"), new IntAndString(2, "B"), new IntAndString(2, "C"), + new IntAndString(3, null)}; public final IntAndString[] bools = new IntAndString[] { - new IntAndString(1, "T"), new IntAndString(2, "F"), new IntAndString(3, null)}; + new IntAndString(1, "T"), new IntAndString(2, "F"), new IntAndString(3, null)}; } /** @@ -933,9 +933,9 @@ public class ReflectiveSchemaTest { /** Schema that contains a table with a date column. */ public static class DateColumnSchema { public final EmployeeWithHireDate[] emps = { - new EmployeeWithHireDate( - 10, 20, "fred", 0f, null, new java.sql.Date(0)), // 1970-1-1 - new EmployeeWithHireDate( + new EmployeeWithHireDate( + 10, 20, "fred", 0f, null, new java.sql.Date(0)), // 1970-1-1 + new EmployeeWithHireDate( 10, 20, "bill", 0f, null, new java.sql.Date(100 * DateTimeUtils.MILLIS_PER_DAY)) // 1970-04-11 }; http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java index 3109b89..cf0c0e8 100644 --- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java @@ -2335,7 +2335,7 @@ public class RelMetadataTest extends SqlToRelTestBase { private static class MyRelMetadataQuery extends RelMetadataQuery { private ColType.Handler colTypeHandler; - public MyRelMetadataQuery() { + MyRelMetadataQuery() { super(THREAD_PROVIDERS.get(), EMPTY); colTypeHandler = initialHandler(ColType.Handler.class); } http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index 3e19616..f43f624 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -222,8 +222,8 @@ public class RelOptRulesTest extends RelOptTestBase { hepPlanner.addRule(ReduceExpressionsRule.PROJECT_INSTANCE); final String sql = "SELECT CASE WHEN 1=2 " - + "THEN cast((values(1)) as integer) " - + "ELSE 2 end from (values(1))"; + + "THEN cast((values(1)) as integer) " + + "ELSE 2 end from (values(1))"; sql(sql).with(hepPlanner).check(); } @@ -234,8 +234,8 @@ public class RelOptRulesTest extends RelOptTestBase { hepPlanner.addRule(ReduceExpressionsRule.PROJECT_INSTANCE); final String sql = "SELECT deptno, ename, CASE WHEN 1=2 " - + "THEN substring(ename, 1, cast(2 as int)) ELSE NULL end from emp" - + " group by deptno, ename, case when 1=2 then substring(ename,1, cast(2 as int)) else null end"; + + "THEN substring(ename, 1, cast(2 as int)) ELSE NULL end from emp" + + " group by deptno, ename, case when 1=2 then substring(ename,1, cast(2 as int)) else null end"; sql(sql).with(hepPlanner).check(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/ScannableTableTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/ScannableTableTest.java b/core/src/test/java/org/apache/calcite/test/ScannableTableTest.java index cf7fb46..bbb2f2b 100644 --- a/core/src/test/java/org/apache/calcite/test/ScannableTableTest.java +++ b/core/src/test/java/org/apache/calcite/test/ScannableTableTest.java @@ -290,8 +290,7 @@ public class ScannableTableTest { // have to iterate (CalciteAssert.toString) and then close the result set b/c it is backed by // an enumerable that only populates the info buffer (buf) on close resultSet.close(); - assertThat(buf.toString(), - equalTo("returnCount=4, projects=[2]")); + assertThat(buf.toString(), is("returnCount=4, projects=[2]")); } /** Test case for @@ -524,10 +523,10 @@ public class ScannableTableTest { } private static final Object[][] BEATLES = { - {4, "John", 1940}, - {4, "Paul", 1942}, - {6, "George", 1943}, - {5, "Ringo", 1940} + {4, "John", 1940}, + {4, "Paul", 1942}, + {6, "George", 1943}, + {5, "Ringo", 1940} }; private static Enumerator<Object[]> beatles(final StringBuilder buf, http://git-wip-us.apache.org/repos/asf/calcite/blob/9ab47c00/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index 581f571..75dfe77 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -41,15 +41,15 @@ import com.google.common.collect.ImmutableSet; import org.junit.Ignore; import org.junit.Test; -import static org.hamcrest.CoreMatchers.notNullValue; -import static org.junit.Assert.assertThat; - import java.io.PrintWriter; import java.io.StringWriter; import java.util.ArrayDeque; import java.util.Deque; import java.util.Set; +import static org.hamcrest.CoreMatchers.notNullValue; +import static org.junit.Assert.assertThat; + /** * Unit test for {@link org.apache.calcite.sql2rel.SqlToRelConverter}. */ @@ -2622,18 +2622,18 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { @Test public void testMatchRecognizePrevLast() { final String sql = "SELECT *\n" - + "FROM emp\n" - + "MATCH_RECOGNIZE (\n" - + " MEASURES\n" - + " STRT.mgr AS start_mgr,\n" - + " LAST(DOWN.mgr) AS bottom_mgr,\n" - + " LAST(UP.mgr) AS end_mgr\n" - + " ONE ROW PER MATCH\n" - + " PATTERN (STRT DOWN+ UP+)\n" - + " DEFINE\n" - + " DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n" - + " UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1)\n" - + ") AS T"; + + "FROM emp\n" + + "MATCH_RECOGNIZE (\n" + + " MEASURES\n" + + " STRT.mgr AS start_mgr,\n" + + " LAST(DOWN.mgr) AS bottom_mgr,\n" + + " LAST(UP.mgr) AS end_mgr\n" + + " ONE ROW PER MATCH\n" + + " PATTERN (STRT DOWN+ UP+)\n" + + " DEFINE\n" + + " DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n" + + " UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1)\n" + + ") AS T"; sql(sql).ok(); }
