mustafasrepo commented on code in PR #9470: URL: https://github.com/apache/arrow-datafusion/pull/9470#discussion_r1515756655
########## datafusion/sqllogictest/test_files/window.slt: ########## @@ -4269,3 +4269,199 @@ LIMIT 5; 3 53 24 31 14 94 + +# Test for ignore nulls in FIRST_VALUE +statement ok +CREATE TABLE t AS VALUES (null::bigint), (3), (4); + +query I +SELECT FIRST_VALUE(column1) OVER() FROM t; +---- +NULL +NULL +NULL + +query I +SELECT FIRST_VALUE(column1) RESPECT NULLS OVER() FROM t; +---- +NULL +NULL +NULL + +query I +SELECT FIRST_VALUE(column1) IGNORE NULLS OVER() FROM t; +---- +3 +3 +3 + +statement ok +DROP TABLE t; + +# Test for ignore nulls with ORDER BY in FIRST_VALUE +statement ok +CREATE TABLE t AS VALUES (3, 4), (4, 3), (null::bigint, 1), (null::bigint, 1); + +query I +SELECT column1 FROM t ORDER BY column2; +---- +NULL +NULL +4 +3 + +query I +SELECT FIRST_VALUE(column1) OVER(ORDER BY column2) FROM t; +---- +NULL +NULL +NULL +NULL + +query I +SELECT FIRST_VALUE(column1) RESPECT NULLS OVER(ORDER BY column2) FROM t; +---- +NULL +NULL +NULL +NULL + +query I +SELECT FIRST_VALUE(column1) IGNORE NULLS OVER(ORDER BY column2) FROM t; +---- +4 +4 +4 +4 Review Comment: I would expect, result of this query to be ``` ---- NULL NULL 4 4 ``` ran it on duck db with following commands ``` CREATE TABLE t AS VALUES (3, 4), (4, 3), (null::bigint, 1), (null::bigint, 1); SELECT FIRST_VALUE(col0 ignore nulls) OVER(ORDER BY col1) FROM t; ``` duckdb produces result above. -- 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]
