[
https://issues.apache.org/jira/browse/CALCITE-4693?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18133983#comment-18133983
]
Gian Merlino commented on CALCITE-4693:
---------------------------------------
I retitled this Jira "Decorrelation drops rows for inner LATERAL join over
aggregate without GROUP BY". (The old title suggested that INNER LATERAL should
become LEFT, which as discussed, is not correct). Raised a PR at:
https://github.com/apache/calcite/pull/5368
> Decorrelation drops rows for inner LATERAL join over aggregate without GROUP
> BY
> -------------------------------------------------------------------------------
>
> Key: CALCITE-4693
> URL: https://issues.apache.org/jira/browse/CALCITE-4693
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Reporter: Shiven Dvrk
> Assignee: xiong duan
> Priority: Major
> Labels: pull-request-available
>
> The following query:
> {code:java}
> SELECT
> K.ID,
> K.C1 - t2.totalAmount AS amountDue
> FROM
> T895 AS K,
> LATERAL (
> SELECT
> sum(C201 + C202) AS totalAmount
> FROM T902
> WHERE
> C200 = K.ID
> ) AS t2{code}
>
> is converted to following RelNode( this is innerJoin):
>
> {code:java}
> LogicalProject(ID=[$0], amountDue=[-($2, $4)])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
> LogicalTableScan(table=[[T895]])
> LogicalAggregate(group=[{0}], totalAmount=[SUM($1)])
> LogicalProject(C200=[$0], $f0=[+($1, $2)])
> LogicalTableScan(table=[[T902]]) {code}
> we can convert this relnode back to SQL again, it clearly shows that it is
> inner join.
> Tried the Lateral join query on Postgres, the results clearly show that it is
> a left join.
> is there any specific reason behind this behavior ? it seems to be a bug.
>
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
