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

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;