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
