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)
