For example, the following query fails with `There is no supertype for types Array(UInt8), String because some of them are Array and some of them are not`:

```
SELECT toTypeName(if(0, [2, 3, 4], 'String'))
```

### Heterogeneous clusters[​](#heterogeneous-clusters "Direct link to Heterogeneous clusters")

The analyzer significantly changes the communication protocol between servers in the cluster. Thus, it's impossible to run distributed queries on servers with different `enable_analyzer` setting values.

### Mutations are interpreted by previous analyzer[​](#mutations-are-interpreted-by-previous-analyzer "Direct link to Mutations are interpreted by previous analyzer")

Mutations are still using the old analyzer.
This means some new ClickHouse SQL features can't be used in mutations. For example, the `QUALIFY` clause.
The status can be checked [here](https://github.com/ClickHouse/ClickHouse/issues/61563).

### Unsupported features[​](#unsupported-features "Direct link to Unsupported features")

The list of features that the analyzer currently doesn't support is given below:

- Annoy index.
- Hypothesis index. Work in progress [here](https://github.com/ClickHouse/ClickHouse/pull/48381).
- Window view is not supported. There are no plans to support it in the future.

## Cloud Migration[​](#cloud-migration "Direct link to Cloud Migration")

We are enabling the new query analyzer on all instances where it is currently disabled to support new functional and performance optimizations. This change enforces stricter SQL scoping rules, requiring customers to manually update non-compliant queries.

### Migration workflow[​](#migration-workflow "Direct link to Migration workflow")

1. Identify the query by filtering `system.query_log` using the `normalized_query_hash`:

```
SELECT query 
FROM clusterAllReplicas(default, system.query_log)
WHERE normalized_query_hash='{hash}' 
LIMIT 1 
SETTINGS skip_unavailable_shards=1
```

2. Run the query with the analyzer enabled by adding these settings.

```
SETTINGS
    enable_analyzer=1,
    analyzer_compatibility_join_using_top_level_identifier=1
```

3. Refactor and verify the query results to ensure they match the output generated when the analyzer is disabled.

Please refer to the most frequent incompatibilities encountered during internal testing.

### Unknown expression identifier[​](#unknown-expression-identifier "Direct link to Unknown expression identifier")

Error: `Unknown expression identifier ... in scope ... (UNKNOWN_IDENTIFIER)`. Exception code: 47

Cause: Queries that rely on non-standard, permissive legacy behaviors such as referencing calculated aliases in filters, ambiguous subquery projections, or "dynamic" CTE scoping are now correctly identified as invalid and rejected immediately.

Solution: Update your SQL patterns as follows:

- Filter logic: Move logic from WHERE to HAVING if filtering on results, or duplicate the expression in WHERE if filtering on source data.
- Subquery scope: Explicitly select all columns needed by the outer query.
- JOIN keys: Use ON with full expressions instead of USING if the key is an alias.
- In outer queries, refer to the alias of the Subquery/CTE itself, not the tables inside it.

### Non-Aggregated Columns in GROUP BY[​](#non-aggregated-columns-in-group-by "Direct link to Non-Aggregated Columns in GROUP BY")

Error: `Column ... is not under aggregate function and not in GROUP BY keys (NOT_AN_AGGREGATE)`. Exception code: 215

Cause: The old analyzer allowed selecting columns not present in the GROUP BY clause (often picking an arbitrary value). The analyzer adheres to standard SQL: every selected column must be either an aggregate or a grouping key.

Solution: Wrap the column in `any()`, `argMax()`, or add it to the GROUP BY.

```
/* ORIGINAL QUERY */
-- device_id is ambiguous
SELECT user_id, device_id FROM table GROUP BY user_id

/* FIXED QUERY */
SELECT user_id, any(device_id) FROM table GROUP BY user_id
-- OR
SELECT user_id, device_id FROM table GROUP BY user_id, device_id
```

### Duplicate CTE names[​](#duplicate-cte-names "Direct link to Duplicate CTE names")

Error: `CTE with name ... already exists (MULTIPLE_EXPRESSIONS_FOR_ALIAS)`. Exception code: 179

Cause: The old analyzer permitted defining multiple Common Table Expressions (WITH ...) with the same name shadowing the earlier one. The analyzer forbids this ambiguity.

Solution: Rename duplicate CTEs to be unique.

```
/* ORIGINAL QUERY */
WITH 
  data AS (SELECT 1 AS id), 
  data AS (SELECT 2 AS id) -- Redefined
SELECT * FROM data;

/* FIXED QUERY */
WITH 
  raw_data AS (SELECT 1 AS id), 
  processed_data AS (SELECT 2 AS id)
SELECT * FROM processed_data;
```

### Ambiguous column identifiers[​](#ambiguous-column-identifiers "Direct link to Ambiguous column identifiers")

Error: `JOIN [JOIN TYPE] ambiguous identifier ... (AMBIGUOUS_IDENTIFIER)` Exception code: 207

Cause: The query references a column name present in multiple tables within a JOIN without specifying the source table. The old analyzer often guessed the column based on internal logic, the analyzer requires explicit name.

Solution: Fully qualify the column with table\_alias.column\_name.

```
/* ORIGINAL QUERY */
SELECT table1.ID AS ID FROM table1, table2 WHERE ID...

/* FIXED QUERY */
SELECT table1.ID AS ID_RENAMED FROM table1, table2 WHERE ID_RENAMED...
```

### Invalid usage of FINAL[​](#invalid-usage-of-final "Direct link to Invalid usage of FINAL")

Error: `Table expression modifiers FINAL are not supported for subquery...` or `Storage ... doesn't support FINAL` (`UNSUPPORTED_METHOD`). Exception codes: 1, 181

Cause: FINAL is a modifier for table storage (specifically [Shared]ReplacingMergeTree). The analyzer rejects FINAL when applied to:

- Subqueries or derived tables (e.g., FROM (SELECT ...) FINAL).
- Table engines that do not support it (e.g., SharedMergeTree).

Solution: Apply FINAL only to the source table inside the subquery, or remove it if the engine does not support it.

```
/* ORIGINAL QUERY */
SELECT * FROM (SELECT * FROM my_table) AS subquery FINAL ...

/* FIXED QUERY */
SELECT * FROM (SELECT * FROM my_table FINAL) AS subquery ...
```

### `countDistinct()` function case-insensitivity[​](#countdistinct-case-insensitivity "Direct link to countdistinct-case-insensitivity")

Error: `Function with name countdistinct does not exist (UNKNOWN_FUNCTION)`. Exception code: 46

Cause: Function names are case-sensitive or strictly mapped in the analyzer. `countdistinct` (all lowercase) is no longer resolved automatically.

Solution: Use the standard `countDistinct` (camelCase) or the ClickHouse specific uniq.

- [Known incompatibilities](#known-incompatibilities)
  - [Invalid queries are no longer optimized](#invalid-queries-are-no-longer-optimized)- [`CREATE VIEW` with an invalid query](#create-view-with-invalid-query)- [Known incompatibilities of the `JOIN` clause](#known-incompatibilities-of-the-join-clause)- [Projection column names changes](#projection-column-names-changes)- [Incompatible function arguments types](#incompatible-function-arguments-types)- [Heterogeneous clusters](#heterogeneous-clusters)- [Mutations are interpreted by previous analyzer](#mutations-are-interpreted-by-previous-analyzer)- [Unsupported features](#unsupported-features)- [Cloud Migration](#cloud-migration)
    - [Migration workflow](#migration-workflow)- [Unknown expression identifier](#unknown-expression-identifier)- [Non-Aggregated Columns in GROUP BY](#non-aggregated-columns-in-group-by)- [Duplicate CTE names](#duplicate-cte-names)- [Ambiguous column identifiers](#ambiguous-column-identifiers)- [Invalid usage of FINAL](#invalid-usage-of-final)- [`countDistinct()` function case-insensitivity](#countdistinct-case-insensitivity)

Was this page helpful?