CTE results in Redshift error "column reference is ambiguious for code"

Question

What is causing the error "column reference "col_1" is ambiguous?

WITH    cte1 AS
        (
        SELECT  col_1, col_4
        from table_1
        ),
        cte2 AS
        (
        SELECT  two.col_1, two.col_5, three.col_6
        from table_2 as two
        left join table_3 as three
        on two.col_1 = three.col_1 
        ),
        cte3 AS
        (
        SELECT col_1, col_10
        from table_4
)

SELECT  cte1.col_1, cte1.col_4,
        cte2.col_1, cte2.col_5, cte2.col_6,
        cte3.col_1, cte3.col_10
FROM cte1
left join cte2 on cte1.col_1 = cte2.col_6
left join cte3 on cte1.col_1 = cte3.col_10
; 

I suspect cte2 is causing the ambiguous column name issue in the final outer query?

Answer 1

Please check below query. Though it's using garbage data there is no ambiguous reference in the query.

 WITH    cte1 AS
         (
         SELECT  col_1, col_4
         from (select 1 col_1,2 col_4)d
         ),
         cte2 AS
         (
         SELECT  two.col_1, two.col_5, three.col_6
         from (select 1 col_1, 2 col_5) as two
         left join (select 1 col_1,1 col_6) as three
         on two.col_1 = three.col_1 
         ),
         cte3 AS
         (
         SELECT col_1, col_10
         from (select 1 col_1, 2 col_10) table_4
 )
 
 SELECT  cte1.col_1, cte1.col_4,
         cte2.col_1, cte2.col_5, cte2.col_6,
         cte3.col_1, cte3.col_10
 FROM cte1
 left join cte2 on cte1.col_1 = cte2.col_6
 left join cte3 on cte1.col_1 = cte3.col_10

Output:

col_1	col_4	col_1	col_5	col_6	col_1	col_10
1	2	1	2	1	null	null

db<fiddle here