[jira] [Comment Edited] (CALCITE-3124) Infinite rule matching when AggregateRemoveRule is enabled for SUM0
[ https://issues.apache.org/jira/browse/CALCITE-3124?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16979110#comment-16979110 ] Jin Xing edited comment on CALCITE-3124 at 12/4/19 11:44 AM: - Sql in testHavingNot2 and its corresponding plan are as below: {code:java} LogicalProject(EXPR$0=[1]) LogicalFilter(condition=[>=($1, 2)]) --> 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, 2)]) 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)), 2)]) 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)), 2)]) 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)), 2)]) -> 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)), 2)]) 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)), 2)]) EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2, store]]){code} As we can see, a more complex LogicalFilter is generated and then step-3 will happen again. And then step-2 & step-3 will 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, 2)]) > 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
[jira] [Comment Edited] (CALCITE-3124) Infinite rule matching when AggregateRemoveRule is enabled for SUM0
[ https://issues.apache.org/jira/browse/CALCITE-3124?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16979110#comment-16979110 ] Jin Xing edited comment on CALCITE-3124 at 11/21/19 9:17 AM: - Sql in testHavingNot2 and its corresponding plan are as below: {code:java} LogicalProject(EXPR$0=[1]) LogicalFilter(condition=[>=($1, 2)]) > 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, 2)]) 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)), 2)]) 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)), 2)]) 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)), 2)]) -> 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)), 2)]) 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)), 2)]) EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2, store]]){code} As we can see, a more complex LogicalFilter is generated and then step-3 will happen again. And then step-2 & step-3 will 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, 2)]) > 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
[jira] [Comment Edited] (CALCITE-3124) Infinite rule matching when AggregateRemoveRule is enabled for SUM0
[ https://issues.apache.org/jira/browse/CALCITE-3124?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16979110#comment-16979110 ] Jin Xing edited comment on CALCITE-3124 at 11/21/19 9:17 AM: - Sql in testHavingNot2 and its corresponding plan are as below: {code:java} LogicalProject(EXPR$0=[1]) LogicalFilter(condition=[>=($1, 2)]) > 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, 2)]) 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)), 2)]) 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)), 2)]) 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)), 2)]) -> 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)), 2)]) 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)), 2)]) EnumerableTableScan(subset=[rel#62:Subset#0.NONE.[]], table=[[foodmart2, store]]){code} As we can see, a more complex LogicalFilter is generated and then step-3 will happen again. And then step-2 & step-3 will 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, 2)]) > 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
[jira] [Comment Edited] (CALCITE-3124) Infinite rule matching when AggregateRemoveRule is enabled for SUM0
[ https://issues.apache.org/jira/browse/CALCITE-3124?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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, 2)]) > 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, 2)]) 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)), 2)]) 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)), 2)]) 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)), 2)]) -> 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)), 2)]) 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)), 2)]) 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, 2)]) > 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