[jira] [Created] (CALCITE-5081) Bug in RelDecorrelator.decorrelate(Aggregate, boolean) is changing the group keys

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

Shiven Dvrk created CALCITE-5081:
------------------------------------

             Summary: Bug in RelDecorrelator.decorrelate(Aggregate, boolean) is changing the group keys
                 Key: CALCITE-5081
                 URL: https://issues.apache.org/jira/browse/CALCITE-5081
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: Shiven Dvrk


We have following tables

table1:
    "ID", SqlTypeName.VARCHAR,
    "K509", SqlTypeName.BIGINT,
    "K505", SqlTypeName.BIGINT

    table2:
      "K14507", SqlTypeName.VARCHAR,
      "K14506", SqlTypeName.BIGINT,
      "K14509", SqlTypeName.BIGINT

A source relnode representing the following SQL:
{quote}    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`
{quote}
 

   After decorelation, the decorrelated relNode represents the following incorrect SQL:
{quote}   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`


{quote}
   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)

 
{quote}{{   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()}}
{quote}



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