If you’re getting error 3579 in MySQL, which reads something like “Window name ‘wf3’ is not defined“, it’s probably because you’re referring to a named window that doesn’t exist.
To fix this issue, make sure you refer to a named window that exists.
Example of Error
Here’s an example of code that produces the error:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf2)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (PARTITION BY District)
ORDER BY District, Name, "City Population";Result:
ERROR 3579 (HY000): Window name 'wf2' is not defined.
Here, the fourth column in my SELECT list has an OVER clause that refers to a named window called wf2. The problem is, I haven’t created a named window called wf2.
We can see by looking at the WINDOW clause that I’ve created one called wf1, but not wf2.
Solution
The solution is simple. Make sure the named window exists.
We have a couple of options for fixing the above code.
The first option is to modify our OVER clause (in the SELECT list) to refer to the named window that does in fact exist:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf1)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (PARTITION BY District)
ORDER BY District, Name, "City Population";Result:
+-----------------+---------------+------------+---------------------------+ | District | Name | Population | SUM(Population) OVER(wf1) | +-----------------+---------------+------------+---------------------------+ | Capital Region | Canberra | 322723 | 322723 | | New South Wales | Central Coast | 227657 | 3993949 | | New South Wales | Newcastle | 270324 | 3993949 | | New South Wales | Sydney | 3276207 | 3993949 | | New South Wales | Wollongong | 219761 | 3993949 | | Queensland | Brisbane | 1291117 | 1805236 | | Queensland | Cairns | 92273 | 1805236 | | Queensland | Gold Coast | 311932 | 1805236 | | Queensland | Townsville | 109914 | 1805236 | | South Australia | Adelaide | 978100 | 978100 | | Tasmania | Hobart | 126118 | 126118 | | Victoria | Geelong | 125382 | 2990711 | | Victoria | Melbourne | 2865329 | 2990711 | | West Australia | Perth | 1096829 | 1096829 | +-----------------+---------------+------------+---------------------------+ 14 rows in set (0.01 sec)
In this case we simply changed wf2 to wf1
Another way to fix the issue is to add the named window to our WINDOW clause:
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)
ORDER BY District, Name, "City Population";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)
Here we added wf2 AS (wf1 ORDER BY Name) to our WINDOW clause (the WINDOW clause can be used to create multiple named windows, each definition separated by a comma).
So in this case there was no need to update the OVER clause in the SELECT list.