[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

2020-04-19 Thread Haisheng Yuan (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087324#comment-17087324
 ] 

Haisheng Yuan commented on CALCITE-3918:


Sorry, my fault. Haven't read this piece of code for a long time. Will close 
this issue.

> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
> 
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),  
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
>   EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
>   EnumerableCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>   EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], 
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
>   EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), 
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class 
> java.lang.Long))])
>   EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], 
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
>   EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

2020-04-19 Thread Vineet Garg (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087319#comment-17087319
 ] 

Vineet Garg commented on CALCITE-3918:
--

SubQueryFilterRemoveRule can not decorrelate the plan. It basically removes 
subquery (RexSubQuery node) by rewriting queries into join and it may introduce 
Correlate expressions if there is any correlation in the query. RelDecorrelator 
is then suppose to be used to remove this correlation.

> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
> 
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),  
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
>   EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
>   EnumerableCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>   EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], 
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
>   EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), 
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class 
> java.lang.Long))])
>   EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], 
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
>   EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

2020-04-19 Thread Haisheng Yuan (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087318#comment-17087318
 ] 

Haisheng Yuan commented on CALCITE-3918:


I am expecting SubQueryFilterRemoveRule can decorrelate the plan. Am I getting 
it wrong?

> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
> 
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),  
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
>   EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
>   EnumerableCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>   EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], 
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
>   EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), 
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class 
> java.lang.Long))])
>   EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], 
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
>   EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

2020-04-19 Thread Vineet Garg (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087316#comment-17087316
 ] 

Vineet Garg commented on CALCITE-3918:
--

Thanks [~hyuan]. I am confused about this. If you disable RelDecorrelator how 
can you expect to remove Correlate expressions from the plan? What are the 
expectations here?

> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
> 
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),  
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
>   EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
>   EnumerableCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>   EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], 
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
>   EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), 
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class 
> java.lang.Long))])
>   EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], 
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
>   EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

2020-04-19 Thread Haisheng Yuan (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087315#comment-17087315
 ] 

Haisheng Yuan commented on CALCITE-3918:


Modify the code and return the relnode directly without decorrelating in 
RelDecorrelator.

> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
> 
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),  
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
>   EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
>   EnumerableCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>   EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], 
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
>   EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), 
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class 
> java.lang.Long))])
>   EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], 
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
>   EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

2020-04-19 Thread Vineet Garg (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087313#comment-17087313
 ] 

Vineet Garg commented on CALCITE-3918:
--

[~hyuan] How do I disable the RelDecorrelator for this test?

> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
> 
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),  
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
>   EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
>   EnumerableCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>   EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], 
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
>   EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), 
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class 
> java.lang.Long))])
>   EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], 
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
>   EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)