Skip to content

Regression in last_value functionality #15676

Closed

Description

@andygrove

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 (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

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

Activity

  1. andygrove commented on Apr 10, 2025

    @andygrove
    MemberAuthor

    The issue starts happening with #15542, which seems to have changed the behavior of the last_value aggregate. I am still investigating.

  2. andygrove commented on Apr 10, 2025

    @andygrove
    MemberAuthor

    The following query previously produced different values for FIRST and LAST but now produces the same value for both.

    SELECT _g1, _g2, FIRST(_3), LAST(_3) FROM v GROUP BY _g1, _g2 ORDER BY _g1, _g2

    Before:

    [0,0,0,9] 
    

    After

    [0,0,0,0] 
    
  3. changed the title [-]Possible regression in aggregate query correctness[/-] [+]Regression in `last_value` functionality[/+] on Apr 10, 2025
  4. andygrove commented on Apr 10, 2025

    @andygrove
    MemberAuthor

    I confirmed that the behavior in DataFusion also changed when #15542 was merged.

    Before

    query III
    SELECT y, FIRST_VALUE(x), LAST_VALUE(x)
    FROM FOO
    GROUP BY y
    ORDER BY y;
    ----
    2 1 1
    3 2 1
    

    After

    query III
    SELECT y, FIRST_VALUE(x), LAST_VALUE(x)
    FROM FOO
    GROUP BY y
    ORDER BY y;
    ----
    2 1 1
    3 2 2
    
  5. andygrove commented on Apr 10, 2025

    @andygrove
    MemberAuthor
  6. Dandandan commented on Apr 10, 2025

    @Dandandan
    Contributor

    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).

  7. Dandandan commented on Apr 10, 2025

    @Dandandan
    Contributor

    (But I think we should of course fix this regression).

  8. andygrove commented on Apr 10, 2025

    @andygrove
    MemberAuthor

    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.

  9. UBarney commented on Apr 11, 2025

    @UBarney
    Contributor

    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.

    https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value

  10. jayzhan211 commented on Apr 11, 2025

    @jayzhan211
    Contributor

    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.

    https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value

    The doc isn't correct, we should return the last value in the group

  11. jayzhan211 commented on Apr 11, 2025

    @jayzhan211
    Contributor

    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 first last too, I guess it would be useful in some cases? 🤔

  12. jayzhan211 commented on Apr 11, 2025

    @jayzhan211
    Contributor
        fn should_update_state(
            &self,
            group_idx: usize,
            new_ordering_values: &[ScalarValue],
        ) -> Result<bool> {
            if !self.is_sets.get_bit(group_idx) {
                return Ok(true);
            }
    
            assert!(new_ordering_values.len() == self.ordering_req.len());
            let current_ordering = &self.orderings[group_idx];
            compare_rows(current_ordering, new_ordering_values, &self.sort_options).map(|x| {
                if self.pick_first_in_group {
                    x.is_gt()
                } else {
                    x.is_lt()
                }
            })
        }

    For last value, even the ordering is empty we still need to update the value.

  13. UBarney commented on Apr 11, 2025

    @UBarney
    Contributor

    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.

    https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value

    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.

  14. UBarney commented on Apr 11, 2025

    @UBarney
    Contributor

    #15681 may fix this regression

  15. 4 remaining items

  16. Dandandan commented on Apr 11, 2025

    @Dandandan
    Contributor

    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 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.

  17. UBarney commented on Apr 11, 2025

    @UBarney
    Contributor

    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:)

    CREATE TABLE first_null (
    k INT,
    val INT,
    o int
    ) as VALUES
    (0, NULL, -9),
    (0, 1, 1),
    (1, 1, 1);
    query II rowsort
    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.

    https://datafusion.apache.org/user-guide/sql/aggregate_functions.html#last-value

  18. Dandandan commented on Apr 11, 2025

    @Dandandan
    Contributor

    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

    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.

  19. jayzhan211 commented on Apr 11, 2025

    @jayzhan211
    Contributor

    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.

    FirstValue:

    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.

  20. jayzhan211 commented on Apr 11, 2025

    @jayzhan211
    Contributor

    I believe the same issue applies to nth_value and array_agg order-sensitive aggregation

  21. UBarney commented on Apr 11, 2025

    @UBarney
    Contributor

    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.

  22. berkaysynnada commented on Apr 11, 2025

    @berkaysynnada
    Contributor

    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)

  23. berkaysynnada commented on Apr 11, 2025

    @berkaysynnada
    Contributor

    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.

    That's a very aggressive action, IMO

  24. jayzhan211 commented on Apr 11, 2025

    @jayzhan211
    Contributor

    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.

  25. andygrove commented on Apr 11, 2025

    @andygrove
    MemberAuthor

    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

    Any one of these options can work.

  26. kazuyukitanimura commented on Apr 11, 2025

    @kazuyukitanimura
    Contributor

    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?

  27. andygrove commented on Apr 11, 2025

    @andygrove
    MemberAuthor

    Perhaps we can add order by for the tests?

    Spark SQL doesn't seem to support an ORDER BY clause in this context.

  28. andygrove commented on Apr 11, 2025

    @andygrove
    MemberAuthor

    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.

  29. Dandandan commented on Apr 11, 2025

    @Dandandan
    Contributor

    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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions