[jira] [Created] (CALCITE-4243) Wrong SQL conversion for JOIN and NOT EXISTS subquery

["Martin Raszyk (Jira)" <ji...@apache.org>]

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)