Re: Transforming the query after decorrelation

[Benchao Li <li...@apache.org>]

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 <sh...@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