Shivender Devarakonda created CALCITE-4693: ----------------------------------------------
Summary: 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: Shivender Devarakonda 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)