[CALCITE-4630] Wrong logical plan for INNER JOIN with subquery

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Invalid
• Affects Version/s: 1.27.0
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Dmitry Sysolyatin
• Reporter: Dmitry Sysolyatin
• Created: 02/Jun/21 15:11
• Updated: 09/Jun/21 13:29
• Resolved: 09/Jun/21 13:29

Description

I tried to execute the following query:

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 t.fleet = 'South' AND d.fuel_state < 0.1

calcite generates me the following logical plan:

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

But `LogicalFilter(condition=[<($2, 0.1:DECIMAL(2, 1))])` should not be inside LogicalCorrelate . It should be upper than LogicalCorrelate

Issue Links

• links to: GitHub Pull Request #2429