kosiew commented on code in PR #26533: URL: https://github.com/apache/datafusion/pull/26533#discussion_r4335066997
########## datafusion/sqllogictest/test_files/window.slt: ########## @@ -4821,6 +4821,90 @@ NULL 1 statement ok DROP TABLE t; +# Test for ignore nulls in LAST_VALUE, FIRST_VALUE and NTH_VALUE over partitions that span many +# batches, with runs of NULLs that cross several words of the validity bitmap. Partition 0 +# alternates NULLs, partition 1 has runs of 497 NULLs, partition 2 is all NULLs and partition 3 +# has none. `v` increases with `i` in every partition, so the last (first) non-NULL value of a +# frame is also its maximum (minimum), which `max` (`min`) computes independently. +statement ok +set datafusion.execution.batch_size = 100; + +statement ok +CREATE TABLE ignore_nulls_t AS +SELECT i % 4 AS p, i, + CASE i % 4 + WHEN 0 THEN CASE WHEN (i / 4) % 2 = 0 THEN i END + WHEN 1 THEN CASE WHEN (i / 4) % 500 < 3 THEN i END + WHEN 3 THEN i + END AS v +FROM (SELECT value AS i FROM generate_series(1, 30000)); + +query IIII +SELECT count(lv), sum(CASE WHEN lv IS DISTINCT FROM mx THEN 1 ELSE 0 END), + count(fv), sum(CASE WHEN fv IS DISTINCT FROM mn THEN 1 ELSE 0 END) +FROM (SELECT + LAST_VALUE(v) IGNORE NULLS OVER (PARTITION BY p ORDER BY i) AS lv, + max(v) OVER (PARTITION BY p ORDER BY i) AS mx, + FIRST_VALUE(v) IGNORE NULLS OVER (PARTITION BY p ORDER BY i) AS fv, + min(v) OVER (PARTITION BY p ORDER BY i) AS mn +FROM ignore_nulls_t); +---- +22499 0 22499 0 + +query IIII +SELECT count(lv), sum(CASE WHEN lv IS DISTINCT FROM mx THEN 1 ELSE 0 END), + count(fv), sum(CASE WHEN fv IS DISTINCT FROM mn THEN 1 ELSE 0 END) +FROM (SELECT + LAST_VALUE(v) IGNORE NULLS OVER ( + PARTITION BY p ORDER BY i ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW) AS lv, + max(v) OVER ( + PARTITION BY p ORDER BY i ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW) AS mx, + FIRST_VALUE(v) IGNORE NULLS OVER ( + PARTITION BY p ORDER BY i ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW) AS fv, + min(v) OVER ( + PARTITION BY p ORDER BY i ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW) AS mn +FROM ignore_nulls_t); +---- +22499 0 22499 0 + +query IIII +SELECT count(n1), sum(CASE WHEN n1 IS DISTINCT FROM mn THEN 1 ELSE 0 END), + count(nl), sum(CASE WHEN nl IS DISTINCT FROM mx THEN 1 ELSE 0 END) +FROM (SELECT + NTH_VALUE(v, 1) IGNORE NULLS OVER (PARTITION BY p ORDER BY i) AS n1, + min(v) OVER (PARTITION BY p ORDER BY i) AS mn, + NTH_VALUE(v, -1) IGNORE NULLS OVER (PARTITION BY p ORDER BY i) AS nl, Review Comment: Optional: Could we add a sliding-frame SQL test for NTH_VALUE(v, -65) IGNORE NULLS with fewer than, exactly, and more than 65 valid rows and a nonzero frame start? The helper already tests rank 65, but this would verify the full evaluator path against independently calculated expected values. -- 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]
