comphead commented on code in PR #22134: URL: https://github.com/apache/datafusion/pull/22134#discussion_r3669922912
########## datafusion/sqllogictest/test_files/window.slt: ########## @@ -4169,30 +4169,328 @@ select arrow_typeof(nth_value(a, 1) over ()) from (select 1 a) ---- Int64 +# === Basic LEAD/LAG with NULL default (2 rows) === + # test LEAD window function works NULL as default value query I -select lead(a, 1, null) over (order by a) from (select 1 a union all select 2 a) +WITH t(a) AS (VALUES (1), (2)) +SELECT lead(a, 1, null) OVER (ORDER BY a) FROM t ---- 2 NULL # test LAG window function works NULL as default value query I -select lag(a, 1, null) over (order by a) from (select 1 a union all select 2 a) +WITH t(a) AS (VALUES (1), (2)) +SELECT lag(a, 1, null) OVER (ORDER BY a) FROM t ---- NULL 1 + +# === Basic LEAD/LAG with column default (3 rows with a, b) === + +# test LAG with another column as default (first row falls back to b) +query II +WITH t(a, b) AS (VALUES (1, 10), (2, 20), (3, 30)) +SELECT a, lag(a, 1, b) OVER (ORDER BY a) FROM t +---- +1 10 +2 1 +3 2 + +# test LEAD with another column as default (last row falls back to b) +query II +WITH t(a, b) AS (VALUES (1, 10), (2, 20), (3, 30)) +SELECT a, lead(a, 1, b) OVER (ORDER BY a) FROM t +---- +1 2 +2 3 +3 30 + + +# === Offset 2 with column default (4 rows with a, b) === + +# test LAG with offset 2 and column default (first 2 rows fall back to b) +query II +WITH t(a, b) AS (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) +SELECT a, lag(a, 2, b) OVER (ORDER BY a) FROM t +---- +1 10 +2 20 +3 1 +4 2 + +# test LEAD with offset 2 and column default (last 2 rows fall back to b) +query II +WITH t(a, b) AS (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) +SELECT a, lead(a, 2, b) OVER (ORDER BY a) FROM t +---- +1 3 +2 4 +3 30 +4 40 + + +# === Expression defaults (4 rows with a, b) === + +# test LAG with column expression default +query II +WITH t(a, b) AS (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) +SELECT a, lag(a, 1, b / 2) OVER (ORDER BY a) FROM t +---- +1 5 +2 1 +3 2 +4 3 + +# test LAG with mixed-column expression default +query II +WITH t(a, b, c) AS ( + VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400) +) +SELECT a, lag(a, 1, b * c) OVER (ORDER BY a) FROM t +---- +1 1000 +2 1 +3 2 +4 3 + + +# === Simple expression defaults on single column (2-4 rows) === + +# test LAG with a * 2 as default +query II +WITH t(a) AS (VALUES (1), (2)) +SELECT a, lag(a, 1, a * 2) OVER (ORDER BY a) FROM t +---- +1 2 +2 1 + +# test LAG with offset 2 and a * 3 as default +query II +WITH t(a) AS (VALUES (1), (2), (3)) +SELECT a, lag(a, 2, a * 3) OVER (ORDER BY a) FROM t +---- +1 3 +2 6 +3 1 + +# test LEAD with a * 2 as default +query II +WITH t(a) AS (VALUES (1), (2), (3)) +SELECT a, lead(a, 1, a * 2) OVER (ORDER BY a) FROM t +---- +1 2 +2 3 +3 6 + +# test LEAD with offset 2 and a * 3 as default +query II +WITH t(a) AS (VALUES (1), (2), (3), (4)) +SELECT a, lead(a, 2, a * 3) OVER (ORDER BY a) FROM t +---- +1 3 +2 4 +3 9 +4 12 + + +# === Combined LAG and LEAD (3 rows) === + +# test Both lag and lead in same query with expression defaults +query III +WITH t(a) AS (VALUES (1), (2), (3)) +SELECT a, + lag(a, 1, a * 2) OVER (ORDER BY a), + lead(a, 1, a * 3) OVER (ORDER BY a) +FROM t +---- +1 2 2 +2 1 3 +3 2 9 + + +# === Multi-column expression defaults (3 rows with a, b, c) === + +# test LAG default is sum of two columns +query II +WITH t(a, b, c) AS (VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300)) +SELECT a, lag(a, 1, b + c) OVER (ORDER BY a) FROM t Review Comment: 👍 -- 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]
