This MySQL error occurs when using non-constant expressions (like column references or complex calculations) in RANGE frame boundaries for window functions. RANGE frames require constant numeric or interval values to define the window boundaries, not dynamic expressions that change per row.
The ER_WINDOW_RANGE_BOUND_NOT_CONSTANT (3590) error is specific to MySQL window functions when using the RANGE frame specification. RANGE frames work by comparing values in the ORDER BY column(s) to determine which rows fall within a specified range relative to the current row. For example, "RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING" includes rows where the ORDER BY column value is within ±10 of the current row's value. The boundary values (10 in this case) must be constant expressions - they cannot reference columns, use subqueries, or be complex expressions that vary per row. This restriction exists because MySQL needs to be able to statically determine the window boundaries for optimization and execution. Dynamic boundaries would require re-evaluating the frame for each row, which would be computationally expensive and could lead to ambiguous results.
Change dynamic column references to constant numeric values or intervals:
-- 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;If you need dynamic window sizes, switch to ROWS frames which allow more flexibility:
```sql
-- Problem: Want dynamic window based on another column value
SELECT
student_id,
test_score,
AVG(test_score) OVER (
ORDER BY test_date
RANGE BETWEEN window_size PRECEDING AND CURRENT ROW -- window_size is a column
) as moving_avg
FROM test_results;
-- Solution: Use ROWS with calculated offset (if possible)
SELECT
student_id,
test_score,
AVG(test_score) OVER (
ORDER BY test_date
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW -- Fixed number of rows
) as moving_avg
FROM test_results;
-- Alternative: Calculate window in application logic or with multiple queries
-- If you truly need value-based dynamic windows, you may need to:
-- 1. Process in application code
-- 2. Use multiple queries with different constant ranges
-- 3. Consider if RANGE is really needed vs ROWS
If you need boundaries based on column values, calculate a constant value first:
-- 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;If boundaries need to change based on conditions, use session variables:
-- 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;Know the different requirements for RANGE and ROWS frames:
```sql
-- ROWS frames: Allow dynamic expressions (in some cases)
-- MySQL 8.0+ allows some expressions in ROWS boundaries
SELECT
id,
value,
SUM(value) OVER (
ORDER BY id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- Constant: OK
) as sum_3_rows,
SUM(value) OVER (
ORDER BY id
ROWS BETWEEN 0 PRECEDING AND (GREATEST(1, LEAST(5, id))) FOLLOWING -- Expression: May work
) as dynamic_sum
FROM sample_data;
-- RANGE frames: Strictly constant boundaries only
-- These work:
-- RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
-- RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
-- RANGE BETWEEN UNBOUNDED PRECEDING AND 100 FOLLOWING
-- These cause ER_WINDOW_RANGE_BOUND_NOT_CONSTANT:
-- RANGE BETWEEN column_name PRECEDING AND CURRENT ROW
-- RANGE BETWEEN (SELECT MAX(value) FROM config) PRECEDING AND CURRENT ROW
-- RANGE BETWEEN (value * 0.1) PRECEDING AND (value * 0.1) FOLLOWING
-- When to use which:
-- ROWS: When you want fixed number of rows before/after
-- RANGE: When you want value-based windows (same/similar values)
-- RANGE: When you want time-based windows (dates/timestamps)
-- If you need dynamic value-based windows, consider:
-- 1. Application-side processing
-- 2. Stored procedures with multiple queries
-- 3. Different database design
The constant boundary requirement for RANGE frames has important implications:
1. Performance Optimization: Constant boundaries allow MySQL to optimize window function execution more effectively, as the frame size is known statically.
2. Query Planning: The query planner can make better decisions about indexes and execution strategies when frame boundaries are constant.
3. Deterministic Results: Constant boundaries ensure the window function produces deterministic results that don't depend on the data distribution.
4. Migration Considerations: Other databases like PostgreSQL have different rules for window frame boundaries. PostgreSQL allows more flexibility with dynamic expressions in some cases, so queries may need adjustment when migrating to MySQL.
5. Workarounds: For truly dynamic value-based windows, consider:
- Using multiple window functions with different constant ranges and combining results with CASE
- Processing in application code after fetching the data
- Using stored procedures to iterate through data
- Materializing intermediate results with calculated ranges
6. MySQL Version Differences: Earlier MySQL 8.0 releases had stricter limitations. Check your specific version's documentation for any changes to window function behavior.
7. Future MySQL Versions: Future MySQL releases may relax some restrictions, but the constant boundary requirement is fundamental to RANGE frame optimization.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions