[jira] [Created] (CALCITE-4384) Predicate can not satisfy the condition of materialized recognition

["xzh_dz (Jira)" <ji...@apache.org>]

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)