Abdulrehman-PIAIC80387 opened a new pull request, #42793:
URL: https://github.com/apache/superset/pull/42793
### SUMMARY
Fixes #40289.
Charts on **virtual (SQL-defined) ClickHouse datasets** with a time-grain
groupby (e.g. monthly) fail with:
```
Code: 215. DB::Exception: Column ... is not under aggregate function and not
in GROUP BY
```
on ClickHouse 25.3+, even though the SELECT and GROUP BY expressions are
lexically identical. **Confirmed regression from 4.1.1** by three independent
reporters (@manyyy, @cpassnat, @cizara). Cross-referencing the same issue
thread.
### CAUSE
Superset generates:
```sql
SELECT toStartOfMonth(t.create_time) AS create_time,
COUNT(*) AS count
FROM (SELECT create_time FROM raw) AS t
GROUP BY toStartOfMonth(t.create_time)
```
When the alias (`create_time`) collides lexically with a column name inside
the subquery, ClickHouse 25.3+'s aggregate checker fails to correlate the outer
alias with the GROUP BY expression and raises Code 215.
The `_mutate_label` override on the ClickHouse spec used to suffix every
alias with a 6-character hash (`create_time_b16a62`) so this collision never
happened. [#38280](https://github.com/apache/superset/pull/38280) removed it on
the theory that `clickhouse-connect>=0.13.0` made it unnecessary — but the
workaround was actually guarding against server-side ClickHouse behavior that
has since tightened.
### FIX
Restore `_mutate_label` on `ClickHouseBaseEngineSpec`. Byte-for-byte
identical to the code removed in #38280:
```python
@staticmethod
def _mutate_label(label: str) -> str:
"""Suffix labels with a short hash of the label. ..."""
return f"{label}_{hash_from_str(label)[:6]}"
```
Localized one-method change:
- No signature changes
- No cross-engine ripple
- No changes to core query generation
- Easily reversible if a future ClickHouse release relaxes the checker
### BEHAVIOR MATRIX
| Scenario | Before | After |
|---|---|---|
| Chart on physical ClickHouse table (any grain) | ✓ works | ✓ works
(aliases now suffixed — same as pre-6.0) |
| Chart on virtual ClickHouse dataset (Day/Week grain) | ✓ works | ✓ works |
| **Chart on virtual ClickHouse dataset (Month grain, CH 25.3+)** | ❌ Code:
215 | ✓ works |
| Non-ClickHouse engines | — | — (unchanged) |
| Exported CSV/XLSX column names | `create_time` | `create_time_b16a62`
(documented in UPDATING.md) |
### TESTING INSTRUCTIONS
**Manual (requires ClickHouse 25.3+):**
1. Create a virtual dataset: `SELECT create_time, value FROM some_table`
2. Chart with time grain = Month, groupby = `create_time`
3. **Before:** `Code: 215` error
4. **After:** chart renders correctly
**Automated:**
```bash
pytest tests/unit_tests/db_engine_specs/test_clickhouse.py -v -k mutate_label
```
Three regression tests:
- `test_clickhouse_mutate_label_suffixes_hash` — pins the exact bytes so an
accidental removal fails CI
- `test_clickhouse_mutate_label_is_deterministic` — locks in the base
contract
- `test_clickhouse_mutate_label_is_unique_across_inputs` — locks in the base
contract
### BREAKING CHANGE NOTE
Exported CSV/XLSX column names for ClickHouse charts will regain the
`_XXXXXX` hash suffix (same as pre-#38280 / pre-6.0 behavior). Documented in
[UPDATING.md](UPDATING.md).
### ADDITIONAL INFORMATION
- [x] Has associated issue: Fixes #40289
- [x] Required feature flags: none
- [x] Changes UI: no
- [x] Includes DB Migration: no
- [x] Includes CLI or Node.js commands: no
- [x] Breaking change: exported column names for ClickHouse regain hash
suffix (documented in UPDATING.md)
--
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]
