SQL API: ungrouped query selecting a group_by-locked multi-stage measure generates aggregates without GROUP BY (rejected by Postgres)
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
## Describe the bug
When the SQL API receives an **ungrouped** query (a bare `SELECT` without `GROUP BY` — exactly what BI tools like Metabase send when a user browses a table) that selects a **multi-stage measure locked with `group_by` / `reduce_by`** together with at least one dimension, the generated data-source SQL contains plain aggregate functions (`sum(...)`) next to bare dimension columns **but no `GROUP BY` clause anywhere**. Any database that enforces SQL grouping rules rejects it:
```
ERROR: Database Execution Error: column "fk_aggregate_keys.orders__city"
must appear in the GROUP BY clause or be used in an aggregate function
```
Grouped queries (`GROUP BY` + `MEASURE(...)`) over the same members work correctly. Ungrouped queries selecting only plain measures, or only dimensions, or the multi-stage measure without any dimension, also work.
Real-world impact: Metabase's table-browse view emits exactly this bare-select shape, so any table synced through the SQL API whose view exposes such a measure is un-browsable.
## To Reproduce
Data model (any Postgres source table `public.orders(id, customer_id, status, city)`):
```yaml
cubes:
- name: orders
sql_table: public.orders
dimensions:
- name: status
sql: status
type: string
- name: city
sql: city
type: string
measures:
- name: unique_customers
type: count_distinct
sql: customer_id
# multi-stage measure: total locked to `status` grain
- name: unique_customers_by_status
multi_stage: true
type: sum
sql: "{unique_customers}"
group_by:
- status
```
Connect any Postgres client to the SQL API and run:
```sql
SELECT unique_customers_by_status, city FROM orders LIMIT 1;
```
The query fails with the `must appear in the GROUP BY clause` error from the upstream database. Selecting the measure alone (no dimension) works; adding any dimension fails. `SELECT status, MEASURE(unique_customers_by_status) FROM orders GROUP BY 1` works.
Note the inner measure being non-sum-rollable (`count_distinct`, `avg`, `max`, …) matters: with a plain `sum`-over-`sum`/`count` the planner takes the window-function path (`sum(...) OVER (...)`), which is valid without `GROUP BY`, so the bug is masked.
## Generated SQL (simplified)
The multi-stage Aggregate stage CTE is rendered as:
```sql
cte_2 AS (
SELECT
"fk_aggregate_keys"."orders__status" "orders__status",
"fk_aggregate_keys"."orders__city" "orders__city",
sum("q_0"."orders__unique_customers") "orders__unique_customers_by_status"
FROM (SELECT DISTINCT "orders__status", "orders__city" FROM cte_1) AS "fk_aggregate_keys"
LEFT JOIN (SELECT * FROM cte_0) AS "q_0"
ON ("fk_aggregate_keys"."orders__status" = "q_0"."orders__status")
-- missing: GROUP BY "fk_aggregate_keys"."orders__status", "fk_aggregate_keys"."orders__city"
)
```
`sum()` next to bare dimension columns, and no `GROUP BY` in the whole statement.
## Root cause (traced in v1.7.7 sources)
The statement is produced by the Tesseract planner (`rust/cube/cubesqlplanner`):
1. The SQL API pushdown marks the whole query `ungrouped: true`.
2. [`planner/planners/multi_stage/multi_stage_query_planner.rs#L168-L171`](https://github.com/cube-js/cube/blob/v1.7.7/rust/cube/cubesqlplanner/cubesqlplanner/src/planner/planners/multi_stage/multi_stage_query_planner.rs#L168-L171) — an **Aggregate** inode inherits `is_ungrupped` from the top-level query's `ungrouped` flag. (Rank/Calculate inodes are always `true`, which is fine — they render window functions / plain expressions, not bare aggregates.)
3. [`planner/planners/multi_stage/member_query_planner.rs#L298`](https://github.com/cube-js/cube/blob/v1.7.7/rust/cube/cubesqlplanner/cubesqlplanner/src/planner/planners/multi_stage/member_query_planner.rs#L298) — the flag is stored on `MultiStageMeasureCalculation`.
4. [`physical_plan_builder/processors/multi_stage_measure_calculation.rs#L61`](https://github.com/cube-js/cube/blob/v1.7.7/rust/cube/cubesqlplanner/cubesqlplanner/src/physical_plan_builder/processors/multi_stage_measure_calculation.rs#L61) — `GROUP BY` is emitted only `if !measure_calculation.is_ungrouped()`, so it is skipped.
5. But the measure projection in the same select renders the outer aggregate function (`sum(...)`) unconditionally: the ungrouped-measure rendering path (`physical_plan/sql_nodes/factory.rs#L220`) applies only to leaf CTEs via `measure_for_ungrouped`, never to the Aggregate calculation stage.
So for `calculation_type == Aggregate` with `window_function_to_use == None`, the ungrouped flag suppresses `GROUP BY` while the projection still contains plain aggregates → invalid SQL. Reproduced on v1.7.1 through v1.7.7; the relevant code is unchanged on current master.
## Expected behavior
A non-windowed Aggregate stage must always be grouped by its projected dimensions — it renders plain aggregate functions by construction. The query-level ungrouped semantics are preserved because the enclosing `FullKeyAggregate` join broadcasts the stage output back to detail rows.
## Version
- Cube: v1.7.1 … v1.7.7 (Docker `cubejs/cube`), code unchanged on master
- Data source: PostgreSQL (any version enforcing standard grouping rules)
- Client: any Postgres client via SQL API (reproduced with psql and Metabase v0.63.x)
Contributor guide
Research direction
Start with planner/planners/multi_stage/multi_stage_query_planner.rs, member_query_planner.rs, and physical_plan_builder/processors/multi_stage_measure_calculation.rs; trace how ungrouped state reaches Aggregate calculations and SQL rendering in physical_plan/sql_nodes/factory.rs. Reproduce the described query against PostgreSQL and verify that non-windowed Aggregate stages include GROUP BY for their projected dimensions while the ungrouped query still works.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, rust, sql
- Domain
- api, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 65/100