Moving to the new analyzer

ClickHouse 24.3 made the new query analyzer (the “Analyzer”, built on a query tree) the default. Per the official analyzer page, the old analysis path is removed in 26.9: enable_analyzer is then an obsolete setting that can no longer be turned off. If you are still running with enable_analyzer = 0, or you have stored queries, views and dashboards nobody has re-run since the upgrade, this is the migration you owe your future self.

Why the new analyzer exists

• One consistent name-resolution model, instead of rules scattered across the old interpreter.
• Better optimisation, especially for subqueries, JOINs and a lot of the newer features (parallel replicas, the newer JOIN algorithms, recent optimisations) which only work with it.
• Much better error messages, which includes rejecting queries that were only “working” by accident.

That last point is the whole migration story: the new analyzer is stricter. Almost every query keeps working untouched; the ones that break were usually wrong in a way the old code tolerated.

Step 1: find out what breaks, before the upgrade forces it

The setting is still available on 26.4, so you can test both ways per query:

SELECT ... SETTINGS enable_analyzer = 0;   -- old behaviour (temporary escape hatch)
SELECT ... SETTINGS enable_analyzer = 1;   -- new behaviour

To do it at scale, replay a representative set of your real queries (from system.query_log, your dashboards and your application code) against a staging copy with the analyzer on, and collect the exceptions.

Step 2: the usual failures

Non-aggregated columns the old analyzer let through

-- the demo table "events": see the end of the article
SELECT event_type, sum(duration_ms)
FROM events
GROUP BY event_type
HAVING country = 'US';

Old analyzer: works, quietly treating country = 'US' as a row filter. New analyzer:

Column 'default.events.country' is not under aggregate function and not in GROUP BY keys. (NOT_AN_AGGREGATE)

The fix is to say what you mean: a row filter belongs in WHERE.

SELECT event_type, sum(duration_ms)
FROM events
WHERE country = 'US'
GROUP BY event_type;

The same applies to selecting a column that is not in GROUP BY:

SELECT number FROM numbers(2) GROUP BY toString(number);   -- NOT_AN_AGGREGATE now

Group by the column, or wrap it in any() / argMax() if you really want an arbitrary value.

Other differences to expect

From the analyzer documentation (check it for your exact version, because compatibility settings come and go and I could not run all of them on 26.4):

• Aliases defined in the SELECT list can no longer be used as JOIN ... USING keys by default; qualify the column or rename it. There is a compatibility setting, analyzer_compatibility_join_using_top_level_identifier.
• CREATE VIEW validates the SELECT at creation time; a view that was invalid but never queried now fails when created (or when the server loads it).
• if() and friends are stricter about argument types being compatible.

For each of these the fix is “write the query you meant”, not a setting.

Step 3: replace neighbor() and runningDifference()

Two functions that depend on block boundaries rather than on a defined ordering are deprecated, because their results can change with the data layout and the number of threads. On 26.4 they refuse to run:

Function neighbor is deprecated since its usage is error-prone (see docs). Please use proper window
function or set `allow_deprecated_error_prone_window_functions` setting to enable it. (DEPRECATED_FUNCTION)

The replacement is a window function with an explicit ORDER BY. That ORDER BY is the point: neighbor has no idea what order “previous” means.

Old	New
neighbor(x, -1)	lagInFrame(x, 1) OVER (ORDER BY ...)
neighbor(x, 1)	leadInFrame(x, 1) OVER (ORDER BY ...)
neighbor(x, -1, d)	lagInFrame(x, 1, d) OVER (ORDER BY ...)
runningDifference(x)	x - lagInFrame(x, 1, x) OVER (ORDER BY ...)

SELECT
    number,
    lagInFrame(number, 1)  OVER () AS previous,
    leadInFrame(number, 1) OVER () AS next
FROM numbers(5);

0  0  1
1  0  2
2  1  3
3  2  4
4  3  0

Missing neighbours return the type’s default value (0 here), exactly as neighbor did; pass a third argument to choose a different default:

SELECT number, lagInFrame(number, 1, 100) OVER (ORDER BY number) AS previous
FROM numbers(3);
-- 0 100 / 1 0 / 2 1

Rebuilding runningDifference (the change from the previous row):

SELECT
    number,
    number * number AS sq,
    sq - lagInFrame(sq, 1, 0) OVER (ORDER BY number
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS delta
FROM numbers(5);

0  0   0
1  1   1
2  4   3
3  9   5
4  16  7

One subtlety: lagInFrame and leadInFrame look inside the window frame, not the whole partition. With the default frame of an ORDER BY window the frame ends at the current row, so leadInFrame sees nothing ahead and returns the default:

SELECT number,
       leadInFrame(number, 1, 100) OVER (ORDER BY number) AS default_frame,
       leadInFrame(number, 1, 100) OVER (ORDER BY number
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS full_frame
FROM numbers(3);
-- 0 100 1 / 1 100 2 / 2 100 100

So for leadInFrame spell the frame out:

leadInFrame(x, 1) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

See Window frames in ClickHouse for the frame rules.

The demo table

The events queries above use this schema:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

Checklist

1. Run your important queries once with enable_analyzer = 1 on a test copy.
2. Fix the failures: filters into WHERE, explicit GROUP BY, qualified join columns.
3. Replace neighbor and runningDifference with window functions that have an explicit ORDER BY.
4. Recreate or at least re-run views, materialized views and dictionaries that embed queries.
5. Only then remove the safety net (enable_analyzer = 0 in profiles and application settings).