If you’re getting error 3580 in MySQL, which reads “There is a circularity in the window dependency graph“, it’s probably because you’re using named windows that refer to each other in a way that creates a circular reference.
To fix this issue, make sure you don’t create a circular reference when referring to multiple named windows.
Example of Error
Here’s an example of code that produces the error:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf3)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (PARTITION BY District), wf2 AS (wf3), wf3 AS (wf2)
ORDER BY District, Name, "City Population";Result:
ERROR 3580 (HY000): There is a circularity in the window dependency graph.
Here, I’m using a WINDOW clause to create multiple named windows (which can be referred to in the OVER clause). However, in this case I’ve created a circular reference, which isn’t allowed in MySQL. In particular, wf2 references wf3, but wf3 references wf2. This creates a circular reference, which is not allowed, and so we get the error.
Solution
The solution is simple. Don’t create a circular reference.
Here’s an example of fixing the above issue:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf3)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (PARTITION BY District), wf2 AS (wf1 ORDER BY Name ASC), wf3 AS (wf2)
ORDER BY District, Name, "City Population";Result:
+-----------------+---------------+------------+---------------------------+ | District | Name | Population | SUM(Population) OVER(wf3) | +-----------------+---------------+------------+---------------------------+ | 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 changed the definition of wf2 to reference wf1 instead of wf3. This allows wf3 to reference wf2 without creating a cycle, which therefore eliminates the error.
That’s just one example of how we can fix this error. Another way to fix it is to remove any unnecessary named window declarations. For example, we could remove wf3 altogether if we deem it to be redundant:
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 ASC)
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)
Of course, in this case we also had to update our SELECT list to refer to wf2 instead of wf3.