Martin Raszyk created CALCITE-4243:
--------------------------------------
Summary: Wrong SQL conversion for JOIN and NOT EXISTS subquery
Key: CALCITE-4243
URL: https://issues.apache.org/jira/browse/CALCITE-4243
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);
{code}
The following query
{code:java}
SELECT *
FROM P JOIN Q ON TRUE
WHERE NOT EXISTS (
SELECT * FROM R
WHERE x = z
)
{code}
is parsed and converted to the following plan
{code:java}
LogicalProject(X=[$0], Y=[$1])
LogicalFilter(condition=[IS NULL($3)])
LogicalJoin(condition=[=($0, $2)], joinType=[left])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[Bug, P]])
LogicalTableScan(table=[[Bug, Q]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(Z=[$0], $f0=[true])
LogicalTableScan(table=[[Bug, R]])
{code}
that is subsequently converted to the following SQL query
{code:java}
SELECT P.X, Q.Y
FROM Bug.P,
Bug.Q
LEFT JOIN (SELECT Z, MIN(TRUE) AS $f1
FROM Bug.R
GROUP BY Z) AS t0 ON P.X = t0.Z
WHERE t0.$f1 IS NULL{code}
which is not valid in SQL, because the LEFT JOIN operation acts only
on the tables Q and R, so the ON condition cannot access the column X
from the table P.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
