CODEWITHSUNDEEP
mysqlcommon-table-expressionrecursive-query
Jimmix
Jimmix

Reputation: 6536

MySQL 8 Recursive CTE Error Code: 1054. Unknown column

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?

Upvotes: 0

Views: 1077

Answers (2)

L483
L483

Reputation: 226

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.

Upvotes: 2

forpas
forpas

Reputation: 164174

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;

Upvotes: 3

Related Questions