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)