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

migueldichoso commented 3 months ago

Is this a new bug in dbt-redshift?

[X] I believe this is a new bug in dbt-redshift
[X] 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

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

```shell
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

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 commented 2 months ago

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 commented 2 months ago

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:

https://github.com/dbt-labs/dbt-adapters/issues/207

ashrafkasmipfs commented 1 week ago

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

Caitlin-Syntax commented 1 week ago

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

dbt-labs / dbt-redshift