[jira] [Commented] (CALCITE-4242) Wrong plan for nested NOT EXISTS subqueries

["James Starr (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17193795#comment-17193795 ] 

James Starr commented on CALCITE-4242:
--------------------------------------

The following generates the expected results in postgres.  I believe the behavior your seeing coming from how the min is interacting with the left join.  Perhaps is would be simpler to rewrite the query to simpiler string literal instead of an aggregate.
{code:sql}
SELECT P.X
FROM P
LEFT JOIN (SELECT t0.Z, TRUE AS f1
FROM Q
LEFT JOIN (SELECT Z, TRUE AS f1
FROM R
GROUP BY Z) AS t0 ON TRUE
WHERE t0.f1
GROUP BY t0.Z) AS t3 ON P.X = t3.Z
WHERE t3.f1
{code}

> Wrong plan for nested NOT EXISTS subqueries
> -------------------------------------------
>
>                 Key: CALCITE-4242
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4242
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Martin Raszyk
>            Priority: Major
>
> Suppose we initialize an empty database as follows.
>  
> {code:java}
> CREATE TABLE P(x INTEGER);
> CREATE TABLE Q(y INTEGER);
> CREATE TABLE R(z INTEGER);
> INSERT INTO P VALUES (1);
> INSERT INTO Q VALUES (1);{code}
>  
> The following query is supposed to yield an empty table as the result.
>  
> {code:java}
> SELECT x FROM P
> WHERE NOT EXISTS (
>   SELECT y FROM Q
>   WHERE NOT EXISTS (
>     SELECT z FROM R
>     WHERE x = z
>   )
> ){code}
>  
> However, the query is parsed and converted to the following plan
> {code:java}
> LogicalProject(X=[$0])
>   LogicalFilter(condition=[IS NULL($2)])
>     LogicalJoin(condition=[=($0, $1)], joinType=[left])
>       LogicalTableScan(table=[[Bug, P]])
>       LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
>         LogicalProject(Z=[$1], $f0=[true])
>           LogicalFilter(condition=[IS NULL($2)])
>             LogicalJoin(condition=[true], joinType=[left])
>               LogicalTableScan(table=[[Bug, Q]])
>               LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
>                 LogicalProject(Z=[$0], $f0=[true])
>                   LogicalTableScan(table=[[Bug, R]])
> {code}
> that corresponds to the following SQL query
> {code:java}
> SELECT P.X
> FROM Bug.P
> LEFT JOIN (SELECT t0.Z, MIN(TRUE) AS $f1
> FROM Bug.Q
> LEFT JOIN (SELECT Z, MIN(TRUE) AS $f1
> FROM Bug.R
> GROUP BY Z) AS t0 ON TRUE
> WHERE t0.$f1 IS NULL
> GROUP BY t0.Z) AS t3 ON P.X = t3.Z
> WHERE t3.$f1 IS NULL
> {code}
> which yields the (non-empty) table P as the result.
> Hence, the parsed and converted query is not equivalent to the input query.



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