You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Anton Haidai (Jira)" <ji...@apache.org> on 2019/11/26 18:03:00 UTC
[jira] [Updated] (CALCITE-3537) Can't apply materialized view with
a simple filter
[ https://issues.apache.org/jira/browse/CALCITE-3537?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Anton Haidai updated CALCITE-3537:
----------------------------------
Description:
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.
was:
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 group field.*
Result: Success.
{code}
"select \"name\", count(*) from \"emps\" where \"name\"= 'Sebastian' OR \"name\"= 'Peter' group by \"name\" "
{code}
*Case 3: complex filter on 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.
> 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)