You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2021/06/02 16:40:00 UTC

[jira] [Commented] (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:comment-tabpanel&focusedCommentId=17355856#comment-17355856 ] 

Stamatis Zampetakis commented on CALCITE-4630:
----------------------------------------------

I have an intuition that the problem starts from \{{FilterCorrelateRule}}; possibly we are pushing down a filter and we shouldn't. [~dmsysolyatin] if you plan to work on this please add also test cases with data.

> Wrong logical plan for INNER JOIN with subquery
> -----------------------------------------------
>
>                 Key: CALCITE-4630
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4630
>             Project: Calcite
>          Issue Type: Bug
>          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)