[jira] [Created] (CALCITE-5716) Two level nested correlated subquery translates to incorrect ON condition

["John Wright (Jira)" <ji...@apache.org>]

John Wright created CALCITE-5716:
------------------------------------

             Summary: Two level nested correlated subquery translates to incorrect ON condition
                 Key: CALCITE-5716
                 URL: https://issues.apache.org/jira/browse/CALCITE-5716
             Project: Calcite
          Issue Type: Improvement
            Reporter: John Wright


I have a query that goes through the Calcite Core decorrelation rules and comes out with an ON condition that isn't correct and returns incorrect results to a different engine.

 
{code:java}
 SELECT summary.id,
       (SELECT Max(detail.id)
        FROM   detail
        WHERE  date = (SELECT Max(date) AS maxDate
                       FROM   detail detail2
                       WHERE  detail2.summary_id = summary.id)
               AND detail.summary_id = summary.id) AS Detail_With_MaxDate,
       summary.data
FROM   summary  {code}
translates to:
{code:java}
select summary.ID AS id, t3.SINGLE_DETAIL AS Detail_With_MaxDate, summary.data AS data
from summary
left join (select t2.summary_id, MAX(t2.ID) AS SINGLE_DETAIL
    FROM (select t.summary_id, t.ID
        FROM (select *
            FROM detail
            WHERE summary_id IS NOT NULL) AS t
            INNER JOIN (select summary_id, MAX(date) AS maxDate
                        FROM detail
                        WHERE summary_id IS NOT NULL
                        GROUP BY summary_id) AS t1 
            ON t.ID = t1.summary_id
            WHERE t.date = t1.maxDate) AS t2
    INNER JOIN summary AS summary0 ON t2.summary_id = summary0.ID
    GROUP BY t2.summary_id) AS t3 
ON summary.ID = t3.summary_id {code}
where the condition `ON t.ID = t1.summary_id` doesn't make any sense.

[http://sqlfiddle.com/#!9/5758a6/3]

includes testdata and the incorrect results (null for the one column).

When I manually decorrelate the query I can get the correct answer:

[http://sqlfiddle.com/#!9/5758a6/6]
{code:java}
SELECT summary.id,
       extraction.single_detail AS Detail_With_MaxDate,
       summary.data
FROM   summary
       JOIN (SELECT detail.summary_id,
                    Max(detail.id) AS SINGLE_DETAIL
             FROM   detail
                    JOIN (SELECT summary_id,
                                 Max(date) AS maxDate
                          FROM   detail
                          GROUP  BY summary_id) maxDates
                      ON detail.summary_id = maxDates.summary_id
             WHERE  detail.date = maxDates.maxdate
             GROUP  BY detail.summary_id) extraction
         ON summary.id = extraction.summary_id  {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)