3175
MySQLERRORNotableWindow FunctionsHIGH confidence
RANGE window frame requires ORDER BY
Production Risk
Low — syntax error caught at parse time.
What this means
A window frame specified as RANGE requires an ORDER BY clause in the window definition. Without ORDER BY, RANGE-based framing is undefined because there is no ordering by which to measure the range.
Why it happens
- 1RANGE BETWEEN ... specified in a window definition that has no ORDER BY clause.
How to reproduce
trigger — this will error
trigger — this will error
SELECT SUM(val) OVER (PARTITION BY cat RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
expected output
ERROR 3175 (HY000): Window specification requires ORDER BY with RANGE frame.
Fix 1
Add ORDER BY to the window definition
Add ORDER BY to the window definition
SELECT SUM(val) OVER (PARTITION BY cat ORDER BY dt RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
Why this works
ORDER BY provides the ordering needed to evaluate the RANGE frame.
Fix 2
Switch to ROWS mode
Switch to ROWS mode
SELECT SUM(val) OVER (PARTITION BY cat ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
Why this works
ROWS mode counts physical rows and does not require ORDER BY for framing.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3175 ER_WL6897_RANGE_AND_ORDER
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev