How to fix ER_WINDOW_RANGE_FRAME_ORDER_TYPE (3587): RANGE requires ORDER BY with numeric/temporal type in MySQL

-- Problematic query with RANGE
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (
    PARTITION BY department_id
    ORDER BY employee_name  -- String column!
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as avg_salary
FROM employees;

-- Fixed query using ROWS instead
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (
    PARTITION BY department_id
    ORDER BY employee_name
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- Changed to ROWS
  ) as avg_salary
FROM employees;

-- Problem: ORDER BY on string column
SELECT
  order_id,
  amount,
  SUM(amount) OVER (
    ORDER BY customer_name  -- String column causes error
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) as rolling_sum
FROM orders;

-- Solution: ORDER BY on numeric column
SELECT
  order_id,
  amount,
  SUM(amount) OVER (
    ORDER BY amount  -- Numeric column works with RANGE
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) as rolling_sum
FROM orders;

-- Temporal example with dates
SELECT
  date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date  -- Date column works with RANGE
    RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
  ) as weekly_revenue
FROM daily_sales;

-- Problem: String column that looks numeric
SELECT
  product_id,
  price_string,  -- VARCHAR like '99.99'
  AVG(CAST(price_string AS DECIMAL(10,2))) OVER (
    ORDER BY price_string  -- Still a string!
    RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
  ) as avg_price
FROM products;

-- Solution: Convert in ORDER BY
SELECT
  product_id,
  price_string,
  AVG(CAST(price_string AS DECIMAL(10,2))) OVER (
    ORDER BY CAST(price_string AS DECIMAL(10,2))  -- Converted to numeric
    RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
  ) as avg_price
FROM products;

-- Or better: Store as proper numeric type
ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2);

SELECT
  product_id,
  price,
  AVG(price) OVER (
    ORDER BY price  -- Now a proper numeric column
    RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
  ) as avg_price
FROM products;

-- ROWS: Count 3 rows before current row
SELECT
  timestamp,
  temperature,
  AVG(temperature) OVER (
    ORDER BY timestamp
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) as moving_avg
FROM sensor_readings;
-- Includes exactly 3 previous rows, regardless of time gaps

-- RANGE: Include rows within 5 minutes
SELECT
  timestamp,
  temperature,
  AVG(temperature) OVER (
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL 5 MINUTE PRECEDING AND CURRENT ROW
  ) as moving_avg
FROM sensor_readings;
-- Includes all rows within 5-minute window, even if many rows

-- Common use cases for RANGE:
-- 1. Time-based windows (dates, timestamps)
-- 2. Value-based windows (amounts, scores, measurements)
-- 3. When you want to include "peer" rows with identical values

-- RANGE with peer rows example:
SELECT
  student_id,
  test_score,
  RANK() OVER (
    ORDER BY test_score
    RANGE BETWEEN CURRENT ROW AND CURRENT ROW  -- Includes peers with same score
  ) as rank_with_peers
FROM test_results;

-- Check MySQL version
SELECT VERSION();

-- Window functions require MySQL 8.0+ or MariaDB 10.2+
-- MySQL 5.7 and earlier do not support window functions at all

-- Test basic window function support
SELECT
  1 as test,
  ROW_NUMBER() OVER () as row_num;

-- If this fails, you need to upgrade MySQL
-- Upgrade path: MySQL 5.7 → 8.0

-- Check for known bugs or limitations in your version
-- Some early MySQL 8.0 releases had window function bugs

-- For MariaDB users:
SELECT @@version, @@version_comment;
-- MariaDB 10.2+ supports window functions

-- Consider compatibility mode if migrating from other databases
SET SESSION sql_mode = 'ANSI';
-- Or explicitly set window function behavior
SET SESSION windowing_use_high_precision = ON;