[
https://issues.apache.org/jira/browse/CALCITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17607177#comment-17607177
]
Paul Jackson edited comment on CALCITE-4509 at 9/20/22 12:54 PM:
-----------------------------------------------------------------
I think I've hit a simpler case of the same problem:
I start with a simple filter:
{code:java}
LogicalFilter(condition=[=($2, '[1]')])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
Apply this filter to it (using {{{}relBuilder.push(relNode).filter( x
).build(){}}}) where {{x}} is:
{code:java}
OR(=($2, '[0]'), =($2, '[1]')){code}
The filter method applies simplification to the expression rendering:
{code:java}
LogicalFilter(condition=[SEARCH($2, Sarg['[0]':VARCHAR(14),
'[1]':VARCHAR(14)]:VARCHAR(14))])
LogicalFilter(condition=[=($2, '[1]')])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
After {{FILTER_MERGE}} and {{{}FILTER_REDUCE_EXPRESSIONS{}}}:
{code:java}
LogicalFilter(condition=[AND(=($2, '[1]'), SEARCH($2, Sarg['[0]':VARCHAR(14),
'[1]':VARCHAR(14)]:VARCHAR(14)))])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
But it should simplify to either:
{code:java}
LogicalFilter(condition=[SEARCH($2, Sarg['[1]':VARCHAR(14)]:VARCHAR(14))])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
or ideally:
{code:java}
LogicalFilter(condition=[=($2, '[1]')])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
was (Author: pauljackson123):
I think I've hit a simpler case of the same problem:
I start with a simple filter:
{code:java}
LogicalFilter(condition=[=($2, '[1]')])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
Apply this filter to it (using
{{{}relBuilder.push(relNode).filter(x).build(){}}}) where {{x}} is:
{code:java}
OR(=($2, '[0]'), =($2, '[1]')){code}
The filter method applies simplification to the expression rendering:
{code:java}
LogicalFilter(condition=[SEARCH($2, Sarg['[0]':VARCHAR(14),
'[1]':VARCHAR(14)]:VARCHAR(14))])
LogicalFilter(condition=[=($2, '[1]')])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
After {{FILTER_MERGE}} and {{{}FILTER_REDUCE_EXPRESSIONS{}}}:
{code:java}
LogicalFilter(condition=[AND(=($2, '[1]'), SEARCH($2, Sarg['[0]':VARCHAR(14),
'[1]':VARCHAR(14)]:VARCHAR(14)))])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
But it should simplify to either:
{code:java}
LogicalFilter(condition=[SEARCH($2, Sarg['[1]':VARCHAR(14)]:VARCHAR(14))])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
or ideally:
{code:java}
LogicalFilter(condition=[=($2, '[1]')])
JdbcTableScan(table=[[SIMPLE, PUBLIC, EMPLOYEES]]){code}
> RexSimplify can't simplify redundant predicate in filter merge rule
> -------------------------------------------------------------------
>
> Key: CALCITE-4509
> URL: https://issues.apache.org/jira/browse/CALCITE-4509
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Environment: jvm: open-jdk8
> Reporter: yanjing.wang
> Priority: Major
>
> my sql is :
> String sql = "select b.ds, count(distinct a.id), count(a.name) from users a
> join (select * from depts where ds > '0' and ds >= 20200101 ) b on a.dept_id
> = b.id and a.ds = b.ds where a.ds > '1' and a.ds > 20200103 and a.product_id
> in (1,2,3) group by b.ds having count(a.name) > 5 limit 5000";
>
> the rel is
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 41:LogicalFilter(condition=[>=($5, 20200101)])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 42:LogicalFilter(condition=[AND(SEARCH($3, Sarg[(20200103..+∞)]), IS NOT
> NULL($3))])
> 33:LogicalFilter(condition=[>=($3, 20200101)])
> 3:JdbcTableScan(table=[[default, depts]])
>
> after JOIN_PUSH_TRANSITIVE_PREDICATES and FILTER_MERGE rule etc optimize the
> rel,
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 49:LogicalFilter(condition=[AND(>=($3, 20200101), SEARCH($3,
> Sarg[(20200103..+∞)]))])
> 3:JdbcTableScan(table=[[default, depts]])
>
> we can find :
> $5 condition has been merged but $3 has redundant '>=($3, 20200101)'.
>
> i dive into the source code and find 'simplifyAnd' method in 'RexSimplify'
> class has the line:
>
> {code:java}
> if (sargCollector.map.values().stream().anyMatch(b -> b.complexity() > 1)) {
> operands.clear(); terms.forEach(t ->
> operands.add(sargCollector.fix(rexBuilder, t))); }
> {code}
>
> so users table has $6 filter with complexity > 1, so the operands get
> refilled.
> but depts table only has $3 filter complexity = 1 , so operands don't change,
> and conditions has not been merged.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
