SQL API fails to translate EXTRACT(DOW ...) to BigQuery DAYOFWEEK in complex WHERE clauses
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
## Bug Report
### Describe the bug
When querying via the SQL API (Postgres wire protocol), `EXTRACT(DOW FROM ...)` is correctly translated to BigQuery's `DAYOFWEEK` in simple cases, but the translation fails in more complex WHERE clauses — the raw `dow` date part is passed to BigQuery, which rejects it.
Error:
```
A valid date part name is required but found dow
```
This occurs with both `CUBEJS_TESSERACT_SQL_PLANNER=true` and `false`.
### Passing cases (translation works)
```sql
-- Simple: EXTRACT(DOW FROM CURRENT_DATE) in WHERE
SELECT COUNT(*) as order_count
FROM test_orders
WHERE EXTRACT(DOW FROM CURRENT_DATE) = 1
-- With modulo + INTERVAL arithmetic
SELECT COUNT(*) as order_count
FROM test_orders
WHERE CAST(test_orders.delivered_on_ts_cast AS DATE) < (
CAST(DATE_TRUNC('DAY', CAST(CURRENT_DATE AS TIMESTAMP)) AS DATE) - (
((7 + CAST(EXTRACT(DOW FROM CURRENT_DATE) AS BIGINT) - 1) % 7) * INTERVAL '1 DAY'
)
)
```
### Failing cases (translation breaks)
**Case 1:** `EXTRACT(DOW FROM column_reference)` in WHERE:
```sql
SELECT COUNT(*) as order_count
FROM test_orders
WHERE
EXTRACT(DOW FROM test_orders.delivered_on_ts_cast) = 1
AND test_orders.delivered_on IS NOT NULL
```
**Case 2:** CASE statement + EXTRACT(DOW) + multiple AND conditions:
```sql
SELECT COUNT(*) as order_count
FROM test_orders
WHERE
CAST(test_orders.delivered_on_ts_cast AS DATE) < (
CAST(DATE_TRUNC('DAY', CAST(CURRENT_DATE AS TIMESTAMP)) AS DATE) - (
((7 + CAST(EXTRACT(DOW FROM CURRENT_DATE) AS BIGINT) - 1) % 7) * INTERVAL '1 DAY'
)
)
AND (
CASE
WHEN test_orders.customer_id = 'CUST-001' THEN 'outbound'
WHEN test_orders.customer_id = 'CUST-002' THEN 'outbound'
ELSE 'inbound'
END
) = 'outbound'
AND test_orders.status = 'completed'
AND test_orders.delivered_on IS NOT NULL
```
### To Reproduce
**Cube version:** 1.6.30
**Database:** BigQuery
### Expected behaviour
The `DOW` → `DAYOFWEEK` translation should apply consistently regardless of WHERE clause complexity, whether the argument is `CURRENT_DATE` or a column reference, and whether the query includes CASE expressions.
### Additional context
- Reproduces with both Tesseract enabled and disabled
- Query patterns originate from Tableau connecting via the SQL API (Postgres wire protocol)
- The passing cases suggest the translation logic exists but doesn't cover all code paths — it works with CURRENT_DATE but fails with column references and more complex WHERE clauses
Contributor guide
Assessment
This issue has not been assessed yet.