http://git-wip-us.apache.org/repos/asf/calcite/blob/1f81e135/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 29603b1..bce7626 100644 --- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java +++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java @@ -16,6 +16,7 @@ */ package org.apache.calcite.test; +import org.apache.calcite.adapter.java.ReflectiveSchema; import org.apache.calcite.jdbc.JavaTypeFactoryImpl; import org.apache.calcite.materialize.MaterializationService; import org.apache.calcite.plan.RelOptPlanner; @@ -24,6 +25,8 @@ import org.apache.calcite.plan.RelOptTable; import org.apache.calcite.plan.SubstitutionVisitor; import org.apache.calcite.prepare.Prepare; import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.RelReferentialConstraint; +import org.apache.calcite.rel.RelReferentialConstraintImpl; import org.apache.calcite.rel.RelVisitor; import org.apache.calcite.rel.core.TableScan; import org.apache.calcite.rel.rules.MaterializedViewJoinRule; @@ -36,11 +39,19 @@ import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexSimplify; import org.apache.calcite.rex.RexUtil; import org.apache.calcite.runtime.Hook; +import org.apache.calcite.schema.QueryableTable; +import org.apache.calcite.schema.TranslatableTable; import org.apache.calcite.sql.fun.SqlStdOperatorTable; +import org.apache.calcite.test.JdbcTest.Department; +import org.apache.calcite.test.JdbcTest.Dependent; +import org.apache.calcite.test.JdbcTest.Employee; +import org.apache.calcite.test.JdbcTest.Location; import org.apache.calcite.tools.RuleSet; import org.apache.calcite.tools.RuleSets; import org.apache.calcite.util.JsonBuilder; +import org.apache.calcite.util.Smalls; import org.apache.calcite.util.TryThreadLocal; +import org.apache.calcite.util.mapping.IntPair; import com.google.common.base.Function; import com.google.common.collect.ImmutableList; @@ -52,6 +63,7 @@ import org.junit.Test; import java.math.BigDecimal; import java.sql.ResultSet; import java.util.ArrayList; +import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.Map; @@ -86,6 +98,25 @@ public class MaterializationTest { CASE_INSENSITIVE_LIST_LIST_COMPARATOR = CASE_INSENSITIVE_LIST_COMPARATOR.lexicographical(); + private static final String HR_FKUK_SCHEMA = "{\n" + + " type: 'custom',\n" + + " name: 'hr',\n" + + " factory: '" + + ReflectiveSchema.Factory.class.getName() + + "',\n" + + " operand: {\n" + + " class: '" + HrFKUKSchema.class.getName() + "'\n" + + " }\n" + + " }\n"; + + private static final String HR_FKUK_MODEL = "{\n" + + " version: '1.0',\n" + + " defaultSchema: 'hr',\n" + + " schemas: [\n" + + HR_FKUK_SCHEMA + + " ]\n" + + "}"; + final JavaTypeFactoryImpl typeFactory = new JavaTypeFactoryImpl(RelDataTypeSystem.DEFAULT); private final RexBuilder rexBuilder = new RexBuilder(typeFactory); @@ -122,7 +153,7 @@ public class MaterializationTest { @Test public void testFilter() { CalciteAssert.that() .withMaterializations( - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"deptno\" = 10") .query( @@ -137,7 +168,7 @@ public class MaterializationTest { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations( - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, "m0", "select \"deptno\", \"empid\" from \"emps\"") .query( @@ -151,14 +182,14 @@ 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, JdbcTest.HR_MODEL, CONTAINS_M0, + checkMaterialize(materialize, query, HR_FKUK_MODEL, CONTAINS_M0, RuleSets.ofList(ImmutableList.<RelOptRule>of())); } /** Checks that a given query can use a materialized view with a given * definition. */ private void checkMaterializeWithRules(String materialize, String query, RuleSet rules) { - checkMaterialize(materialize, query, JdbcTest.HR_MODEL, CONTAINS_M0, rules); + checkMaterialize(materialize, query, HR_FKUK_MODEL, CONTAINS_M0, rules); } /** Checks that a given query can use a materialized view with a given @@ -247,7 +278,7 @@ public class MaterializationTest { checkNoMaterialize( "select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", "select \"name\" from \"emps\" where \"deptno\" + 10 = 20", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnProjectView3()} but also contains an @@ -258,10 +289,10 @@ public class MaterializationTest { + "from \"emps\"", "select \"name\", \"empid\" + 1 as e\n" + "from \"emps\" where \"deptno\" - 10 = 2", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], " - + "expr#4=[=($t0, $t3)], name=[$t2], E=[$t1], $condition=[$t4])\n" + + "expr#4=[=($t0, $t3)], name=[$t2], EE=[$t1], $condition=[$t4])\n" + " EnumerableTableScan(table=[[hr, m0]]")); } @@ -270,7 +301,7 @@ public class MaterializationTest { checkNoMaterialize( "select \"deptno\" - 10 as \"x\", \"empid\" from \"emps\"", "select \"name\" from \"emps\" where \"deptno\" - 10 = 0", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnProjectView3()} but also contains an @@ -279,7 +310,7 @@ public class MaterializationTest { checkNoMaterialize( "select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", "select \"name\", \"empid\" + 2 from \"emps\" where \"deptno\" - 10 = 0", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** Test case for @@ -355,7 +386,7 @@ public class MaterializationTest { "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", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalcRel(expr#0..2=[{inputs}], expr#3=[1], " + "expr#4=[+($t1, $t3)], X=[$t4], name=[$t2], condition=?)\n" @@ -407,7 +438,7 @@ public class MaterializationTest { checkNoMaterialize( "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in @@ -417,7 +448,7 @@ public class MaterializationTest { "select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", "select \"name\", \"empid\" from \"emps\" " + "where \"deptno\" > 30 or \"empid\" > 10", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition currently @@ -428,7 +459,7 @@ public class MaterializationTest { + "and \"name\" = \'calcite\'", "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30 " + "or \"empid\" > 10", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in @@ -440,7 +471,7 @@ public class MaterializationTest { + "(\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000)", "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition of @@ -450,7 +481,7 @@ public class MaterializationTest { checkNoMaterialize( "select \"name\", \"deptno\" from \"emps\" where \"salary\" > 2000.5", "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in @@ -463,7 +494,7 @@ public class MaterializationTest { + "or (\"empid\" > 400 and \"salary\" > 5000)", "select \"name\" from \"emps\" where \"salary\" > 1000 " + "or (\"deptno\" > 30 and \"salary\" > 3000)", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } /** As {@link #testFilterQueryOnFilterView7()} but columns in materialized @@ -506,7 +537,7 @@ public class MaterializationTest { "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\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], " + "expr#3=[+($t1, $t2)], C=[$t3], deptno=[$t0])\n" @@ -522,7 +553,7 @@ public class MaterializationTest { 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\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "xxx")); } @@ -928,7 +959,7 @@ public class MaterializationTest { String q = "select *\n" + "from (select * from \"emps\" union all select * from \"emps\")\n" + "join \"depts\" using (\"deptno\")"; - checkNoMaterialize(q, q, JdbcTest.HR_MODEL); + checkNoMaterialize(q, q, HR_FKUK_MODEL); } @Test public void testJoinMaterialization() { @@ -965,7 +996,7 @@ public class MaterializationTest { 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, JdbcTest.HR_MODEL, + checkMaterialize(m, q, HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableTableScan(table=[[hr, m0]])", 1)); } @@ -974,7 +1005,7 @@ public class MaterializationTest { checkMaterialize( "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableTableScan(table=[[hr, m0]])")); } @@ -983,7 +1014,7 @@ public class MaterializationTest { checkMaterialize( "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" group by \"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableTableScan(table=[[hr, m0]])")); @@ -993,14 +1024,14 @@ public class MaterializationTest { checkNoMaterialize( "select \"deptno\" from \"emps\" group by \"deptno\"", "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", - JdbcTest.HR_MODEL); + 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\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableTableScan(table=[[hr, m0]])")); @@ -1010,14 +1041,14 @@ public class MaterializationTest { checkNoMaterialize( "select \"empid\", \"deptno\" from \"emps\" where \"deptno\" = 5 group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" where \"deptno\" = 10 group by \"deptno\"", - JdbcTest.HR_MODEL); + 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\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[10], expr#3=[>($t1, $t2)], " @@ -1029,14 +1060,14 @@ public class MaterializationTest { checkNoMaterialize( "select \"empid\", \"deptno\" from \"emps\" where \"deptno\" > 5 group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" where \"deptno\" < 10 group by \"deptno\"", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } @Test public void testAggregateMaterializationNoAggregateFuncs8() { checkNoMaterialize( "select \"empid\" from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" group by \"deptno\"", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } @Test public void testAggregateMaterializationNoAggregateFuncs9() { @@ -1045,7 +1076,7 @@ public class MaterializationTest { + "where \"salary\" > 1000 group by \"name\", \"empid\", \"deptno\"", "select \"empid\" from \"emps\"\n" + "where \"salary\" > 2000 group by \"name\", \"empid\"", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } @Test public void testAggregateMaterializationAggregateFuncs1() { @@ -1053,7 +1084,7 @@ public class MaterializationTest { "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" group by \"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableTableScan(table=[[hr, m0]])")); @@ -1065,7 +1096,7 @@ public class MaterializationTest { + "from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}], C=[$SUM0($2)], S=[$SUM0($3)])\n" + " EnumerableTableScan(table=[[hr, m0]])")); @@ -1077,7 +1108,7 @@ public class MaterializationTest { + "from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\", \"empid\", sum(\"empid\") as s, count(*) as c\n" + "from \"emps\" group by \"empid\", \"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], " + "S=[$t3], C=[$t2])\n" @@ -1090,7 +1121,7 @@ public class MaterializationTest { + "from \"emps\" where \"deptno\" >= 10 group by \"empid\", \"deptno\"", "select \"deptno\", sum(\"empid\") as s\n" + "from \"emps\" where \"deptno\" > 10 group by \"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n" + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[>($t1, $t4)], " @@ -1104,7 +1135,7 @@ public class MaterializationTest { + "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\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1, $t2)], " + "deptno=[$t0], S=[$t3])\n" @@ -1120,7 +1151,7 @@ public class MaterializationTest { + "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\"", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } @Test public void testAggregateMaterializationAggregateFuncs7() { @@ -1129,7 +1160,7 @@ public class MaterializationTest { + "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\"", - JdbcTest.HR_MODEL, + 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" @@ -1158,7 +1189,7 @@ public class MaterializationTest { "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" + "group by \"empid\", \"depts\".\"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " + "empid=[$t0], $condition=[$t3])\n" @@ -1173,7 +1204,7 @@ public class MaterializationTest { "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" + "group by \"empid\", \"depts\".\"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t0, $t2)], " + "empid=[$t1], $condition=[$t3])\n" @@ -1189,7 +1220,7 @@ public class MaterializationTest { "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" + "group by \"empid\", \"depts\".\"deptno\"", - JdbcTest.HR_MODEL); + HR_FKUK_MODEL); } @Test public void testJoinAggregateMaterializationNoAggregateFuncs4() { @@ -1200,7 +1231,7 @@ public class MaterializationTest { "select \"empid\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" > 20\n" + "group by \"empid\", \"depts\".\"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20], expr#3=[>($t1, $t2)], " + "empid=[$t0], $condition=[$t3])\n" @@ -1215,7 +1246,7 @@ public class MaterializationTest { "select \"depts\".\"deptno\" from \"depts\"\n" + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 15\n" + "group by \"depts\".\"deptno\", \"emps\".\"empid\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15], expr#3=[>($t1, $t2)], " + "deptno=[$t0], $condition=[$t3])\n" @@ -1230,7 +1261,7 @@ public class MaterializationTest { "select \"depts\".\"deptno\" from \"depts\"\n" + "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" > 15\n" + "group by \"depts\".\"deptno\"", - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{0}])\n" + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15], expr#3=[>($t1, $t2)], " @@ -1238,26 +1269,169 @@ public class MaterializationTest { + " 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]])")); + } + + @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]])")); + } + + @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]])")); + } + + @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]])")); + } + + @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]])")); + } + @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", - JdbcTest.HR_MODEL, + 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 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]])")); + } + + @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]])")); + } + + @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); + } + + @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]])")); + } + + @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]])")); + } + @Test public void testSubQuery() { String q = "select \"empid\", \"deptno\", \"salary\" from \"emps\" e1\n" + "where \"empid\" = (\n" + " select max(\"empid\") from \"emps\"\n" + " where \"deptno\" = e1.\"deptno\")"; final String m = "select \"empid\", \"deptno\" from \"emps\"\n"; - checkMaterialize(m, q, JdbcTest.HR_MODEL, + checkMaterialize(m, q, HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableTableScan(table=[[hr, m0]])", 1)); } @@ -1273,7 +1447,7 @@ public class MaterializationTest { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() - .withMaterializations(JdbcTest.HR_MODEL, + .withMaterializations(HR_FKUK_MODEL, "m0", m) .query(q) .withHook(Hook.SUB, @@ -1300,7 +1474,7 @@ public class MaterializationTest { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations( - JdbcTest.HR_MODEL, + HR_FKUK_MODEL, new Function<JsonBuilder, List<Object>>() { public List<Object> apply(JsonBuilder builder) { final Map<String, Object> map = builder.map(); @@ -1364,7 +1538,7 @@ 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, JdbcTest.HR_MODEL, + checkMaterialize(m, q, HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableTableScan(table=[[hr, m0]])", 2)); } @@ -1376,7 +1550,7 @@ public class MaterializationTest { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() - .withMaterializations(JdbcTest.HR_MODEL, + .withMaterializations(HR_FKUK_MODEL, "m0", "select \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" group by \"deptno\"", "m1", "select * from \"emps\" where \"empid\" < 500") .query(q) @@ -1394,7 +1568,7 @@ public class MaterializationTest { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() - .withMaterializations(JdbcTest.HR_MODEL, + .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500") .query(q) .enableMaterializations(true) @@ -1412,7 +1586,7 @@ public class MaterializationTest { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() - .withMaterializations(JdbcTest.HR_MODEL, + .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500", "m1", "select * from \"depts\" where \"deptno\" > 100") .query(q) @@ -1442,7 +1616,7 @@ public class MaterializationTest { MaterializationService.setThreadLocal(); final List<List<List<String>>> substitutedNames = new ArrayList<>(); CalciteAssert.that() - .withMaterializations(JdbcTest.HR_MODEL, + .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 300", "m1", "select * from \"emps\" where \"empid\" < 600") .query(q) @@ -1486,7 +1660,7 @@ public class MaterializationTest { MaterializationService.setThreadLocal(); final List<List<List<String>>> substitutedNames = new ArrayList<>(); CalciteAssert.that() - .withMaterializations(JdbcTest.HR_MODEL, + .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 300", "m1", "select * from \"emps\" where \"empid\" < 600", "m2", "select * from \"m1\"") @@ -1542,6 +1716,50 @@ public class MaterializationTest { super.visit(node, ordinal, parent); } } + + /** + * Hr schema with FK-UK relationship. + */ + public static class HrFKUKSchema { + @Override public String toString() { + return "HrFKUKSchema"; + } + + 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), + }; + 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), + }; + public final Dependent[] dependents = { + new Dependent(10, "Michael"), + new Dependent(10, "Jane"), + }; + public final Dependent[] locations = { + new Dependent(10, "San Francisco"), + new Dependent(20, "San Diego"), + }; + + public final RelReferentialConstraint rcs0 = + RelReferentialConstraintImpl.of( + ImmutableList.of("hr", "emps"), ImmutableList.of("hr", "depts"), + ImmutableList.of(IntPair.of(1, 0))); + + public QueryableTable foo(int count) { + return Smalls.generateStrings(count); + } + + public TranslatableTable view(String s) { + return Smalls.view(s); + } + } } // End MaterializationTest.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/1f81e135/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 52733c4..c3e112c 100644 --- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java +++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java @@ -33,6 +33,7 @@ import org.apache.calcite.rel.RelDistribution; import org.apache.calcite.rel.RelDistributions; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.RelReferentialConstraint; import org.apache.calcite.rel.logical.LogicalFilter; import org.apache.calcite.rel.logical.LogicalProject; import org.apache.calcite.rel.logical.LogicalTableScan; @@ -669,6 +670,8 @@ public class MockCatalogReader extends CalciteCatalogReader { protected final List<Map.Entry<String, RelDataType>> columnList = new ArrayList<>(); protected final List<Integer> keyList = new ArrayList<>(); + protected final List<RelReferentialConstraint> referentialConstraints = + new ArrayList<>(); protected RelDataType rowType; protected List<RelCollation> collationList; protected final List<String> names; @@ -859,6 +862,10 @@ public class MockCatalogReader extends CalciteCatalogReader { && columns.contains(ImmutableBitSet.of(keyList)); } + public List<RelReferentialConstraint> getReferentialConstraints() { + return referentialConstraints; + } + public RelDataType getRowType() { return rowType; } @@ -1461,6 +1468,10 @@ public class MockCatalogReader extends CalciteCatalogReader { return table.isKey(columns); } + public List<RelReferentialConstraint> getReferentialConstraints() { + return table.getReferentialConstraints(); + } + public List<RelCollation> getCollations() { return table.collationList; } http://git-wip-us.apache.org/repos/asf/calcite/blob/1f81e135/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java index 536bc4e..0e0d234 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java @@ -30,6 +30,7 @@ import org.apache.calcite.rel.RelDistribution; import org.apache.calcite.rel.RelDistributions; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.RelReferentialConstraint; import org.apache.calcite.rel.RelRoot; import org.apache.calcite.rel.core.RelFactories; import org.apache.calcite.rel.logical.LogicalTableScan; @@ -398,6 +399,10 @@ public abstract class SqlToRelTestBase { return false; } + public List<RelReferentialConstraint> getReferentialConstraints() { + return ImmutableList.of(); + } + public Expression getExpression(Class clazz) { return null; } @@ -467,6 +472,10 @@ public abstract class SqlToRelTestBase { public boolean isKey(ImmutableBitSet columns) { return parent.isKey(columns); } + + public List<RelReferentialConstraint> getReferentialConstraints() { + return parent.getReferentialConstraints(); + } } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/1f81e135/site/_docs/materialized_views.md ---------------------------------------------------------------------- diff --git a/site/_docs/materialized_views.md b/site/_docs/materialized_views.md index 9856a87..cae361b 100644 --- a/site/_docs/materialized_views.md +++ b/site/_docs/materialized_views.md @@ -82,6 +82,5 @@ shortcomings that we plan to address with follow-up extensions: * It does not produce rewritings using Union operators, e.g., a given query could be partially answered from the {mv} (year = 2014) and from the query (not(year=2014)). This can be useful if {mv} is stored in a system such as Druid. -* Currently query and {mv} must use the same tables. This rule is currently enabled by default.
