[ https://issues.apache.org/jira/browse/CALCITE-3124?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16979110#comment-16979110 ]
Jin Xing commented on CALCITE-3124: ----------------------------------- 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)