You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/08/19 07:25:12 UTC
(datafusion) branch gh-readonly-queue/main/pr-24357-c429919c7c8bd81e4e687531a609e2380ed60649 created (now 85d6d4aea1)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-24357-c429919c7c8bd81e4e687531a609e2380ed60649
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at 85d6d4aea1 fix: Support RANGE window frames over binary ORDER BY keys (#24357)
This branch includes the following new commits:
new 85d6d4aea1 fix: Support RANGE window frames over binary ORDER BY keys (#24357)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: fix: Support RANGE window frames over binary ORDER BY keys (#24357)
Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-24357-c429919c7c8bd81e4e687531a609e2380ed60649
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 85d6d4aea18e9baec3f8c599d9945049ee07e758
Author: Fredrik Fornwall <fr...@fornwall.net>
AuthorDate: Wed Aug 19 07:24:48 2026 +0000
fix: Support RANGE window frames over binary ORDER BY keys (#24357)
## Which issue does this PR close?
- Closes #24327.
## Rationale for this change
A window function over a binary `ORDER BY` key fails even with no frame
clause:
```sql
SELECT x, COUNT(*) OVER (ORDER BY x)
FROM (VALUES (arrow_cast('a', 'Binary')), (arrow_cast('b', 'Binary'))) t(x);
Internal error: Cannot run range queries on datatype: Binary.
```
`ORDER BY` without a frame defaults to `RANGE BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW`, and `extract_window_frame_target_type` had
no arm for the binary types. `LargeBinary`, `BinaryView`,
`FixedSizeBinary` and dictionary-wrapped binary are affected too. Those
bounds only need to compare order key values, which binary supports just
as `Utf8` does.
A finite offset like `1 PRECEDING` is different: the bound is computed
as `current_value - 1`, which needs arithmetic on the order key type.
Binary has none, and neither do `Utf8`, `Boolean`, `List` and `Null` --
yet offset frames over `Utf8`, `Boolean` and `List` keys were accepted,
and since 54.0.0 the failing arithmetic is swallowed by the overflow
handling from #22140, silently collapsing the bound to the partition
edge:
```sql
SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM (VALUES ('a'), ('b'), ('c')) t(x) ORDER BY x;
```
returns the running count `1`, `2`, `3` -- a silently widened frame --
instead of an error. This PR rejects such frames at planning time
instead, matching PostgreSQL.
## What changes are included in this PR?
- Accept the binary types in `extract_window_frame_target_type`. The
existing dictionary arm already recurses into the value type.
- Reject finite RANGE offsets during planning when the target type has
no arithmetic, meaning anything other than the numerics and `Interval`.
The message follows PostgreSQL's `RANGE with offset PRECEDING/FOLLOWING
is not supported for column type ...`.
- Make `WindowFrame::free_range()` `pub`, so the check reuses the
crate's existing notion of "bounds that are UNBOUNDED or CURRENT ROW".
- Document the restriction in the window functions guide and the
`55.0.0` upgrade guide.
## Are these changes tested?
Yes, in `window.slt`: all four binary types plus dictionary-wrapped
binary, ascending and descending, an aggregate and `RANK`, with
duplicate values asserting peer semantics rather than positional
counting. Planning errors for offsets over `Binary`, `Utf8`, `Boolean`,
`List(Int64)` and `Null`, on the end bound as well as the start. Free
range frames over `Utf8` and `Boolean` still return results, and
`Decimal128` keeps its offsets.
The full sqllogictest suite passes.
## Are there any user-facing changes?
Yes, one **breaking SQL change**: `RANGE BETWEEN <offset>
PRECEDING/FOLLOWING` over a `Utf8`, `Binary`, `Boolean`, `List` or
`Null` `ORDER BY` key now fails at planning time with `RANGE with offset
PRECEDING/FOLLOWING is not supported for ORDER BY type ...`. Over
`Utf8`, `Boolean` and `List` keys this replaces the silently widened
frames described above; over `Null` and binary keys such queries already
failed at planning time, so only the error message changes. The `55.0.0`
upgrade guide entry covers the per-type history and the migration paths:
use a `ROWS` frame to count rows, or state `UNBOUNDED PRECEDING` /
`UNBOUNDED FOLLOWING` explicitly to compare against the partition edge.
In the other direction, binary order keys now work with free range
frames, where they previously raised the internal error above.
`WindowFrame::free_range()` going from private to `pub` is additive, so
not a breaking Rust API change.
---
AI usage: Created with Claude Code and Opus 5. I have reviewed the code
and made modifications where it made sense. I have also tested this end
to end in an internal project.
---------
Signed-off-by: Fredrik Fornwall <fr...@fornwall.net>
---
datafusion/expr/src/window_frame.rs | 8 +-
datafusion/optimizer/src/analyzer/type_coercion.rs | 16 ++-
datafusion/sqllogictest/test_files/window.slt | 140 +++++++++++++++++++++
dev/update_function_docs.sh | 2 +
docs/source/library-user-guide/upgrading/55.0.0.md | 44 +++++++
docs/source/user-guide/sql/window_functions.md | 2 +
6 files changed, 210 insertions(+), 2 deletions(-)
diff --git a/datafusion/expr/src/window_frame.rs b/datafusion/expr/src/window_frame.rs
index a61d9d689a..d9db35875a 100644
--- a/datafusion/expr/src/window_frame.rs
+++ b/datafusion/expr/src/window_frame.rs
@@ -282,7 +282,13 @@ impl WindowFrame {
/// Returns whether the window frame is "free range"; i.e. its start/end
/// bounds are UNBOUNDED or CURRENT ROW.
- fn free_range(&self) -> bool {
+ ///
+ /// This inspects only the bounds, not the frame units, so it returns `true`
+ /// for ROWS and GROUPS frames with such bounds as well. For RANGE frames,
+ /// such bounds are located by comparing order key values, whereas a finite
+ /// offset bound (e.g. `5 PRECEDING`) has to be computed arithmetically from
+ /// the current row's order key value.
+ pub fn free_range(&self) -> bool {
(self.start_bound.is_unbounded()
|| self.start_bound == WindowFrameBound::CurrentRow)
&& (self.end_bound.is_unbounded()
diff --git a/datafusion/optimizer/src/analyzer/type_coercion.rs b/datafusion/optimizer/src/analyzer/type_coercion.rs
index ca1527fac8..feadc0370b 100644
--- a/datafusion/optimizer/src/analyzer/type_coercion.rs
+++ b/datafusion/optimizer/src/analyzer/type_coercion.rs
@@ -1068,6 +1068,7 @@ fn coerce_frame_bound(
fn extract_window_frame_target_type(col_type: &DataType) -> Result<DataType> {
if col_type.is_numeric()
|| col_type.is_string()
+ || col_type.is_binary()
|| col_type.is_null()
|| matches!(
col_type,
@@ -1102,7 +1103,20 @@ fn coerce_window_frame(
.map(|s| s.expr.get_type(schema))
.transpose()?;
if let Some(col_type) = current_types {
- extract_window_frame_target_type(&col_type)?
+ let target_type = extract_window_frame_target_type(&col_type)?;
+ // A finite offset bound (e.g. `5 PRECEDING`) is computed as
+ // `current_value ± offset`, so it is only meaningful for target
+ // types that support arithmetic. Strings, binaries, booleans
+ // and lists are orderable -- which is all a free range frame
+ // needs -- but have no such arithmetic.
+ let supports_offset_arithmetic =
+ target_type.is_numeric() || is_interval(&target_type);
+ if !supports_offset_arithmetic && !window_frame.free_range() {
+ return plan_err!(
+ "RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type {target_type}"
+ );
+ }
+ target_type
} else {
return internal_err!("ORDER BY column cannot be empty");
}
diff --git a/datafusion/sqllogictest/test_files/window.slt b/datafusion/sqllogictest/test_files/window.slt
index 6374cbf4f4..fe8bbdc345 100644
--- a/datafusion/sqllogictest/test_files/window.slt
+++ b/datafusion/sqllogictest/test_files/window.slt
@@ -6874,3 +6874,143 @@ ORDER BY id
2 NULL 3 3 3 3
3 NULL NULL NULL NULL NULL
4 7 7 7 7 7
+
+# RANGE window frame over a binary ORDER BY key. The default frame for an
+# ORDER BY without an explicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND
+# CURRENT ROW, which used to fail with
+# "Internal error: Cannot run range queries on datatype: Binary".
+# Binary is orderable, so peer/range comparison is well defined just like Utf8.
+query ?I
+SELECT x, COUNT(*) OVER (ORDER BY x)
+FROM (VALUES (arrow_cast('a', 'Binary')),
+ (arrow_cast('b', 'Binary')),
+ (arrow_cast('b', 'Binary')),
+ (arrow_cast('c', 'Binary'))) t(x)
+ORDER BY x
+----
+61 1
+62 3
+62 3
+63 4
+
+query ?I
+SELECT x, COUNT(*) OVER (ORDER BY x DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+FROM (VALUES (arrow_cast('a', 'LargeBinary')),
+ (arrow_cast('b', 'LargeBinary')),
+ (arrow_cast('b', 'LargeBinary'))) t(x)
+ORDER BY x
+----
+61 3
+62 2
+62 2
+
+query ?I
+SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+FROM (VALUES (arrow_cast('a', 'BinaryView')),
+ (arrow_cast('b', 'BinaryView')),
+ (arrow_cast('b', 'BinaryView'))) t(x)
+ORDER BY x
+----
+61 3
+62 2
+62 2
+
+query ?I
+SELECT x, COUNT(*) OVER (ORDER BY x)
+FROM (VALUES (arrow_cast(arrow_cast('a', 'Binary'), 'FixedSizeBinary(1)')),
+ (arrow_cast(arrow_cast('b', 'Binary'), 'FixedSizeBinary(1)'))) t(x)
+ORDER BY x
+----
+61 1
+62 2
+
+# A binary ORDER BY key nested in a dictionary resolves through the same arm.
+query ?I
+SELECT x, COUNT(*) OVER (ORDER BY x)
+FROM (VALUES (arrow_cast(arrow_cast('a', 'Binary'), 'Dictionary(Int32, Binary)')),
+ (arrow_cast(arrow_cast('b', 'Binary'), 'Dictionary(Int32, Binary)')),
+ (arrow_cast(arrow_cast('b', 'Binary'), 'Dictionary(Int32, Binary)'))) t(x)
+ORDER BY x
+----
+61 1
+62 3
+62 3
+
+# A non-aggregate window function over a binary ORDER BY key.
+query ?I
+SELECT x, RANK() OVER (ORDER BY x)
+FROM (VALUES (arrow_cast('a', 'Binary')),
+ (arrow_cast('b', 'Binary')),
+ (arrow_cast('b', 'Binary'))) t(x)
+ORDER BY x
+----
+61 1
+62 2
+62 2
+
+# Unsupported RANGE ORDER BY types still propagate the type-coercion error.
+query error DataFusion error: type_coercion\ncaused by\nInternal error: Cannot run range queries on datatype: Struct\("c0": Int64\)
+SELECT COUNT(*) OVER (ORDER BY x)
+FROM (VALUES (struct(1))) t(x)
+
+# An order key only needs to be comparable for a free range frame, whose bounds
+# are all UNBOUNDED or CURRENT ROW. A finite offset such as `1 PRECEDING` instead
+# has to be computed as `current_value - 1`, so it additionally requires
+# arithmetic on the order key type. Reject such frames during planning for every
+# order key type that has no arithmetic, rather than silently widening the frame
+# to the whole partition.
+query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Binary
+SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES (arrow_cast('a', 'Binary'))) t(x)
+
+# Dictionary-wrapped binary is unwrapped before checking offset arithmetic.
+query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Binary
+SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES (arrow_cast(arrow_cast('a', 'Binary'), 'Dictionary(Int32, Binary)'))) t(x)
+
+query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Utf8
+SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES ('a')) t(x)
+
+# The end bound is rejected on the same grounds as the start bound.
+query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Boolean
+SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING)
+FROM (VALUES (true)) t(x)
+
+query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type List\(Int64\)
+SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES ([1,2])) t(x)
+
+query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Null
+SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES (NULL)) t(x)
+
+# Numeric order keys keep their finite offsets, decimals included.
+query RI
+SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES (arrow_cast(1.5, 'Decimal128(10, 2)')),
+ (arrow_cast(2.0, 'Decimal128(10, 2)'))) t(x)
+ORDER BY x
+----
+1.5 1
+2 2
+
+# Free range frames over those same types keep working, so the rejection above is
+# narrowed to the offset form rather than to the order key type as a whole.
+query TI
+SELECT x, COUNT(*) OVER (ORDER BY x)
+FROM (VALUES ('a'), ('b'), ('b')) t(x)
+ORDER BY x
+----
+a 1
+b 3
+b 3
+
+query BI
+SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+FROM (VALUES (false), (true), (true)) t(x)
+ORDER BY x
+----
+false 3
+true 2
+true 2
diff --git a/dev/update_function_docs.sh b/dev/update_function_docs.sh
index 04266b8cbc..2ead1253ac 100755
--- a/dev/update_function_docs.sh
+++ b/dev/update_function_docs.sh
@@ -317,6 +317,8 @@ where **offset** is an non-negative integer.
RANGE and GROUPS modes require an ORDER BY clause (with RANGE the ORDER BY must specify exactly one column).
+In RANGE mode an **offset** is measured in ORDER BY values rather than in rows, so the bound is computed by adding it to or subtracting it from the current row's ORDER BY value. That restricts `offset PRECEDING` and `offset FOLLOWING` to ORDER BY types supporting such arithmetic, namely the numeric, date, and timestamp types. Other orderable types, such as strings and binaries, can still be used with `UNBOUNDED PRECEDING`, `CURRENT ROW` and `UNBOUNDED FOLLOWING`, which are located by com [...]
+
## Filter clause for aggregate window functions
Aggregate window functions support the SQL `FILTER (WHERE ...)` clause to include only rows that satisfy the predicate from the window frame in the aggregation.
diff --git a/docs/source/library-user-guide/upgrading/55.0.0.md b/docs/source/library-user-guide/upgrading/55.0.0.md
index ef2785edb7..c87dbc3b8a 100644
--- a/docs/source/library-user-guide/upgrading/55.0.0.md
+++ b/docs/source/library-user-guide/upgrading/55.0.0.md
@@ -1408,3 +1408,47 @@ let table_opts = TableParquetOptions::try_from(&proto_table_opts)?;
```
See [issue #24019](https://github.com/apache/datafusion/issues/24019) for details.
+
+### RANGE window frames with offsets now reject ORDER BY types without arithmetic
+
+A `RANGE` frame offset such as `1 PRECEDING` is measured in `ORDER BY` values
+rather than in rows, so the frame bound is computed as `current_value - 1`. That
+requires arithmetic on the `ORDER BY` type. For types that have no such
+arithmetic -- `Utf8`, `Binary`, `Boolean`, `List` and `Null` -- such frames are
+now rejected when the query is planned:
+
+```sql
+SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
+FROM (VALUES ('a'), ('b'), ('c')) t(x)
+ORDER BY x;
+-- Error during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Utf8
+```
+
+What this replaces depends on the `ORDER BY` type. Over `Utf8`, `Boolean` and
+`List` keys such queries planned successfully, and in DataFusion 54 the failing
+frame-bound arithmetic was silently treated as an overflow
+([#22140](https://github.com/apache/datafusion/pull/22140)): the bound collapsed
+to the partition edge, and the query returned results computed over that wrongly
+widened frame instead of an offset window. DataFusion 53 and earlier failed
+during execution with an arithmetic error. Rows whose order key value was `NULL`
+never reached the arithmetic, so queries where every order key value was `NULL`
+returned correct results throughout; those also now fail at planning. Over a
+`Null`-typed key the offset itself failed to coerce, erroring at planning time
+with `Cast error: Casting from Utf8 to Null not supported`, and binary keys were
+rejected for every `RANGE` frame with the internal error quoted below -- for
+those two, only the error message changes.
+
+Such an `ORDER BY` type is still usable with the `UNBOUNDED PRECEDING`,
+`CURRENT ROW` and `UNBOUNDED FOLLOWING` bounds, which are located by comparing
+`ORDER BY` values rather than by computing them. Binary order keys additionally
+gained support for those bounds in this release, having previously failed with
+`Internal error: Cannot run range queries on datatype: Binary`.
+
+**Who is affected:**
+
+- Queries using `offset PRECEDING` or `offset FOLLOWING` in a `RANGE` frame over
+ a non-numeric, non-temporal `ORDER BY` key. To count rows rather than compare
+ values, use a `ROWS` frame; to include everything up to the partition edge,
+ use `UNBOUNDED PRECEDING` or `UNBOUNDED FOLLOWING`.
+
+See [issue #24327](https://github.com/apache/datafusion/issues/24327) for details.
diff --git a/docs/source/user-guide/sql/window_functions.md b/docs/source/user-guide/sql/window_functions.md
index 2c8050ce1f..6496da1102 100644
--- a/docs/source/user-guide/sql/window_functions.md
+++ b/docs/source/user-guide/sql/window_functions.md
@@ -145,6 +145,8 @@ where **offset** is an non-negative integer.
RANGE and GROUPS modes require an ORDER BY clause (with RANGE the ORDER BY must specify exactly one column).
+In RANGE mode an **offset** is measured in ORDER BY values rather than in rows, so the bound is computed by adding it to or subtracting it from the current row's ORDER BY value. That restricts `offset PRECEDING` and `offset FOLLOWING` to ORDER BY types supporting such arithmetic, namely the numeric, date, and timestamp types. Other orderable types, such as strings and binaries, can still be used with `UNBOUNDED PRECEDING`, `CURRENT ROW` and `UNBOUNDED FOLLOWING`, which are located by com [...]
+
## Filter clause for aggregate window functions
Aggregate window functions support the SQL `FILTER (WHERE ...)` clause to include only rows that satisfy the predicate from the window frame in the aggregation.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org