uros-b commented on code in PR #59591:
URL: https://github.com/apache/spark/pull/59591#discussion_r4316120986
##########
sql/core/src/test/resources/sql-tests/inputs/join-asof-semantics.sql:
##########
@@ -321,3 +321,48 @@ FROM using_left l LEFT ASOF JOIN (using_right r LEFT JOIN
using_keys d USING (k)
SELECT l.lts, d.k
FROM using_left l LEFT ASOF JOIN (using_right r JOIN using_keys d USING (k))
MATCH_CONDITION (l.k <= d.k);
+
+-- FVT-ASOF-2-043: an ASOF JOIN with USING passes up the hidden columns of its
inputs
+SELECT l.lts, d.k
+FROM using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k);
+
+-- FVT-ASOF-2-044: a qualified star reads a hidden column from below an ASOF
JOIN with USING
+SELECT d.*
+FROM using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k);
+
+-- FVT-ASOF-2-045: a hidden column from below an ASOF JOIN with USING is a
MATCH_CONDITION operand
+SELECT l.lts, x.k
+FROM (using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k))
+ ASOF JOIN (VALUES (1), (2)) AS x(k) MATCH_CONDITION (d.k >= x.k);
+
+-- FVT-ASOF-2-046: a hidden MATCH_CONDITION column whose table is also on the
other side
+SELECT d.k, x.k
+FROM (using_right r LEFT JOIN using_keys d USING (k))
+ ASOF JOIN using_keys x MATCH_CONDITION (d.k >= x.k);
+
+-- FVT-ASOF-2-047: same as FVT-ASOF-2-045, with the hidden column's table also
on the other side
+SELECT l.lts, x.k
+FROM (using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k))
+ ASOF JOIN using_keys x MATCH_CONDITION (d.k >= x.k);
+
+-- FVT-ASOF-2-048: a JOIN above an ASOF JOIN with USING joins on a hidden
column of its inputs
+SELECT l.lts, x.k
+FROM (using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k))
+ JOIN (VALUES (1), (2), (8)) AS x(k) ON d.k = x.k;
+
+-- FVT-ASOF-2-049: a LEFT ASOF JOIN with USING makes a NOT NULL hidden column
of its inputs nullable
+SELECT l.lts, d.k
+FROM using_left l LEFT ASOF JOIN (using_right r JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k);
+
+-- FVT-ASOF-2-050: same as FVT-ASOF-2-046, with the hidden column on the right
side. The first CTE
Review Comment:
050 covers the right-side CTE case for a direct ASOF child. It does not
cover a regular JOIN that meets the hidden column below the ASOF.
Please add (both sides of the ASOF):
```
-- FVT-ASOF-2-051: a regular JOIN below the ASOF remaps a hidden column when
the same table is on the other side of that JOIN
SELECT d.k, z.k
FROM (
(using_right r LEFT JOIN using_keys d USING (k))
JOIN using_keys y ON y.k = 1
)
ASOF JOIN (VALUES (1), (2)) AS z(k)
MATCH_CONDITION (d.k >= z.k);
```
Expected: (1, 1), (2, 2). Single-pass today returns (1, 1), (2, 1), (NULL,
1).
##########
sql/core/src/test/resources/sql-tests/inputs/join-asof-semantics.sql:
##########
@@ -321,3 +321,48 @@ FROM using_left l LEFT ASOF JOIN (using_right r LEFT JOIN
using_keys d USING (k)
SELECT l.lts, d.k
FROM using_left l LEFT ASOF JOIN (using_right r JOIN using_keys d USING (k))
MATCH_CONDITION (l.k <= d.k);
+
+-- FVT-ASOF-2-043: an ASOF JOIN with USING passes up the hidden columns of its
inputs
+SELECT l.lts, d.k
+FROM using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k);
+
+-- FVT-ASOF-2-044: a qualified star reads a hidden column from below an ASOF
JOIN with USING
+SELECT d.*
+FROM using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k);
+
+-- FVT-ASOF-2-045: a hidden column from below an ASOF JOIN with USING is a
MATCH_CONDITION operand
+SELECT l.lts, x.k
+FROM (using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k))
+ ASOF JOIN (VALUES (1), (2)) AS x(k) MATCH_CONDITION (d.k >= x.k);
+
+-- FVT-ASOF-2-046: a hidden MATCH_CONDITION column whose table is also on the
other side
+SELECT d.k, x.k
+FROM (using_right r LEFT JOIN using_keys d USING (k))
+ ASOF JOIN using_keys x MATCH_CONDITION (d.k >= x.k);
+
+-- FVT-ASOF-2-047: same as FVT-ASOF-2-045, with the hidden column's table also
on the other side
+SELECT l.lts, x.k
+FROM (using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k))
+ ASOF JOIN using_keys x MATCH_CONDITION (d.k >= x.k);
+
+-- FVT-ASOF-2-048: a JOIN above an ASOF JOIN with USING joins on a hidden
column of its inputs
+SELECT l.lts, x.k
+FROM (using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k))
+ JOIN (VALUES (1), (2), (8)) AS x(k) ON d.k = x.k;
+
+-- FVT-ASOF-2-049: a LEFT ASOF JOIN with USING makes a NOT NULL hidden column
of its inputs nullable
+SELECT l.lts, d.k
+FROM using_left l LEFT ASOF JOIN (using_right r JOIN using_keys d USING (k))
+ MATCH_CONDITION (l.lts >= r.rts) USING (k);
+
+-- FVT-ASOF-2-050: same as FVT-ASOF-2-046, with the hidden column on the right
side. The first CTE
Review Comment:
048 is not this: x is VALUES, so there is no ID collision. 038 joins on
visible l.k.
If you also fix JoinResolver, add the regular-join form to using-join.sql:
```
SELECT d.k, x.k
FROM (using_right r LEFT JOIN using_keys d USING (k))
JOIN using_keys x ON d.k = x.k
```
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
