[
https://issues.apache.org/jira/browse/CALCITE-4693?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-4693:
------------------------------------
Labels: pull-request-available (was: )
> 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: 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
(v8.20.10#820010)
