Java tutorial
/* * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to you under the Apache License, Version 2.0 * (the "License"); you may not use this file except in compliance with * the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.calcite.test; import org.apache.calcite.jdbc.JavaTypeFactoryImpl; import org.apache.calcite.materialize.MaterializationService; 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.RelVisitor; import org.apache.calcite.rel.core.TableScan; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.rex.RexBuilder; import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexUtil; import org.apache.calcite.runtime.Hook; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.util.JsonBuilder; import org.apache.calcite.util.TryThreadLocal; import org.apache.calcite.util.Util; import org.apache.commons.lang3.StringUtils; import com.google.common.base.Function; import com.google.common.collect.ImmutableList; import com.google.common.collect.Ordering; import org.junit.Ignore; import org.junit.Test; import java.math.BigDecimal; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Map; import static org.hamcrest.CoreMatchers.equalTo; import static org.hamcrest.CoreMatchers.is; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertThat; import static org.junit.Assert.assertTrue; /** * Unit test for the materialized view rewrite mechanism. Each test has a * query and one or more materializations (what Oracle calls materialized views) * and checks that the materialization is used. */ public class MaterializationTest { private static final Function<ResultSet, Void> CONTAINS_M0 = CalciteAssert .checkResultContains("EnumerableTableScan(table=[[hr, m0]])"); private static final Function<ResultSet, Void> CONTAINS_LOCATIONS = CalciteAssert .checkResultContains("EnumerableTableScan(table=[[hr, locations]])"); 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 = CASE_INSENSITIVE_LIST_COMPARATOR .lexicographical(); final JavaTypeFactoryImpl typeFactory = new JavaTypeFactoryImpl(RelDataTypeSystem.DEFAULT); final RexBuilder rexBuilder = new RexBuilder(typeFactory); @Test public void testScan() { CalciteAssert.that() .withMaterializations( "{\n" + " version: '1.0',\n" + " defaultSchema: 'SCOTT_CLONE',\n" + " schemas: [ {\n" + " name: 'SCOTT_CLONE',\n" + " type: 'custom',\n" + " factory: 'org.apache.calcite.adapter.clone.CloneSchema$Factory',\n" + " operand: {\n" + " jdbcDriver: '" + JdbcTest.SCOTT.driver + "',\n" + " jdbcUser: '" + JdbcTest.SCOTT.username + "',\n" + " jdbcPassword: '" + JdbcTest.SCOTT.password + "',\n" + " jdbcUrl: '" + JdbcTest.SCOTT.url + "',\n" + " jdbcSchema: 'SCOTT'\n" + " } } ]\n" + "}", "m0", "select empno, deptno from emp order by deptno") .query("select empno, deptno from emp").enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[SCOTT_CLONE, m0]])") .sameResultWithMaterializationsDisabled(); } @Test public void testFilter() { CalciteAssert.that() .withMaterializations(JdbcTest.HR_MODEL, "m0", "select * from \"emps\" where \"deptno\" = 10") .query("select \"empid\" + 1 from \"emps\" where \"deptno\" = 10").enableMaterializations(true) .explainContains("EnumerableTableScan(table=[[hr, m0]])").sameResultWithMaterializationsDisabled(); } @Test public void testFilterQueryOnProjectView() { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(JdbcTest.HR_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 void checkMaterialize(String materialize, String query) { checkMaterialize(materialize, query, JdbcTest.HR_MODEL, CONTAINS_M0); } /** Checks that a given query can use a materialized view with a given * definition. */ private void checkMaterialize(String materialize, String query, String model, Function<ResultSet, Void> explainChecker) { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that().withMaterializations(model, "m0", materialize).query(query) .enableMaterializations(true).explainMatches("", explainChecker) .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 (final 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]])"); } } /** Runs the same test as {@link #testFilterQueryOnProjectView()} but more * concisely. */ @Test public void testFilterQueryOnProjectView0() { checkMaterialize("select \"deptno\", \"empid\" from \"emps\"", "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10"); } /** As {@link #testFilterQueryOnProjectView()} but with extra column in * materialized view. */ @Test public void testFilterQueryOnProjectView1() { checkMaterialize("select \"deptno\", \"empid\", \"name\" from \"emps\"", "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10"); } /** As {@link #testFilterQueryOnProjectView()} but with extra column in both * materialized view and query. */ @Test public void testFilterQueryOnProjectView2() { checkMaterialize("select \"deptno\", \"empid\", \"name\" from \"emps\"", "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10"); } @Test public void testFilterQueryOnProjectView3() { checkMaterialize("select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", "select \"name\" from \"emps\" where \"deptno\" - 10 = 0"); } /** As {@link #testFilterQueryOnProjectView3()} but materialized view cannot * be used because it does not contain required expression. */ @Test public void testFilterQueryOnProjectView4() { checkNoMaterialize("select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", "select \"name\" from \"emps\" where \"deptno\" + 10 = 20", JdbcTest.HR_MODEL); } /** As {@link #testFilterQueryOnProjectView3()} but also contains an * expression column. */ @Test public void testFilterQueryOnProjectView5() { checkMaterialize("select \"deptno\" - 10 as \"x\", \"empid\" + 1 as ee, \"name\"\n" + "from \"emps\"", "select \"name\", \"empid\" + 1 as e\n" + "from \"emps\" where \"deptno\" - 10 = 2", JdbcTest.HR_MODEL, CalciteAssert.checkResultContains("EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], " + "expr#4=[=($t0, $t3)], name=[$t2], E=[$t1], $condition=[$t4])\n" + " EnumerableTableScan(table=[[hr, m0]]")); } /** Cannot materialize because "name" is not projected in the MV. */ @Test public void testFilterQueryOnProjectView6() { checkNoMaterialize("select \"deptno\" - 10 as \"x\", \"empid\" from \"emps\"", "select \"name\" from \"emps\" where \"deptno\" - 10 = 0", JdbcTest.HR_MODEL); } /** As {@link #testFilterQueryOnProjectView3()} but also contains an * expression column. */ @Test public void testFilterQueryOnProjectView7() { checkNoMaterialize("select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", "select \"name\", \"empid\" + 2 from \"emps\" where \"deptno\" - 10 = 0", JdbcTest.HR_MODEL); } /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-988">[CALCITE-988] * FilterToProjectUnifyRule.invert(MutableRel, MutableRel, MutableProject) * works incorrectly</a>. */ @Test public void testFilterQueryOnProjectView8() { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); final String m = "select \"salary\", \"commission\",\n" + "\"deptno\", \"empid\", \"name\" from \"emps\""; final String v = "select * from \"emps\" where \"name\" is null"; final String q = "select * from V where \"commission\" is null"; final JsonBuilder builder = new JsonBuilder(); final String model = "{\n" + " version: '1.0',\n" + " defaultSchema: 'hr',\n" + " schemas: [\n" + " {\n" + " materializations: [\n" + " {\n" + " table: 'm0',\n" + " view: 'm0v',\n" + " sql: " + builder.toJsonString(m) + " }\n" + " ],\n" + " tables: [\n" + " {\n" + " name: 'V',\n" + " type: 'view',\n" + " sql: " + builder.toJsonString(v) + "\n" + " }\n" + " ],\n" + " type: 'custom',\n" + " name: 'hr',\n" + " factory: 'org.apache.calcite.adapter.java.ReflectiveSchema$Factory',\n" + " operand: {\n" + " class: 'org.apache.calcite.test.JdbcTest$HrSchema'\n" + " }\n" + " }\n" + " ]\n" + "}\n"; CalciteAssert.that().withModel(model).query(q).enableMaterializations(true) .explainMatches("", CONTAINS_M0).sameResultWithMaterializationsDisabled(); } } @Test public void testFilterQueryOnFilterView() { checkMaterialize("select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10", "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10"); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. */ @Ignore @Test public void testFilterQueryOnFilterView2() { checkMaterialize("select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10", "select \"empid\" + 1 as x, \"name\" from \"emps\" " + "where \"deptno\" = 10 and \"empid\" < 150"); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * view. */ @Ignore("not implemented") @Test public void testFilterQueryOnFilterView3() { checkMaterialize( "select \"deptno\", \"empid\", \"name\" from \"emps\" " + "where \"deptno\" = 10 or \"deptno\" = 20 or \"empid\" < 160", "select \"empid\" + 1 as x, \"name\" from \"emps\" where \"deptno\" = 10", JdbcTest.HR_MODEL, CalciteAssert.checkResultContains("EnumerableCalcRel(expr#0..2=[{inputs}], expr#3=[1], " + "expr#4=[+($t1, $t3)], X=[$t4], name=[$t2], condition=?)\n" + " EnumerableTableScan(table=[[hr, m0]])")); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. */ @Test public void testFilterQueryOnFilterView4() { checkMaterialize("select * from \"emps\" where \"deptno\" > 10", "select \"name\" from \"emps\" where \"deptno\" > 30"); } /** 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"); } /** 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"); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query and columns selected are subset of columns in materialized view * Condition here is complex*/ @Test public void testFilterQueryOnFilterView7() { checkMaterialize( "select * from \"emps\" where " + "((\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000) " + "or \"salary\" > 500)", "select \"name\" from \"emps\" where (\"salary\" > 1000 " + "or (\"deptno\" >= 30 and \"salary\" <= 500))"); } /** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. However, columns selected are not present in columns of materialized view, * hence should not use materialized view*/ @Test public void testFilterQueryOnFilterView8() { checkNoMaterialize("select \"name\", \"deptno\" from \"emps\" where \"deptno\" > 10", "select \"name\", \"empid\" from \"emps\" where \"deptno\" > 30", JdbcTest.HR_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", JdbcTest.HR_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", JdbcTest.HR_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * query and columns selected are subset of columns in materialized view * Condition here is complex*/ @Test public void testFilterQueryOnFilterView11() { checkNoMaterialize( "select \"name\", \"deptno\" from \"emps\" where " + "(\"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); } /** As {@link #testFilterQueryOnFilterView()} but condition of * query is stronger but is on the column not present in MV (salary). */ @Test public void testFilterQueryOnFilterView12() { checkNoMaterialize("select \"name\", \"deptno\" from \"emps\" where \"salary\" > 2000.5", "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000", JdbcTest.HR_MODEL); } /** As {@link #testFilterQueryOnFilterView()} but condition is weaker in * query and columns selected are subset of columns in materialized view * Condition here is complex*/ @Test public void testFilterQueryOnFilterView13() { checkNoMaterialize( "select * from \"emps\" where " + "(\"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)", JdbcTest.HR_MODEL); } /** As {@link #testFilterQueryOnFilterView7()} but columns in materialized * view are a permutation of columns in the query*/ @Test public void testFilterQueryOnFilterView14() { String q = "select * from \"emps\" where (\"salary\" > 1000 " + "or (\"deptno\" >= 30 and \"salary\" <= 500))"; String m = "select \"deptno\", \"empid\", \"name\", \"salary\", \"commission\" " + "from \"emps\" as em where " + "((\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000) " + "or \"salary\" > 500)"; checkMaterialize(m, q); } /** As {@link #testFilterQueryOnFilterView13()} but using alias * 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)"); } /** Aggregation query at same level of aggregation as aggregation * materialization. */ @Test public void testAggregate() { checkMaterialize("select \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" group by \"deptno\"", "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\""); } /** Aggregation query at coarser level of aggregation than aggregation * materialization. Requires an additional aggregate to roll up. Note that * COUNT is rolled up using SUM. */ @Test public void testAggregateRollUp() { checkMaterialize( "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" " + "group by \"empid\", \"deptno\"", "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"", JdbcTest.HR_MODEL, CalciteAssert.checkResultContains("EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], " + "expr#3=[+($t1, $t2)], C=[$t3], deptno=[$t0])\n" + " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($2)])\n" + " EnumerableTableScan(table=[[hr, m0]])")); } /** Aggregation materialization with a project. */ @Ignore("work in progress") @Test public void testAggregateProject() { // Note that materialization does not start with the GROUP BY columns. // Not a smart way to design a materialization, but people may do it. checkMaterialize( "select \"deptno\", count(*) as c, \"empid\" + 2, sum(\"empid\") as s from \"emps\" group by \"empid\", \"deptno\"", "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"", JdbcTest.HR_MODEL, CalciteAssert.checkResultContains("xxx")); } @Ignore @Test public void testSwapJoin() { String q1 = "select count(*) as c from \"foodmart\".\"sales_fact_1997\" as s join \"foodmart\".\"time_by_day\" as t on s.\"time_id\" = t.\"time_id\""; String q2 = "select count(*) as c from \"foodmart\".\"time_by_day\" as t join \"foodmart\".\"sales_fact_1997\" as s on t.\"time_id\" = s.\"time_id\""; } @Ignore @Test public void testOrderByQueryOnProjectView() { checkMaterialize("select \"deptno\", \"empid\" from \"emps\"", "select \"empid\" from \"emps\" order by \"deptno\""); } @Ignore @Test public void testOrderByQueryOnOrderByView() { checkMaterialize("select \"deptno\", \"empid\" from \"emps\" order by \"deptno\"", "select \"empid\" from \"emps\" order by \"deptno\""); } @Ignore @Test public void testDifferentColumnNames() { } @Ignore @Test public void testDifferentType() { } @Ignore @Test public void testPartialUnion() { } @Ignore @Test public void testNonDisjointUnion() { } @Ignore @Test public void testMaterializationReferencesTableInOtherSchema() { } /** Unit test for logic functions * {@link org.apache.calcite.plan.SubstitutionVisitor#mayBeSatisfiable} and * {@link RexUtil#simplify}. */ @Test public void testSatisfiable() { // TRUE may be satisfiable checkSatisfiable(rexBuilder.makeLiteral(true), "true"); // FALSE is not satisfiable checkNotSatisfiable(rexBuilder.makeLiteral(false)); // The expression "$0 = 1". final RexNode i0_eq_0 = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, rexBuilder.makeInputRef(typeFactory.createType(int.class), 0), rexBuilder.makeExactLiteral(BigDecimal.ZERO)); // "$0 = 1" may be satisfiable checkSatisfiable(i0_eq_0, "=($0, 0)"); // "$0 = 1 AND TRUE" may be satisfiable final RexNode e0 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder.makeLiteral(true)); checkSatisfiable(e0, "=($0, 0)"); // "$0 = 1 AND FALSE" is not satisfiable final RexNode e1 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder.makeLiteral(false)); checkNotSatisfiable(e1); // "$0 = 0 AND NOT $0 = 0" is not satisfiable final RexNode e2 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder.makeCall(SqlStdOperatorTable.NOT, i0_eq_0)); checkNotSatisfiable(e2); // "TRUE AND NOT $0 = 0" may be satisfiable. Can simplify. final RexNode e3 = rexBuilder.makeCall(SqlStdOperatorTable.AND, rexBuilder.makeLiteral(true), rexBuilder.makeCall(SqlStdOperatorTable.NOT, i0_eq_0)); checkSatisfiable(e3, "NOT(=($0, 0))"); // The expression "$1 = 1". final RexNode i1_eq_1 = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, rexBuilder.makeInputRef(typeFactory.createType(int.class), 1), rexBuilder.makeExactLiteral(BigDecimal.ONE)); // "$0 = 0 AND $1 = 1 AND NOT $0 = 0" is not satisfiable final RexNode e4 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder .makeCall(SqlStdOperatorTable.AND, i1_eq_1, rexBuilder.makeCall(SqlStdOperatorTable.NOT, i0_eq_0))); checkNotSatisfiable(e4); // "$0 = 0 AND NOT $1 = 1" may be satisfiable. Can't simplify. final RexNode e5 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder.makeCall(SqlStdOperatorTable.NOT, i1_eq_1)); checkSatisfiable(e5, "AND(=($0, 0), NOT(=($1, 1)))"); // "$0 = 0 AND NOT ($0 = 0 AND $1 = 1)" may be satisfiable. Can simplify. final RexNode e6 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder .makeCall(SqlStdOperatorTable.NOT, rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, i1_eq_1))); checkSatisfiable(e6, "AND(=($0, 0), NOT(AND(=($0, 0), =($1, 1))))"); // "$0 = 0 AND ($1 = 1 AND NOT ($0 = 0))" is not satisfiable. final RexNode e7 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder .makeCall(SqlStdOperatorTable.AND, i1_eq_1, rexBuilder.makeCall(SqlStdOperatorTable.NOT, i0_eq_0))); checkNotSatisfiable(e7); // The expression "$2". final RexInputRef i2 = rexBuilder.makeInputRef(typeFactory.createType(boolean.class), 2); // The expression "$3". final RexInputRef i3 = rexBuilder.makeInputRef(typeFactory.createType(boolean.class), 3); // The expression "$4". final RexInputRef i4 = rexBuilder.makeInputRef(typeFactory.createType(boolean.class), 4); // "$0 = 0 AND $2 AND $3 AND NOT ($2 AND $3 AND $4) AND NOT ($2 AND $4)" may // be satisfiable. Can't simplify. final RexNode e8 = rexBuilder.makeCall(SqlStdOperatorTable.AND, i0_eq_0, rexBuilder.makeCall(SqlStdOperatorTable.AND, i2, rexBuilder.makeCall(SqlStdOperatorTable.AND, i3, rexBuilder.makeCall(SqlStdOperatorTable.NOT, rexBuilder.makeCall(SqlStdOperatorTable.AND, i2, i3, i4)), rexBuilder.makeCall(SqlStdOperatorTable.NOT, i4)))); checkSatisfiable(e8, "AND(=($0, 0), $2, $3, NOT(AND($2, $3, $4)), NOT($4))"); } private void checkNotSatisfiable(RexNode e) { assertFalse(SubstitutionVisitor.mayBeSatisfiable(e)); final RexNode simple = RexUtil.simplify(rexBuilder, e); assertFalse(RexLiteral.booleanValue(simple)); } private void checkSatisfiable(RexNode e, String s) { assertTrue(SubstitutionVisitor.mayBeSatisfiable(e)); final RexNode simple = RexUtil.simplify(rexBuilder, e); assertEquals(s, simple.toString()); } @Test public void testSplitFilter() { final RexLiteral i1 = rexBuilder.makeExactLiteral(BigDecimal.ONE); final RexLiteral i2 = rexBuilder.makeExactLiteral(BigDecimal.valueOf(2)); final RexLiteral i3 = rexBuilder.makeExactLiteral(BigDecimal.valueOf(3)); final RelDataType intType = typeFactory.createType(int.class); final RexInputRef x = rexBuilder.makeInputRef(intType, 0); // $0 final RexInputRef y = rexBuilder.makeInputRef(intType, 1); // $1 final RexInputRef z = rexBuilder.makeInputRef(intType, 2); // $2 final RexNode x_eq_1 = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, x, i1); // $0 = 1 final RexNode x_eq_1_b = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, x, i1); // $0 = 1 again final RexNode y_eq_2 = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, y, i2); // $1 = 2 final RexNode z_eq_3 = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, z, i3); // $2 = 3 RexNode newFilter; // Example 1. // TODO: // Example 2. // condition: x = 1, // target: x = 1 or z = 3 // yields // residue: not (z = 3) newFilter = SubstitutionVisitor.splitFilter(rexBuilder, x_eq_1, rexBuilder.makeCall(SqlStdOperatorTable.OR, x_eq_1, z_eq_3)); assertThat(newFilter.toString(), equalTo("NOT(=($2, 3))")); // 2b. // condition: x = 1 or y = 2 // target: x = 1 or y = 2 or z = 3 // yields // residue: not (z = 3) newFilter = SubstitutionVisitor.splitFilter(rexBuilder, rexBuilder.makeCall(SqlStdOperatorTable.OR, x_eq_1, y_eq_2), rexBuilder.makeCall(SqlStdOperatorTable.OR, x_eq_1, y_eq_2, z_eq_3)); assertThat(newFilter.toString(), equalTo("NOT(=($2, 3))")); // 2c. // condition: x = 1 // target: x = 1 or y = 2 or z = 3 // yields // residue: not (y = 2) and not (z = 3) newFilter = SubstitutionVisitor.splitFilter(rexBuilder, x_eq_1, rexBuilder.makeCall(SqlStdOperatorTable.OR, x_eq_1, y_eq_2, z_eq_3)); assertThat(newFilter.toString(), equalTo("AND(NOT(=($1, 2)), NOT(=($2, 3)))")); // 2d. // condition: x = 1 or y = 2 // target: y = 2 or x = 1 // yields // residue: true newFilter = SubstitutionVisitor.splitFilter(rexBuilder, rexBuilder.makeCall(SqlStdOperatorTable.OR, x_eq_1, y_eq_2), rexBuilder.makeCall(SqlStdOperatorTable.OR, y_eq_2, x_eq_1)); assertThat(newFilter.isAlwaysTrue(), equalTo(true)); // 2e. // condition: x = 1 // target: x = 1 (different object) // yields // residue: true newFilter = SubstitutionVisitor.splitFilter(rexBuilder, x_eq_1, x_eq_1_b); assertThat(newFilter.isAlwaysTrue(), equalTo(true)); // 2f. // condition: x = 1 or y = 2 // target: x = 1 // yields // residue: null // TODO: // Example 3. // Condition [x = 1 and y = 2], // target [y = 2 and x = 1] yields // residue [true]. // TODO: // Example 4. // TODO: } /** Tests a complicated star-join query on a complicated materialized * star-join query. Some of the features: * * 1. query joins in different order; * 2. query's join conditions are in where clause; * 3. query does not use all join tables (safe to omit them because they are * many-to-mandatory-one joins); * 4. query is at higher granularity, therefore needs to roll up; * 5. query has a condition on one of the materialization's grouping columns. */ @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\"", "select t.\"the_month\", count(*) as x\n" + "from (\n" + " select \"time_id\", \"the_year\", \"the_month\"\n" + " from \"foodmart\".\"time_by_day\") as t,\n" + " \"foodmart\".\"sales_fact_1997\" as f\n" + "where t.\"the_year\" = 1997\n" + "and t.\"time_id\" = f.\"time_id\"\n" + "group by t.\"the_year\",\n" + " t.\"the_month\"\n", JdbcTest.FOODMART_MODEL, CONTAINS_M0); } /** Simpler than {@link #testFilterGroupQueryOnStar()}, tests a query on a * materialization that is just a join. */ @Ignore @Test public void testQueryOnStar() { String q = "select *\n" + "from \"foodmart\".\"sales_fact_1997\" as f\n" + "join \"foodmart\".\"time_by_day\" as t on f.\"time_id\" = t.\"time_id\"\n" + "join \"foodmart\".\"product\" as p on f.\"product_id\" = p.\"product_id\"\n" + "join \"foodmart\".\"product_class\" as pc on p.\"product_class_id\" = pc.\"product_class_id\"\n"; checkMaterialize(q, q + "where t.\"month_of_year\" = 10", JdbcTest.FOODMART_MODEL, CONTAINS_M0); } /** A materialization that is a join of a union cannot at present be converted * to a star table and therefore cannot be recognized. This test checks that * nothing unpleasant happens. */ @Ignore @Test public void testJoinOnUnionMaterialization() { String q = "select *\n" + "from (select * from \"emps\" union all select * from \"emps\")\n" + "join \"depts\" using (\"deptno\")"; checkNoMaterialize(q, q, JdbcTest.HR_MODEL); } @Test public void testJoinMaterialization() { String q = "select *\n" + "from (select * from \"emps\" where \"empid\" < 300)\n" + "join \"depts\" using (\"deptno\")"; checkMaterialize("select * from \"emps\" where \"empid\" < 500", q); } /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-891">[CALCITE-891] * TableScan without Project cannot be substituted by any projected * materialization</a>. */ @Test public void testJoinMaterialization2() { String q = "select *\n" + "from \"emps\"\n" + "join \"depts\" using (\"deptno\")"; final String m = "select \"deptno\", \"empid\", \"name\",\n" + "\"salary\", \"commission\" from \"emps\""; checkMaterialize(m, q); } /** 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 (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that().withMaterializations(JdbcTest.HR_MODEL, new Function<JsonBuilder, List<Object>>() { public List<Object> apply(JsonBuilder 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.<Object>of(map); } }).query(q).enableMaterializations(true).explainMatches("", CONTAINS_LOCATIONS) .sameResultWithMaterializationsDisabled(); } } @Test public void testViewSchemaPath() { try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); final String m = "select empno, deptno from emp"; final String q = "select deptno from scott.emp"; final List<String> path = ImmutableList.of("SCOTT"); final JsonBuilder builder = new JsonBuilder(); final String model = "{\n" + " version: '1.0',\n" + " defaultSchema: 'hr',\n" + " schemas: [\n" + JdbcTest.SCOTT_SCHEMA + " ,\n" + " {\n" + " materializations: [\n" + " {\n" + " table: 'm0',\n" + " view: 'm0v',\n" + " sql: " + builder.toJsonString(m) + ",\n" + " viewSchemaPath: " + builder.toJsonString(path) + " }\n" + " ],\n" + " type: 'custom',\n" + " name: 'hr',\n" + " factory: 'org.apache.calcite.adapter.java.ReflectiveSchema$Factory',\n" + " operand: {\n" + " class: 'org.apache.calcite.test.JdbcTest$HrSchema'\n" + " }\n" + " }\n" + " ]\n" + "}\n"; CalciteAssert.that().withModel(model).query(q).enableMaterializations(true) .explainMatches("", CONTAINS_M0).sameResultWithMaterializationsDisabled(); } } @Test public void testSingleMaterializationMultiUsage() { String q = "select *\n" + "from (select * from \"emps\" where \"empid\" < 300)\n" + "join (select * from \"emps\" where \"empid\" < 200) using (\"empid\")"; try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(JdbcTest.HR_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500") .query(q).enableMaterializations(true).explainMatches("", new Function<ResultSet, Void>() { public Void apply(ResultSet s) { try { final String actual = Util.toLinux(CalciteAssert.toString(s)); final String scan = "EnumerableTableScan(table=[[hr, m0]])"; assertTrue(actual + " should have had two occurrences of " + scan, StringUtils.countMatches(actual, scan) == 2); return null; } catch (SQLException e) { throw new RuntimeException(e); } } }).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 (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that().withMaterializations(JdbcTest.HR_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 testMaterializationOnJoinQuery() { final String q = "select *\n" + "from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" < 300 "; try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(JdbcTest.HR_MODEL, "m0", "select * from \"emps\" where \"empid\" < 500") .query(q).enableMaterializations(true).explainContains("EnumerableTableScan(table=[[hr, m0]])") .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 (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); CalciteAssert.that() .withMaterializations(JdbcTest.HR_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(); } } @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 (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { MaterializationService.setThreadLocal(); final List<List<List<String>>> substitutedNames = new ArrayList<>(); CalciteAssert.that() .withMaterializations(JdbcTest.HR_MODEL, "m0", "select * from \"emps\" where \"empid\" < 300", "m1", "select * from \"emps\" where \"empid\" < 600") .query(q).withHook(Hook.SUB, new Function<RelNode, Void>() { public Void apply(RelNode input) { substitutedNames.add(new TableNameVisitor().run(input)); return null; } }).enableMaterializations(true).sameResultWithMaterializationsDisabled(); Collections.sort(substitutedNames, CASE_INSENSITIVE_LIST_LIST_COMPARATOR); assertThat(substitutedNames, is(list3(expectedNames))); } } @Test public void testMaterializationSubstitution2() { 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", "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(); final List<List<List<String>>> substitutedNames = new ArrayList<>(); CalciteAssert.that() .withMaterializations(JdbcTest.HR_MODEL, "m0", "select * from \"emps\" where \"empid\" < 300", "m1", "select * from \"emps\" where \"empid\" < 600", "m2", "select * from \"m1\"") .query(q).withHook(Hook.SUB, new Function<RelNode, Void>() { public Void apply(RelNode input) { substitutedNames.add(new TableNameVisitor().run(input)); return null; } }).enableMaterializations(true).sameResultWithMaterializationsDisabled(); Collections.sort(substitutedNames, CASE_INSENSITIVE_LIST_LIST_COMPARATOR); assertThat(substitutedNames, is(list3(expectedNames))); } } private static <E> List<List<List<E>>> list3(E[][][] as) { final ImmutableList.Builder<List<List<E>>> builder = ImmutableList.builder(); for (E[][] a : as) { builder.add(list2(a)); } return builder.build(); } private static <E> List<List<E>> list2(E[][] as) { final ImmutableList.Builder<List<E>> builder = ImmutableList.builder(); for (E[] a : as) { builder.add(ImmutableList.copyOf(a)); } return builder.build(); } /** * Implementation of RelVisitor to extract substituted table names. */ private static class TableNameVisitor extends RelVisitor { private List<List<String>> names = new ArrayList<>(); List<List<String>> run(RelNode input) { go(input); return names; } @Override public void visit(RelNode node, int ordinal, RelNode parent) { if (node instanceof TableScan) { RelOptTable table = node.getTable(); List<String> qName = table.getQualifiedName(); names.add(qName); } super.visit(node, ordinal, parent); } } } // End MaterializationTest.java