[jira] [Created] (CALCITE-4693) Query with Lateral Join is converted to Inner Join instead of Left Join

["Shivender Devarakonda (Jira)" <ji...@apache.org>]

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)