dbt-labs / dbt-labs/dbt

[BUG] [DuckDB] Parser rejects valid CASE...END followed by comparison operator (dbt0209)

Open
#14,539 5 comments 0 reactions 0 assignees View on GitHub
adapter:duckdb area:static-analysis engine:v2 status:needs-repro status:triage type:bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.