[
https://issues.apache.org/jira/browse/CALCITE-4242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17194049#comment-17194049
]
Martin Raszyk commented on CALCITE-4242:
----------------------------------------
Your last query makes sense in the set semantics. However, it seems hard to
enforce the right multiplicities, e.g., on a database obtained by
{code:java}
DELETE FROM P;
DELETE FROM Q;
DELETE FROM R;
INSERT INTO P VALUES (1);
INSERT INTO P VALUES (1);
INSERT INTO P VALUES (0);
INSERT INTO P VALUES (0);
INSERT INTO Q VALUES (1);
INSERT INTO Q VALUES (0);
INSERT INTO R VALUES (1);
INSERT INTO R VALUES (0);
{code}
Let me also point out that it still makes sense to keep the LEFT JOIN
right-associative for nested NOT EXISTS subqueries without data dependencies
crossing two nesting levels, e.g., in the input query
{code:java}
SELECT x FROM P
WHERE NOT EXISTS (
SELECT y FROM Q
WHERE x = y AND NOT EXISTS (
SELECT z FROM R
WHERE y = z
)
)
{code}
for which the currently converted query
{code:java}
SELECT P.X
FROM PQR.P
LEFT JOIN (SELECT Y, MIN(TRUE) AS $f1
FROM (SELECT Q.Y, t0.$f1 AS $f0
FROM PQR.Q
LEFT JOIN (SELECT Z, MIN(TRUE) AS $f1
FROM PQR.R
GROUP BY Z) AS t0 ON Q.Y = t0.Z) AS t1
WHERE t1.$f0 IS NULL
GROUP BY Y) AS t4 ON P.X = t4.Y
WHERE t4.$f1 IS NULL
{code}
is equivalent (also w.r.t. multiplicities) and could be more efficient than
with a left-associative LEFT JOIN.
Finally, let me point out that the WHERE clause from your last query becomes
more involved if the nesting depth increases, e.g., on the input query
{code:java}
SELECT x FROM P
WHERE NOT EXISTS (
SELECT y FROM Q
WHERE x = y AND NOT EXISTS (
SELECT z FROM R
WHERE x = z AND y = z AND NOT EXISTS (
SELECT w FROM S
WHERE x = w AND y = w AND z = w AND NOT EXISTS (
SELECT v FROM T
WHERE x = v AND y = v AND z = v AND w = v
)
)
)
)
{code}
where it should read
{code:java}
WHERE my_q.present IS NULL OR (my_r.present IS NOT NULL AND (my_s.present IS
NULL OR my_t.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)
