| /* |
| * 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.adapter.java.ReflectiveSchema; |
| import org.apache.calcite.materialize.MaterializationService; |
| import org.apache.calcite.plan.RelOptTable; |
| 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.runtime.Hook; |
| import org.apache.calcite.schema.QueryableTable; |
| import org.apache.calcite.schema.TranslatableTable; |
| import org.apache.calcite.test.schemata.hr.Department; |
| import org.apache.calcite.test.schemata.hr.DepartmentPlus; |
| import org.apache.calcite.test.schemata.hr.Dependent; |
| import org.apache.calcite.test.schemata.hr.Employee; |
| import org.apache.calcite.test.schemata.hr.Event; |
| import org.apache.calcite.test.schemata.hr.Location; |
| 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.collect.ImmutableList; |
| import com.google.common.collect.Ordering; |
| |
| import org.checkerframework.checker.nullness.qual.Nullable; |
| import org.junit.jupiter.api.Disabled; |
| import org.junit.jupiter.api.Tag; |
| import org.junit.jupiter.api.Test; |
| |
| import java.sql.ResultSet; |
| import java.sql.Timestamp; |
| import java.util.ArrayList; |
| import java.util.Arrays; |
| import java.util.Collections; |
| import java.util.List; |
| import java.util.Map; |
| import java.util.function.Consumer; |
| |
| import static org.hamcrest.CoreMatchers.is; |
| import static org.hamcrest.MatcherAssert.assertThat; |
| |
| /** |
| * Integration tests 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. |
| */ |
| @Tag("slow") |
| public class MaterializationTest { |
| private static final Consumer<ResultSet> CONTAINS_M0 = |
| CalciteAssert.checkResultContains( |
| "EnumerableTableScan(table=[[hr, m0]])"); |
| |
| private static final Consumer<ResultSet> 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(); |
| |
| 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" |
| + "}"; |
| |
| @Test 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 void testViewMaterialization() { |
| try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) { |
| MaterializationService.setThreadLocal(); |
| String materialize = "select \"depts\".\"name\"\n" |
| + "from \"depts\"\n" |
| + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")"; |
| String query = "select \"depts\".\"name\"\n" |
| + "from \"depts\"\n" |
| + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")"; |
| |
| CalciteAssert.that() |
| .withMaterializations(HR_FKUK_MODEL, true, "matview", materialize) |
| .query(query) |
| .enableMaterializations(true) |
| .explainMatches( |
| "", CalciteAssert.checkResultContains( |
| "EnumerableValues(tuples=[[{ 'noname' }]])")).returnsValue("noname"); |
| } |
| } |
| |
| @Test 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 case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-761">[CALCITE-761] |
| * Pre-populated materializations</a>. */ |
| @Test void testPrePopulated() { |
| String q = "select distinct \"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 distinct `deptno` as `empid`, '' as `name`\n" |
| + "from `emps`"; |
| final String sql2 = sql.replace("`", "\""); |
| map.put("sql", sql2); |
| return ImmutableList.of(map); |
| }) |
| .query(q) |
| .enableMaterializations(true) |
| .sameResultWithMaterializationsDisabled(); |
| } |
| } |
| |
| @Test void testViewSchemaPath() { |
| try (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 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(); |
| } |
| } |
| |
| @Disabled("Creating mv for depts considering all its column throws exception") |
| @Test 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(); |
| } |
| } |
| |
| @Test 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 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 (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", |
| "m2", "select * from \"m1\"") |
| .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))); |
| } |
| } |
| |
| 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, @Nullable RelNode parent) { |
| if (node instanceof TableScan) { |
| RelOptTable table = node.getTable(); |
| List<String> qName = table.getQualifiedName(); |
| names.add(qName); |
| } |
| 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", 10000, 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", ImmutableList.of(), |
| new Location(0, 52)), |
| new Department(20, "HR", Collections.singletonList(emps[1]), null), |
| }; |
| public final DepartmentPlus[] depts2 = { |
| new DepartmentPlus(10, "Sales", Arrays.asList(emps[0], emps[2], emps[3]), |
| new Location(-122, 38), new Timestamp(0)), |
| new DepartmentPlus(30, "Marketing", ImmutableList.of(), |
| new Location(0, 52), new Timestamp(0)), |
| new DepartmentPlus(20, "HR", Collections.singletonList(emps[1]), |
| null, new Timestamp(0)), |
| }; |
| 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 Event[] events = { |
| new Event(100, new Timestamp(0)), |
| new Event(200, new Timestamp(0)), |
| new Event(150, new Timestamp(0)), |
| new Event(110, null), |
| }; |
| |
| 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); |
| } |
| |
| public TranslatableTable matview() { |
| return Smalls.strView("noname"); |
| } |
| } |
| } |
