You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 (apache/datafusion-comet#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
I wonder, why do we even support LAST_VALUE / FIRST_VALUE as plain aggregate functions?
I think hey don't make any sense as aggregate functions (but only as window functions).
I wonder, why do we even support LAST_VALUE / FIRST_VALUE as plain aggregate functions? I think hey don't make any sense as aggregate functions (but only as window functions).
They are valid in Spark as plain aggregates. I'm not sure if any other query engines support this use.
I implemented it according to the behavior defined in this document
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
I implemented it according to the behavior defined in this document
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
I wonder, why do we even support LAST_VALUE / FIRST_VALUE as plain aggregate functions? I think hey don't make any sense as aggregate functions (but only as window functions).
DuckDb has firstlast too, I guess it would be useful in some cases? 🤔
I implemented it according to the behavior defined in this document
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
The doc isn't correct, we should return the last value in the group
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.
If we want a deterministic result, I think we should add ORDER BY. Just like when we want deterministic output with LIMIT, we need to add ORDER BY.
Yes, but in that case it would become equivalent to the window functionsFIRST_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.
FIRST_VALUE and LAST_VALUE aggregation functions are not deterministic
If we include an ORDER BY clause, and the column used for ordering contains no duplicate values, then the results of FIRST_VALUE and LAST_VALUE can be considered deterministic.
(For example, the result of this query should always be deterministic:)
select k, first_value(val order by o) IGNORE NULLS from first_null group by k;
----
0 1
1 1
IMO we should at least clearly document this behaviour
I believe the current documentation already makes this clear—these functions are not deterministic when no ORDER BY is specified:
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
SELECT FIRST(col1), LAST(col1) FROM t
SELECT FIRST(col1), LAST(col1), col3 FROM t GROUP BY col3
Will break in the future whenever we change some internals that changes the order of batches / rows to appear in an aggregation function (not only depending on the order of which t is scanned.
So a query SELECT FIRST(col1), LAST(col1) FROM t
Can be seen as semantically equivalent to queries like the following (just giving some extreme examples to make a point):
SELECT ANY_VALUE(col1), ANY_VALUE(col1) FROM t
SELECT MIN(col1), MAX(col1) FROM t
SELECT FIRST(col1), LAST(col1) FROM (SELECT * FROM t ORDER BY RANDOM())
SELECT FIRST(col1), LAST(col1) FROM (SELECT * FROM t LIMIT 1)
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
I think the undeterministic is based on the input not order-preserved, so if the input is order-preserved, we can have deterministic result for first/last.
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
LastValue:
Returns the last element in an aggregation group (if any), based on the specified ordering.
If the input is not order-preserved, the result may be non-deterministic.
If no ordering is specified, the function returns the last value in the group.
I think the undeterministic is based on the input not order-preserved, so if the input is order-preserved, we can have deterministic result for first/last.
Just to clarify, what does "order-preserved" mean exactly? Does it only refer to a single partition with ordered input, or can the order be determined by ORDER BY fields?
If ordering is specified, it returns the first value among those "last values" with the same ordering keys, due to the performance reason
I don't understand why we should "return the first value among those "last values" with the same ordering keys". If the ordering keys are the same, the result may still be non-deterministic unless the input is ordered and in a single partition.
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)
IMO we should at least clearly document this behaviour and/or maybe either deprecate these functions (users can include said function into contrib themselves) or move them to a contrib crate.
Just to clarify, what does "order-preserved" mean exactly? Does it only refer to a single partition with ordered input, or can the order be determined by ORDER BY fields?
The input of first/last is the same as the data in data source. In this case, without ordering, the first element is "true" first value, and the last element is "true" last value.
I don't understand why we should "return the first value among those "last values" with the same ordering keys". If the ordering keys are the same, the result may still be non-deterministic unless the input is ordered and in a single partition.
This case is single partition (order-preserved) + ordering.
To be more specifically, whether the output is deterministic depends on the sort algorithm, if we have stable sort where the order is preserved for same keys, then we have deterministic output.
Sorry to cause so much work for everyone discussing this 😞
For order-preserved input, such as single-partition & single-thread, DataFusion has implemented the same behavior as Spark for the past ~1 year. In the past week, the behavior changed. It isn't necessarily "wrong," but it is a breaking change for some downstream users. Comet's test suites run the same query against Spark and Comet/DataFusion and compare the results. The tests are mostly deterministic. Upgrading to the soon-to-be DataFusion 47 causes test failures, hence I reported this issue.
In the Comet project, we now have the following choices:
Rewrite our tests for LAST to stop comparing to Spark and implement some other means to determine that the behavior is correct, and also document that Comet is not compatible with Spark in some cases
Fork the LAST implementation and maintain it in Comet
See if there are options for DataFusion to support the order-preserved case
Rewrite our tests for LAST to stop comparing to Spark and implement some other means to determine that the behavior is correct, and also document that Comet is not compatible with Spark in some cases
Perhaps we can add order by for the tests?
Fork the LAST implementation and maintain it in Comet
What is the way for forking? Any downside like performance to make it deterministic? If we can do this in the forked version without downside, we can also do the same change in DataFusion itself so that we can keep the previous behavior?
I'm ok with closing this issue since the behavior of first/last aggregates without explicit ordering is not generally deterministic. We will figure out an approach in Comet. Thanks for all of the feedback and 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.
Activity
andygrove commentedon Apr 10, 2025
The issue starts happening with #15542, which seems to have changed the behavior of the
last_valueaggregate. I am still investigating.andygrove commentedon Apr 10, 2025
The following query previously produced different values for
FIRSTandLASTbut now produces the same value for both.Before:
After
[-]Possible regression in aggregate query correctness[/-][+]Regression in `last_value` functionality[/+]andygrove commentedon Apr 10, 2025
I confirmed that the behavior in DataFusion also changed when #15542 was merged.
Before
After
andygrove commentedon Apr 10, 2025
@UBarney fyi
Dandandan commentedon Apr 10, 2025
I wonder, why do we even support LAST_VALUE / FIRST_VALUE as plain aggregate functions?
I think hey don't make any sense as aggregate functions (but only as window functions).
Dandandan commentedon Apr 10, 2025
(But I think we should of course fix this regression).
andygrove commentedon Apr 10, 2025
They are valid in Spark as plain aggregates. I'm not sure if any other query engines support this use.
UBarney commentedon Apr 11, 2025
I implemented it according to the behavior defined in this document
https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value
jayzhan211 commentedon Apr 11, 2025
The doc isn't correct, we should return the last value in the group
jayzhan211 commentedon Apr 11, 2025
DuckDb has
firstlasttoo, I guess it would be useful in some cases? 🤔jayzhan211 commentedon Apr 11, 2025
For last value, even the ordering is empty we still need to update the value.
UBarney commentedon Apr 11, 2025
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.UBarney commentedon Apr 11, 2025
#15681 may fix this regression
4 remaining items
Dandandan commentedon Apr 11, 2025
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.
UBarney commentedon Apr 11, 2025
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:)
datafusion/datafusion/sqllogictest/test_files/group_by.slt
Lines 2687 to 2700 in 221df48
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
Dandandan commentedon Apr 11, 2025
Thanks @UBarney, nice to see it is already documented.
So to give another example, tests with queries like the following in DataFusion Comet:
https://github.com/apache/datafusion-comet/blob/fdaec64fd313954ae788413ec02cf652cfa570b9/spark/src/test/scala/org/apache/comet/exec/CometAggregateSuite.scala#L1052
Will break in the future whenever we change some internals that changes the order of batches / rows to appear in an aggregation function (not only depending on the order of which t is scanned.
So a query
SELECT FIRST(col1), LAST(col1) FROM tCan be seen as semantically equivalent to queries like the following (just giving some extreme examples to make a point):
jayzhan211 commentedon Apr 11, 2025
I think the undeterministic is based on the input not order-preserved, so if the input is order-preserved, we can have deterministic result for first/last.
LastValue:
Returns the last element in an aggregation group (if any), based on the specified ordering.
If the input is not order-preserved, the result may be non-deterministic.
If no ordering is specified, the function returns the last value in the group.
If ordering is specified, it returns the first value among those "last values" with the same ordering keys, due to the performance reason, See Last Accumulator
update_batchdoesn't take the last value if the order by value are equals #14232FirstValue:
Returns the first element in an aggregation group (if any), based on the specified ordering.
If the input is not order-preserved, the result may be non-deterministic.
If no ordering is specified, the function returns the first value in the group.
If ordering is specified, it returns the first value.
However, I agree order-preserved is less common, so maybe these functions are not that useful at all.
jayzhan211 commentedon Apr 11, 2025
I believe the same issue applies to
nth_valueandarray_aggorder-sensitive aggregationUBarney commentedon Apr 11, 2025
Just to clarify, what does "order-preserved" mean exactly? Does it only refer to a single partition with ordered input, or can the order be determined by ORDER BY fields?
I don't understand why we should "return the first value among those "last values" with the same ordering keys". If the ordering keys are the same, the result may still be non-deterministic unless the input is ordered and in a single partition.
berkaysynnada commentedon Apr 11, 2025
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)berkaysynnada commentedon Apr 11, 2025
That's a very aggressive action, IMO
jayzhan211 commentedon Apr 11, 2025
The input of first/last is the same as the data in data source. In this case, without ordering, the first element is "true" first value, and the last element is "true" last value.
This case is single partition (order-preserved) + ordering.
To be more specifically, whether the output is deterministic depends on the sort algorithm, if we have stable sort where the order is preserved for same keys, then we have deterministic output.
andygrove commentedon Apr 11, 2025
Sorry to cause so much work for everyone discussing this 😞
For order-preserved input, such as single-partition & single-thread, DataFusion has implemented the same behavior as Spark for the past ~1 year. In the past week, the behavior changed. It isn't necessarily "wrong," but it is a breaking change for some downstream users. Comet's test suites run the same query against Spark and Comet/DataFusion and compare the results. The tests are mostly deterministic. Upgrading to the soon-to-be DataFusion 47 causes test failures, hence I reported this issue.
In the Comet project, we now have the following choices:
LASTto stop comparing to Spark and implement some other means to determine that the behavior is correct, and also document that Comet is not compatible with Spark in some casesLASTimplementation and maintain it in CometAny one of these options can work.
kazuyukitanimura commentedon Apr 11, 2025
Perhaps we can add order by for the tests?
What is the way for forking? Any downside like performance to make it deterministic? If we can do this in the forked version without downside, we can also do the same change in DataFusion itself so that we can keep the previous behavior?
andygrove commentedon Apr 11, 2025
Spark SQL doesn't seem to support an
ORDER BYclause in this context.andygrove commentedon Apr 11, 2025
I'm ok with closing this issue since the behavior of first/last aggregates without explicit ordering is not generally deterministic. We will figure out an approach in Comet. Thanks for all of the feedback and discussion.
Dandandan commentedon Apr 11, 2025
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.