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

[migueldichoso]

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.

Expected Behavior

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

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

2. 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

2. 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

[Caitlin-Syntax]

Seconding this! I have a few instances where I need to use recursion, and it's annoying not to be able to build/test my work the same way I do for everything else. I get the same error, even though my queries run normally and do not error in the AWS Console/Redshift Query Editor, and dbt is able to build and test the resulting tables successfully.

[jtcohen6]

Thanks @Caitlin-Syntax! I think we're going to resolve this issue by changing the default behavior of get_show_sql to avoid wrapping in a subquery, given edge cases like this one:

• Change the default behavior of get_show_sql to avoid wrapping in subquery dbt-adapters#207

[ashrafkasmipfs]

Any news on this bug ?
Our pipelines are blocked by this issue :/

[Caitlin-Syntax]

@ashrafkasmipfs you likely have a different issue if your pipelines are blocked. This only affects the Preview functionality in the Cloud IDE; it does not impact Build, Run, or Test functionality in the Cloud IDE (or elsewhere), and it does not affect Job execution. The problem here is that the Preview function wraps up the compiled SQL as a subquery to apply a LIMIT clause (e.g. SELECT * FROM (your query) as model_limit_subq limit 500), but Build doesn't add the extra wrapper. It could be that you have a macro or something else that's causing your recursive CTE to get wrapped.

[givemelove]

I'm affected by this issue. We're using BigQuery instead of redshift. Our query runs just fine on BQ, yet I can't run / build the model in DBT. No matter what, it will run a select * FROM with my query below, thus throwing the following error: WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.

select * from (WITH RECURSIVE ...

[Caitlin-Syntax]

This issue is resolved for me - I just used Preview in the Cloud IDE for a recursive query!

I'm assuming this is a result of the fix for Issue 207 in PR 249.

[brooketran1]

Not working for me yet. Still getting the error: "Recursive CTE in subquery are not supported."

[Caitlin-Syntax]

@brooketran1 I saw the changed behavior after switching my Dev Environment from a pretty old version up to Cloud Versionless; I assume this hasn't been fixed in all older versions. If that isn't the issue for you/upgrading isn't an option for you, there are some templates for overriding get_show_sql that you could try in the meantime detailed on these issues:
dbt-labs/dbt-adapters#207
dbt-labs/dbt-core#9600