Fix ERROR 3581 “A window which depends on another cannot define partitioning” in MySQL

If you’re getting error 3581 in MySQL, which reads “A window which depends on another cannot define partitioning“, it’s probably because you’re using the PARTITION BY clause in a window function that refers to a named window.

To fix this issue, don’t use the PARTITION BY clause when referring to a named window.

Example of Error

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

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER(wf PARTITION BY Name)
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW wf AS (PARTITION BY District);

Result:

ERROR 3581 (HY000): A window which depends on another cannot define partitioning.

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

The problem is that I include the PARTITION BY clause in the OVER clause. This is not allowed when referring to a named window, and so MySQL returns an error.

Solution

The solution is simple. Don’t try to define partitioning when referring to another window. More specifically, don’t include a PARTITION BY clause in the OVER clause if that OVER clause refers to a named window.

Here’s an example of fixing the above example:

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER(wf)
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW wf AS (PARTITION BY District);

Result:

+-----------------+---------------+------------+--------------------------+
| District        | Name          | Population | SUM(Population) OVER(wf) |
+-----------------+---------------+------------+--------------------------+
| Capital Region  | Canberra      |     322723 |                   322723 |
| New South Wales | Sydney        |    3276207 |                  3993949 |
| New South Wales | Newcastle     |     270324 |                  3993949 |
| New South Wales | Central Coast |     227657 |                  3993949 |
| New South Wales | Wollongong    |     219761 |                  3993949 |
| Queensland      | Brisbane      |    1291117 |                  1805236 |
| Queensland      | Gold Coast    |     311932 |                  1805236 |
| Queensland      | Townsville    |     109914 |                  1805236 |
| Queensland      | Cairns        |      92273 |                  1805236 |
| South Australia | Adelaide      |     978100 |                   978100 |
| Tasmania        | Hobart        |     126118 |                   126118 |
| Victoria        | Melbourne     |    2865329 |                  2990711 |
| Victoria        | Geelong       |     125382 |                  2990711 |
| West Australia  | Perth         |    1096829 |                  1096829 |
+-----------------+---------------+------------+--------------------------+
14 rows in set (0.00 sec)

So we simply removed the PARTITION BY clause from the OVER clause.

This doesn’t mean that we can’t provide other clauses to modify the named window. For example, we can add the ORDER BY clause without error:

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER(wf ORDER BY Name)
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW wf AS (PARTITION BY District);

Result:

+-----------------+---------------+------------+----------------------------------------+
| District        | Name          | Population | SUM(Population) OVER(wf ORDER BY Name) |
+-----------------+---------------+------------+----------------------------------------+
| Capital Region  | Canberra      |     322723 |                                 322723 |
| New South Wales | Central Coast |     227657 |                                 227657 |
| New South Wales | Newcastle     |     270324 |                                 497981 |
| New South Wales | Sydney        |    3276207 |                                3774188 |
| New South Wales | Wollongong    |     219761 |                                3993949 |
| Queensland      | Brisbane      |    1291117 |                                1291117 |
| Queensland      | Cairns        |      92273 |                                1383390 |
| Queensland      | Gold Coast    |     311932 |                                1695322 |
| Queensland      | Townsville    |     109914 |                                1805236 |
| South Australia | Adelaide      |     978100 |                                 978100 |
| Tasmania        | Hobart        |     126118 |                                 126118 |
| Victoria        | Geelong       |     125382 |                                 125382 |
| Victoria        | Melbourne     |    2865329 |                                2990711 |
| West Australia  | Perth         |    1096829 |                                1096829 |
+-----------------+---------------+------------+----------------------------------------+
14 rows in set (0.00 sec)