MySQL 8 Recursive CTE Error Code: 1054. Unknown column

Question

This MySQL 8 query:

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT 
        n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT *
FROM cte_count;

Produces:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
+------+

But this one:

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
        ,"one" as `one`
      UNION ALL
      SELECT 
        n + 1 
        ,"one" as `one`
      FROM cte_count 
      WHERE n < 3
    )
SELECT *
FROM cte_count;

generates the error:

Error Code: 1054. Unknown column 'n' in 'field list'

Can you explain why and how to solve that error?

What is the meaning of (n) after WITH RECURSIVE cte_count?

score 3 · Accepted Answer · answered Oct 26 '19 at 15:42

There are 2 ways that you can write this.
Without declaring the column names alongside with the cte's name:

WITH RECURSIVE cte_count  
AS (
      SELECT 1 as n
        ,'one' as `one`
      UNION ALL
      SELECT 
        n + 1 
        ,'one'
      FROM cte_count 
      WHERE n < 3
    )
SELECT *
FROM cte_count;

and declaring them without the need to alias them inside the query's body:

WITH RECURSIVE cte_count (n, `one`) 
AS (
      SELECT 1
        ,'one'
      UNION ALL
      SELECT 
        n + 1 
        ,'one' 
      FROM cte_count 
      WHERE n < 3
    )
SELECT *
FROM cte_count;

score 2 · Answer 2 · answered Oct 26 '19 at 15:43

You can look at WITH RECURSIVE cte_count (n) in a more abstract way. You always need WITH RECURSIVE for your recursive query followed by a name for it (in your case cte_count).

What follows next is a comma separated list which defines the number of attributes for your query as well as the respective names for your columns (e.g. (n)).

The problem in the second query is that you introduced a new column with "one" as 'one'.

Try to only write "one" instead of "one" as 'one' (both times!) and make (n) to (n, one) to fix your problem.

MySQL 8 Recursive CTE Error Code: 1054. Unknown column

2 Answers2