[ https://issues.apache.org/jira/browse/CALCITE-4242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17193868#comment-17193868 ]
James Starr commented on CALCITE-4242: -------------------------------------- This is or at least reasonable close to what the decorrelated query should look like. So perhaps how logic for pushing up joins is not correct. {code:java} SELECT my_p.X FROM P my_p LEFT JOIN (SELECT TRUE as present FROM Q ) AS my_q ON TRUE LEFT JOIN (SELECT Z, TRUE AS present FROM R GROUP BY Z) AS my_r ON my_p.X = my_r.Z WHERE my_q.present IS NULL OR my_r.present IS NOT NULL {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)