How to fix ER_WINDOW_FRAME_END_ILLEGAL (3585): Illegal frame end in MySQL

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.