dosubot[bot] commented on issue #40289:
URL: https://github.com/apache/superset/issues/40289#issuecomment-4500900601
<!-- Answer -->
@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]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py#L1614-L1653)
where groupby expressions are built, and then applied via
`qry.group_by(*groupby_all_columns.values())`
[[2]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py#L1744-L1745).
**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]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py#L1964-L1967),
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]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/db_engine_specs/clickhouse.py).
2. **PR #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]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/db_engine_specs/clickhouse.py#L410-L419)
and the dependency still reads `clickhouse-connect>=0.5.14, <1.0`
[[6]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/pyproject.toml#L116).
That PR may have helped tangentially but wouldn't fully resolve this subquery
scoping issue.
3. **`time_groupby_inline = True`**
[[7]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/db_engine_specs/clickhouse.py#L56)
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]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py#L846-L858).
- **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.
<!-- Dosu Comment Footer -->
*To reply, just mention
[@dosu](https://go.dosu.dev/dosubot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=reply-with-mention&utm_term=apache%2Fsuperset).*
---
Share context across your team and agents. Try
[Dosu](https://dosu.dev?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=knowledge-infrastructure-tagline&utm_term=apache%2Fsuperset).
[](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=knowledge-infrastructure-feedback&utm_term=apache%2Fsuperset&message_id=a7ce8f07-a82d-411e-bfbe-08c473fa55dc)
[](https://github.dosu.com/apache/superset?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=knowledge-infrastructure-learn-repo&utm_term=apache%2Fsuperset)
[](https://app.dosu.dev/signup?referrer=openSource&source=github-footer&utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=knowledge-infrastructure-add-team&utm_term=apache%2Fsuperset)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
