Yicong-Huang commented on code in PR #57306: URL: https://github.com/apache/spark/pull/57306#discussion_r3625624789
########## sql/core/src/test/resources/sql-tests/inputs/join-asof-containers.sql: ########## @@ -0,0 +1,192 @@ +-- FVT Category 7: ASOF JOIN container integration (FVT-ASOF-7-*) +-- Source: sql-fvt-plan/plans/asof-join.md + +--SET spark.sql.join.asofJoin.enabled=true + +CREATE OR REPLACE TEMP VIEW trades(trade_time, symbol, quantity) AS + VALUES (TIMESTAMP '2026-06-29 10:00:05', 'AAPL', 100), + (TIMESTAMP '2026-06-29 10:00:11', 'AAPL', 200), + (TIMESTAMP '2026-06-29 10:00:12', 'MSFT', 50), + (TIMESTAMP '2026-06-29 09:59:59', 'GOOG', 30); + +CREATE OR REPLACE TEMP VIEW quotes(quote_time, symbol, bid_price) AS + VALUES (TIMESTAMP '2026-06-29 10:00:00', 'AAPL', 180.10), + (TIMESTAMP '2026-06-29 10:00:07', 'AAPL', 180.15), + (TIMESTAMP '2026-06-29 10:00:10', 'AAPL', 180.20), + (TIMESTAMP '2026-06-29 10:00:08', 'MSFT', 420.50); + +-- FVT-ASOF-7-001: temp view hosting ASOF +CREATE OR REPLACE TEMP VIEW asof_matched_v AS +SELECT t.trade_time, q.bid_price +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol; + +SELECT count(*) AS cnt FROM asof_matched_v; + +-- Permanent backing tables for cases that cannot reference temp views (7-002, 7-006, 7-007). +DROP TABLE IF EXISTS asof_perm_trades; +DROP TABLE IF EXISTS asof_perm_quotes; +CREATE TABLE asof_perm_trades USING parquet AS SELECT * FROM trades; +CREATE TABLE asof_perm_quotes USING parquet AS SELECT * FROM quotes; + +-- FVT-ASOF-7-002: permanent view hosting ASOF +CREATE OR REPLACE VIEW asof_perm_v AS +SELECT t.trade_time, q.bid_price +FROM asof_perm_trades t ASOF JOIN asof_perm_quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol; + +SELECT count(*) AS cnt FROM asof_perm_v; + +-- FVT-ASOF-7-003: deferred — materialized view / streaming table (platform follow-up) + +-- FVT-ASOF-7-004: regular CTE +WITH matched AS ( + SELECT t.trade_time, t.symbol, q.bid_price + FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol +) +SELECT count(*) AS cnt FROM matched; + +-- FVT-ASOF-7-005: recursive CTE with ASOF in recursive leg +WITH RECURSIVE chain AS ( + SELECT trade_time AS ts, symbol, 1 AS depth + FROM trades + WHERE symbol = 'AAPL' AND trade_time = TIMESTAMP '2026-06-29 10:00:05' + UNION ALL + SELECT q.quote_time, c.symbol, c.depth + 1 + FROM chain c ASOF JOIN quotes q + MATCH_CONDITION (c.ts >= q.quote_time) + ON c.symbol = q.symbol + WHERE c.depth < 2 +) +SELECT count(*) AS cnt FROM chain; + +-- FVT-ASOF-7-006: scalar SQL UDF wrapping ASOF count +CREATE OR REPLACE FUNCTION asof_match_count() RETURNS INT RETURN ( + SELECT count(*) + FROM asof_perm_trades t ASOF JOIN asof_perm_quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol +); + +SELECT asof_match_count() AS cnt; + +-- FVT-ASOF-7-007: SQL table function returning ASOF rows +CREATE OR REPLACE FUNCTION asof_matches() +RETURNS TABLE (trade_time TIMESTAMP, bid_price DOUBLE) +RETURN + SELECT t.trade_time, q.bid_price + FROM asof_perm_trades t ASOF JOIN asof_perm_quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol; + +SELECT count(*) AS cnt FROM asof_matches(); + +-- FVT-ASOF-7-008: excluded — CREATE PROCEDURE is not supported in Spark SQL + +-- FVT-ASOF-7-009: CTAS from ASOF result +DROP TABLE IF EXISTS asof_ctas_tgt; +CREATE TABLE asof_ctas_tgt USING parquet AS +SELECT t.trade_time, t.symbol, q.bid_price +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol; + +SELECT count(*) AS cnt FROM asof_ctas_tgt; + +-- FVT-ASOF-7-010: INSERT INTO from ASOF +DROP TABLE IF EXISTS asof_insert_tgt; +CREATE TABLE asof_insert_tgt (trade_time TIMESTAMP, symbol STRING, bid_price DOUBLE) USING parquet; + +INSERT INTO asof_insert_tgt +SELECT t.trade_time, t.symbol, q.bid_price +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol; + +SELECT count(*) AS cnt FROM asof_insert_tgt; + +-- FVT-ASOF-7-011: MERGE USING ASOF subquery +DROP TABLE IF EXISTS asof_merge_tgt; +CREATE TABLE asof_merge_tgt (symbol STRING, trade_time TIMESTAMP, bid_price DOUBLE) USING parquet; + +MERGE INTO asof_merge_tgt AS tgt Review Comment: These DML container cases (7-011 MERGE, and 7-012a/7-012b UPDATE, 7-013 DELETE below) target `USING parquet` tables, which reject row-level DML. The goldens capture `UNSUPPORTED_FEATURE.TABLE_OPERATION` thrown at the DML layer, so the ASOF subquery is never planned or evaluated -- the case never exercises ASOF-in-container. This is the same non-exercising shape you already fixed for 7-002/7-006/7-007, and the permanent-table change doesn't help here (parquet is v1; no row-level DML). Suggest dropping these cases or restructuring so the ASOF subquery is analyzed/executed independently of the unsupported DML op. ########## sql/core/src/test/resources/sql-tests/inputs/join-asof-expressions.sql: ########## @@ -0,0 +1,97 @@ +-- FVT Category 8: ASOF JOIN expression integration (FVT-ASOF-8-*) +-- Source: sql-fvt-plan/plans/asof-join.md + +--SET spark.sql.join.asofJoin.enabled=true + +CREATE OR REPLACE TEMP VIEW trades(trade_time, symbol, quantity) AS + VALUES (TIMESTAMP '2026-06-29 10:00:05', 'AAPL', 100), + (TIMESTAMP '2026-06-29 10:00:11', 'AAPL', 200), + (TIMESTAMP '2026-06-29 10:00:12', 'MSFT', 50), + (TIMESTAMP '2026-06-29 09:59:59', 'GOOG', 30); + +CREATE OR REPLACE TEMP VIEW quotes(quote_time, symbol, bid_price) AS + VALUES (TIMESTAMP '2026-06-29 10:00:00', 'AAPL', 180.10), + (TIMESTAMP '2026-06-29 10:00:07', 'AAPL', 180.15), + (TIMESTAMP '2026-06-29 10:00:10', 'AAPL', 180.20), + (TIMESTAMP '2026-06-29 10:00:08', 'MSFT', 420.50); + +-- FVT-ASOF-8-001: window function on ASOF result +SELECT t.trade_time, t.symbol, q.bid_price, + row_number() OVER (PARTITION BY t.symbol ORDER BY t.trade_time) AS rn +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (t.trade_time >= q.quote_time) + ON t.symbol = q.symbol +ORDER BY t.symbol, t.trade_time; + +-- FVT-ASOF-8-002: aggregate over ASOF — covered by FVT-ASOF-6-003 + +-- FVT-ASOF-8-003: deterministic function in MATCH_CONDITION operand +SELECT t.symbol, q.bid_price +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (year(t.trade_time) >= year(q.quote_time)) + ON t.symbol = q.symbol +ORDER BY t.trade_time; + +-- FVT-ASOF-8-004: CAST inside MATCH_CONDITION operand +SELECT t.k, r.k AS matched_k +FROM VALUES (100) AS t(k) ASOF JOIN VALUES (99), (101) AS r(k) + MATCH_CONDITION (CAST(t.k AS STRING) >= CAST(r.k AS STRING)); + +-- FVT-ASOF-8-005: CASE inside MATCH_CONDITION operand +SELECT t.symbol, q.bid_price +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION ( + CASE WHEN t.symbol = 'MSFT' THEN t.trade_time - INTERVAL 1 HOUR + ELSE t.trade_time END >= q.quote_time) + ON t.symbol = q.symbol +ORDER BY t.trade_time; + +-- FVT-ASOF-8-006: deterministic SQL UDF rejected in MATCH_CONDITION (UNSUPPORTED_SQL_UDF_USAGE) +CREATE OR REPLACE FUNCTION shift_ts(ts TIMESTAMP) RETURNS TIMESTAMP +RETURN ts - INTERVAL 1 HOUR; + +SELECT count(*) AS cnt +FROM trades t ASOF JOIN quotes q + MATCH_CONDITION (shift_ts(t.trade_time) >= q.quote_time) + ON t.symbol = q.symbol; + +-- FVT-ASOF-8-006a: builtin interval transform (same semantics as 8-006 without SQL UDF) Review Comment: "same semantics as 8-006" is inaccurate: 8-006 raises UNSUPPORTED_SQL_UDF_USAGE (analysis error) while this case succeeds (cnt=0) -- opposite outcomes. Reword to something like "same operand transform, expressed with a builtin instead of a SQL UDF". ########## sql/core/src/test/resources/sql-tests/inputs/join-asof-grammar.sql: ########## @@ -0,0 +1,135 @@ +-- FVT Category 1: ASOF JOIN grammar coverage (FVT-ASOF-1-*) +-- Source: sql-fvt-plan/plans/asof-join.md Review Comment: This `-- Source: sql-fvt-plan/plans/asof-join.md` header (present in all 10 input files) points to a doc not in the repo and isn't an existing OSS sql-tests convention -- it's invisible to OSS readers once merged. Suggest dropping it from all 10 files. -- 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]
