mustafasrepo commented on code in PR #9470: URL: https://github.com/apache/arrow-datafusion/pull/9470#discussion_r1519579512
########## datafusion/sqllogictest/test_files/window.slt: ########## @@ -4307,3 +4307,275 @@ select lag(a) over () as x1 NULL NULL b + +# 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, 2), (5, 5), (6, 6); + +query II +SELECT column1, column2 FROM t ORDER BY column2; +---- +NULL 1 +NULL 2 +4 3 +3 4 +5 5 +6 6 + +query II +SELECT FIRST_VALUE(column1) OVER(ORDER BY column2), column2 FROM t; +---- +NULL 1 +NULL 2 +NULL 3 +NULL 4 +NULL 5 +NULL 6 + +query II +SELECT FIRST_VALUE(column1) RESPECT NULLS OVER(ORDER BY column2), column2 FROM t; +---- +NULL 1 +NULL 2 +NULL 3 +NULL 4 +NULL 5 +NULL 6 + +query II +SELECT FIRST_VALUE(column1) IGNORE NULLS OVER(ORDER BY column2), column2 FROM t; +---- +NULL 1 +NULL 2 +4 3 +4 4 +4 5 +4 6 + +query II +SELECT FIRST_VALUE(column1)OVER(ORDER BY column2 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING), column2 FROM t; +---- +NULL 1 +NULL 2 +NULL 3 +4 4 +3 5 +5 6 + +query II +SELECT FIRST_VALUE(column1) IGNORE NULLS OVER(ORDER BY column2 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING), column2 FROM t; +---- +NULL 1 +4 2 +4 3 +4 4 +3 5 +5 6 + +statement ok +DROP TABLE t; + +# Test for ignore nulls with ORDER BY in FIRST_VALUE with all NULL values +statement ok +CREATE TABLE t AS VALUES (null::bigint, 4), (null::bigint, 3), (null::bigint, 1), (null::bigint, 2); + +query II +SELECT FIRST_VALUE(column1) OVER(ORDER BY column2), column2 FROM t; +---- +NULL 1 +NULL 2 +NULL 3 +NULL 4 + +query II +SELECT FIRST_VALUE(column1) RESPECT NULLS OVER(ORDER BY column2), column2 FROM t; +---- +NULL 1 +NULL 2 +NULL 3 +NULL 4 + +query II +SELECT FIRST_VALUE(column1) IGNORE NULLS OVER(ORDER BY column2), column2 FROM t; +---- +NULL 1 +NULL 2 +NULL 3 +NULL 4 + +statement ok +DROP TABLE t; + +# Test for ignore nulls in LAST_VALUE +statement ok +CREATE TABLE t AS VALUES (1), (3), (null::bigint); + +query I +SELECT LAST_VALUE(column1) OVER() FROM t; +---- +NULL +NULL +NULL + +query I +SELECT LAST_VALUE(column1) RESPECT NULLS OVER() FROM t; +---- +NULL +NULL +NULL + +query I +SELECT LAST_VALUE(column1) IGNORE NULLS OVER() FROM t; +---- +3 +3 +3 + +statement ok +DROP TABLE t; + +# Test for ignore nulls with ORDER BY in LAST_VALUE +statement ok +CREATE TABLE t AS VALUES (3, 4), (4, 3), (null::bigint, 1), (null::bigint, 2), (5, 5), (6, 6); + +query II +SELECT column1, column2 FROM t ORDER BY column2 DESC NULLS LAST; +---- +6 6 +5 5 +3 4 +4 3 +NULL 2 +NULL 1 + +query II +SELECT LAST_VALUE(column1) OVER(ORDER BY column2 DESC NULLS LAST), column2 FROM t; +---- +6 6 +5 5 +3 4 +4 3 +NULL 2 +NULL 1 + +# query II +# SELECT LAST_VALUE(column1) IGNORE NULLS OVER(ORDER BY column2 DESC NULLS LAST), column2 FROM t; Review Comment: I examined this problem. It seemed like it is related to `memoization`. Sent a commit to fix this test case (Uncommented the test also). Thanks @huaxingao for triggering this error. -- 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]
