EC
3170
MySQLERRORNotableWindow FunctionsHIGH confidence

Window functions cannot be nested

Production Risk

Low — syntax error; restructure with a CTE.

What this means

MySQL does not support nesting window functions — a window function argument or window specification cannot itself contain another window function call.

Why it happens
  1. 1Using a window function as an argument to another window function.
How to reproduce
trigger — this will error
trigger — this will error
SELECT SUM(ROW_NUMBER() OVER (ORDER BY id)) OVER (PARTITION BY cat) FROM t1;

expected output

ERROR 3170 (HY000): Window function cannot be used in the specification of a window function.

Fix

Use a CTE to pre-compute the inner window function

Use a CTE to pre-compute the inner window function
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM t1)
SELECT cat, SUM(rn) OVER (PARTITION BY cat) FROM ranked;

Why this works

The inner window function is materialised first; the outer one then operates on the result.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 3170 ER_WINDOW_NESTED_WINDOW_FUNC_USE_IN_WINDOW_SPEC

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors