How to fix ER_WINDOW_RANGE_BOUND_NOT_CONSTANT (3590): Window frame bound must be constant in MySQL

-- Problem: Using column reference in RANGE boundary
SELECT
  order_id,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN amount PRECEDING AND CURRENT ROW  -- Error: amount is a column!
  ) as dynamic_sum
FROM orders;

-- Solution: Use constant value instead
SELECT
  order_id,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW  -- Constant value works
  ) as fixed_sum
FROM orders;

-- For time-based windows, use constant intervals
SELECT
  timestamp,
  temperature,
  AVG(temperature) OVER (
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW  -- Constant interval
  ) as hourly_avg
FROM sensor_data;

-- Problem: Want window based on percentage of current value
SELECT
  product_id,
  price,
  AVG(price) OVER (
    ORDER BY price
    RANGE BETWEEN (price * 0.1) PRECEDING AND (price * 0.1) FOLLOWING  -- Dynamic expression
  ) as similar_price_avg
FROM products;

-- Solution: Use CASE or calculate outside window function
WITH price_ranges AS (
  SELECT
    product_id,
    price,
    CASE
      WHEN price < 100 THEN 10
      WHEN price < 1000 THEN 100
      ELSE 200
    END as range_size
  FROM products
)
SELECT
  product_id,
  price,
  AVG(price) OVER (
    ORDER BY price
    RANGE BETWEEN range_size PRECEDING AND range_size FOLLOWING  -- Still error: column reference!
  ) as similar_price_avg
FROM price_ranges;

-- Better: Use multiple window functions with different constant ranges
SELECT
  product_id,
  price,
  CASE
    WHEN price < 100 THEN
      AVG(price) OVER (
        ORDER BY price
        RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
      )
    WHEN price < 1000 THEN
      AVG(price) OVER (
        ORDER BY price
        RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING
      )
    ELSE
      AVG(price) OVER (
        ORDER BY price
        RANGE BETWEEN 200 PRECEDING AND 200 FOLLOWING
      )
  END as similar_price_avg
FROM products;

-- Set boundary value based on application logic
SET @window_range = 100;

-- Use in query with constant expression
SELECT
  transaction_id,
  amount,
  SUM(amount) OVER (
    ORDER BY transaction_date
    RANGE BETWEEN @window_range PRECEDING AND CURRENT ROW
  ) as rolling_sum
FROM transactions
WHERE @window_range > 0;

-- Change value and re-run
SET @window_range = 500;

-- Same query works with new boundary
SELECT
  transaction_id,
  amount,
  SUM(amount) OVER (
    ORDER BY transaction_date
    RANGE BETWEEN @window_range PRECEDING AND CURRENT ROW
  ) as rolling_sum
FROM transactions
WHERE @window_range > 0;

-- For user-specific or conditional boundaries:
SET @user_window = (SELECT window_preference FROM users WHERE user_id = 123);

SELECT
  data_point,
  value,
  AVG(value) OVER (
    ORDER BY timestamp
    RANGE BETWEEN @user_window PRECEDING AND CURRENT ROW
  ) as user_avg
FROM measurements;