[jira] [Comment Edited] (CALCITE-5081) Group keys of Aggregate are wrongly changed during decorrelation

["Shiven Dvrk (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5081?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17516130#comment-17516130 ] 

Shiven Dvrk edited comment on CALCITE-5081 at 4/1/22 9:13 PM:
--------------------------------------------------------------

I believe it might be a different issue in the same decorrelation logic


was (Author: shivendvrk):
I believe it might be a different issue in the same decor relation logic

> Group keys of Aggregate are wrongly changed during decorrelation
> ----------------------------------------------------------------
>
>                 Key: CALCITE-5081
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5081
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Shiven Dvrk
>            Priority: Major
>
> Bug in RelDecorrelator.decorrelate(Aggregate, boolean) is changing the group keys. We have following tables
>  table1:
> {noformat}
>     "ID", SqlTypeName.VARCHAR,
>     "K509", SqlTypeName.BIGINT,
>     "K505", SqlTypeName.BIGINT
> {noformat}
>  table2:
> {noformat}
>       "K14507", SqlTypeName.VARCHAR,
>       "K14506", SqlTypeName.BIGINT,
>       "K14509", SqlTypeName.BIGINT
> {noformat}
> A source relnode representing the following SQL:
> {code}    SELECT
>    `K505`,
>    SUM(`test_total`) AS ` $ f1`
> FROM
>    (
>       SELECT
>          ` $ cor0`.`K509`,
>          ` $ cor0`.`K505`,
>          ` $ cor0`.` $ f0` AS `test_total`
>       FROM
>          `table1` AS ` $ cor0`,
>          LATERAL (
>          SELECT
>             SUM(`K14506`) AS ` $ f0`
>          FROM
>             `table2`
>          WHERE
>             `K14507` = ` $ cor0`.`ID`) AS `t1`
>    )
>    AS `t2`
> WHERE
>    `t2`.`K505` = 1000
> GROUP BY
>    `K505`
> {code}
>  
>    After decorelation, the decorrelated relNode represents the following incorrect SQL:
> {code}   SELECT
>    `t`.`K509`,
>    SUM(`t1`.` $ f1`) AS ` $ f1`
> FROM
>    (
>       SELECT
>          *
>       FROM
>          `table1`
>       WHERE
>          `K505` = 1000
>    )
>    AS `t`
>    LEFT JOIN
>       (
>          SELECT
>             `K14507`,
>             SUM(`K14506`) AS ` $ f1`
>          FROM
>             `table2`
>          GROUP BY
>             `K14507`
>       )
>       AS `t1`
>       ON `t`.`ID` = `t1`.`K14507`
> GROUP BY
>    `t`.`K509`
> {code}
>    it changed the group key.
>    
>    it looks like the logic in RelDecorrelator.decorrelate(Aggregate, boolean) is always picking the 0th index group key.
>    We built the relNode using the following logic(used sqls above to explain the problem)
> {code} 
> val builder = RelBuilder.create(createConfig())
> val v = Holder.of[RexCorrelVariable](null)
> val relNode = builder
>   .scan("table1")
>   .variable(v)
>   .scan("table2")
>   .filter(builder.equals(builder.field(0), builder.field(v.get(), "ID")))
>   .project(builder.field("K14506"))
>   .aggregate(builder.groupKey(), builder.sum(builder.field(0)))
>   .project(builder.alias(builder.field(0), "test_total"))
>   .correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "ID"))
>   .project(builder.field(1), builder.field(2), builder.field(3))
>   .filter(builder.equals(builder.field(1), builder.literal(1000)))
>   .aggregate(builder.groupKey("K505"), builder.sum(builder.field(2)))
>   .build()
> {code}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)