ClickHouse Error 215: Column not in GROUP BY when using virtual tables in charts (Superset 6.1.0 + ClickHouse 25.3.1.2703)

[manyyy]

Bug description

Affected Version: Apache Superset 6.1.0
Database: ClickHouse 25.3.1.2703 (official build)
Component: Chart SQL generation (virtual table queries)

Description

When rendering charts in Superset that use a virtual table as a data source, the generated SQL query causes a ClickHouse error (code 215). The issue arises due to a mismatch in how the GROUP BY clause references a column that is defined in the SELECT clause via a function.

Steps to Reproduce

1. Create or edit a chart in Superset using a dataset based on a virtual table (SQL query).
2. Configure the chart to group data by a date field truncated to the day level (e.g., using dateTrunc('DAY', ...)).
3. Execute the query to render the chart.

Actual Behaviour

Superset generates a SQL query where the GROUP BY clause repeats the full expression from the SELECT clause:

SELECT
  dateTrunc('DAY', toDateTime("date")) AS "date",
  ...
FROM (
  ...
) AS "virtual_table"
WHERE
  ...
GROUP BY
  dateTrunc('DAY', toDateTime("date"))
ORDER BY
  ...
LIMIT 10000

This query fails on ClickHouse with the following error:

HTTPDriver for http://ch-s21.cgorod.pw:8123/ received ClickHouse error code 215. Code: 215. DB::Exception: Column virtual_table.date is not under aggregate function and not in GROUP BY keys.

Expected Behaviour

ClickHouse expects the GROUP BY clause to reference the aliased column name ("date") instead of the full expression. The query should be generated as follows:

SELECT
  dateTrunc('DAY', toDateTime("date")) AS "date",
  ...
FROM (
  ...
) AS "virtual_table"
WHERE
  ...
GROUP BY
  "date"
ORDER BY
  ...
LIMIT 10000

With this syntax, the query executes successfully and the chart renders correctly.

Root Cause Analysis

The SQL query generator in Superset (version 6.1.0) does not properly handle column aliases in the GROUP BY clause when the data source is a virtual table. Instead of using the alias defined in the SELECT statement, it duplicates the original expression. While this approach might work in some databases, ClickHouse strictly requires that non‑aggregated columns in the SELECT list must be explicitly listed in the GROUP BY clause by their output name (alias).

Proposed Solution

Modify the SQL generation logic for virtual tables to:

• detect when a column in the SELECT clause is an expression with an alias;
• ensure that the GROUP BY clause uses the column alias (e.g., "date") rather than the full expression (dateTrunc('DAY', toDateTime("date"))).

This change would align the generated SQL with ClickHouse’s requirements and prevent error 215.

Additional Information

• Error Code: 215 (DB::Exception)
• ClickHouse Documentation Reference: The behaviour is consistent with ClickHouse documentation regarding GROUP BY requirements.
• Workaround: Manually edit the SQL query in the chart’s datasource to replace the expression in GROUP BY with the column alias.

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

I don't know

Node version

I don't know

Browser

Not applicable

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[sadpandajoe]

@dosu any insights on this?

[dosubot]

@sadpandajoe Here's what I found:

Root Cause: Superset's GROUP BY generation uses SQLAlchemy Label objects (because allows_alias_in_select = True by default), which causes SQLAlchemy to emit the alias name in GROUP BY. However, when the data source is a virtual table (subquery), ClickHouse cannot resolve the original column reference inside the subquery context — it sees dateTrunc('DAY', toDateTime("date")) referencing virtual_table.date, which isn't directly in GROUP BY keys, triggering error 215.

The relevant code path is in get_sqla_query() [1] where groupby expressions are built, and then applied via qry.group_by(*groupby_all_columns.values()) [2].

Key findings:

1. No virtual-table-aware GROUP BY mechanism exists. Superset has allows_alias_in_orderby which strips labels for ORDER BY when False [3], but there's no equivalent check in the GROUP BY logic. ClickHouse inherits the default flags (allows_alias_in_select = True) and doesn't distinguish between physical and virtual tables [4].

2. PR fix(clickhouse): remove _mutate_label workaround and bump clickhouse-connect to >=0.13.0 #38280 (remove _mutate_label, bump clickhouse-connect to >=0.13.0) does NOT appear to be merged — the _mutate_label method is still present in the codebase [5] and the dependency still reads clickhouse-connect>=0.5.14, <1.0 [6]. That PR may have helped tangentially but wouldn't fully resolve this subquery scoping issue.

3. time_groupby_inline = True [7] is set for ClickHouse but only affects WHERE clause generation in subqueries, not GROUP BY alias behavior.

