dbt-labs/dbt-redshift

[Bug] Previewing Recursive CTE's Throws Error "Recursive CTE in subquery are not supported."

migueldichoso opened this issue · 10 comments

Is this a new bug in dbt-redshift?

  • I believe this is a new bug in dbt-redshift
  • I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When I preview a recursive CTE in dbt cloud IDE, I am getting an error Recursive CTE in subquery are not supported. The same recursive CTE query run successfully with dbt run or dbt build. It also works fine with other datawarehouse such as Snowflake.
image

Expected Behavior

The Preview button should show successful output when previewed the recursive CTE query.
image

Steps To Reproduce

  1. Preview the below recursive query in dbt cloud IDE connected to Redshift.
with recursive daily_dates(upload_day) AS (
select
'2024-01-01'::date as upload_day
union all
select
(upload_day + interval '1 day')::date as upload_day
from daily_dates
where upload_day < current_timestamp
)

select * from daily_dates
  1. The below error should appear.
Runtime Error Database Error in sql_operation inline_query (from remote system.sql) Recursive CTE in subquery are not supported.
Encountered an error: Runtime Error Database Error in sql_operation inline_query (from remote system.sql) Recursive CTE in subquery are not supported.

Relevant log output

1. The below is log output from the `Preview` button.

Began running node model.my_new_project.test_recursive_cte
Acquiring new redshift connection 'model.my_new_project.test_recursive_cte'
Began compiling node model.my_new_project.test_recursive_cte
Writing injected SQL for node "model.my_new_project.test_recursive_cte"
Timing info for model.my_new_project.test_recursive_cte (compile): 03:27:57.672761 => 03:27:57.697862
Began executing node model.my_new_project.test_recursive_cte
Using redshift connection "model.my_new_project.test_recursive_cte"
On model.my_new_project.test_recursive_cte: /* {"app": "dbt", "dbt_version": "1.7.13", "profile_name": "user", "target_name": "default", "node_id": "model.my_new_project.test_recursive_cte"} */
  
    select *
    from (
        with recursive daily_dates(upload_day) AS (
select
'2024-01-01'::date as upload_day
union all
select
(upload_day + interval '1 day')::date as upload_day
from daily_dates
where upload_day < current_timestamp
)

select * from daily_dates
    ) as model_limit_subq
    limit 500
Opening a new connection, currently in state init
Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
Redshift adapter: Connecting to redshift with username/password based auth...
Redshift adapter: Redshift error: Recursive CTE in subquery are not supported.
Timing info for model.my_new_project.test_recursive_cte (execute): 03:27:57.699028 => 03:27:57.728949
On model.my_new_project.test_recursive_cte: Close
Database Error in model test_recursive_cte (models/example/test_recursive_cte.sql)
  Recursive CTE in subquery are not supported.
Finished running node model.my_new_project.test_recursive_cte
  1. The below is the log output using dbt run.
03:26:09 SQL status: SUCCESS in 0.0 seconds
03:26:09 Using redshift connection "model.my_new_project.test_recursive_cte"
03:26:09 On model.my_new_project.test_recursive_cte: /* {"app": "dbt", "dbt_version": "1.7.13", "profile_name": "user", "target_name": "default", "node_id": "model.my_new_project.test_recursive_cte"} */

  
    

  create  table
    "dev"."dbt_mdichoso"."test_recursive_cte__dbt_tmp"
    
    
    
  as (
    with recursive daily_dates(upload_day) AS (
select
'2024-01-01'::date as upload_day
union all
select
(upload_day + interval '1 day')::date as upload_day
from daily_dates
where upload_day < current_timestamp
)

select * from daily_dates
  );
03:26:16 SQL status: SUCCESS in 6.0 seconds
03:26:16 Using redshift connection "model.my_new_project.test_recursive_cte"
03:26:16 On model.my_new_project.test_recursive_cte: /* {"app": "dbt", "dbt_version": "1.7.13", "profile_name": "user", "target_name": "default", "node_id": "model.my_new_project.test_recursive_cte"} */
alter table "dev"."dbt_mdichoso"."test_recursive_cte__dbt_tmp" rename to "test_recursive_cte"
03:26:16 SQL status: SUCCESS in 0.0 seconds


### Environment

```markdown
- OS: macOS
- Python: using dbt cloud
- dbt-core: 1.7.13
- dbt-redshift: 1.7.13

Additional Context

No response