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

["Martin Raszyk (Jira)" <ji...@apache.org>]

Martin Raszyk created CALCITE-4242:
--------------------------------------

             Summary: 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


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)