You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "John Wright (Jira)" <ji...@apache.org> on 2023/05/19 23:53:00 UTC
[jira] [Created] (CALCITE-5716) Two level nested correlated subquery translates to incorrect ON condition
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)