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)

Most upvoted comments

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.

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)

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😃

https://github.com/apache/datafusion/blob/221df48da43eca71a65d7baeb43ae82a166862e9/datafusion/sqllogictest/test_files/group_by.slt#L2687-L2700

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

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? 🤔

The reaction I have is because the result of first_value and last_value when 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).

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.

    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.

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

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