3171
MySQLERRORNotableWindow FunctionsHIGH confidence
ROWS mode window frame cannot use INTERVAL bounds
Production Risk
Low — syntax error caught at parse time.
What this means
The ROWS window frame mode specifies the frame in terms of physical row offsets and cannot use INTERVAL expressions as bounds. INTERVAL bounds are only valid with RANGE mode.
Why it happens
- 1Specifying ROWS BETWEEN INTERVAL ... PRECEDING AND ... FOLLOWING in a window frame.
How to reproduce
trigger — this will error
trigger — this will error
SELECT SUM(val) OVER (ORDER BY dt ROWS BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) FROM t1;
expected output
ERROR 3171 (HY000): Window ROWS frame cannot use an interval.
Fix 1
Use RANGE mode for INTERVAL bounds
Use RANGE mode for INTERVAL bounds
SELECT SUM(val) OVER (ORDER BY dt RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) FROM t1;
Why this works
RANGE mode supports INTERVAL-based logical bounds.
Fix 2
Use integer row counts for ROWS mode
Use integer row counts for ROWS mode
SELECT SUM(val) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) FROM t1;
Why this works
ROWS mode uses integer offsets representing physical row counts.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3171 ER_WINDOW_ROWS_INTERVAL_USE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev