[
https://issues.apache.org/jira/browse/CALCITE-5837?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
LakeShen updated CALCITE-5837:
------------------------------
Description:
In my rule, I used the RexUtil.pullFactors to simplify the Filter's condition
and I tested the rule with TPC-H q19.sql.
When I print the plan tree with RelOptUtil.toString,sometimes the plan is :
{code:java}
LogicalAggregate(group=[{}], revenue=[SUM($0)])
LogicalProject($f0=[*($5, -(1, $6))])
LogicalFilter(condition=[AND(=($16, $1), SEARCH($14, Sarg['AIR':CHAR(7),
'AIR REG']:CHAR(7)), =($13, 'DELIVER IN PERSON'), OR(AND(=($19, 'Brand#12'),
SEARCH($22, Sarg['SM BOX':CHAR(7), 'SM CASE', 'SM PACK', 'SM
PKG':CHAR(7)]:CHAR(7)), >=($4, 1), <=($4, +(1, 10)), SEARCH($21,
Sarg[[1..5]])), AND(=($19, 'Brand#23'), SEARCH($22, Sarg['MED BAG':CHAR(8),
'MED BOX':CHAR(8), 'MED PACK', 'MED PKG':CHAR(8)]:CHAR(8)), >=($4, 10), <=($4,
+(10, 10)), SEARCH($21, Sarg[[1..10]])), AND(=($19, 'Brand#34'), SEARCH($22,
Sarg['LG BOX':CHAR(7), 'LG CASE', 'LG PACK', 'LG PKG':CHAR(7)]:CHAR(7)), >=($4,
20), <=($4, +(20, 10)), SEARCH($21, Sarg[[1..15]]))))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[tpch, LINEITEM]])
LogicalTableScan(table=[[tpch, PART]])
{code}
and sometimes the plan is :
{code:java}
LogicalAggregate(group=[{}], revenue=[SUM($0)])
LogicalProject($f0=[*($5, -(1, $6))])
LogicalFilter(condition=[AND(=($16, $1), =($13, 'DELIVER IN PERSON'),
SEARCH($14, Sarg['AIR':CHAR(7), 'AIR REG']:CHAR(7)), OR(AND(=($19, 'Brand#12'),
SEARCH($22, Sarg['SM BOX':CHAR(7), 'SM CASE', 'SM PACK', 'SM
PKG':CHAR(7)]:CHAR(7)), >=($4, 1), <=($4, +(1, 10)), SEARCH($21,
Sarg[[1..5]])), AND(=($19, 'Brand#23'), SEARCH($22, Sarg['MED BAG':CHAR(8),
'MED BOX':CHAR(8), 'MED PACK', 'MED PKG':CHAR(8)]:CHAR(8)), >=($4, 10), <=($4,
+(10, 10)), SEARCH($21, Sarg[[1..10]])), AND(=($19, 'Brand#34'), SEARCH($22,
Sarg['LG BOX':CHAR(7), 'LG CASE', 'LG PACK', 'LG PKG':CHAR(7)]:CHAR(7)), >=($4,
20), <=($4, +(20, 10)), SEARCH($21, Sarg[[1..15]]))))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[tpch, LINEITEM]])
LogicalTableScan(table=[[tpch, PART]])
{code}
The above two SQL Plan semantics are the same, the only difference is that the
Filter conditions are in different order.
Although this has no effect on the SQL execution results, it is difficult for
me to monitor my plan because of the variability of the plan.
was:
In my rule, I used the RexUtil.pullFactors to simplify the Filter's condition
and I tested the rule with TPC-H q19.sql.
When I print the plan tree with RelOptUtil.toString,sometimes the plan is :
```sql
LogicalAggregate(group=[{}], revenue=[SUM($0)])
LogicalProject($f0=[*($5, -(1, $6))])
LogicalFilter(condition=[AND(=($16, $1), SEARCH($14, Sarg['AIR':CHAR(7),
'AIR REG']:CHAR(7)), =($13, 'DELIVER IN PERSON'), OR(AND(=($19, 'Brand#12'),
SEARCH($22, Sarg['SM BOX':CHAR(7), 'SM CASE', 'SM PACK', 'SM
PKG':CHAR(7)]:CHAR(7)), >=($4, 1), <=($4, +(1, 10)), SEARCH($21,
Sarg[[1..5]])), AND(=($19, 'Brand#23'), SEARCH($22, Sarg['MED BAG':CHAR(8),
'MED BOX':CHAR(8), 'MED PACK', 'MED PKG':CHAR(8)]:CHAR(8)), >=($4, 10), <=($4,
+(10, 10)), SEARCH($21, Sarg[[1..10]])), AND(=($19, 'Brand#34'), SEARCH($22,
Sarg['LG BOX':CHAR(7), 'LG CASE', 'LG PACK', 'LG PKG':CHAR(7)]:CHAR(7)), >=($4,
20), <=($4, +(20, 10)), SEARCH($21, Sarg[[1..15]]))))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[tpch, LINEITEM]])
LogicalTableScan(table=[[tpch, PART]])
```
and sometimes the plan is :
```sql
LogicalAggregate(group=[{}], revenue=[SUM($0)])
LogicalProject($f0=[*($5, -(1, $6))])
LogicalFilter(condition=[AND(=($16, $1), =($13, 'DELIVER IN PERSON'),
SEARCH($14, Sarg['AIR':CHAR(7), 'AIR REG']:CHAR(7)), OR(AND(=($19, 'Brand#12'),
SEARCH($22, Sarg['SM BOX':CHAR(7), 'SM CASE', 'SM PACK', 'SM
PKG':CHAR(7)]:CHAR(7)), >=($4, 1), <=($4, +(1, 10)), SEARCH($21,
Sarg[[1..5]])), AND(=($19, 'Brand#23'), SEARCH($22, Sarg['MED BAG':CHAR(8),
'MED BOX':CHAR(8), 'MED PACK', 'MED PKG':CHAR(8)]:CHAR(8)), >=($4, 10), <=($4,
+(10, 10)), SEARCH($21, Sarg[[1..10]])), AND(=($19, 'Brand#34'), SEARCH($22,
Sarg['LG BOX':CHAR(7), 'LG CASE', 'LG PACK', 'LG PKG':CHAR(7)]:CHAR(7)), >=($4,
20), <=($4, +(20, 10)), SEARCH($21, Sarg[[1..15]]))))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[tpch, LINEITEM]])
LogicalTableScan(table=[[tpch, PART]])
```
The above two SQL Plan semantics are the same, the only difference is that the
Filter conditions are in different order.
Although this has no effect on the SQL execution results, it is difficult for
me to monitor my plan because of the variability of the plan.
> After RexUtil.pullFactors method,sometimes the condition order of RexNode is
> not the same as before
> ---------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5837
> URL: https://issues.apache.org/jira/browse/CALCITE-5837
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: LakeShen
> Priority: Minor
> Fix For: 1.36.0
>
>
> In my rule, I used the RexUtil.pullFactors to simplify the Filter's condition
> and I tested the rule with TPC-H q19.sql.
> When I print the plan tree with RelOptUtil.toString,sometimes the plan is :
> {code:java}
> LogicalAggregate(group=[{}], revenue=[SUM($0)])
> LogicalProject($f0=[*($5, -(1, $6))])
> LogicalFilter(condition=[AND(=($16, $1), SEARCH($14, Sarg['AIR':CHAR(7),
> 'AIR REG']:CHAR(7)), =($13, 'DELIVER IN PERSON'), OR(AND(=($19, 'Brand#12'),
> SEARCH($22, Sarg['SM BOX':CHAR(7), 'SM CASE', 'SM PACK', 'SM
> PKG':CHAR(7)]:CHAR(7)), >=($4, 1), <=($4, +(1, 10)), SEARCH($21,
> Sarg[[1..5]])), AND(=($19, 'Brand#23'), SEARCH($22, Sarg['MED BAG':CHAR(8),
> 'MED BOX':CHAR(8), 'MED PACK', 'MED PKG':CHAR(8)]:CHAR(8)), >=($4, 10),
> <=($4, +(10, 10)), SEARCH($21, Sarg[[1..10]])), AND(=($19, 'Brand#34'),
> SEARCH($22, Sarg['LG BOX':CHAR(7), 'LG CASE', 'LG PACK', 'LG
> PKG':CHAR(7)]:CHAR(7)), >=($4, 20), <=($4, +(20, 10)), SEARCH($21,
> Sarg[[1..15]]))))])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[tpch, LINEITEM]])
> LogicalTableScan(table=[[tpch, PART]])
> {code}
> and sometimes the plan is :
> {code:java}
> LogicalAggregate(group=[{}], revenue=[SUM($0)])
> LogicalProject($f0=[*($5, -(1, $6))])
> LogicalFilter(condition=[AND(=($16, $1), =($13, 'DELIVER IN PERSON'),
> SEARCH($14, Sarg['AIR':CHAR(7), 'AIR REG']:CHAR(7)), OR(AND(=($19,
> 'Brand#12'), SEARCH($22, Sarg['SM BOX':CHAR(7), 'SM CASE', 'SM PACK', 'SM
> PKG':CHAR(7)]:CHAR(7)), >=($4, 1), <=($4, +(1, 10)), SEARCH($21,
> Sarg[[1..5]])), AND(=($19, 'Brand#23'), SEARCH($22, Sarg['MED BAG':CHAR(8),
> 'MED BOX':CHAR(8), 'MED PACK', 'MED PKG':CHAR(8)]:CHAR(8)), >=($4, 10),
> <=($4, +(10, 10)), SEARCH($21, Sarg[[1..10]])), AND(=($19, 'Brand#34'),
> SEARCH($22, Sarg['LG BOX':CHAR(7), 'LG CASE', 'LG PACK', 'LG
> PKG':CHAR(7)]:CHAR(7)), >=($4, 20), <=($4, +(20, 10)), SEARCH($21,
> Sarg[[1..15]]))))])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[tpch, LINEITEM]])
> LogicalTableScan(table=[[tpch, PART]])
> {code}
> The above two SQL Plan semantics are the same, the only difference is that
> the Filter conditions are in different order.
> Although this has no effect on the SQL execution results, it is difficult for
> me to monitor my plan because of the variability of the plan.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
