[jira] [Created] (CALCITE-4509) RexSimplify can't simplify redundant predicate in filter merge rule

["yanjing.wang (Jira)" <ji...@apache.org>]

yanjing.wang created CALCITE-4509:
-------------------------------------

             Summary: 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
            Reporter: yanjing.wang
             Fix For: 1.27.0


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 , so operands don't change, and condition has not been merged.



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