[
https://issues.apache.org/jira/browse/CALCITE-5002?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xurenhe updated CALCITE-5002:
-----------------------------
Description:
Here, it's a test case in the
`org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest`
{code:java}
/**
* It's need be matched, unused project on aggregate in the mv, and query's
rel-root is aggregate.
*/
@Test void testAggregate7() {
String mv = ""
+ "select \"deptno\", sum(\"salary\"), sum(\"commission\") + 1,
sum(\"k\")\n"
+ "from\n"
+ " (select \"deptno\", \"salary\", \"commission\", 100 as \"k\"\n"
+ " from \"emps\")\n"
+ "group by \"deptno\"";
String query = ""
+ "select \"deptno\", sum(\"salary\"), sum(\"k\")\n"
+ "from\n"
+ " (select \"deptno\", \"salary\", 100 as \"k\"\n"
+ " from \"emps\")\n"
+ "group by \"deptno\"";
sql(mv, query).ok();
} {code}
{code:java}
Materialized view failed to be matched by optimized
results:java.lang.AssertionError: Materialized view failed to be matched by
optimized results: at
org.apache.calcite.test.MaterializedViewTester.checkMaterialize(MaterializedViewTester.java:85)
at
org.apache.calcite.test.MaterializedViewFixture.ok(MaterializedViewFixture.java:56)
at
org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest.testAggregate7(MaterializedViewSubstitutionVisitorTest.java:468){code}
!image-2022-02-08-11-50-58-564.png|width=690,height=281!
– query:
{code:java}
Holder
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], k=[$t5]))
Scan(table: [hr, emps])
{code}
– mv:
{code:java}
Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3]))
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2), SUM($3)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps])
{code}
After executing rule of 'AggregateOnCalcToAggregateUnifyRule', it is rewritten
correctly.
{code:java}
-- origin query rel's fragment
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
Calc(program: (expr#0..3=[{inputs}], proj#0..1=[{exprs}], k=[$t3]))
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps])
-- equivalent rewrite
Calc(program: (expr#0..3=[{inputs}], expr#4=[true], proj#0..1=[{exprs}],
EXPR$2=[$t3], $condition=[$t4]))
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2), SUM($3)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps]){code}
But, There is one operator left in the mv, as below
{code:java}
Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3])) {code}
All rels of mv failed to become the part of query's plan, so mv-match failed.
was:
Here, it's a test case in the
`org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest`
{code:java}
/**
* It's need be matched, unused project on aggregate in the mv, and query's
rel-root is aggregate.
*/
@Test void testAggregate7() {
String mv = ""
+ "select \"deptno\", sum(\"salary\"), sum(\"commission\") + 1,
sum(\"k\")\n"
+ "from\n"
+ " (select \"deptno\", \"salary\", \"commission\", 100 as \"k\"\n"
+ " from \"emps\")\n"
+ "group by \"deptno\"";
String query = ""
+ "select \"deptno\", sum(\"salary\"), sum(\"k\")\n"
+ "from\n"
+ " (select \"deptno\", \"salary\", 100 as \"k\"\n"
+ " from \"emps\")\n"
+ "group by \"deptno\"";
sql(mv, query).ok();
} {code}
{code:java}
Materialized view failed to be matched by optimized
results:java.lang.AssertionError: Materialized view failed to be matched by
optimized results: at
org.apache.calcite.test.MaterializedViewTester.checkMaterialize(MaterializedViewTester.java:85)
at
org.apache.calcite.test.MaterializedViewFixture.ok(MaterializedViewFixture.java:56)
at
org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest.testAggregate7(MaterializedViewSubstitutionVisitorTest.java:468){code}
!image-2022-02-08-11-50-58-564.png!
– query:
{code:java}
Holder
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], k=[$t5]))
Scan(table: [hr, emps])
{code}
– mv:
{code:java}
Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3]))
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2), SUM($3)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps])
{code}
After executing rule of 'AggregateOnCalcToAggregateUnifyRule', it is rewritten
correctly.
{code:java}
-- origin query rel's fragment
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
Calc(program: (expr#0..3=[{inputs}], proj#0..1=[{exprs}], k=[$t3]))
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps])
-- equivalent rewrite
Calc(program: (expr#0..3=[{inputs}], expr#4=[true], proj#0..1=[{exprs}],
EXPR$2=[$t3], $condition=[$t4]))
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2), SUM($3)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps]){code}
But, There is one operator left in the mv, as below
{code:java}
Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3])) {code}
All rels of mv failed to become the part of query's plan, so mv-match failed.
> SubstitutionVisitor exec mv-match fail, cause by `unused project on aggregate
> in the mv`
> ----------------------------------------------------------------------------------------
>
> Key: CALCITE-5002
> URL: https://issues.apache.org/jira/browse/CALCITE-5002
> Project: Calcite
> Issue Type: Bug
> Reporter: Xurenhe
> Assignee: Xurenhe
> Priority: Major
> Attachments: image-2022-02-08-11-50-58-564.png
>
>
> Here, it's a test case in the
> `org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest`
> {code:java}
> /**
> * It's need be matched, unused project on aggregate in the mv, and query's
> rel-root is aggregate.
> */
> @Test void testAggregate7() {
> String mv = ""
> + "select \"deptno\", sum(\"salary\"), sum(\"commission\") + 1,
> sum(\"k\")\n"
> + "from\n"
> + " (select \"deptno\", \"salary\", \"commission\", 100 as \"k\"\n"
> + " from \"emps\")\n"
> + "group by \"deptno\"";
> String query = ""
> + "select \"deptno\", sum(\"salary\"), sum(\"k\")\n"
> + "from\n"
> + " (select \"deptno\", \"salary\", 100 as \"k\"\n"
> + " from \"emps\")\n"
> + "group by \"deptno\"";
> sql(mv, query).ok();
> } {code}
>
> {code:java}
> Materialized view failed to be matched by optimized
> results:java.lang.AssertionError: Materialized view failed to be matched by
> optimized results: at
> org.apache.calcite.test.MaterializedViewTester.checkMaterialize(MaterializedViewTester.java:85)
> at
> org.apache.calcite.test.MaterializedViewFixture.ok(MaterializedViewFixture.java:56)
> at
> org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest.testAggregate7(MaterializedViewSubstitutionVisitorTest.java:468){code}
>
> !image-2022-02-08-11-50-58-564.png|width=690,height=281!
> – query:
> {code:java}
> Holder
> Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
> Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
> salary=[$t3], k=[$t5]))
> Scan(table: [hr, emps])
> {code}
> – mv:
> {code:java}
> Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
> proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3]))
> Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2),
> SUM($3)])
> Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
> salary=[$t3], commission=[$t4], k=[$t5]))
> Scan(table: [hr, emps])
> {code}
> After executing rule of 'AggregateOnCalcToAggregateUnifyRule', it is
> rewritten correctly.
> {code:java}
> -- origin query rel's fragment
> Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
> Calc(program: (expr#0..3=[{inputs}], proj#0..1=[{exprs}], k=[$t3]))
> Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
> salary=[$t3], commission=[$t4], k=[$t5]))
> Scan(table: [hr, emps])
> -- equivalent rewrite
> Calc(program: (expr#0..3=[{inputs}], expr#4=[true], proj#0..1=[{exprs}],
> EXPR$2=[$t3], $condition=[$t4]))
> Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2),
> SUM($3)])
> Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
> salary=[$t3], commission=[$t4], k=[$t5]))
> Scan(table: [hr, emps]){code}
>
> But, There is one operator left in the mv, as below
> {code:java}
> Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
> proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3])) {code}
> All rels of mv failed to become the part of query's plan, so mv-match failed.
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
