xzh_dz created CALCITE-4384:
-------------------------------
Summary: Predicate can not satisfy the condition of materialized
recognition
Key: CALCITE-4384
URL: https://issues.apache.org/jira/browse/CALCITE-4384
Project: Calcite
Issue Type: Wish
Reporter: xzh_dz
In the following example, materialized views cannot contain predicate
conditions contained in queries and should not be materialized, but they are
materialized.
{code:java}
org.apache.calcite.test.MaterializedViewRelOptRulesTest#testJoinMaterialization10
// code placeholder
@Test void testJoinMaterialization10() {
sql("select \"depts\".\"deptno\", \"dependents\".\"empid\"\n"
+ "from \"depts\"\n"
+ "join \"dependents\" on (\"depts\".\"name\" =
\"dependents\".\"name\")\n"
+ "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n"
+ "where \"depts\".\"deptno\" > 30",
"select \"dependents\".\"empid\"\n"
+ "from \"depts\"\n"
+ "join \"dependents\" on (\"depts\".\"name\" =
\"dependents\".\"name\")\n"
+ "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n"
+ "where \"depts\".\"deptno\" > 10")
.withChecker(
resultContains("EnumerableUnion(all=[true])",
"EnumerableTableScan(table=[[hr, MV0]])",
"expr#5=[Sarg[(10..30]]], expr#6=[SEARCH($t0, $t5)]"))
.ok();
}
{code}
If we exchange their predicate conditions, they should be identified by
materialization.
{code:java}
// code placeholder
org.apache.calcite.test.MaterializedViewRelOptRulesTest#testJoinMaterialization10
@Test void testJoinMaterialization10() {
sql("select \"depts\".\"deptno\", \"dependents\".\"empid\"\n"
+ "from \"depts\"\n"
+ "join \"dependents\" on (\"depts\".\"name\" =
\"dependents\".\"name\")\n"
+ "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n"
+ "where \"depts\".\"deptno\" > 10",
"select \"dependents\".\"empid\"\n"
+ "from \"depts\"\n"
+ "join \"dependents\" on (\"depts\".\"name\" =
\"dependents\".\"name\")\n"
+ "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n"
+ "where \"depts\".\"deptno\" > 30")
.withChecker(
resultContains("EnumerableCalc(expr#0..1=[{inputs}], expr#2=[30],
expr#3=[<($t2, $t0)"
+ "], empid=[$t1], $condition=[$t3])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])"))
.ok();
}
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
