datafusion: Regression in `last_value` functionality
Describe the bug
In the Comet subproject, we have a PR where we are upgrading to use the latest DataFusion revision from main in preparation for upgrading to DataFusion 47 (https://github.com/apache/datafusion-comet/pull/1563).
After bumping the revision to b22e4d26480202247022bf2c94ba629a07e3275b, we see some tests start to fail, such as:
multiple group-by columns + single aggregate column, with nulls
2025-04-10T17:13:49.0990050Z == Results ==
2025-04-10T17:13:49.0990580Z !== Correct Answer - 2 == == Spark Answer - 2 ==
2025-04-10T17:13:49.0991090Z struct<_g1:tinyint,_g2:smallint,last(_3):int> struct<_g1:tinyint,_g2:smallint,last(_3):int>
2025-04-10T17:13:49.0991750Z [null,null,null] [null,null,null]
2025-04-10T17:13:49.0992100Z ![0,0,9] [0,0,0] (QueryTest.scala:244)
It is also possible that the error is due to other changes in Comet and not related to DataFusion. I will update this issue once I know more.
To Reproduce
No response
Expected behavior
No response
Additional context
No response
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 31 (31 by maintainers)
Yes, but in that case it would become equivalent to the window functions
FIRST_VALUE/LAST_VALUE, which is clearly defined (and has a sane execution path in DataFusion and clear semantics).SELECT FIRST_VALUE(a) OVER (PARTITION BY x ORDER BY y )Sorry for hijacking this issue @andygrove for this discussion but I think it’s important to not have features like these limit the progression of DataFusion in the future.
Sorry to have caused so much discussion.
I’m totally in favor of keeping this open and have the function (without
order by) for now matching the expectation of “first value based on what is provided to the function” or “last value based on what is provided to the function” while acknowledging it might change in the future based on implementation details.We can have a discussion for future direction elsewhere.
~I think these should work in the most performant way if no order specified~. We are not required to provide a deterministic result, and we can change the result comparison to a version which compares the results against the inclusion of the values in the ARRAY_AGG() of the same column)
If we include an
ORDER BYclause, and the column used for ordering contains no duplicate values, then the results ofFIRST_VALUEandLAST_VALUEcan be considered deterministic. (For example, the result of this query should always be deterministic😃https://github.com/apache/datafusion/blob/221df48da43eca71a65d7baeb43ae82a166862e9/datafusion/sqllogictest/test_files/group_by.slt#L2687-L2700
I believe the current documentation already makes this clear—these functions are not deterministic when no
ORDER BYis specified:https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value
The reaction I have is because the result of
first_valueandlast_valuewhen given no ordering that will make results deterministic will be not deterministic (depending on how te function is executed). If DataFusion would switch to another mode of execution or grouping implementation that doesn’t maintain same order, functions like these would break (based on what users might expect from them).As an aside, I’m not sure whether
last_value(as implemented previously) returns a stable result if no ordering is specified, even if the input is ordered.For last value, even the ordering is empty we still need to update the value.
I implemented it according to the behavior defined in this document
https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value
I confirmed that the behavior in DataFusion also changed when #15542 was merged.
Before
After