MySQL ignores frame clauses when used with ranking and offset window functions like RANK(), ROW_NUMBER(), LAG(), and LEAD(). These functions always operate on the entire partition regardless of frame specifications. Remove the frame clause from affected functions or use frame clauses only with aggregate window functions.
MySQL error 3599 occurs when you specify a frame clause (like ROWS BETWEEN or RANGE BETWEEN) with certain window functions that inherently operate on the entire partition. According to SQL standard, these functions should not have frame clauses since they always evaluate across all rows in the partition. MySQL allows the syntax but silently ignores the frame specification, which can lead to unexpected query behavior or confusion about what your query is actually computing. The affected functions are: CUME_DIST(), DENSE_RANK(), LAG(), LEAD(), NTILE(), PERCENT_RANK(), RANK(), and ROW_NUMBER(). Since these functions are designed to rank, number, or offset rows across the partition, frame boundaries do not apply to them.
Review your query for window functions. Functions like RANK(), ROW_NUMBER(), LAG(), LEAD(), NTILE(), CUME_DIST(), DENSE_RANK(), and PERCENT_RANK() always operate on the entire partition.
Example of problematic code:
SELECT
employee_id,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rank
FROM employees;If you're using RANK(), ROW_NUMBER(), LAG(), LEAD(), NTILE(), CUME_DIST(), DENSE_RANK(), or PERCENT_RANK(), remove any ROWS BETWEEN, RANGE BETWEEN, or GROUPS frame specifications. These functions ignore them anyway.
Fixed example:
SELECT
employee_id,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;Frame clauses are meaningful only with aggregate window functions like SUM(), AVG(), COUNT(), MIN(), MAX(), or with FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() that actually respect frame definitions.
Correct use of frame clause:
SELECT
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;If you need to aggregate over a specific frame of rows (running totals, moving averages), ensure you're using aggregate functions that support frames. If you're trying to rank rows, use the ranking functions without frame clauses, as they always use the entire partition.
Example - running salary total:
SELECT
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_salary_total
FROM employees;Example - ranking without frames (correct approach):
SELECT
employee_id,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;Default frame behavior in MySQL depends on whether ORDER BY is present: With ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (the entire partition). This means adding ORDER BY to a query can change results even without an explicit frame clause. For deterministic results, provide explicit frame specifications. The GROUPS frame unit and EXCLUDE clause are parsed but produce errors in MySQL—only ROWS and RANGE are fully supported.
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