[
https://issues.apache.org/jira/browse/CALCITE-6193?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17804944#comment-17804944
]
Jiajun Xie edited comment on CALCITE-6193 at 1/10/24 2:22 AM:
--------------------------------------------------------------
The issue is not related to the filter condition.
The RelNode pattern is `BiRel(BiRel(any(), MatchRel0), MatchRel1)`.
MatchRel0 is same as MatchRel1, but MatchRel1 will not be replaced to MV0.
Here is simple case.
{code:java}
final String mv = ""
+ "select \"empid\", \"deptno\"\n"
+ "from \"emps\"\n"
+ "group by \"empid\", \"deptno\"";
final String matchQuery = "select \"deptno\"\n"
+ "from \"emps\"\n"
+ "group by \"deptno\"\n";
final String query = ""
+ "select t1.\"deptno\"\n"
+ "from (\n"
+ "select \"deptno\"\n"
+ "from \"emps\"\n"
+ "union all\n"
+ matchQuery
+ ") as t1 inner join (\n"
+ matchQuery
+ ") as t2 on t1.\"deptno\" = t2.\"deptno\"\n";
LogicalCalc(expr#0..1=[{inputs}], deptno=[$t0])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalUnion(all=[true])
LogicalCalc(expr#0..1=[{inputs}], deptno=[$t1])
LogicalCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}])
LogicalTableScan(table=[[hr, emps]])
LogicalAggregate(group=[{1}])
EnumerableTableScan(table=[[hr, MV0]])
LogicalAggregate(group=[{0}])
LogicalCalc(expr#0..4=[{inputs}], deptno=[$t1])
LogicalTableScan(table=[[hr, emps]]) -- not be replaced{code}
[~4wei] , Am I right?
was (Author: jiajunbernoulli):
The issue is not related to the filter condition.
The RelNode pattern is `BiRel(BiRel(any(), MatchRel0), MatchRel1)`.
MatchRel0 is same as MatchRel1, but MatchRel1 will not be replaced to MV0.
Here is simple case.
{code:java}
final String mv = ""
+ "select \"empid\", \"deptno\"\n"
+ "from \"emps\"\n"
+ "group by \"empid\", \"deptno\"";
final String matchQuery = "select \"deptno\"\n"
+ "from \"emps\"\n"
+ "group by \"deptno\"\n";
final String query = ""
+ "select t1.\"deptno\"\n"
+ "from (\n"
+ "select \"deptno\"\n"
+ "from \"emps\"\n"
+ "union all\n"
+ matchQuery
+ ") as t1 inner join (\n"
+ matchQuery
+ ") as t2 on t1.\"deptno\" = t2.\"deptno\"\n";
LogicalCalc(expr#0..1=[{inputs}], deptno=[$t0])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalUnion(all=[true])
LogicalCalc(expr#0..1=[{inputs}], deptno=[$t1])
LogicalCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}])
LogicalTableScan(table=[[hr, emps]])
LogicalAggregate(group=[{1}])
EnumerableTableScan(table=[[hr, MV0]])
LogicalAggregate(group=[{0}])
LogicalCalc(expr#0..4=[{inputs}], deptno=[$t1])
LogicalTableScan(table=[[hr, emps]]) -- not be replaced{code}
[~4wei] , Am I right?
> SubstitutionVisitor stop trying incorrect subtree because of wrong parent
> relationship when replace query
> ---------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6193
> URL: https://issues.apache.org/jira/browse/CALCITE-6193
> Project: Calcite
> Issue Type: Bug
> Reporter: Mou Wu
> Assignee: Mou Wu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> @Test void testStopTryIncorrectSubtree() {
> final String mv = ""
> + "select \"empid\", \"deptno\"\n"
> + "from \"emps\"\n"
> + "where \"salary\" > 1000\n"
> + "group by \"empid\", \"deptno\"";
> final String query = ""
> + "select t1.\"deptno\"\n"
> + "from (\n"
> + "select \"deptno\"\n"
> + "from \"emps\"\n"
> + "where \"salary\" > 1000\n"
> + "union all\n"
> + "select \"deptno\"\n"
> + "from \"emps\"\n"
> + "where \"salary\" > 1000\n"
> + "group by \"deptno\"\n"
> + ") as t1 inner join (\n"
> + "select \"deptno\"\n"
> + "from \"emps\"\n"
> + "where \"salary\" > 1000\n"
> + "group by \"deptno\"\n"
> + ") as t2 on t1.\"deptno\" = t2.\"deptno\"\n";
> sql(mv, query)
> .checkingThatResultContains(""
> + "LogicalCalc(expr#0..1=[{inputs}], deptno=[$t0])\n"
> + " LogicalJoin(condition=[=($0, $1)], joinType=[inner])\n"
> + " LogicalUnion(all=[true])\n"
> + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[1000],
> expr#6=[>($t3, $t5)], deptno=[$t1], $condition=[$t6])\n"
> + " LogicalTableScan(table=[[hr, emps]])\n"
> + " LogicalAggregate(group=[{1}])\n"
> + " EnumerableTableScan(table=[[hr, MV0]])\n"
> + " LogicalAggregate(group=[{1}])\n"
> + " EnumerableTableScan(table=[[hr, MV0]])"
> ).ok();
> }{code}
> The test case above will fail because the second mv0 not be matched.
> The root cause is that SubstitutionVisitor replace child nodes with
> targetDescendant node itself, not a deep-copy replica, so they may share the
> same node and the same parent node, thus the incorrect parent relationship
> may occur, it will make stopTrying be wrong.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
