Hi Shiven, AFAIK, Calcite does not have a such rule for now.
There is a similar rule[1], which only pushes the predicates inferred from the other side. [1] https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rules/JoinPushTransitivePredicatesRule.java Shiven D <shiven.d...@gmail.com> 于2022年5月1日周日 06:41写道： > Hi, > Would like to get some inputs on following problem, > We have the following source SQL: > > {code} > SELECT T1_FIELD1, T1_FIELD3, Amountdue > FROM T1, LATERAL ( > SELECT SUM( T2_NUM1 + T2_NUM2 + T2_NUM3) as Amountdue FROM T2 > WHERE T2_NUM4 = T1.ID > ) > WHERE T1_FIELD3 IN (200, 300, 800) AND T1_FIELD4 = "Sales" > {code} > > We applied the decorrelation API: (RelDecorrelator.decorrelateQuery()) and > the resulted decorrelated sql: > > {code} > SELECT > `t`.`T1_FIELD1`, > `t`.`T1_FIELD3`, > `t1`.`Amountdue` > FROM > ( > SELECT > * > FROM > `T1` > WHERE > `T1_FIELD3` IN > ( > 200, > 300, > 800 > ) > AND `T1_FIELD4` = 'Sales' > ) > AS `t` > LEFT JOIN > ( > SELECT > `T2_NUM4`, > SUM(`T2_NUM1` + `T2_NUM2` + `T2_NUM3`) AS `Amountdue` > FROM > `T2` > > GROUP BY > `T2_NUM4` > ) > AS `t1` > ON `t`.`ID` = `t1`.`T2_NUM4` > {code} > > The cardinality of the query on right hand side of the join (on table T2) > is very high. > We are exploring some options to push the filter that is on left side of > join to right side so that we can potentially get a reduced set of results. > something like this: > > {code} > SELECT > `t`.`T1_FIELD1`, > `t`.`T1_FIELD3`, > `t1`.`Amountdue` > FROM > ( > SELECT > * > FROM > `T1` > WHERE > `T1_FIELD3` IN > ( > 200, > 300, > 800 > ) > AND `T1_FIELD4` = 'Sales' > ) > AS `t` > LEFT JOIN > ( > SELECT > `T2_NUM4`, > SUM(`T2_NUM1` + `T2_NUM2` + `T2_NUM3`) AS `Amountdue` > FROM > `T2` > WHERE T2_NUM4 IN ( SELECT `ID` FROM `T1` WHERE `T1_FIELD3` IN > (200, 300, 800) AND `T1_FIELD4` = 'Sales') > GROUP BY > `T2_NUM4` > ) > AS `t1` > ON `t`.`ID` = `t1`.`T2_NUM4` > {code} > > is there any API or existing rule that can do such transformation? any > other suggestions? > > Thanks > -- Best, Benchao Li
