[
https://issues.apache.org/jira/browse/CALCITE-5081?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5081:
---------------------------------
Description:
Bug in RelDecorrelator.decorrelate(Aggregate, boolean) is changing the group
keys. We have following tables
{noformat}
table1:
"ID", SqlTypeName.VARCHAR,
"K509", SqlTypeName.BIGINT,
"K505", SqlTypeName.BIGINT
table2:
"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}
was:
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}
> 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
> {noformat}
> table1:
> "ID", SqlTypeName.VARCHAR,
> "K509", SqlTypeName.BIGINT,
> "K505", SqlTypeName.BIGINT
> table2:
> "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)
