[Bug] Previewing Recursive CTE's Throws Error "Recursive CTE in subquery are not supported."
migueldichoso opened this issue · 10 comments
migueldichoso commented
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
- 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
- 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
- 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