Fix ERROR 3583 “Window ‘…’ cannot inherit ‘…’ since both contain an ORDER BY clause” in MySQL

If you’re getting MySQL error 3583, which reads something like “Window ‘<unnamed window>’ cannot inherit ‘wf’ since both contain an ORDER BY clause“, it’s probably because you’re using an ORDER BY clause when referring to a named window that also has its own ORDER BY clause.

To fix this issue, don’t use the ORDER BY clause when referring to a named window that already has an ORDER BY clause in its definition.

Example of Error

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

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

Result:

ERROR 3583 (HY000): Window '<unnamed window>' cannot inherit 'wf' since both contain an ORDER BY clause.

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).

I include an ORDER BY clause in the OVER clause, which normally wouldn’t be a problem (the ORDER BY clause is a perfectly valid clause for the OVER clause). However in this case, the named window already has its own ORDER BY clause, and so the query results in an error.

When the named window already has its own ORDER BY clause, we can’t include another ORDER BY clause when we refer to it elsewhere in the query. This is also true if we refer to it from another named window:

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

Result:

ERROR 3583 (HY000): Window 'wf2' cannot inherit 'wf1' since both contain an ORDER BY clause.

Same error. The only difference is that, this time our error message refers to wf2 instead of <unnamed window> (because wf2 is the name we’ve given to the window that’s trying to inherit wf1).

Solution

The solution is simple. Don’t include the ORDER BY clause when referring to another window that already has an ORDER BY clause.

In our case, we have a couple of options:

We can remove the ORDER BY clause from our OVER clause
Or we can remove the ORDER BY clause from the named window

Here’s an example of the first option:

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

Result:

+-----------------+---------------+------------+--------------------------+
| District        | Name          | Population | SUM(Population) OVER(wf) |
+-----------------+---------------+------------+--------------------------+
| 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)

So I simply removed the ORDER BY clause from the OVER clause, and the query ran without error.

Here’s an example of the second option:

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)

Same result.

The same concept can be applied when using named windows that refer to other named windows. For example, we can rewrite the following (error producing) code:

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

Result:

ERROR 3583 (HY000): Window 'wf2' cannot inherit 'wf1' since both contain an ORDER BY clause.

To something like this:

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

Result:

+-----------------+---------------+------------+---------------------------+
| District        | Name          | Population | SUM(Population) OVER(wf2) |
+-----------------+---------------+------------+---------------------------+
| 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)

In this case I simply removed the ORDER BY clause from wf1. So now wf1 is responsible for the partitioning, and wf2 does the ordering. This allows me to call wf2 without having to include my own ORDER BY clause, but I also have the option of calling wp1 directly if I want it unsorted, or if I want to specify my own sorting.