[jira] [Commented] (CALCITE-3537) Can't apply materialized view with a simple filter

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

    [ https://issues.apache.org/jira/browse/CALCITE-3537?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16983591#comment-16983591 ] 

Jin Xing commented on CALCITE-3537:
-----------------------------------

Hi [~anha]

Did you run your tests on MaterializedViewSubstitutionVisitor ?

I ran your tests and succeeded on Case 1 and Case 2
{code:java}
MaterializationTest.java
// Case 1
@Test public void testMvPredicate1() {
  checkMaterialize(
      "select \"deptno\", \"name\", \"empid\", count(*) from \"emps\" group by  \"name\", \"deptno\", \"empid\" ",
      "select \"name\", count(*) from \"emps\" where \"deptno\"= 10  group by \"name\" ");
}
// Case 2
@Test public void testMvPredicate2() {
  checkMaterialize(
      "select \"deptno\", \"name\", \"empid\", count(*) from \"emps\" group by  \"name\", \"deptno\", \"empid\" ",
      "select \"name\", count(*) from \"emps\" where \"name\"= 'Sebastian' OR \"name\"= 'Peter'  group by \"name\" ");
}

{code}
I failed with Case 3 
{code:java}
// Case 3
@Test public void testMvPredicate3() {
  checkMaterialize(
      "select "deptno\", \"name\", \"empid\", count(*) from \"emps\" group by  \"name\", \"deptno\", \"empid\" ",
      "select \"name\", count(*) from \"emps\" where \"name\"= 'Sebastian' group by \"name\" ");
}

MV:
LogicalCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], EXPR$2=[$t3])
  LogicalAggregate(group=[{0, 1, 2}], EXPR$2=[COUNT()])
    LogicalCalc(expr#0..4=[{inputs}], name=[$t2], deptno=[$t1], empid=[$t0])
      EnumerableTableScan(table=[[hr, emps]])

Query:
LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
  LogicalCalc(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR], expr#6=['Sebastian':VARCHAR], expr#7=[=($t5, $t6)], name=[$t2], $condition=[$t7])
    EnumerableTableScan(table=[[hr, emps]]){code}
The reason is that the top LogicalCalc is not taken into consideration by current implementation of SubstitutionVisitor;

If you modify the materialize sql to below, you can pass the tests
{code:java}
 "select \"name\", \"deptno\", \"empid\", count(*) from \"emps\" group by \"name\", \"deptno\", \"empid\" "
{code}

> Can't apply materialized view with a simple filter
> --------------------------------------------------
>
>                 Key: CALCITE-3537
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3537
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.21.0
>            Reporter: Anton Haidai
>            Priority: Major
>
> The issue is reproducible in MaterializationTest:
> {code}
>   @Test public void testMvPredicate() {
>       CalciteAssert.that()
>               .withMaterializations(
>                       HR_FKUK_MODEL,
>                       "MyMv",
>                       "select \"deptno\", \"name\", \"empid\", count(*) from \"emps\" group by  \"name\", \"deptno\", \"empid\" "
>               )
>               .query([INSERT SQL HERE])
>               .enableMaterializations(true)
>               .explainContains("MyMv")
>               .sameResultWithMaterializationsDisabled();
>   }
> {code}
> "[INSERT SQL HERE]" can be substituted by the following SQLs:
> *Case 1: simple filter over a non-group field.*
> Result: Success.
> {code}
> "select \"name\", count(*) from \"emps\" where \"deptno\"= 10  group by \"name\" "
> {code}
> *Case 2: complex filter over a group field.*
> Result: Success.
> {code}
> "select \"name\", count(*) from \"emps\" where \"name\"= 'Sebastian' OR \"name\"= 'Peter'  group by \"name\" "
> {code}
> *Case 3: simple filter over a group field*
> Result: Fail
> {code}
> "select \"name\", count(*) from \"emps\" where \"name\"= 'Sebastian' group by \"name\" "
> {code}
> The simple filtering condition makes the materialized view unusable while it could be used just like in the *Case 2*:
> {code}
> java.lang.AssertionError: 
> Expected: a string containing "MyMv"
>      but: was "PLAN=EnumerableAggregate(group=[{2}], EXPR$1=[COUNT()])\n  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR], expr#6=['Sebastian':VARCHAR], expr#7=[=($t5, $t6)], proj#0..4=[{exprs}], $condition=[$t7])\n    EnumerableTableScan(table=[[hr, emps]])\n\n"
> {code}
> Looks like something is wrong with "compensationPreds" calculation in AbstractMaterializedViewRule.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)