| /* |
| // Licensed to Julian Hyde under one or more contributor license |
| // agreements. See the NOTICE file distributed with this work for |
| // additional information regarding copyright ownership. |
| // |
| // Julian Hyde 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 net.hydromatic.optiq.test; |
| |
| import net.hydromatic.linq4j.function.Function1; |
| |
| import net.hydromatic.optiq.jdbc.JavaTypeFactoryImpl; |
| import net.hydromatic.optiq.materialize.MaterializationService; |
| import net.hydromatic.optiq.prepare.Prepare; |
| |
| import org.eigenbase.relopt.SubstitutionVisitor; |
| import org.eigenbase.reltype.RelDataType; |
| import org.eigenbase.rex.*; |
| import org.eigenbase.sql.fun.SqlStdOperatorTable; |
| |
| import org.junit.Ignore; |
| import org.junit.Test; |
| |
| import java.math.BigDecimal; |
| import java.sql.ResultSet; |
| |
| import static org.hamcrest.CoreMatchers.*; |
| import static org.junit.Assert.*; |
| |
| /** |
| * 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 Function1<ResultSet, Void> CONTAINS_M0 = |
| OptiqAssert.checkResultContains( |
| "EnumerableTableAccessRel(table=[[hr, m0]])"); |
| |
| final JavaTypeFactoryImpl typeFactory = new JavaTypeFactoryImpl(); |
| final RexBuilder rexBuilder = new RexBuilder(typeFactory); |
| |
| @Test public void testFilter() { |
| OptiqAssert.that() |
| .with(OptiqAssert.Config.REGULAR) |
| .withMaterializations( |
| JdbcTest.HR_MODEL, |
| "m0", |
| "select * from \"emps\" where \"deptno\" = 10") |
| .query( |
| "select \"empid\" + 1 from \"emps\" where \"deptno\" = 10") |
| .enableMaterializations(true) |
| .explainContains( |
| "EnumerableTableAccessRel(table=[[hr, m0]])") |
| .sameResultWithMaterializationsDisabled(); |
| } |
| |
| @Test public void testFilterQueryOnProjectView() { |
| try { |
| Prepare.THREAD_TRIM.set(true); |
| MaterializationService.setThreadLocal(); |
| OptiqAssert.that() |
| .with(OptiqAssert.Config.REGULAR) |
| .withMaterializations( |
| JdbcTest.HR_MODEL, |
| "m0", |
| "select \"deptno\", \"empid\" from \"emps\"") |
| .query( |
| "select \"empid\" + 1 as x from \"emps\" where \"deptno\" = 10") |
| .enableMaterializations(true) |
| .explainContains( |
| "EnumerableTableAccessRel(table=[[hr, m0]])") |
| .sameResultWithMaterializationsDisabled(); |
| } finally { |
| Prepare.THREAD_TRIM.set(false); |
| } |
| } |
| |
| /** 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, |
| Function1<ResultSet, Void> checker) { |
| try { |
| Prepare.THREAD_TRIM.set(true); |
| MaterializationService.setThreadLocal(); |
| OptiqAssert.that() |
| .with(OptiqAssert.Config.REGULAR) |
| .withMaterializations(model, "m0", materialize) |
| .query(query) |
| .enableMaterializations(true) |
| .explainMatches(checker) |
| .sameResultWithMaterializationsDisabled(); |
| } finally { |
| Prepare.THREAD_TRIM.set(false); |
| } |
| } |
| |
| /** 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 { |
| Prepare.THREAD_TRIM.set(true); |
| MaterializationService.setThreadLocal(); |
| OptiqAssert.that() |
| .with(OptiqAssert.Config.REGULAR) |
| .withMaterializations(model, "m0", materialize) |
| .query(query) |
| .enableMaterializations(true) |
| .explainContains( |
| "EnumerableTableAccessRel(table=[[hr, emps]])"); |
| } finally { |
| Prepare.THREAD_TRIM.set(false); |
| } |
| } |
| |
| /** 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"); |
| } |
| |
| /** As {@link #testFilterQueryOnProjectView()} but materialized view contains |
| * an expression and query. */ |
| @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. */ |
| @Ignore("fix project expr on filter - plans, but wrong results") |
| @Test public void testFilterQueryOnProjectView5() { |
| checkMaterialize( |
| "select \"deptno\" - 10 as \"x\", \"empid\" + 1, \"name\" from \"emps\"", |
| "select \"name\", \"empid\" + 1 from \"emps\" where \"deptno\" - 10 = 0"); |
| } |
| |
| /** As {@link #testFilterQueryOnProjectView3()} but also contains an |
| * expression column. */ |
| @Ignore("fix project expr on filter") |
| @Test public void testFilterQueryOnProjectView6() { |
| 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 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, |
| OptiqAssert.checkResultContains( |
| "EnumerableCalcRel(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t1, $t3)], X=[$t4], name=[$t2], condition=?)\n" |
| + " EnumerableTableAccessRel(table=[[hr, m0]])")); |
| } |
| |
| /** 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 AggregateRel to roll up. */ |
| @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, |
| OptiqAssert.checkResultContains( |
| "EnumerableCalcRel(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1, $t2)], C=[$t3], deptno=[$t0])\n" |
| + " EnumerableAggregateRel(group=[{1}], agg#0=[COUNT($1)])\n" |
| + " EnumerableTableAccessRel(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, |
| OptiqAssert.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 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.eigenbase.relopt.SubstitutionVisitor#mayBeSatisfiable} and |
| * {@link org.eigenbase.relopt.SubstitutionVisitor#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 = SubstitutionVisitor.simplify(rexBuilder, e); |
| assertFalse(RexLiteral.booleanValue(simple)); |
| } |
| |
| private void checkSatisfiable(RexNode e, String s) { |
| assertTrue(SubstitutionVisitor.mayBeSatisfiable(e)); |
| final RexNode simple = SubstitutionVisitor.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\", 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); |
| } |
| } |
| |
| // End MaterializationTest.java |
