SeedTable.SomeColumn +1 in a recursive CTE not working as expected

Question

I'm trying this query in Redshift:

WITH  RECURSIVE CTE AS
        (
        SELECT  id
        ,       managerid
        ,       name
        ,       1 AS level
        FROM    users parent
        WHERE   name = 'Jason Ramsay'
        UNION ALL
        SELECT  child.id
        ,       child.managerid
        ,       child.name
        ,       level +1
        FROM    users child, users parent
        WHERE   child.managerid = parent.id
        )
SELECT *
FROM    CTE

but I keep getting this error: (500310) Invalid operation: column "level" does not exist in child, parent; 1 statement failed.

What am I doing wrong? According to the documentation I found here, Redshift accepts recursive and this is the way to go about it: https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html#r_WITH_clause-recursive-cte

Answer 1

I believe that for recursive cte, the cte itself must be referenced in the union all part:

WITH RECURSIVE cte AS (
    SELECT id
         , managerid
         , name
         , 1 AS level
    FROM users parent
    WHERE name = 'Jason Ramsay'

    UNION ALL

    SELECT child.id
         , child.managerid
         , child.name
         , level + 1
    FROM cte parent -- <<-- this
    JOIN users child ON parent.id = child.managerid
)
SELECT *
FROM cte

The documentation you linked to clearly mentions this:

recursive_query A UNION ALL query that consists of two SELECT subqueries:

• The first SELECT subquery doesn't have a recursive reference to the same CTE_table_name. [...]

• The second SELECT subquery references the same CTE_table_name in its FROM clause. [...]