[
https://issues.apache.org/jira/browse/CALCITE-5081?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17518942#comment-17518942
]
Benchao Li commented on CALCITE-5081:
-------------------------------------
[~ShivenDvrk] I tried this using sql:
{code:sql}
SELECT
deptno,
SUM(test_total) AS f1
FROM
(
SELECT
t0.empno,
t0.deptno,
t1.f0 AS test_total
FROM
"scott".emp AS t0,
LATERAL (
SELECT
SUM(deptno) AS f0
FROM
"scott".dept
WHERE
dname = t0.ename) AS t1
)
AS t2
WHERE
t2.deptno = 1000
GROUP BY
deptno
{code}
The RelNode before decorrelation is:
{code:java}
LogicalAggregate(group=[{0}], F1=[SUM($1)])
LogicalProject(DEPTNO=[$1], TEST_TOTAL=[$2])
LogicalFilter(condition=[=(CAST($1):INTEGER, 1000)])
LogicalProject(EMPNO=[$0], DEPTNO=[$7], TEST_TOTAL=[$8])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{1}])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{}], F0=[SUM($0)])
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[=($1, CAST($cor0.ENAME):VARCHAR(14))])
LogicalTableScan(table=[[scott, DEPT]])
{code}
And after decorrelation:
{code:java}
LogicalAggregate(group=[{0}], F1=[SUM($1)])
LogicalProject(DEPTNO=[$7], TEST_TOTAL=[$10])
LogicalJoin(condition=[=($8, $9)], joinType=[inner])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], ENAME0=[CAST($1):VARCHAR(14)])
LogicalFilter(condition=[=(CAST($7):INTEGER, 1000)])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{0}], F0=[SUM($1)])
LogicalProject(DNAME=[$1], DEPTNO=[$0])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[scott, DEPT]])
{code}
It seems that both RelNode is correct. Can you share your RelNode constructed
by the RelBuilder, maybe the tree is not correct from the beginning.
> 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)
