SQL-to-SQL translation falls back to post-processing for valid CubeSQL patterns (chained CTEs, multi-cube joins)
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
## Description
When using the `/v1/sql` translation endpoint (with `format=sql`) to convert CubeSQL into native data-source SQL, certain query patterns that are valid CubeSQL fall back to post-processing instead of being pushed down.
The `/v1/sql` endpoint (`gateway.ts` → `sql4sql()`) returns the native result wrapped as `{ sql: result }`. The native Rust layer (`sql4sql.rs` → `Sql4SqlResponse::to_js`) serializes successful pushdown/regular results with `result.sql` as a `[sqlString, values]` tuple array and `result.status = "ok"`. For post-processing fallbacks, `result.sql` is **absent** — only `result.status = "error"` and `result.error` are set.
This means consumers must check `Array.isArray(response.sql.sql)` to determine whether usable SQL was returned. When this is `false`, the query cannot be executed against the underlying data source — it would require Cube's in-memory engine. For use cases where the translated SQL is sent directly to the data source (e.g., ClickHouse), post-processing results are unusable.
**Cube version:** 1.6.6
**Data source:** ClickHouse
## Issue 1: CTE referencing another CTE triggers post-processing
A `WITH` clause where one CTE references a sibling CTE cannot be pushed down.
### Minimal reproduction
```sql
WITH base_counts AS (
SELECT
some_dimension AS dim,
some_category AS category,
COUNT(*) AS cnt
FROM my_cube
WHERE status = 'completed'
GROUP BY 1, 2
),
dim_totals AS (
SELECT
dim,
SUM(cnt) AS total
FROM base_counts -- ← references sibling CTE
GROUP BY dim
)
SELECT
bc.dim,
bc.category,
ROUND(100.0 * bc.cnt / dt.total, 2) AS percentage
FROM base_counts bc
JOIN dim_totals dt ON bc.dim = dt.dim
ORDER BY 1, 2
```
**Expected:** `response.sql.status === "ok"` with `response.sql.sql` containing a `[sqlString, values]` tuple that can be sent to ClickHouse.
**Actual:** `response.sql.status === "error"`, `response.sql.sql` is absent, `response.sql.query_type === "post_processing"`, `response.sql.error === "Provided query can not be executed without post-processing."`
## Issue 2: Multi-cube `__cubeJoinField` joins + `EXTRACT(EPOCH FROM ...)` fail pushdown
A query joining multiple cubes via Cube's own `__cubeJoinField` syntax combined with `EXTRACT(EPOCH FROM ...)` cannot be pushed down to ClickHouse.
### Minimal reproduction
```sql
SELECT
cube_a.user_id,
cube_b.first_name,
cube_c.start_time,
cube_c.end_time,
(EXTRACT(EPOCH FROM cube_c.end_time) - EXTRACT(EPOCH FROM cube_c.start_time)) / 60 AS duration_minutes
FROM cube_a
LEFT JOIN cube_b ON cube_b.__cubeJoinField = cube_a.__cubeJoinField
LEFT JOIN cube_c ON cube_c.__cubeJoinField = cube_a.__cubeJoinField
WHERE cube_c.status = 'active'
ORDER BY 1
```
**Expected:** `response.sql.status === "ok"` — Cube resolves the `__cubeJoinField` joins and translates `EXTRACT(EPOCH FROM ...)` to ClickHouse-compatible SQL (e.g., `toUnixTimestamp()`).
**Actual:** `response.sql.status === "error"`, `response.sql.sql` is absent, `response.sql.query_type === "post_processing"`.
## Summary
| Pattern | CubeSQL feature? | Pushes down? | Blocker |
|---------|:-:|:-:|---------|
| CTE referencing sibling CTE | Standard SQL | No | `Subquery` node not handled in `generate_sql_for_node` |
| `__cubeJoinField` with 3+ cubes | Cube-specific syntax | No | Multi-way join rewrite doesn't collapse |
| `EXTRACT(EPOCH FROM ...)` on ClickHouse | Standard SQL | No | Missing `expressions/extract` template in ClickHouseQuery |
The `__cubeJoinField` pattern is especially notable — it is **Cube's own virtual join syntax**, so users would reasonably expect the `/v1/sql` translator to handle it. Similarly, CTEs referencing sibling CTEs are standard SQL that most databases support natively.
## Environment
- **Cube version:** 1.6.6
- **Data source:** ClickHouse
- **Endpoint:** `/v1/sql` with `format=sql`
Contributor guide
Assessment
This issue has not been assessed yet.