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 ce39143
Browse filesBrowse the repository at this point in the historyBrowse files
fix: Support RANGE window frames over binary ORDER BY keys
Closesapache#24327.
A window with an `ORDER BY` over a binary column and no explicit frame gets
the default `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Computing
that frame's bounds goes through `extract_window_frame_target_type`, which
had no arm for the binary types, so any window function whose leading
`ORDER BY` key was `Binary`/`LargeBinary`/`BinaryView`/`FixedSizeBinary`
failed with:
Internal error: Cannot run range queries on datatype: Binary
Binary is orderable, so peer/range comparison is well defined for it in
exactly the same way it already is for `Utf8`. Accept the binary types
alongside the string types.
A finite RANGE offset such as `1 PRECEDING` asks for more than ordering: the
bound is computed as `current_value - 1`, so the order key type must also
support arithmetic. Binary does not, and neither do the `Utf8`, `Boolean`,
`List` and `Null` order keys that `extract_window_frame_target_type` already
accepted. For those, `ScalarValue::sub_checked` failed during execution and
the error was swallowed by the overflow handling from apache#22140, which collapses
a failed bound to the partition edge. So
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM (VALUES ('a'), ('b'), ('c')) t(x)
silently returned the running count 1, 2, 3 -- the whole partition -- rather
than an error.
Reject the offset form during planning for every target type that has no
arithmetic. For the string, boolean, list and null order keys this turns a
wrong result into a plan error, which matches PostgreSQL's "RANGE with offset
PRECEDING/FOLLOWING is not supported for column type ...".
`WindowFrame::free_range()` becomes public so that check can be expressed in
the vocabulary the crate already uses for "bounds that need no arithmetic".
Document the restriction in the window functions user guide, and the changed
SQL behavior in the 55.0.0 upgrade guide.
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
+
# An order key only needs to be comparable for a free range frame, whose bounds
6952
+
# are all UNBOUNDED or CURRENT ROW. A finite offset such as `1 PRECEDING` instead
6953
+
# has to be computed as `current_value - 1`, so it additionally requires
6954
+
# arithmetic on the order key type. Reject such frames during planning for every
6955
+
# order key type that has no arithmetic, rather than silently widening the frame
6956
+
# to the whole partition.
6957
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE frames with finite offsets are not supported for ORDER BY type Binary
6958
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6959
+
FROM (VALUES (arrow_cast('a', 'Binary'))) t(x)
6960
+
6961
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE frames with finite offsets are not supported for ORDER BY type Utf8
6962
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6963
+
FROM (VALUES ('a')) t(x)
6964
+
6965
+
# The end bound is rejected on the same grounds as the start bound.
6966
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE frames with finite offsets are not supported for ORDER BY type Boolean
6967
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING)
6968
+
FROM (VALUES (true)) t(x)
6969
+
6970
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE frames with finite offsets are not supported for ORDER BY type List\(Int64\)
6971
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6972
+
FROM (VALUES ([1,2])) t(x)
6973
+
6974
+
query error DataFusion error: type_coercion\ncaused by\nError during planning: RANGE frames with finite offsets are not supported for ORDER BY type Null
6975
+
SELECT COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6976
+
FROM (VALUES (NULL)) t(x)
6977
+
6978
+
# Numeric order keys keep their finite offsets, decimals included.
6979
+
query RI
6980
+
SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
6981
+
FROM (VALUES (arrow_cast(1.5, 'Decimal128(10, 2)')),
6982
+
(arrow_cast(2.0, 'Decimal128(10, 2)'))) t(x)
6983
+
ORDER BY x
6984
+
----
6985
+
1.5 1
6986
+
2 2
6987
+
6988
+
# Free range frames over those same types keep working, so the rejection above is
6989
+
# narrowed to the offset form rather than to the order key type as a whole.
6990
+
query TI
6991
+
SELECT x, COUNT(*) OVER (ORDER BY x)
6992
+
FROM (VALUES ('a'), ('b'), ('b')) t(x)
6993
+
ORDER BY x
6994
+
----
6995
+
a 1
6996
+
b 3
6997
+
b 3
6998
+
6999
+
query BI
7000
+
SELECT x, COUNT(*) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
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 and the date/time 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