cube-js / cube-js/cube

SQL API: ungrouped query selecting a group_by-locked multi-stage measure generates aggregates without GROUP BY (rejected by Postgres)

Open
#11,327 2 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.