calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jcama...@apache.org
Subject [06/11] calcite git commit: [CALCITE-1731] Materialized view rewriting for join and aggregate operators
Date Wed, 26 Apr 2017 19:18:40 GMT
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.


Mime
View raw message