> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Page detailing the ClickHouse query analyzer

# Analyzer

In ClickHouse version `24.3`, the analyzer was enabled by default.
You can read more details about how it works [here](/guides/clickhouse/performance-and-monitoring/understanding-query-execution-with-the-analyzer#analyzer).

Since version `26.9` the analyzer is mandatory: the `enable_analyzer` setting is obsolete, an attempt to set it to `0` is rejected, and the query analysis that ClickHouse used before `24.3` is no longer supported. The incompatibilities listed below describe how that older analysis differed, so that a query written for it can be updated; to observe its behaviour, run the query against a ClickHouse version older than `26.9`.

<h2 id="known-incompatibilities">
  Known incompatibilities
</h2>

Despite fixing a large number of bugs and introducing new optimizations, it also introduces some breaking changes in ClickHouse behaviour. Please read the following changes to determine how to rewrite your queries for the analyzer.

<h3 id="invalid-queries-are-no-longer-optimized">
  Invalid queries are no longer optimized
</h3>

The previous query planning infrastructure applied AST-level optimizations before the query validation step.
Optimizations could rewrite the initial query to be valid and executable.

In the analyzer, query validation takes place before the optimization step.
This means that invalid queries which were previously possible to execute, are now unsupported.
In such cases, the query must be fixed manually.

<h4 id="example-1">
  Example 1
</h4>

The following query uses column `number` in the projection list when only `toString(number)` is available after the aggregation.
In the old analyzer, `GROUP BY toString(number)` was optimized into `GROUP BY number,` making the query valid.

```sql theme={null}
SELECT number
FROM numbers(1)
GROUP BY toString(number)
```

<h4 id="example-2">
  Example 2
</h4>

The same problem occurs in this query. Column `number` is used after aggregation with another key.
The previous query analyzer fixed this query by moving the `number > 5` filter from the `HAVING` clause to the `WHERE` clause.

```sql theme={null}
SELECT
    number % 2 AS n,
    sum(number)
FROM numbers(10)
GROUP BY n
HAVING number > 5
```

To fix the query, you should move all conditions that apply to non-aggregated columns to the `WHERE` section to conform to standard SQL syntax:

```sql theme={null}
SELECT
    number % 2 AS n,
    sum(number)
FROM numbers(10)
WHERE number > 5
GROUP BY n
```

As a migration aid, the analyzer can replicate the old `HAVING`-to-`WHERE` rewrite for non-aggregate AND-conjuncts. Enable `analyzer_compatibility_allow_non_aggregate_in_having = 1` to opt into this behavior. The setting is available since ClickHouse `26.7`. The setting is ignored for `WITH CUBE`, `WITH ROLLUP`, `WITH TOTALS`, and `GROUPING SETS`. Conjuncts containing aggregate, `grouping`, or non-deterministic functions stay in `HAVING`; if any conjunct contains a window function or a stateful function (for example `rowNumberInBlock`), the rewrite is disabled for the whole `HAVING`, matching the legacy behavior.

<h3 id="create-view-with-invalid-query">
  `CREATE VIEW` with an invalid query
</h3>

The analyzer always performs type-checking.
Previously, it was possible to create a `VIEW` with an invalid `SELECT` query.
It would then fail during the first `SELECT` or `INSERT` (in the case of `MATERIALIZED VIEW`).

It is no longer possible to create a `VIEW` in this way.

<h4 id="example-view">
  Example
</h4>

```sql theme={null}
CREATE TABLE source (data String)
ENGINE=MergeTree
ORDER BY tuple();

CREATE VIEW some_view
AS SELECT JSONExtract(data, 'test', 'DateTime64(3)')
FROM source;
```

<h3 id="known-incompatibilities-of-the-join-clause">
  Known incompatibilities of the `JOIN` clause
</h3>

<h4 id="join-using-column-from-projection">
  `JOIN` using a column from a projection
</h4>

An alias from the `SELECT` list can not be used as a `JOIN USING` key by default.

A new setting, `analyzer_compatibility_join_using_top_level_identifier`, when enabled, alters the behavior of `JOIN USING` to prefer resolving identifiers based on expressions from the projection list of the `SELECT` query, rather than using the columns from the left table directly.

For example:

```sql theme={null}
SELECT a + 1 AS b, t2.s
FROM VALUES('a UInt64, b UInt64', (1, 1)) AS t1
JOIN VALUES('b UInt64, s String', (1, 'one'), (2, 'two')) t2
USING (b);
```

With `analyzer_compatibility_join_using_top_level_identifier` set to `true`, the join condition is interpreted as `t1.a + 1 = t2.b`, matching the behavior of the earlier versions.
The result will be `2, 'two'`.
When the setting is `false`, the join condition defaults to `t1.b = t2.b`, and the query will return `2, 'one'`.
If `b` is not present in `t1`, the query will fail with an error.

<h4 id="changes-in-behavior-with-join-using-and-aliasmaterialized-columns">
  Changes in behavior with `JOIN USING` and `ALIAS`/`MATERIALIZED` columns
</h4>

In the analyzer, using `*` in a `JOIN USING` query that involves `ALIAS` or `MATERIALIZED` columns will include those columns in the result-set by default.

For example:

```sql theme={null}
CREATE TABLE t1 (id UInt64, payload ALIAS sipHash64(id)) ENGINE = MergeTree ORDER BY id;
INSERT INTO t1 VALUES (1), (2);

CREATE TABLE t2 (id UInt64, payload ALIAS sipHash64(id)) ENGINE = MergeTree ORDER BY id;
INSERT INTO t2 VALUES (2), (3);

SELECT * FROM t1
FULL JOIN t2 USING (payload);
```

In the analyzer, the result of this query will include the `payload` column along with `id` from both tables.
In contrast, the previous analyzer would only include these `ALIAS` columns if specific settings (`asterisk_include_alias_columns` or `asterisk_include_materialized_columns`) were enabled,
and the columns might appear in a different order.

To ensure consistent and expected results, especially when migrating old queries to the analyzer, it is advisable to specify columns explicitly in the `SELECT` clause rather than using `*`.

<h4 id="handling-of-type-modifiers-for-columns-in-using-clause">
  Handling of type modifiers for columns in the `USING` clause
</h4>

In the analyzer, the rules for determining the common supertype for columns specified in the `USING` clause have been standardized to produce more predictable outcomes,
especially when dealing with type modifiers like `LowCardinality` and `Nullable`.

* `LowCardinality(T)` and `T`: When a column of type `LowCardinality(T)` is joined with a column of type `T`, the resulting common supertype will be `T`, effectively discarding the `LowCardinality` modifier.
* `Nullable(T)` and `T`: When a column of type `Nullable(T)` is joined with a column of type `T`, the resulting common supertype will be `Nullable(T)`, ensuring that the nullable property is preserved.

For example:

```sql theme={null}
SELECT id, toTypeName(id)
FROM VALUES('id LowCardinality(String)', ('a')) AS t1
FULL OUTER JOIN VALUES('id String', ('b')) AS t2
USING (id);
```

In this query, the common supertype for `id` is determined as `String`, discarding the `LowCardinality` modifier from `t1`.

<h3 id="projection-column-names-changes">
  Projection column names changes
</h3>

During projection names computation, aliases are not substituted.

```sql theme={null}
SELECT
    1 + 1 AS x,
    x + 1
FORMAT PrettyCompact
```

Before `24.3` the second column was named after the substituted alias:

```text theme={null}
   ┌─x─┬─plus(plus(1, 1), 1)─┐
1. │ 2 │                   3 │
   └───┴─────────────────────┘
```

The analyzer keeps the alias in the name:

```text theme={null}
   ┌─x─┬─plus(x, 1)─┐
1. │ 2 │          3 │
   └───┴────────────┘
```

<h3 id="incompatible-function-arguments-types">
  Incompatible function arguments types
</h3>

In the analyzer, type inference happens during initial query analysis.
This change means that type checks are done before short-circuit evaluation; thus, the `if` function arguments must always have a common supertype.

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`:

```sql theme={null}
SELECT toTypeName(if(0, [2, 3, 4], 'String'))
```

<h3 id="heterogeneous-clusters">
  Heterogeneous clusters
</h3>

The analyzer significantly changes the communication protocol between servers in the cluster. Thus, it's impossible to run distributed queries on servers that do not agree on whether the analyzer is used - which, for a cluster of servers older than `26.9`, means servers with different `enable_analyzer` setting values.

A server of version `26.10` or newer has no other query analysis left, so it ignores the value an older initiator sends and analyzes the query with the analyzer regardless. The two analyses do not name the result columns the same way, and an initiator matches the block a shard returns by column name, so such a query can fail on the initiator with `NOT_FOUND_COLUMN_IN_BLOCK` - for example when it selects a function written in non-canonical case (`hostname()`), which the analyzer resolves to its canonical name (`hostName()`). A cluster still running with the older query analysis must therefore set `enable_analyzer = 1` on every server before any of them is upgraded to `26.10`.

<h3 id="unsupported-features">
  Unsupported features
</h3>

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).

<h2 id="cloud-migration">
  Cloud Migration
</h2>

We are enabling the 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.

<h3 id="migration-workflow">
  Migration workflow
</h3>

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

```sql theme={null}
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, adding the compatibility setting that restores the identifier resolution of the older analysis where the query relies on it.

```sql theme={null}
SETTINGS
    analyzer_compatibility_join_using_top_level_identifier=1
```

3. Refactor and verify the query results to ensure they match the output the query produced before the migration.

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

<h3 id="unknown-expression-identifier">
  Unknown expression identifier
</h3>

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.

<h3 id="non-aggregated-columns-in-group-by">
  Non-Aggregated Columns in GROUP BY
</h3>

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.

```sql theme={null}
/* 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
```

<h3 id="non-aggregated-columns-in-having">
  Non-Aggregated Columns in HAVING
</h3>

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

Cause: The old analyzer silently moved non-aggregate AND-conjuncts of `HAVING` to `WHERE`, treating them as pre-aggregation filters. The analyzer adheres to standard SQL: `HAVING` may only reference aggregation keys and aggregate functions.

Solution: Move the predicate from `HAVING` to `WHERE` manually, or enable `analyzer_compatibility_allow_non_aggregate_in_having = 1` (available since ClickHouse `26.7`) to restore the legacy rewrite as a migration aid. The compatibility setting is ignored for `WITH CUBE`, `WITH ROLLUP`, `WITH TOTALS`, and `GROUPING SETS`. Conjuncts containing aggregate, `grouping`, or non-deterministic functions stay in `HAVING`; if any conjunct contains a window function or a stateful function (for example `rowNumberInBlock`), the rewrite is disabled for the whole `HAVING`, matching the legacy behavior.

```sql theme={null}
/* ORIGINAL QUERY */
SELECT category, sum(value) FROM t GROUP BY category HAVING service = 'svc1';

/* FIXED QUERY */
SELECT category, sum(value) FROM t WHERE service = 'svc1' GROUP BY category;
```

<h3 id="duplicate-cte-names">
  Duplicate CTE names
</h3>

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, a later definition shadowing the earlier one. The analyzer rejects this ambiguity by default.

Solution: Rename duplicate CTEs to be unique. As a migration aid, enable `analyzer_compatibility_allow_cte_redefinition = 1` (available since ClickHouse `26.10`) to restore the legacy behavior: a reference binds to the latest definition of the name that is not being resolved at that moment, so a redefinition can read the previous definition and the query body reads the last one.

Limitations: a CTE declared as `MATERIALIZED` and a CTE in a `WITH RECURSIVE` clause cannot be redefined even with the setting enabled. One shape differs from the old analyzer: a CTE declared between two definitions of a name also binds to the last definition, where the old analyzer bound it to the definition visible at its declaration point.

```sql theme={null}
/* ORIGINAL QUERY */
WITH
  data AS (SELECT 1 AS id),
  data AS (SELECT id + 1 AS id FROM data) -- Redefined, reads the previous definition
SELECT * FROM data;

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

/* LEGACY BEHAVIOR AS A MIGRATION AID */
WITH
  data AS (SELECT 1 AS id),
  data AS (SELECT id + 1 AS id FROM data)
SELECT * FROM data
SETTINGS analyzer_compatibility_allow_cte_redefinition = 1;
```

<h3 id="ambiguous-column-identifiers">
  Ambiguous column identifiers
</h3>

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.

```sql theme={null}
/* 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...
```

<h3 id="invalid-usage-of-final">
  Invalid usage of FINAL
</h3>

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.

```sql theme={null}
/* ORIGINAL QUERY */
SELECT * FROM (SELECT * FROM my_table) AS subquery FINAL ...

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

<h3 id="countdistinct-case-insensitivity">
  `countDistinct()` function case-insensitivity
</h3>

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.