[BUG] [DuckDB] Parser rejects valid CASE...END followed by comparison operator (dbt0209)
- Dominant language
- Rust
- Stars
- 13.8k
- Forks
- 2.6k
- Avg merge
- 21h 31m
- Merged PRs (30d)
- 56
Description
## Description
dbt-fusion's SQL parser (error code `dbt0209`) rejects valid SQL where a `CASE...END` expression is immediately followed by a comparison operator. This pattern is valid in DuckDB (and all standard SQL dialects) but Fusion's DuckDB parser fails to parse it.
This was discovered via the ADE-Bench helixops_saas project, where the pattern appears in two shared upstream models (`int_support_sla.sql` and `int_account_users.sql`), causing parse failures that cascade to 6+ downstream models per task.
## Failing SQL (from `int_support_sla.sql`)
```sql
select
t.ticket_id,
t.priority,
t.first_response_minutes,
case
when t.priority = 'urgent' then 30
when t.priority = 'high' then 60
when t.priority = 'medium' then 240
else 1440
end as response_sla_minutes,
case
when t.priority = 'urgent' then 30
when t.priority = 'high' then 60
when t.priority = 'medium' then 240
else 1440
end >= t.first_response_minutes as met_response_sla
from tickets t
```
The key pattern is on the last CASE expression: `CASE ... END >= t.first_response_minutes as met_response_sla` — the CASE result is compared with `>=` and aliased.
## Minimal reproduction model
```sql
-- models/case_end_comparison.sql
select
1 as id,
'urgent' as priority,
25 as first_response_minutes,
case
when 'urgent' = 'urgent' then 30
when 'urgent' = 'high' then 60
when 'urgent' = 'medium' then 240
else 1440
end as response_sla_minutes,
case
when 'urgent' = 'urgent' then 30
when 'urgent' = 'high' then 60
when 'urgent' = 'medium' then 240
else 1440
end >= 25 as met_response_sla
```
With a DuckDB profile and minimal `dbt_project.yml`, run `dbt run`.
## Exact error message
From dbt-fusion 2.0.0-preview.173 on DuckDB:
```
error: dbt0209: mismatched input '(' expecting {, '(+)', '::', 'AND', 'AT', 'BETWEEN', 'IN', 'IS', 'ISNULL', 'ILIKE', 'LIKE', 'NOT', 'NOTNULL', 'OR', 'SIMILAR', '[', '.', '=', '==', NEQ, '<', '<=', '>', '>=', '+', '->>', '->', '-', '**', '//', '*', '/', '%', '||', '&', '|', '#', '^', '<<', '>>', '~', '~~', '~~*', '!~~', '!~~*', '~*'}
--> models/intermediate/int_support_sla.sql:1:18 (target/compiled/models/intermediate/int_support_sla.sql:1:18)
```
Note: The same error (same error code, same error message, same position `1:18`) also appears for `int_account_users.sql`, which uses `date_diff('day', cast(u.last_login_at as date), cast(now() as date))` — suggesting the parser issue may be broader than just `CASE...END` followed by comparison.
## Expected behavior
The SQL should parse and execute successfully, just as it does with:
- **dbt-core 1.10.11 on DuckDB** (confirmed: all models pass)
- **DuckDB CLI** (standard valid SQL)
## Impact
- **22 ADE-Bench task configurations affected** (all helixops_saas variants)
- 2 models fail to parse per task (`int_support_sla`, `int_account_users`)
- 6 downstream models skipped per task due to upstream failures (`dim_accounts`, `fct_daily_account_usage`, `int_account_engagement`, `fct_support_tickets`, `mart_account_health`, `mart_account_360`)
- Net effect: 8 of 28 models (29%) broken per task run
## Environment
- dbt-fusion: 2.0.0-preview.173
- Target: DuckDB
- Does NOT reproduce on Snowflake target
---
## Reproduction project
A minimal dbt project is attached as `dbt0209-repro.zip`. Download and run:
```bash
unzip dbt0209-repro.zip && cd dbt0209-repro
# dbt-core (works):
dbt run --profiles-dir .
# dbt-fusion (fails with dbt0209):
dbt run --profiles-dir .
```
Contributor guide
Assessment
This issue has not been assessed yet.