[ https://issues.apache.org/jira/browse/CALCITE-4242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17197422#comment-17197422 ]
Martin Raszyk commented on CALCITE-4242: ---------------------------------------- I tried to use `LEFT SEMI JOIN` in PostgreSQL version `psql (PostgreSQL) 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)`, but I didn't succeed. The query {code:java} SELECT x FROM P LEFT SEMI JOIN Q ON TRUE; {code} leads to syntax error. The query {code:java} SELECT x FROM P SEMI JOIN Q ON TRUE; {code} is evaluated, but does not yield the proper multiplicities. Finally, the query {code:java} SELECT my_p.x FROM P AS my_p SEMI JOIN Q ON TRUE; {code} leads to syntax error, too. Moreover, I said that "one could keep the LEFT JOIN right-associative for nested NOT EXISTS subqueries *without data dependencies crossing two nesting levels*". In my very original query {code:java} SELECT x FROM P WHERE NOT EXISTS ( SELECT y FROM Q WHERE NOT EXISTS ( SELECT z FROM R WHERE x = z ) ) {code} this is not the case and your most recent query which I fixed as follows so that it evaluates {code:java} WITH q_present AS (SELECT TRUE as present FROM q), r_present_given_z AS (SELECT TRUE as present, z FROM r GROUP BY z), q_present_given_r_present_given_z AS ( SELECT TRUE AS present, r_present_given_z.present AS r_present, r_present_given_z.z AS r_z FROM q_present LEFT JOIN r_present_given_z ON TRUE ) SELECT my_p.x FROM P my_p LEFT JOIN q_present_given_r_present_given_z ON q_present_given_r_present_given_z.r_z = my_p.x WHERE NOT( q_present_given_r_present_given_z.present AND NOT q_present_given_r_present_given_z.r_present ); {code} is not equivalent to my very original query with the same counter-example as you very first suggestion: {code:java} CREATE TABLE P(x INTEGER); CREATE TABLE Q(y INTEGER); CREATE TABLE R(z INTEGER); INSERT INTO P VALUES (1); {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)