How to fix ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification in MySQL

MySQLADVANCEDMEDIUMUpdated Dec 22, 2025· 114 views

This error occurs when using unsupported or invalid frame specifications in MySQL window functions. It typically happens when using GROUPS frame units, EXCLUDE clauses, or invalid combinations of frame boundaries.

Quick fix

Fixes ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification

-- Example query that might cause the error
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (
    ORDER BY hire_date
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- GROUPS not supported
  ) AS avg_salary
FROM employees;
  1. Identify the problematic frame specification
  2. Replace GROUPS with ROWS or RANGE
  3. Remove EXCLUDE clauses
  4. Verify frame boundary order
mysql — error
-- Example query that might cause the error
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (
    ORDER BY hire_date
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- GROUPS not supported
  ) AS avg_salary
FROM employees;
ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification

What this error means

The ER_WINDOW_FRAME_ILLEGAL error is thrown by MySQL when you specify an invalid or unsupported frame clause in a window function. Window functions in MySQL allow you to perform calculations across sets of rows related to the current row, and the frame clause defines which rows within the partition to include in the calculation. MySQL has specific restrictions on frame specifications. While the parser may accept certain syntax (like GROUPS or EXCLUDE), the query execution engine will reject these unsupported features. Additionally, certain combinations of frame boundaries are logically invalid, such as specifying an ending boundary that comes before the starting boundary, or using inappropriate frame types for certain operations. This error indicates that your window function's OVER clause contains a frame specification that violates MySQL's rules for valid frame definitions.

Typical symptoms

  • Query with window function fails with error code 3586
  • Error occurs specifically when using ROWS, RANGE, or GROUPS frame specifications
  • Query works in other databases but fails in MySQL
  • Error appears during query execution, not during parsing

Common causes

  1. Using GROUPS frame unit (only ROWS and RANGE are supported in MySQL)
  2. Using EXCLUDE clause in frame specification (not supported in MySQL)
  3. Using FROM LAST in frame specification (only FROM FIRST is supported)
  4. Specifying an end boundary that precedes the start boundary
  5. Using RANGE with expressions that don't match the ORDER BY type
  6. Invalid combination of UNBOUNDED, CURRENT ROW, or expression-based boundaries

How to fix "ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification"

1Identify the problematic frame specification

First, locate the window function causing the error. Look for the OVER clause with a frame specification:

sql
-- Example query that might cause the error
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (
    ORDER BY hire_date
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- GROUPS not supported
  ) AS avg_salary
FROM employees;

Check your query for GROUPS, EXCLUDE, or FROM LAST keywords in the frame specification.

2Replace GROUPS with ROWS or RANGE

If you're using GROUPS, switch to ROWS or RANGE depending on your needs:

sql
-- Instead of GROUPS, use ROWS for a fixed number of rows
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (
    ORDER BY hire_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_salary
FROM employees;

-- Or use RANGE for value-based windows
SELECT
  order_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
  ) AS weekly_total
FROM orders;

ROWS defines the frame by row position, while RANGE defines it by value.

3Remove EXCLUDE clauses

If you're using EXCLUDE, remove it as it's not supported in MySQL:

sql
-- This will fail
SELECT
  product_id,
  sale_amount,
  AVG(sale_amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    EXCLUDE CURRENT ROW  -- Not supported
  ) AS avg_excluding_current
FROM sales;

-- Workaround: Calculate manually
SELECT
  product_id,
  sale_amount,
  (SUM(sale_amount) OVER (ORDER BY sale_date) - sale_amount) /
  (COUNT(*) OVER (ORDER BY sale_date) - 1) AS avg_excluding_current
FROM sales;
4Verify frame boundary order

Ensure your frame boundaries are in valid order. The end boundary must not precede the start boundary:

sql
-- Invalid: end comes before start
SELECT
  id,
  value,
  SUM(value) OVER (
    ROWS BETWEEN 1 FOLLOWING AND 1 PRECEDING  -- Error!
  ) AS invalid_sum
FROM data;

-- Valid: proper boundary order
SELECT
  id,
  value,
  SUM(value) OVER (
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS valid_sum
FROM data;

Valid patterns include:
- UNBOUNDED PRECEDING to CURRENT ROW
- CURRENT ROW to UNBOUNDED FOLLOWING
- N PRECEDING to M FOLLOWING (where the boundaries make logical sense)

5Match RANGE expressions with ORDER BY type

When using RANGE with an expression, ensure it matches the ORDER BY column type:

sql
-- Invalid: RANGE with numeric offset on date column
SELECT
  order_id,
  order_date,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN 5 PRECEDING AND CURRENT ROW  -- Error!
  ) AS total
FROM orders;

-- Valid: use INTERVAL for temporal columns
SELECT
  order_id,
  order_date,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND CURRENT ROW
  ) AS total
FROM orders;

-- Valid: numeric offset for numeric columns
SELECT
  product_id,
  price,
  AVG(price) OVER (
    ORDER BY price
    RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
  ) AS avg_nearby_prices
FROM products;
6Test with simplified frame specifications

If the error persists, simplify your frame specification to isolate the issue:

sql
-- Start with the simplest valid frame (default)
SELECT
  id,
  value,
  SUM(value) OVER (ORDER BY id) AS running_sum
FROM data;

-- Gradually add frame specifications
SELECT
  id,
  value,
  SUM(value) OVER (
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM data;

This helps identify which part of your frame specification is invalid.

Advanced notes

MySQL Window Function Limitations:

MySQL supports only ROWS and RANGE frame units. If you need GROUPS functionality (which groups together rows with equal values in the ORDER BY expression), you'll need to implement workarounds using subqueries or CTEs.

Default Frame Behavior:

If you omit the frame clause entirely, MySQL applies default frames based on whether ORDER BY is present:
- Without ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (entire partition)
- With ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (up to current row)

ROWS vs RANGE:

- ROWS defines frames by physical row positions, counting rows regardless of their values
- RANGE defines frames by logical value ranges, grouping rows with equal values together

For many use cases, ROWS is simpler and more predictable. Use RANGE when you need to handle ties in the ORDER BY column specially, or when working with temporal ranges.

Performance Considerations:

Complex frame specifications can impact query performance. When possible, use simpler frames like UNBOUNDED PRECEDING AND CURRENT ROW for running totals, or avoid frame specifications entirely if the default behavior suits your needs.

Cross-Database Compatibility:

PostgreSQL and some other databases support GROUPS and EXCLUDE. If migrating queries from other databases, you'll need to rewrite frame specifications to use only MySQL-supported syntax.

Frequently asked questions

What does "ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification" mean?
The ER_WINDOW_FRAME_ILLEGAL error is thrown by MySQL when you specify an invalid or unsupported frame clause in a window function. Window functions in MySQL allow you to perform calculations across sets of rows related to the current row, and the frame clause defines which rows within the partition to include in the calculation. MySQL has specific restrictions on frame specifications.
How do I fix "ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification"?
This error occurs when using unsupported or invalid frame specifications in MySQL window functions. It typically happens when using GROUPS frame units, EXCLUDE clauses, or invalid combinations of frame boundaries.
What causes "ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification"?
Using GROUPS frame unit (only ROWS and RANGE are supported in MySQL) Using EXCLUDE clause in frame specification (not supported in MySQL) Using FROM LAST in frame specification (only FROM FIRST is supported)
Is the "ER_WINDOW_FRAME_ILLEGAL (3586): Illegal frame specification" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading