manyyy opened a new issue, #40289:
URL: https://github.com/apache/superset/issues/40289
### 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:
```sql
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:
```sql
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
- [x] 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.
--
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]
