This error occurs when a MySQL window function frame specification uses UNBOUNDED PRECEDING as the frame end boundary. Window frames must follow logical ordering where the frame start cannot occur later than the frame end in the dataset.
Error 3585 is raised by MySQL when you attempt to define a window function frame with an illegal end boundary. Specifically, MySQL does not permit UNBOUNDED PRECEDING to be used as the frame end in a BETWEEN clause because it violates the fundamental ordering constraint of window frames. Window functions in MySQL operate on a "frame" - a subset of rows relative to the current row. The frame is defined with ROWS or RANGE keywords followed by boundaries. The database enforces that the frame_start boundary must not occur logically later than the frame_end boundary. Since UNBOUNDED PRECEDING refers to the very first row of the partition and a frame end should be at or after the frame start, using it as the end creates an impossible frame definition. This error typically surfaces when developers misunderstand the semantics of frame boundary keywords or accidentally reverse the BETWEEN clause arguments in window function specifications.
Locate the window function in your query that's causing the error. Look for OVER() clauses with frame specifications:
-- Example that triggers the error
SELECT
employee_id,
salary,
SUM(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING -- ERROR HERE
) as running_total
FROM employees;The error message will specify which window is problematic if you're using named windows.
Review the permitted frame boundary values and their logical order:
Valid frame boundaries:
- UNBOUNDED PRECEDING (partition start)
- expr PRECEDING (N rows/values before current)
- CURRENT ROW
- expr FOLLOWING (N rows/values after current)
- UNBOUNDED FOLLOWING (partition end)
Ordering rule: frame_start must come at or before frame_end in the partition order.
Valid combinations:
- BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- BETWEEN 5 PRECEDING AND 5 FOLLOWING
- BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
Invalid combinations:
- BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING (end comes before start)
- BETWEEN 10 FOLLOWING AND 5 PRECEDING (end comes before start)
Fix the frame boundaries to respect the logical ordering:
-- WRONG: Frame end cannot be UNBOUNDED PRECEDING
SELECT
employee_id,
SUM(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING
) as total
FROM employees;
-- CORRECT: Swap boundaries or use proper frame definition
SELECT
employee_id,
SUM(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM employees;For a running total from the start of the partition to the current row, use BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
If your intent matches the default behavior, you can omit the frame clause entirely:
-- With explicit frame (verbose)
SELECT
dept_id,
employee_id,
salary,
AVG(salary) OVER (
PARTITION BY dept_id
ORDER BY salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as avg_up_to_current
FROM employees;
-- Using default frame (simpler)
SELECT
dept_id,
employee_id,
salary,
AVG(salary) OVER (
PARTITION BY dept_id
ORDER BY salary
) as avg_up_to_current
FROM employees;When you include ORDER BY but omit the frame clause, MySQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all partition rows from the start up to and including peers of the current row.
Verify your frame works correctly with both ROWS and RANGE if applicable:
-- ROWS: Physical row boundaries
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) as rolling_4_day_sum
FROM orders;
-- RANGE: Value-based boundaries (includes ties)
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW
) as rolling_4_day_sum
FROM orders;ROWS counts physical rows, while RANGE considers values and includes rows with equal ORDER BY values (peers). Choose based on your use case.
Named Window Clauses:
When using named WINDOW clauses, the error message will include the window name:
SELECT
employee_id,
SUM(salary) OVER w as total
FROM employees
WINDOW w AS (ORDER BY hire_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING);
-- Error: Window 'w': frame end cannot be UNBOUNDED PRECEDING.Frame Semantics with NULL Values:
Frame specifications with RANGE can behave non-intuitively when the ORDER BY column contains NULL values. With ORDER BY col ASC, NULL values sort first, and with RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, all NULL values are considered peers and included in the frame for rows with NULL col values.
Performance Considerations:
Frame specifications affect query performance. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING processes the entire partition for each row, which can be expensive on large datasets. Consider narrower frames when possible (e.g., ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING) to improve performance.
MySQL Version Requirements:
Window functions were introduced in MySQL 8.0. If you're using MySQL 5.7 or earlier, you'll need to upgrade to use window functions. MariaDB added window function support starting with version 10.2.
Comparison with Other SQL Dialects:
PostgreSQL, SQL Server, and Oracle have similar frame ordering rules, but some dialects permit additional frame specifications. Always verify frame syntax when migrating queries between database systems.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions