Columns get mutator suffix to avoid field collisions, but are not added in GROUP BY

[LudvigJuelsson]

Hello all!
I am planning to change my clickhouse database connector, from clickhouse-sqlalchemy -> clickhouse-connect. When updating however, many of my chart are breaking due to code 215 (see point 1. Error). After doing some deep diving, I figured out that it had to do with the _mutate_label function that adds a suffix to avoid field name collisions, and there has been some discussions regarding this before (see point 4. Previous related raised issues and discussions).
By removing this functionality, I got all of my charts back to a working state, and no mutations are added.

I am a bit confused on why the mutations are still used, when removing the alias mutation suffix makes my charts work as intended.
Is there any reason for the label mutation still being used?
Or is there any special cases I am missing where it is still needed?

1. Error:

Error: HTTPDriver for https://<host>:<port> received ClickHouse error code 215
 Code: 215. DB::Exception: Column `count` is not under aggregate function and not in GROUP BY. 
Have columns: ['count()','timestamp']: While processing count DESC.
 (NOT_AN_AGGREGATE) (version 24.10.2.80 (official build))

2. Rendered SQL:

SELECT `timestamp` AS `timestamp_d7e6d5`, COUNT(*) AS `count_e2942a`
FROM `database_1`.`column_1`
WHERE `timestamp` >= toDateTime('2024-12-14 00:00:00')
AND `timestamp` < toDateTime('2025-01-14 00:00:00')
GROUP BY `timestamp`
ORDER BY `count` DESC
LIMIT 10000;

The timestamp works fine even with the alias mutation, due to timestamp_d7e6d5 being the same as the timestamp column. But, the count_e2942a alias mutation is expected to come from the a 'count' column, not the rendered 'COUNT(*) which is the used metric.

3. How I solved it:
I solved it by removing the mutation in the function _mutate_label:

superset/superset/db_engine_specs/clickhouse.py

Lines 395 to 403 in 2935557

	def _mutate_label(label: str) -> str:
	"""
	Suffix with the first six characters from the md5 of the label to avoid
	collisions with original column names
	:param label: Expected expression label
	:return: Conditionally mutated label
	"""
	return f"{label}_{md5_sha_from_str(label)[:6]}"

I did this by running a sed command in my Dockerfile, like such:

RUN sed -i -e 's/f"{label}_{md5_sha_from_str(label)\[:6]}"/label/g' /app/superset/db_engine_specs/clickhouse.py

This sed command simply removes the added mutation suffix and return the original field name, i.e., 'label'

4. Previous related raised issues and discussions:
#25150
#25073
These issues seemed to have solved/have a work around for some of the issues regarding alias mutations.

5. Relevant versions and Information:
Superset version==4.0.2
clickhouse-connect==0.8.13
clickhouse-sqlalchemy==0.2.7

[dosubot[bot]]

Hey there, @LudvigJuelsson! 👋 I'm here to help you with bug fixes, questions, and becoming a contributor.

The _mutate_label function in the clickhouse.py file is designed to append a suffix to labels to avoid field name collisions, which can be particularly useful in complex queries or when multiple fields might have the same name ^[1]. However, as you've experienced, this can lead to issues like the error code 215, where a column is not under an aggregate function or in a GROUP BY clause.

Removing the mutation suffix resolved your issue because it allowed the SQL query to correctly reference the intended columns without the added suffixes, which were causing mismatches in the GROUP BY clause. The necessity of label mutations generally depends on the specific use case and the potential for naming conflicts in your queries. In your scenario, it seems that the mutations were not necessary, and removing them allowed your charts to function correctly.

The use of label mutations might still be relevant in cases where there is a risk of name collisions, especially in more complex queries or when using different aggregations. It's important to weigh the benefits of avoiding potential collisions against the issues that mutations might introduce, as seen in your case. The discussions and issues you've referenced indicate that this is an ongoing topic within the community, with various solutions and workarounds being explored ^[1].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}