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
{{ message }}
Repository navigation
Commit f1f0449
Browse filesBrowse the repository at this point in the historyBrowse files
fix: Support RANGE window frames over binary ORDER BY keys (apache#24357)
## Which issue does this PR close?
- Closesapache#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 apache#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 <[email protected]>
# A non-aggregate window function over a binary ORDER BY key.
6940
+
query ?I
6941
+
SELECT x, RANK() OVER (ORDER BY x)
6942
+
FROM (VALUES (arrow_cast('a', 'Binary')),
6943
+
(arrow_cast('b', 'Binary')),
6944
+
(arrow_cast('b', 'Binary'))) t(x)
6945
+
ORDER BY x
6946
+
----
6947
+
61 1
6948
+
62 2
6949
+
62 2
6950
+
6951
+
# Unsupported RANGE ORDER BY types still propagate the type-coercion error.
6952
+
query error DataFusion error: type_coercion\ncaused by\nInternal error: Cannot run range queries on datatype: Struct\("c0": Int64\)
6953
+
SELECT COUNT(*) OVER (ORDER BY x)
6954
+
FROM (VALUES (struct(1))) t(x)
6955
+
6956
+
# An order key only needs to be comparable for a free range frame, whose bounds
6957
+
# are all UNBOUNDED or CURRENT ROW. A finite offset such as `1 PRECEDING` instead
6958
+
# has to be computed as `current_value - 1`, so it additionally requires
6959
+
# arithmetic on the order key type. Reject such frames during planning for every
6960
+
# order key type that has no arithmetic, rather than silently widening the frame
6961
+
# to the whole partition.
6962
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Binary
6963
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6964
+
FROM (VALUES (arrow_cast('a', 'Binary'))) t(x)
6965
+
6966
+
# Dictionary-wrapped binary is unwrapped before checking offset arithmetic.
6967
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Binary
6968
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6969
+
FROM (VALUES (arrow_cast(arrow_cast('a', 'Binary'), 'Dictionary(Int32, Binary)'))) t(x)
6970
+
6971
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Utf8
6972
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6973
+
FROM (VALUES ('a')) t(x)
6974
+
6975
+
# The end bound is rejected on the same grounds as the start bound.
6976
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Boolean
6977
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING)
6978
+
FROM (VALUES (true)) t(x)
6979
+
6980
+
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\)
6981
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6982
+
FROM (VALUES ([1,2])) t(x)
6983
+
6984
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE with offset PRECEDING/FOLLOWING is not supported for ORDER BY type Null
6985
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6986
+
FROM (VALUES (NULL)) t(x)
6987
+
6988
+
# Numeric order keys keep their finite offsets, decimals included.
6989
+
query RI
6990
+
SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6991
+
FROM (VALUES (arrow_cast(1.5, 'Decimal128(10, 2)')),
6992
+
(arrow_cast(2.0, 'Decimal128(10, 2)'))) t(x)
6993
+
ORDER BY x
6994
+
----
6995
+
1.5 1
6996
+
2 2
6997
+
6998
+
# Free range frames over those same types keep working, so the rejection above is
6999
+
# narrowed to the offset form rather than to the order key type as a whole.
7000
+
query TI
7001
+
SELECT x, COUNT(*) OVER (ORDER BY x)
7002
+
FROM (VALUES ('a'), ('b'), ('b')) t(x)
7003
+
ORDER BY x
7004
+
----
7005
+
a 1
7006
+
b 3
7007
+
b 3
7008
+
7009
+
query BI
7010
+
SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Copy file name to clipboardExpand all lines: dev/update_function_docs.sh
+2Lines changed: 2 additions & 0 deletions
Original file line number
Diff line number
Diff line change
@@ -317,6 +317,8 @@ where **offset** is an non-negative integer.
317
317
318
318
RANGE and GROUPS modes require an ORDER BY clause (with RANGE the ORDER BY must specify exactly one column).
319
319
320
+
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 comparing ORDER BY values.
321
+
320
322
## Filter clause for aggregate window functions
321
323
322
324
Aggregate window functions support the SQL `FILTER (WHERE ...)` clause to include only rows that satisfy the predicate from the window frame in the aggregation.
Copy file name to clipboardExpand all lines: docs/source/user-guide/sql/window_functions.md
+2Lines changed: 2 additions & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -145,6 +145,8 @@ where **offset** is an non-negative integer.
145
145
146
146
RANGE and GROUPS modes require an ORDER BY clause (with RANGE the ORDER BY must specify exactly one column).
147
147
148
+
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 comparing ORDER BY values.
149
+
148
150
## Filter clause for aggregate window functions
149
151
150
152
Aggregate window functions support the SQL `FILTER (WHERE ...)` clause to include only rows that satisfy the predicate from the window frame in the aggregation.
0 commit comments