Skip to content

Commit 18b1e35

Browse files
u70b3claude
andauthored
fix: grouped first_value/last_value FILTER excludes NULL predicate rows (#23707)
## Which issue does this PR close? - Closes #22666 ## Rationale for this change Under SQL aggregate `FILTER` semantics, a row passes only when the predicate evaluates to `true`; rows where the predicate is `null` must be excluded. Grouped `first_value` / `last_value` checked only `BooleanArray::value(idx)`, without checking validity, so a NULL predicate row whose underlying value bit is set (as produced by comparison kernels, e.g. `null::int < 1`) was treated as passing: ```sql SELECT g, first_value(a ORDER BY a) FILTER (WHERE b < 1) AS fv FROM (VALUES (0, 10, CAST(NULL AS INT)), (0, 20, 2)) AS t(g, a, b) GROUP BY g; -- returned fv = 10, must return fv = NULL -- (row 1: b < 1 is NULL; row 2: b < 1 is FALSE — no row satisfies `true`) ``` ## What changes are included in this PR? `datafusion/functions-aggregate/src/first_last.rs`, in `FirstLastGroupsAccumulator::get_filtered_extreme_of_each_group` (shared by `first_value` and `last_value`, and by both the `update_batch` and `merge_batch` paths): - `passed_filter` now requires `is_valid(idx) && value(idx)` (the `Some(true)` semantics), matching the convention already used by `variance.rs` / `correlation.rs`. - The `is_set_arr` read gets the same validity check. This is *not* only an internal bitmap: `convert_to_state` stores the user FILTER clause (including its nulls) in the last state column, so on the merge path (e.g. skip-partial-aggregation) NULL predicate rows were likewise treated as set. Verified with a forced skip-partial run (100k unique groups, all-NULL predicates): 83,616 groups were incorrectly assigned non-NULL values before the fix, 0 after. For genuine internal bitmaps (no nulls) the added check is trivially true, so behavior there is unchanged. Regression coverage: - sqllogictest (`aggregate.slt`): the issue reproducer, the `last_value` counterpart, mixed TRUE/FALSE/NULL predicates, all-TRUE and no-FILTER controls, the (already correct) non-grouped path, and a window-function no-regression case. - Unit tests: `test_group_acc_filter_null_predicate` (update path) and `test_group_acc_merge_null_is_set` (merge path via `convert_to_state` → `merge_batch`), both constructing `BooleanArray`s whose null slots carry a set value bit. ## Are these changes tested? Yes — see above. Verified `./dev/rust_lint.sh`, `cargo test -p datafusion-functions-aggregate --lib`, the `aggregate`/`window` sqllogictest files, and `datafusion-cli` end-to-end (grouped first/last_value now return NULL for the issue reproducer; mixed-predicate and non-grouped results unchanged). Also audited the rest of `functions-aggregate` for the same validity-blind pattern: shared helpers (`nulls.rs::filter_to_validity`, `accumulate.rs`, `prim_op.rs`, `count.rs`, `array_agg.rs`, `variance.rs`, `correlation.rs`) already handle validity correctly, and non-grouped paths pre-filter with arrow's `filter` kernel (which drops NULL predicate rows), so no other aggregate needs changes. ## Performance `functions-aggregate/benches/first_last.rs` was run against `main`. The added checks are one validity-bit test per row on the grouped path; measured deltas were within the machine's noise floor (±5% on unchanged `filter=false` cases). A variant hoisting the null check out of the row loop showed no measurable benefit beyond noise, so the simple idiomatic form is kept. ## Are there any user-facing changes? Only the bug fix: grouped `first_value`/`last_value` with a nullable `FILTER` predicate now correctly exclude NULL-predicate rows, matching SQL semantics and the behavior of other aggregates. No API or configuration changes. --------- Co-authored-by: Claude <[email protected]>
1 parent f40d99a commit 18b1e35

2 files changed

Lines changed: 185 additions & 2 deletions

File tree

‎datafusion/functions-aggregate/src/first_last.rs‎

Lines changed: 122 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -555,8 +555,15 @@ impl<S: ValueState> FirstLastGroupsAccumulator<S> {
555555
for (idx_in_val, group_idx) in group_indices.iter().enumerate() {
556556
let group_idx = *group_idx;
557557

558-
let passed_filter = opt_filter.is_none_or(|x| x.value(idx_in_val));
559-
let is_set = is_set_arr.is_none_or(|x| x.value(idx_in_val));
558+
// A row passes the FILTER clause only when the predicate is
559+
// `true`; rows whose predicate evaluates to `null` are excluded.
560+
let passed_filter =
561+
opt_filter.is_none_or(|x| x.is_valid(idx_in_val) && x.value(idx_in_val));
562+
// `is_set_arr` carries the user FILTER clause (including its
563+
// nulls) when the state was produced by `convert_to_state`, so
564+
// the validity check is required here as well (#22666).
565+
let is_set =
566+
is_set_arr.is_none_or(|x| x.is_valid(idx_in_val) && x.value(idx_in_val));
560567

561568
if !passed_filter || !is_set {
562569
continue;
@@ -1415,6 +1422,7 @@ mod tests {
14151422

14161423
use arrow::{
14171424
array::{BooleanArray, Int64Array, ListArray, PrimitiveArray, StringArray},
1425+
buffer::NullBuffer,
14181426
compute::SortOptions,
14191427
datatypes::Schema,
14201428
};
@@ -1773,6 +1781,118 @@ mod tests {
17731781
Ok(())
17741782
}
17751783

1784+
/// Rows whose FILTER predicate evaluates to `null` must not pass the
1785+
/// filter, even when the underlying value bit at the null slot is `true`
1786+
/// (#22666).
1787+
#[test]
1788+
fn test_group_acc_filter_null_predicate() -> Result<()> {
1789+
let schema = Arc::new(Schema::new(vec![
1790+
Field::new("a", DataType::Int64, true),
1791+
Field::new("c", DataType::Int64, true),
1792+
]));
1793+
1794+
let sort_keys = [PhysicalSortExpr {
1795+
expr: col("c", &schema).unwrap(),
1796+
options: SortOptions::default(),
1797+
}];
1798+
1799+
let mut group_acc = FirstLastGroupsAccumulator::try_new(
1800+
PrimitiveValueState::<Int64Type>::new(DataType::Int64),
1801+
sort_keys.into(),
1802+
true,
1803+
&[DataType::Int64],
1804+
true,
1805+
)?;
1806+
1807+
let val_with_orderings: Vec<ArrayRef> = vec![
1808+
Arc::new(Int64Array::from(vec![10, 20, 30])),
1809+
Arc::new(Int64Array::from(vec![10, 20, 30])),
1810+
];
1811+
1812+
// Row 0: predicate is null (but its value bit is true, as produced by
1813+
// kernels such as `b < 1` when the null slot's underlying value is 0)
1814+
// Row 1: predicate is false
1815+
// Row 2: predicate is true
1816+
let filter = BooleanArray::new(
1817+
BooleanBuffer::from(vec![false, true, false, true]),
1818+
Some(NullBuffer::from(BooleanBuffer::from(vec![
1819+
true, false, true, true,
1820+
]))),
1821+
)
1822+
.slice(1, 3);
1823+
assert_eq!(filter.offset(), 1);
1824+
1825+
group_acc.update_batch(&val_with_orderings, &[0, 0, 1], Some(&filter), 2)?;
1826+
1827+
let binding = group_acc.evaluate(EmitTo::All)?;
1828+
let eval_result = binding.as_any().downcast_ref::<Int64Array>().unwrap();
1829+
1830+
// Group 0 has no row with a `true` predicate, so it must stay unset.
1831+
// Group 1 takes the only row with a `true` predicate.
1832+
let expect: PrimitiveArray<Int64Type> = Int64Array::from(vec![None, Some(30)]);
1833+
assert_eq!(eval_result, &expect);
1834+
1835+
Ok(())
1836+
}
1837+
1838+
/// `convert_to_state` stores the user FILTER clause (including its nulls)
1839+
/// in the `is_set` state column, so `merge_batch` must not treat a null
1840+
/// `is_set` entry with a set value bit as "is set" (#22666).
1841+
#[test]
1842+
fn test_group_acc_merge_null_is_set() -> Result<()> {
1843+
let schema = Arc::new(Schema::new(vec![
1844+
Field::new("a", DataType::Int64, true),
1845+
Field::new("c", DataType::Int64, true),
1846+
]));
1847+
1848+
let sort_keys = [PhysicalSortExpr {
1849+
expr: col("c", &schema).unwrap(),
1850+
options: SortOptions::default(),
1851+
}];
1852+
1853+
let group_acc = FirstLastGroupsAccumulator::try_new(
1854+
PrimitiveValueState::<Int64Type>::new(DataType::Int64),
1855+
sort_keys.clone().into(),
1856+
true,
1857+
&[DataType::Int64],
1858+
true,
1859+
)?;
1860+
1861+
let val_with_orderings: Vec<ArrayRef> = vec![
1862+
Arc::new(Int64Array::from(vec![10, 20])),
1863+
Arc::new(Int64Array::from(vec![10, 20])),
1864+
];
1865+
1866+
// Same null-with-set-value-bit filter as above, carried into the state
1867+
let filter = BooleanArray::new(
1868+
BooleanBuffer::from(vec![true, true]),
1869+
Some(NullBuffer::from(BooleanBuffer::from(vec![false, true]))),
1870+
);
1871+
1872+
let state = group_acc.convert_to_state(&val_with_orderings, Some(&filter))?;
1873+
assert_eq!(state.len(), 3);
1874+
1875+
let mut merging_acc = FirstLastGroupsAccumulator::try_new(
1876+
PrimitiveValueState::<Int64Type>::new(DataType::Int64),
1877+
sort_keys.into(),
1878+
true,
1879+
&[DataType::Int64],
1880+
true,
1881+
)?;
1882+
1883+
merging_acc.merge_batch(&state, &[0, 0], 1)?;
1884+
1885+
let binding = merging_acc.evaluate(EmitTo::All)?;
1886+
let eval_result = binding.as_any().downcast_ref::<Int64Array>().unwrap();
1887+
1888+
// Only the second row is valid and passes; the null-predicate row must
1889+
// be skipped even though its value bit is true.
1890+
let expect: PrimitiveArray<Int64Type> = Int64Array::from(vec![Some(20)]);
1891+
assert_eq!(eval_result, &expect);
1892+
1893+
Ok(())
1894+
}
1895+
17761896
#[test]
17771897
fn test_first_list_acc_size() -> Result<()> {
17781898
fn size_after_batch(values: &[ArrayRef]) -> Result<usize> {

‎datafusion/sqllogictest/test_files/aggregate.slt‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6689,6 +6689,69 @@ GROUP BY g
66896689
----
66906690
0 0
66916691

6692+
# first_value_with_group_by_and_nullable_filter
6693+
# Rows whose FILTER predicate evaluates to NULL must be excluded (#22666)
6694+
query II rowsort
6695+
SELECT g, first_value(a ORDER BY a) FILTER (WHERE b < 1) AS fv
6696+
FROM (VALUES (0, 10, CAST(NULL AS INT)), (0, 20, 2)) AS t(g, a, b)
6697+
GROUP BY g
6698+
----
6699+
0 NULL
6700+
6701+
# last_value_with_group_by_and_nullable_filter
6702+
query II rowsort
6703+
SELECT g, last_value(a ORDER BY a) FILTER (WHERE b < 1) AS lv
6704+
FROM (VALUES (0, 10, CAST(NULL AS INT)), (0, 20, 2)) AS t(g, a, b)
6705+
GROUP BY g
6706+
----
6707+
0 NULL
6708+
6709+
# first_last_value_with_group_by_and_mixed_filter_results
6710+
# Only rows whose FILTER predicate is TRUE participate: a = 10 (b = 1) and
6711+
# a = 20 (b = 0) in group 0. The NULL-predicate row (a = 5) and the
6712+
# FALSE-predicate row (a = 30) are excluded. No row passes the filter in
6713+
# group 1, so the aggregates return NULL there.
6714+
query III rowsort
6715+
SELECT g,
6716+
first_value(a ORDER BY a) FILTER (WHERE b < 2) AS fv,
6717+
last_value(a ORDER BY a) FILTER (WHERE b < 2) AS lv
6718+
FROM (VALUES (0, 5, CAST(NULL AS INT)), (0, 10, 1), (0, 30, 2), (0, 20, 0),
6719+
(1, 100, CAST(NULL AS INT)), (1, 50, 3)) AS t(g, a, b)
6720+
GROUP BY g
6721+
----
6722+
0 10 20
6723+
1 NULL NULL
6724+
6725+
# first_last_value_with_group_by_filter_all_true_and_no_filter
6726+
# Behavior is unchanged when every row passes the FILTER or there is no FILTER
6727+
query IIIII rowsort
6728+
SELECT g,
6729+
first_value(a ORDER BY a) FILTER (WHERE a > 0) AS fv,
6730+
last_value(a ORDER BY a) FILTER (WHERE a > 0) AS lv,
6731+
first_value(a ORDER BY a) AS fv_no_filter,
6732+
last_value(a ORDER BY a) AS lv_no_filter
6733+
FROM (VALUES (0, 5, CAST(NULL AS INT)), (0, 10, 1), (0, 30, 2), (0, 20, 0)) AS t(g, a, b)
6734+
GROUP BY g
6735+
----
6736+
0 5 30 5 30
6737+
6738+
# first_value_without_group_by_and_nullable_filter
6739+
query I rowsort
6740+
SELECT first_value(a ORDER BY a) FILTER (WHERE b < 1) AS fv
6741+
FROM (VALUES (10, CAST(NULL AS INT)), (20, 2)) AS t(a, b)
6742+
----
6743+
NULL
6744+
6745+
# first_value_window_function_no_regression
6746+
query II
6747+
SELECT a, first_value(a) OVER (ORDER BY a) AS fv
6748+
FROM (VALUES (10), (20), (5)) AS t(a)
6749+
ORDER BY a
6750+
----
6751+
5 5
6752+
10 5
6753+
20 5
6754+
66926755
# query_with_untyped_null_filter
66936756
query I
66946757
SELECT count(*) FILTER (WHERE NULL)

0 commit comments

Comments
 (0)