Hi David, If I am not mistaken, the LogicalCorrelate that "appears" in 1.26.0 is an issue with RelDecorrelator (that cannot decorrelate certain plans with Sort + fetch) introduced by [1]. This problem should be fixed in the next release 1.27.0 via [2] and [3].
Best regards, Ruben [1] https://issues.apache.org/jira/browse/CALCITE-4206 [2] https://issues.apache.org/jira/browse/CALCITE-4333 [3] https://issues.apache.org/jira/browse/CALCITE-4437 On Tue, Mar 23, 2021 at 10:54 PM David Susanibar <[email protected]> wrote: > Hi Team, > > I am working to upgrade my calcite dependency from 1.23.0 to 1.26.0 > > For all my upgrades I use TPCH Query to test the result plan to validate if > we would have some problems at the future > > Please if you could help me to understand LogicalCorrelate that appear on > the plan for calcite 1.26.0 and did not appear on 1.23.0 > > TPCH-02 = "select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, > s_phone, s_comment from part, supplier, partsupp, nation, region where > p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 15 and > p_type like '%BRASS' and s_nationkey = n_nationkey and n_regionkey = > r_regionkey and r_name = 'EUROPE' and ps_supplycost = ( select > min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey > = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and > n_regionkey = r_regionkey and r_name = 'EUROPE' ) order by s_acctbal desc, > n_name, s_name, p_partkey limit 100" > > These are the differences in out calcite plan for 1.23.0 versus 1.26.0 > (LogicalCorrelate appear): > > return planner.rel(validatedSqlNode).project(); > > > *Results*: > > > *apache calcite 1.24.0 non optimized (using rules configured by > default on calcite)* > LogicalSort(sort0=[$0], sort1=[$2], sort2=[$1], sort3=[$3], > dir0=[DESC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) > LogicalProject(s_acctbal=[$14], s_name=[$10], n_name=[$22], > p_partkey=[$0], p_mfgr=[$2], s_address=[$11], s_phone=[$13], > s_comment=[$15]) > LogicalJoin(condition=[AND(=($0, $28), =($19, $29))], joinType=[inner]) > LogicalJoin(condition=[=($23, $25)], joinType=[inner]) > LogicalJoin(condition=[=($12, $21)], joinType=[inner]) > LogicalJoin(condition=[AND(=($0, $16), =($9, $17))], > joinType=[inner]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalFilter(condition=[AND(=($5, 15), LIKE($4, '%BRASS'))]) > LogicalTableScan(table=[[main, part]]) > LogicalTableScan(table=[[main, supplier]]) > LogicalTableScan(table=[[main, partsupp]]) > LogicalTableScan(table=[[main, nation]]) > LogicalFilter(condition=[=($1, 'EUROPE')]) > LogicalTableScan(table=[[main, region]]) > LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)]) > LogicalProject(ps_partkey=[$0], ps_supplycost=[$3]) > LogicalJoin(condition=[=($14, $16)], joinType=[inner]) > LogicalJoin(condition=[=($8, $12)], joinType=[inner]) > LogicalJoin(condition=[=($5, $1)], joinType=[inner]) > LogicalFilter(condition=[IS NOT NULL($0)]) > LogicalTableScan(table=[[main, partsupp]]) > LogicalTableScan(table=[[main, supplier]]) > LogicalTableScan(table=[[main, nation]]) > LogicalFilter(condition=[=($1, 'EUROPE')]) > LogicalTableScan(table=[[main, region]]) > > > > *apache calcite 1.26.0 non optimized (using rules configured by > default on calcite)* > LogicalSort(sort0=[$0], sort1=[$2], sort2=[$1], sort3=[$3], > dir0=[DESC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) > LogicalProject(s_acctbal=[$14], s_name=[$10], n_name=[$22], > p_partkey=[$0], p_mfgr=[$2], s_address=[$11], s_phone=[$13], > s_comment=[$15]) > LogicalProject(p_partkey=[$0], p_name=[$1], p_mfgr=[$2], > p_brand=[$3], p_type=[$4], p_size=[$5], p_container=[$6], > p_retailprice=[$7], p_comment=[$8], s_suppkey=[$9], s_name=[$10], > s_address=[$11], s_nationkey=[$12], s_phone=[$13], s_acctbal=[$14], > s_comment=[$15], ps_partkey=[$16], ps_suppkey=[$17], > ps_availqty=[$18], ps_supplycost=[$19], ps_comment=[$20], > n_nationkey=[$21], n_name=[$22], n_regionkey=[$23], n_comment=[$24], > r_regionkey=[$25], r_name=[$26], r_comment=[$27], EXPR$0=[$28]) > LogicalFilter(condition=[=($19, $28)]) > LogicalCorrelate(correlation=[$cor0], joinType=[left], > requiredColumns=[{0}]) ******* NEW rule appear! ******* > LogicalFilter(condition=[AND(=($0, $16), =($9, $17), =($5, > 15), LIKE($4, '%BRASS'), =($12, $21), =($23, $25), =($26, 'EUROPE'))]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[main, part]]) > LogicalTableScan(table=[[main, supplier]]) > LogicalTableScan(table=[[main, partsupp]]) > LogicalTableScan(table=[[main, nation]]) > LogicalTableScan(table=[[main, region]]) > LogicalAggregate(group=[{}], EXPR$0=[MIN($0)]) > LogicalProject(ps_supplycost=[$3]) > LogicalJoin(condition=[=($14, $16)], joinType=[inner]) > LogicalJoin(condition=[=($8, $12)], joinType=[inner]) > LogicalJoin(condition=[=($5, $1)], joinType=[inner]) > LogicalFilter(condition=[=($cor0.p_partkey, $0)]) > LogicalTableScan(table=[[main, partsupp]]) > LogicalTableScan(table=[[main, supplier]]) > LogicalTableScan(table=[[main, nation]]) > LogicalFilter(condition=[=($1, 'EUROPE')]) > LogicalTableScan(table=[[main, region]]) > > > Please your support on these questions: > > > 1. In what calcite version LogicalCorrelate rule was configured by > default? > 2. Is there some way to remove the LogicalCorrelate rule applied? > 3. If I am not be able to remove the LogicalCorrelate rule: Is there > some kind of configuration / rule that I could use to push down > logicalfilter and logicalproject inside the logicaltablescan thru > logicacorrelate? > > > Thank you in advance for your support. > > David >
