[
https://issues.apache.org/jira/browse/CALCITE-3124?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16979110#comment-16979110
]
Jin Xing edited comment on CALCITE-3124 at 11/21/19 9:09 AM:
-------------------------------------------------------------
Sql in testHavingNot2 and its corresponding plan are as below:
{code:java}
LogicalProject(EXPR$0=[1])
LogicalFilter(condition=[>=($1, 20000)])
------------------------------------> Filter0
LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
------------------------------------> Aggregate0
LogicalProject(store_street_address=[$5], grocery_sqft=[$16])
EnumerableTableScan(table=[[foodmart2, store]])
{code}
The infinite loop happens by below steps:
Step-1
Aggregate0 can be transformed to below equivalent plans and collect into
RelSubset-A
{code:java}
RelSubset-A:
1. Aggregate0
LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
LogicalProject(store_street_address=[$5], grocery_sqft=[$16])
EnumerableTableScan(table=[[foodmart2, store]])
2. Aggregate0-equiv0 (transformed by AggregateReduceFunctionsRule,
AggregateRemoveRule, ProjectMergeRule)
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2,
store]])
3. Aggregate0-equiv1 (transformed by AggregateProjectMergeRule,
AggregateRemoveRule)
LogicalProject(store_street_address=[$5], grocery_sqft=[$16])
EnumerableTableScan(table=[[foodmart2, store]])
{code}
Step-2
RelSubset-A is child node of Filter0 and FilterProjectTransposeRule will
transform Filter0 with Aggregate0-equiv0 as below:
{code:java}
From:
LogicalFilter(condition=[>=($1, 20000)])
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
EnumerableTableScan(table=[[foodmart2, store]])
To:
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
---------------------------------->Filter0-equiv
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($16), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($16, 0)), 20000)])
EnumerableTableScan(table=[[foodmart2, store]]){code}
Step-3
ProjectFilterTransposeRule will transform Filter0-equiv as below:
{code:java}
From:
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
---------------------------------->Filter0-equiv
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($16), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($16, 0)), 20000)])
EnumerableTableScan(table=[[foodmart2, store]])
To:
LogicalProject(store_street_address=[$0], $f1=[CASE(=(CASE(IS NOT NULL($1),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($1, 0))])
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($1), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($1, 0)), 20000)])
---------------------------------> FilterX
LogicalProject([$5], [$16]) -----------------------------------> ProjectX
EnumerableTableScan(table=[[foodmart2, store]]){code}
We will find ProjectX exactly exists in RelSubset-A as Aggregate0-equiv1, thus
FilterProjectTransposeRule will transform FilterX with Aggregate0-equiv0 as
below:
{code:java}
From:
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($1), 1:BIGINT, 0:BIGINT),
0), null:INTEGER, COALESCE($1, 0)), 20000)])
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2,
store]])
To:
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL(CASE(=(CASE(IS NOT
NULL($16), 1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($1, 0)), 20000)])
EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2,
store]]){code}
As we can see, a more complex LogicalFilter is generated and step-2 & step-3
alternate infinitely.
was (Author: jinxing6...@126.com):
Sql in testHavingNot2 and its corresponding plan are as below:
{code:java}
LogicalProject(EXPR$0=[1])
LogicalFilter(condition=[>=($1, 20000)])
------------------------------------> Filter0
LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
------------------------------------> Aggregate0
LogicalProject(store_street_address=[$5], grocery_sqft=[$16])
EnumerableTableScan(table=[[foodmart2, store]])
{code}
The infinite loop happens by below steps:
Step-1
Aggregate0 can be transformed to below equivalent plans and collect into
RelSubset-A
{code:java}
RelSubset-A:
1. Aggregate0
LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
LogicalProject(store_street_address=[$5], grocery_sqft=[$16])
EnumerableTableScan(table=[[foodmart2, store]])
2. Aggregate0-equiv0 (transformed by AggregateReduceFunctionsRule,
AggregateRemoveRule, ProjectMergeRule)
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2,
store]])
3. Aggregate0-equiv1 (transformed by AggregateProjectMergeRule,
AggregateRemoveRule)
LogicalProject(store_street_address=[$5], grocery_sqft=[$16])
EnumerableTableScan(table=[[foodmart2, store]])
{code}
Step-2
RelSubset-A is child node of Filter0 and FilterProjectTransposeRule will
transform Filter0 with Aggregate0-equiv0 as below:
{code:java}
From:
LogicalFilter(condition=[>=($1, 20000)])
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
EnumerableTableScan(table=[[foodmart2, store]])
To:
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
---------------------------------->Filter0-equiv
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($16), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($16, 0)), 20000)])
EnumerableTableScan(table=[[foodmart2, store]]){code}
Step-3
ProjectFilterTransposeRule will transform Filter0-equiv as below:
{code:java}
From:
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
---------------------------------->Filter0-equiv
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($16), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($16, 0)), 20000)])
EnumerableTableScan(table=[[foodmart2, store]])
To:
LogicalProject(store_street_address=[$0], $f1=[CASE(=(CASE(IS NOT NULL($1),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($1, 0))])
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($1), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($1, 0)), 20000)])
---------------------------------> FilterX
LogicalProject([$5], [$16]) -----------------------------------> ProjectX
EnumerableTableScan(table=[[foodmart2, store]]){code}
We will find ProjectX exactly exists in RelSubset-A as Aggregate0-equiv1, thus
FilterProjectTransposeRule will transform FilterX with Aggregate0-equiv0 as
below:
{code:java}
From:
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL($1), 1:BIGINT, 0:BIGINT),
0), null:INTEGER, COALESCE($1, 0)), 20000)])
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2,
store]])
To:
LogicalProject(store_street_address=[$5], $f1=[CASE(=(CASE(IS NOT NULL($16),
1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))])
LogicalFilter(condition=[>=($CASE(=(CASE(IS NOT NULL(CASE(=(CASE(IS NOT
NULL($16), 1:BIGINT, 0:BIGINT), 0), null:INTEGER, COALESCE($16, 0))), 1:BIGINT,
0:BIGINT), 0), null:INTEGER, COALESCE($1, 0)), 20000)])
EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2,
store]]){code}
As we can see, a more complex LogicalFilter is generated and step-2 & step-3
alternate infinitely.
> Infinite rule matching when AggregateRemoveRule is enabled for SUM0
> -------------------------------------------------------------------
>
> Key: CALCITE-3124
> URL: https://issues.apache.org/jira/browse/CALCITE-3124
> Project: Calcite
> Issue Type: Bug
> Reporter: Haisheng Yuan
> Assignee: Forward Xu
> Priority: Major
>
> Make the following changes (uncomment return clause) to AggregateRemoveRule,
> the test case {{JdbcTest.testHavingNot2}} won't complete due to infinite rule
> matching.
> {noformat}
> --- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
> +++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
> @@ -102,7 +102,7 @@ public void onMatch(RelOptRuleCall call) {
> if (aggregation.getKind() == SqlKind.SUM0) {
> // Bail out for SUM0 to avoid potential infinite rule matching,
> // because it may be generated by transforming SUM aggregate
> // function to SUM0 and COUNT.
> - return;
> +// return;
> }
> final SqlSplittableAggFunction splitter =
> Objects.requireNonNull(
> {noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
