srielau commented on code in PR #57306: URL: https://github.com/apache/spark/pull/57306#discussion_r3625716489
########## 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: Dropped 7-011, 7-012a, 7-012b, and 7-013 with exclusion comments (parquet v1 rejects row-level DML, so the ASOF subquery never gets planned — same issue as the old 7-002/7-006/7-007 temp-view cases). Goldens regenerated. --- <!-- GITHUB_MCP_FOOTER: This attribution is automatically appended by GitHub MCP. --> _This comment was generated with [GitHub MCP](http://go/mcps)._ ########## 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: Reworded to: "same operand transform, expressed with a builtin instead of a SQL UDF". --- <!-- GITHUB_MCP_FOOTER: This attribution is automatically appended by GitHub MCP. --> _This comment was generated with [GitHub MCP](http://go/mcps)._ ########## 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: Removed the `-- Source: sql-fvt-plan/plans/asof-join.md` header from all 10 input files. --- <!-- GITHUB_MCP_FOOTER: This attribution is automatically appended by GitHub MCP. --> _This comment was generated with [GitHub MCP](http://go/mcps)._ -- 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]
