Fix ERROR 3582 “Window ‘…’ has a frame definition, so cannot be referenced by another window” in MySQL

Posted on by Ian

If you’re getting MySQL error 3582, which reads something like “Window ‘w1’ has a frame definition, so cannot be referenced by another window“, it’s probably because you’re referencing a named window that has a frame clause.

This can happen when we use the WINDOW clause to define a named window for a window function, and we include a frame clause in that window. When we then try to reference that window, MySQL tells us that we can’t, due to the frame clause.

Here are a couple of options for fixing this issue:

  • Try removing any parentheses from the OVER clause that references the named window, OR
  • Don’t include a frame clause in the named window. Instead, try moving the frame clause to the OVER clause that references the named window.

Example of Error

Here’s an example of code that produces the error:

SELECT
    District,
    Name,
    Population,
    AVG(Population) OVER (w1) AS "Running Average"
FROM City
WHERE CountryCode = 'AUS'
WINDOW w1 AS (PARTITION BY District ORDER BY Name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY District, Name;

Result:

ERROR 3582 (HY000): Window 'w1' has a frame definition, so cannot be referenced by another window.

Here, the last column in my SELECT list has an OVER clause that refers to a named window called w1. The named window is defined later in the query (in the WINDOW clause).

However, the named window includes a frame clause, which results in the error when we try to reference the window. In this case, the frame clause is the part that goes ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.

We’ll also get the error if we refer to it from another named window:

SELECT
    District,
    Name,
    Population,
    AVG(Population) OVER (w2) AS "Running Average"
FROM City
WHERE CountryCode = 'AUS'
WINDOW  w1 AS (PARTITION BY District ORDER BY Name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 
        w2 AS (w1)
ORDER BY District, Name;

Result:

ERROR 3582 (HY000): Window 'w1' has a frame definition, so cannot be referenced by another window.

Same error.

In this case, we still get the error, even if we remove all references to any of the named windows in the SELECT list:

SELECT
    District,
    Name,
    Population,
    AVG(Population) OVER () AS "Running Average"
FROM City
WHERE CountryCode = 'AUS'
WINDOW  w1 AS (PARTITION BY District ORDER BY Name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 
        w2 AS (w1)
ORDER BY District, Name;

Result:

ERROR 3582 (HY000): Window 'w1' has a frame definition, so cannot be referenced by another window.

That’s because the second named window (w2) is trying to reference the first named window (w1). In this case it has nothing to do with the SELECT list – the issue in this case is completely within the WINDOW clause.

Solution 1

The quickest way to deal with this issue is to remove the parentheses from the OVER clause:

SELECT
    District,
    Name,
    Population,
    AVG(Population) OVER w1 AS "Running Average"
FROM City
WHERE CountryCode = 'AUS'
WINDOW w1 AS (PARTITION BY District ORDER BY Name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY District, Name;

Result:

+-----------------+---------------+------------+-----------------+
| District        | Name          | Population | Running Average |
+-----------------+---------------+------------+-----------------+
| Capital Region  | Canberra      |     322723 |     322723.0000 |
| New South Wales | Central Coast |     227657 |     248990.5000 |
| New South Wales | Newcastle     |     270324 |    1258062.6667 |
| New South Wales | Sydney        |    3276207 |    1255430.6667 |
| New South Wales | Wollongong    |     219761 |    1747984.0000 |
| Queensland      | Brisbane      |    1291117 |     691695.0000 |
| Queensland      | Cairns        |      92273 |     565107.3333 |
| Queensland      | Gold Coast    |     311932 |     171373.0000 |
| Queensland      | Townsville    |     109914 |     210923.0000 |
| South Australia | Adelaide      |     978100 |     978100.0000 |
| Tasmania        | Hobart        |     126118 |     126118.0000 |
| Victoria        | Geelong       |     125382 |    1495355.5000 |
| Victoria        | Melbourne     |    2865329 |    1495355.5000 |
| West Australia  | Perth         |    1096829 |    1096829.0000 |
+-----------------+---------------+------------+-----------------+
14 rows in set (0.00 sec)

This assumes that it was the OVER clause that was causing the issue. If the issue was caused by a named window referencing another named window (like in our second example that caused the error), then we’d need to try the next solution.

Solution 2

Another way to deal with the problem is to remove the frame clause away from the named windows.

Therefore, we can move the frame clause up to the OVER clause in the SELECT list:

SELECT
    District,
    Name,
    Population,
    AVG(Population) OVER (w1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "Running Average"
FROM City
WHERE CountryCode = 'AUS'
WINDOW w1 AS (PARTITION BY District ORDER BY Name)
ORDER BY District, Name;

Result:

+-----------------+---------------+------------+-----------------+
| District        | Name          | Population | Running Average |
+-----------------+---------------+------------+-----------------+
| Capital Region  | Canberra      |     322723 |     322723.0000 |
| New South Wales | Central Coast |     227657 |     248990.5000 |
| New South Wales | Newcastle     |     270324 |    1258062.6667 |
| New South Wales | Sydney        |    3276207 |    1255430.6667 |
| New South Wales | Wollongong    |     219761 |    1747984.0000 |
| Queensland      | Brisbane      |    1291117 |     691695.0000 |
| Queensland      | Cairns        |      92273 |     565107.3333 |
| Queensland      | Gold Coast    |     311932 |     171373.0000 |
| Queensland      | Townsville    |     109914 |     210923.0000 |
| South Australia | Adelaide      |     978100 |     978100.0000 |
| Tasmania        | Hobart        |     126118 |     126118.0000 |
| Victoria        | Geelong       |     125382 |    1495355.5000 |
| Victoria        | Melbourne     |    2865329 |    1495355.5000 |
| West Australia  | Perth         |    1096829 |    1096829.0000 |
+-----------------+---------------+------------+-----------------+
14 rows in set (0.01 sec)

In this case, moving the frame clause up to the SELECT list allowed us to reference the named window without error. This option might be more suitable if you have other clauses in the OVER clause (because this would require that we keep the parentheses).

How to Identify the Frame Clause

If you’re not sure which part of your query is the frame clause, it will be the part that starts with either ROWS or RANGE.

See the MySQL documentation for the Window Function Frame Specification for more information.