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
