julianhyde commented on code in PR #4763: URL: https://github.com/apache/calcite/pull/4763#discussion_r2726635140
########## core/src/test/resources/sql/sub-query.iq: ########## @@ -8467,5 +8467,241 @@ ON t2.a = foo.a +---+---+---+ (1 row) +!ok + +# [CALCITE-6504] JOIN_SUB_QUERY_TO_CORRELATE/Join SubQueryRemoveRule produces incorrect tree when using correlated sub-query in on clause of equi-join +# Case 1: Correlated scalar subquery in ON clause of INNER JOIN +WITH Review Comment: since this is a test case for a bug, it would be useful to say how the result was different (wrong) before the bug was fixed. ########## core/src/test/resources/sql/sub-query.iq: ########## @@ -8467,5 +8467,241 @@ ON t2.a = foo.a +---+---+---+ (1 row) +!ok + +# [CALCITE-6504] JOIN_SUB_QUERY_TO_CORRELATE/Join SubQueryRemoveRule produces incorrect tree when using correlated sub-query in on clause of equi-join Review Comment: line is too long ########## core/src/test/resources/sql/sub-query.iq: ########## @@ -8467,5 +8467,241 @@ ON t2.a = foo.a +---+---+---+ (1 row) +!ok + +# [CALCITE-6504] JOIN_SUB_QUERY_TO_CORRELATE/Join SubQueryRemoveRule produces incorrect tree when using correlated sub-query in on clause of equi-join +# Case 1: Correlated scalar subquery in ON clause of INNER JOIN +WITH + A(id) AS (VALUES (101), (102)), + C(id) AS (VALUES (301), (302)), + B(id, fk_A, fk_C) AS (VALUES (201, 101, 301), (202, 101, 999), (203, 999, 301), (204, 999, 999)) +SELECT A.id, C.id +FROM A +INNER JOIN C +ON ( + SELECT min(B.fk_C) + FROM B + WHERE A.id = B.fk_A +) = C.id; ++-----+-----+ +| ID | ID | ++-----+-----+ +| 101 | 301 | ++-----+-----+ +(1 row) + +!ok + +# Case 1 (Verification): Manual rewrite of Case 1 using a derived table +WITH + A(id) AS (VALUES (101), (102)), + C(id) AS (VALUES (301), (302)), + B(id, fk_A, fk_C) AS (VALUES (201, 101, 301), (202, 101, 999), (203, 999, 301), (204, 999, 999)) +SELECT A.id, C.id +FROM ( + SELECT + *, + (SELECT min(B.fk_C) FROM B WHERE A.id = B.fk_A) AS fk_C + FROM A +) AS A +INNER JOIN C +ON fk_c = C.id; ++-----+-----+ +| ID | ID | ++-----+-----+ +| 101 | 301 | ++-----+-----+ +(1 row) + +!ok + +# Case 2: Correlated scalar subquery in ON clause of LEFT JOIN Review Comment: I would repeat the jira case number for every case (1 through 5). At some point these 5 cases will be in the middle of the file, not at the end, and it will be difficult to see where the next jira case begins. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
