/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-761">[CALCITE-761] * Pre-populated materializations</a>. */ @Test public void testPrePopulated() { String q = "select \"deptno\" from \"emps\""; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations( HR_FKUK_MODEL, builder -> { final Map<String, Object> map = builder.map(); map.put("table", "locations"); String sql = "select `deptno` as `empid`, '' as `name`\n" + "from `emps`"; final String sql2 = sql.replaceAll("`", "\""); map.put("sql", sql2); return ImmutableList.of(map); }) .query(q) .enableMaterializations(true) .explainMatches("", CONTAINS_LOCATIONS) .sameResultWithMaterializationsDisabled(); } }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-761">[CALCITE-761] * Pre-populated materializations</a>. */ @Test public void testPrePopulated() { String q = "select \"deptno\" from \"emps\""; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations( HR_FKUK_MODEL, builder -> { final Map<String, Object> map = builder.map(); map.put("table", "locations"); String sql = "select `deptno` as `empid`, '' as `name`\n" + "from `emps`"; final String sql2 = sql.replaceAll("`", "\""); map.put("sql", sql2); return ImmutableList.of(map); }) .query(q) .enableMaterializations(true) .explainMatches("", CONTAINS_LOCATIONS) .sameResultWithMaterializationsDisabled(); } }
/** Checks that a given query CAN NOT use a materialized view with a given * definition. */ private void checkNoMaterialize(String materialize, String query, String model) { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(model, "m0", materialize) .query(query) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, emps]])"); } }
/** Checks that a given query CAN NOT use a materialized view with a given * definition. */ private void checkNoMaterialize(String materialize, String query, String model) { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(model, "m0", materialize) .query(query) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, emps]])"); } }
@Test public void testMaterializationSubstitution() { String q = "select *\n" + "from (select * from \"emps\" where \"empid\" < 300)\n" + "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"}}}; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); final List<List<List<String>>> substitutedNames = new ArrayList<>(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 300", "m1", "select * from \"emps\" where \"empid\" < 600") .query(q) .withHook(Hook.SUB, (Consumer<RelNode>) r -> substitutedNames.add(new TableNameVisitor().run(r))) .enableMaterializations(true) .sameResultWithMaterializationsDisabled(); substitutedNames.sort(CASE_INSENSITIVE_LIST_LIST_COMPARATOR); assertThat(substitutedNames, is(list3(expectedNames))); } }
@Test public void testTableModify() { final String m = "select \"deptno\", \"empid\", \"name\"" + "from \"emps\" where \"deptno\" = 10"; final String q = "upsert into \"dependents\"" + "select \"empid\" + 1 as x, \"name\"" + "from \"emps\" where \"deptno\" = 10"; final List<List<List<String>>> substitutedNames = new ArrayList<>(); try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", m) .query(q) .withHook(Hook.SUB, (Consumer<RelNode>) r -> substitutedNames.add(new TableNameVisitor().run(r))) .enableMaterializations(true) .explainContains("hr, m0"); } catch (Exception e) { // Table "dependents" not modifiable. } assertThat(substitutedNames, is(list3(new String[][][]{{{"hr", "m0"}}}))); }
@Test public void testTableModify() { final String m = "select \"deptno\", \"empid\", \"name\"" + "from \"emps\" where \"deptno\" = 10"; final String q = "upsert into \"dependents\"" + "select \"empid\" + 1 as x, \"name\"" + "from \"emps\" where \"deptno\" = 10"; final List<List<List<String>>> substitutedNames = new ArrayList<>(); try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", m) .query(q) .withHook(Hook.SUB, (Consumer<RelNode>) r -> substitutedNames.add(new TableNameVisitor().run(r))) .enableMaterializations(true) .explainContains("hr, m0"); } catch (Exception e) { // Table "dependents" not modifiable. } assertThat(substitutedNames, is(list3(new String[][][]{{{"hr", "m0"}}}))); }
/** Checks that a given query can use a materialized view with a given * definition. */ private CalciteAssert.AssertQuery checkThatMaterialize(String materialize, String query, String name, boolean existing, String model, Consumer<ResultSet> explainChecker, final RuleSet rules) { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.AssertQuery that = CalciteAssert.that() .withMaterializations(model, existing, name, materialize) .query(query) .enableMaterializations(true); // Add any additional rules required for the test if (rules.iterator().hasNext()) { that.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> { for (RelOptRule rule : rules) { planner.addRule(rule); } }); } return that.explainMatches("", explainChecker); } }
@Test public void testMaterializationOnJoinQuery() { final String q = "select *\n" + "from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" < 300 "; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500") .query(q) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .sameResultWithMaterializationsDisabled(); } }
@Test public void testFilterQueryOnProjectView() { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations( HR_FKUK_MODEL, "m0", "select \"deptno\", \"empid\" from \"emps\"") .query( "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10") .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .sameResultWithMaterializationsDisabled(); } }
/** Checks that a given query can use a materialized view with a given * definition. */ private CalciteAssert.AssertQuery checkThatMaterialize(String materialize, String query, String name, boolean existing, String model, Consumer<ResultSet> explainChecker, final RuleSet rules) { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.AssertQuery that = CalciteAssert.that() .withMaterializations(model, existing, name, materialize) .query(query) .enableMaterializations(true); // Add any additional rules required for the test if (rules.iterator().hasNext()) { that.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> { for (RelOptRule rule : rules) { planner.addRule(rule); } }); } return that.explainMatches("", explainChecker); } }
@Test public void testFilterQueryOnProjectView() { try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations( HR_FKUK_MODEL, "m0", "select \"deptno\", \"empid\" from \"emps\"") .query( "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10") .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .sameResultWithMaterializationsDisabled(); } }
@Test public void testMaterializationOnJoinQuery() { final String q = "select *\n" + "from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" < 300 "; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500") .query(q) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .sameResultWithMaterializationsDisabled(); } }
private void checkTileAlgorithm(String statisticProvider, String expectedExplain) { MaterializationService.setThreadLocal(); MaterializationService.instance().clear(); foodmartLatticeModel(statisticProvider) .query("select distinct t.\"the_year\", t.\"quarter\"\n" + "from \"foodmart\".\"sales_fact_1997\" as s\n" + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n") .enableMaterializations(true) // disable for MySQL; times out running star-join query // disable for H2; it thinks our generated SQL has invalid syntax .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.MYSQL && CalciteAssert.DB != CalciteAssert.DatabaseInstance.H2) .explainContains(expectedExplain) .returnsUnordered("the_year=1997; quarter=Q1", "the_year=1997; quarter=Q2", "the_year=1997; quarter=Q3", "the_year=1997; quarter=Q4"); }
private void checkTileAlgorithm(String statisticProvider, String expectedExplain) { MaterializationService.setThreadLocal(); MaterializationService.instance().clear(); foodmartLatticeModel(statisticProvider) .query("select distinct t.\"the_year\", t.\"quarter\"\n" + "from \"foodmart\".\"sales_fact_1997\" as s\n" + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n") .enableMaterializations(true) // disable for MySQL; times out running star-join query // disable for H2; it thinks our generated SQL has invalid syntax .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.MYSQL && CalciteAssert.DB != CalciteAssert.DatabaseInstance.H2) .explainContains(expectedExplain) .returnsUnordered("the_year=1997; quarter=Q1", "the_year=1997; quarter=Q2", "the_year=1997; quarter=Q3", "the_year=1997; quarter=Q4"); }
@Test public void testMultiMaterializationMultiUsage() { String q = "select *\n" + "from (select * from \"emps\" where \"empid\" < 300)\n" + "join (select \"deptno\", count(*) as c from \"emps\" group by \"deptno\") using (\"deptno\")"; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .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) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .explainContains("EnumerableTableScan(table=[[hr, m1]])") .sameResultWithMaterializationsDisabled(); } }
@Test public void testMultiMaterializationMultiUsage() { String q = "select *\n" + "from (select * from \"emps\" where \"empid\" < 300)\n" + "join (select \"deptno\", count(*) as c from \"emps\" group by \"deptno\") using (\"deptno\")"; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .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) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .explainContains("EnumerableTableScan(table=[[hr, m1]])") .sameResultWithMaterializationsDisabled(); } }
@Ignore("Creating mv for depts considering all its column throws exception") @Test public void testMultiMaterializationOnJoinQuery() { final String q = "select *\n" + "from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" < 300 " + "and \"depts\".\"deptno\" > 200"; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500", "m1", "select * from \"depts\" where \"deptno\" > 100") .query(q) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .explainContains("EnumerableTableScan(table=[[hr, m1]])") .sameResultWithMaterializationsDisabled(); } }
@Ignore("Creating mv for depts considering all its column throws exception") @Test public void testMultiMaterializationOnJoinQuery() { final String q = "select *\n" + "from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" < 300 " + "and \"depts\".\"deptno\" > 200"; try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(HR_FKUK_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500", "m1", "select * from \"depts\" where \"deptno\" > 100") .query(q) .enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])") .explainContains("EnumerableTableScan(table=[[hr, m1]])") .sameResultWithMaterializationsDisabled(); } }
public RelNode run(RelOptPlanner planner, RelNode rel, RelTraitSet requiredOutputTraits, List<RelOptMaterialization> materializations, List<RelOptLattice> lattices) { planner.clear(); planner.addRelTraitDef(ConventionTraitDef.INSTANCE); planner.addRelTraitDef(RelCollationTraitDef.INSTANCE); //((VolcanoPlanner) planner).registerAbstractRelationalRules(); RelOptUtil.registerAbstractRels(planner); for (RelOptRule rule : ruleSet) { planner.addRule(rule); } planner.addRule(Bindables.BINDABLE_TABLE_SCAN_RULE); planner.addRule(ProjectTableScanRule.INSTANCE); planner.addRule(ProjectTableScanRule.INTERPRETER); planner.addRule(EnumerableInterpreterRule.INSTANCE); final CalciteSchema rootSchema = CalciteSchema.from(context.getRootSchema()); planner.setExecutor(new RexExecutorImpl(null)); planner.setRoot(rel); MaterializationService.setThreadLocal(materializationService); plannerHolder.setPlanner(planner); populateMaterializationsAndLattice(plannerHolder, rootSchema); if (!rel.getTraitSet().equals(requiredOutputTraits)) { rel = planner.changeTraits(rel, requiredOutputTraits); planner.setRoot(rel); } RelOptPlanner planner2 = planner.chooseDelegate(); return planner2.findBestExp(); }