Possible fix paths:

• Targeted: Add an allows_alias_in_groupby flag (or reuse allows_alias_in_orderby for GROUP BY too) and set it to False for ClickHouse. The ORDER BY logic already demonstrates this pattern — stripping labels from expressions when the flag is False.
• More precise: Override get_allows_alias_in_select() in ClickHouseConnectEngineSpec to return False when the dataset is a virtual table, which would cause make_sqla_column_compatible() to skip labeling [8].
• Simplest workaround for users: Restructure the virtual table SQL to apply the time truncation inside the inner query, so the outer query only references a pre-computed column.

This does look like a legitimate gap in Superset's SQL generation for ClickHouse virtual tables.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[manyyy]

Hi,

We’d prefer the precise fix:

Override get_allows_alias_in_select() in ClickHouseConnectEngineSpec to return False when the dataset is a virtual table, which would cause make_sqla_column_compatible() to skip labeling

Could you estimate:

• how long the implementation and testing might take?
• when it could land in a release?

Thanks!

@dosu

[dosubot]

@manyyy Thanks for the feedback on the preferred approach. Here's an honest assessment:

Implementation complexity is higher than it appears. The current get_allows_alias_in_select() signature only receives a Database object — it has no access to whether the current dataset is a virtual table [1]. The is_virtual flag exists on the SqlaTable model [2] and flows through query generation, but it's never passed down to the engine spec layer where this decision is made [3].

Realistic options with estimates:

Approach	Effort	Risk
(A) Modify method signature to pass datasource context — cleanest but breaking change to 40+ engine specs	5–7 days	Medium-high (interface change)
(B) Targeted call-site fix — check is_virtual + engine type directly in make_sqla_column_compatible() before calling the engine spec method	2–3 days	Low (no interface changes)
(C) Context variable — thread virtual-table info via contextvars without signature changes	3–4 days	Medium (hidden dependency, async concerns)

Option (B) is probably the most pragmatic path for a near-term fix — it avoids breaking the engine spec interface while still providing automatic detection. It's not architecturally pristine (engine-specific logic in generic code), but it's contained and testable.

Release timeline: Superset follows a roughly monthly patch cadence. A targeted fix like Option B could realistically land in a patch release within 1–2 months of being merged, assuming a contributor picks it up and it passes review.

That said, I can't commit to timelines on behalf of the maintainers — this depends on contributor availability and review bandwidth. If you or someone on your team is interested in submitting a PR, Option B would be the most straightforward to implement and get through review.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[cpassnat]

I have a similar issue using a regular dataset. It seems to be a regression since the issue was not present in v4.1.1

Query generated with v4.1.1 (working,, using random aliases)

SELECT toStartOfMonth(toDateTime(`create_time`)) AS `create_time_b16a62`, COUNT(*) AS `count_e2942a` 
FROM `hmr_insights`.`completed_queries` GROUP BY toStartOfMonth(toDateTime(`create_time`)) ORDER BY `count_e2942a` DESC
 LIMIT 10000;

Query generated with v46.1.0 (not working)

SELECT
  dateTrunc('MONTH', toDateTime("create_time")) AS "create_time",
  COUNT(*) AS "count"
FROM "hmr_insights"."completed_queries"
GROUP BY
  dateTrunc('MONTH', toDateTime("create_time"))
ORDER BY
  "count" DESC
LIMIT 10000

[cizara]

Hi, we are experiencing this exact regression on Superset 6.1.0 with ClickHouse.

Setup: Virtual SQL Dataset (SELECT ... FROM ...) AS virtual_table.
Issue: Time Series Line Charts set to Monthly time grain return 0 for calculated metrics (e.g., sum(A) / sum(B)).

Its odd that the exact same setup works fine on Day/Week grains and in Table view

[Abdulrehman-PIAIC80387]

Thanks @manyyy for the fix pointer — traced this back to #38280 (Feb 2026), which removed a _mutate_label override on the ClickHouse spec that used to suffix every column alias with a 6-char hash (create_time → create_time_b16a62). Turns out that suffix was the specific workaround guarding against the alias-vs-subquery-column collision that ClickHouse 25.3+'s aggregate checker now rejects with Code 215.

Sending PR #42793 that restores the override on ClickHouseBaseEngineSpec — byte-for-byte identical to the removed code. Localized one-method change, no signature changes, no cross-engine ripple. Full trace + rejected alternatives in the PR body.

CC @cpassnat @cizara — this should also fix your reproductions since you both hit the same failure mode on virtual/wrapped datasets. Would appreciate testing against your ClickHouse setups once it lands.