[
https://issues.apache.org/jira/browse/CALCITE-4693?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17382782#comment-17382782
]
Julian Hyde commented on CALCITE-4693:
--------------------------------------
I agree that calcite gives the wrong result. I do not agree that the solution
is to make all LATERAL queries into LEFT JOINs.
For example, if you use COUNT rather than SUM I think Postgres will return
zeros for the unmatched rows. LEFT would instead return NULL values.
The right query is an aggregate with no GROUP BY. It must return one row, even
if the WHERE filters out all rows. That is what Calcite is getting wrong. LEFT
solves this in a few cases but not all.
> Query with Lateral Join is converted to Inner Join instead of Left Join
> -----------------------------------------------------------------------
>
> 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: duan xiong
> Priority: Major
> Attachments: 截屏2021-07-15 19.05.23.png, 截屏2021-07-15 19.06.24.png
>
>
> 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
(v8.3.4#803005)
