Redshift递归CTE结合数组展开报错：set_cte_path无法找到CTE dates

递归CTE dates 单独执行时完全正常，数组展开操作单独执行也无问题，但将二者结合在同一条查询中时，Amazon Redshift会抛出错误：ERROR: set_cte_path cannot find CTE dates。

报错的完整查询代码 #

with recursive
    start_dt as (select '2024-12-01'::date s_dt),
    end_dt as (select dateadd(day, 1, '2025-01-01'::date)::date e_dt),
    -- 递归CTE，声明列`dt`
    dates (dt) as (
        -- 起始日期
        select s_dt dt from start_dt
        union all
        -- 递归生成后续日期
        select dateadd(day, 1, dt)::date dt  -- 转换为date类型避免类型不匹配
        from dates
        where dt <= (select e_dt from end_dt)  -- 终止条件：到结束日期为止
),
all_dates as (
    select dt as range_date
    from dates
),
test_data as (
    select 'test1' as account_id, '2025-01-01' as date, array('123', '456') as ids_array
    union
    select 'test2' as account_id, '2025-01-01' as date, array('789', '101') as ids_array
),
test_data_with_dates as (
    select td.*
    from test_data td
    join all_dates d on d.range_date = td.date
)
select tdd.account_id, id from test_data_with_dates as tdd, tdd.ids_array as id

单独运行正常的场景 #

1. 仅递归CTE的查询 #

代码

with recursive
    start_dt as (select '2024-12-01'::date s_dt),
    end_dt as (select dateadd(day, 1, '2025-01-01'::date)::date e_dt),
    -- 递归CTE，声明列`dt`
    dates (dt) as (
        -- 起始日期
        select s_dt dt from start_dt
        union all
        -- 递归生成后续日期
        select dateadd(day, 1, dt)::date dt  -- 转换为date类型避免类型不匹配
        from dates
        where dt <= (select e_dt from end_dt)  -- 终止条件：到结束日期为止
),
all_dates as (
    select dt as range_date
    from dates
),
test_data as (
    select 'test1' as account_id, '2025-01-01' as date, array('123', '456') as ids_array
    union
    select 'test2' as account_id, '2025-01-01' as date, array('789', '101') as ids_array
),
test_data_with_dates as (
    select td.*
    from test_data td
    join all_dates d on d.range_date = td.date
)
select tdd.* from test_data_with_dates as tdd

查询结果

account_id,date,ids_array
test2,2025-01-01,["789","101"]
test1,2025-01-01,["123","456"]

2. 仅数组展开的查询 #

代码

with test_data as (
    select 'test1' as account_id, '2025-01-01' as date, array('123', '456') as ids_array
    union
    select 'test2' as account_id, '2025-01-01' as date, array('789', '101') as ids_array
),
test_data_with_dates as (
    select td.*
    from test_data td
)
select tdd.account_id, id from test_data_with_dates as tdd, tdd.ids_array as id

查询结果

account_id,id
test1,"123"
test1,"456"
test2,"789"
test2,"101"

解决方法 #

该问题大概率是Redshift解析器在处理递归CTE与隐式数组展开（逗号分隔语法）结合时的bug，可通过以下两种方式规避：

方式1：改用显式unnest函数 #

将隐式数组展开的语法替换为显式调用unnest函数，修改后的查询代码：

with recursive
    start_dt as (select '2024-12-01'::date s_dt),
    end_dt as (select dateadd(day, 1, '2025-01-01'::date)::date e_dt),
    dates (dt) as (
        select s_dt dt from start_dt
        union all
        select dateadd(day, 1, dt)::date dt
        from dates
        where dt <= (select e_dt from end_dt)
),
all_dates as (
    select dt as range_date
    from dates
),
test_data as (
    select 'test1' as account_id, '2025-01-01'::date as date, array('123', '456') as ids_array
    union
    select 'test2' as account_id, '2025-01-01'::date as date, array('789', '101') as ids_array
),
test_data_with_dates as (
    select td.*
    from test_data td
    join all_dates d on d.range_date = td.date
)
select tdd.account_id, unnest(tdd.ids_array) as id 
from test_data_with_dates as tdd

方式2：物化递归CTE结果 #

先将递归CTE生成的日期数据存入临时表，再进行后续的关联和数组展开操作：

-- 物化递归CTE结果到临时表
create temp table temp_dates as
with recursive
    start_dt as (select '2024-12-01'::date s_dt),
    end_dt as (select dateadd(day, 1, '2025-01-01'::date)::date e_dt),
    dates (dt) as (
        select s_dt dt from start_dt
        union all
        select dateadd(day, 1, dt)::date dt
        from dates
        where dt <= (select e_dt from end_dt)
    )
select dt as range_date from dates;

-- 执行后续查询
with test_data as (
    select 'test1' as account_id, '2025-01-01'::date as date, array('123', '456') as ids_array
    union
    select 'test2' as account_id, '2025-01-01'::date as date, array('789', '101') as ids_array
),
test_data_with_dates as (
    select td.*
    from test_data td
    join temp_dates d on d.range_date = td.date
)
select tdd.account_id, id from test_data_with_dates as tdd, tdd.ids_array as id;

内容的提问来源于stack exchange，提问作者mochatiger