[jira] [Created] (CALCITE-4630) Wrong logical plan for INNER JOIN with subquery

["Dmitry Sysolyatin (Jira)" <ji...@apache.org>]

Dmitry Sysolyatin created CALCITE-4630:
------------------------------------------

             Summary: Wrong logical plan for INNER JOIN with subquery
                 Key: CALCITE-4630
                 URL: https://issues.apache.org/jira/browse/CALCITE-4630
             Project: Calcite
          Issue Type: Improvement
          Components: core
    Affects Versions: 1.26.0
            Reporter: Dmitry Sysolyatin


I tried to execute the following query:
{code:sql}
SELECT
    d.name,
    d.timestamp_ns
FROM trucks t
INNER JOIN LATERAL (
  SELECT
      name, timestamp_ns, fuel_state
   FROM
      trucks_diagnostics
   WHERE
       trucks_diagnostics.name = t.name
    ORDER BY timestamp_ns DESC
    LIMIT 1) d ON true WHERE s.fleet = 'South' AND d.fuel_state < 0.1
{code}
calcite generates me the following logical plan:
{code:java}
LogicalProject(name=[$8], timestamp_ns=[$9])
  LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{4}])
    LogicalFilter(condition=[=(CAST($6):VARCHAR, 'South')])
      DataTableScan
    LogicalFilter(condition=[<($2, 0.1:DECIMAL(2, 1))])
      LogicalSort(sort0=[$1], dir0=[DESC], fetch=[1])
        LogicalProject(name=[$4], timestamp_ns=[$0], fuel_state=[$1])
          LogicalFilter(condition=[=(CAST($4):VARCHAR, CAST($cor0.name):VARCHAR)])
            DataTableScan
{code}
But `LogicalFilter(condition=[<($2, 0.1:DECIMAL(2, 1))])` should not be inside LogicalCorrelate . It should be upper than LogicalCorrelate



--
This message was sent by Atlassian Jira
(v8.3.4#803005)