You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Dmitry Sysolyatin (Jira)" <ji...@apache.org> on 2021/06/02 15:15:00 UTC
[jira] [Updated] (CALCITE-4630) Wrong logical plan for INNER JOIN
with subquery
[ https://issues.apache.org/jira/browse/CALCITE-4630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dmitry Sysolyatin updated CALCITE-4630:
---------------------------------------
Description:
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 t.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
was:
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
> 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
> Priority: Critical
>
> 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 t.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)