Why the mandatory analyzer in ClickHouse 26.9 needs a planned test cycle
If your team runs self-managed ClickHouse and must move to 26.9, the old query analysis is no longer an option, so the new analyzer becomes the only path your queries take. The main risk is that query results or behavior can shift in HAVING, JOIN and alias handling, so the upgrade needs a test cycle that finishes before any production replica changes version. Treat this as a planned change to query behavior, not a routine binary swap.
Start by fixing the version targets. The ClickHouse security policy lists 26.9, 26.8, 26.7 and 26.3 as the versions that receive security updates, and every other version is unsupported. Stable releases ship roughly monthly, with the three latest stable releases receiving diagnostics and bug fix backports. LTS releases ship twice a year and are supported for a year after release. Choose your target line deliberately, because an LTS team and a stable-track team will face different test windows and different support horizons.
Whichever line you choose, read the changelog for every release between your current version and the target before scheduling the upgrade. The ClickHouse production guidance warns that minor backward-incompatible changes can occur, and query analysis changes are exactly the kind of change that does not show up as a failed startup. Confirm with your own query owners which dashboards, scheduled jobs and application queries use HAVING, JOIN or column aliases, since those are the places where a silent difference is most likely to reach users.
What changes in query analysis and what each change affects
This table lists what changes when ClickHouse 26.9 removes the old query analysis path, what each change can affect in existing SQL, and the action to take before upgrading. Read each row as a test area for your query inventory. The exact list of affected queries must be confirmed against the 26.9 changelog and the analyzer documentation for your target build.
| Change | Impact | Action needed |
|---|---|---|
| Old query analysis path removed | From 26.9, every query is analyzed by the new analyzer, so queries that relied on the old behavior may return different results or fail | Run representative queries on 26.x builds before 26.9 and log every difference in results or errors |
| HAVING with non-aggregate conditions | The analyzer no longer applies the old HAVING-to-WHERE rewrite, so filters may return different rows | Rewrite affected HAVING predicates explicitly. On 26.7 or later, you can set analyzer_compatibility_allow_non_aggregate_in_having as a temporary aid |
| JOIN queries | Some join forms have known incompatibilities, and the exact list depends on the target build | Inventory JOIN queries in application code and dashboards, then compare their results on copies of your data |
| Column aliases | Alias resolution changes can alter which expression a name refers to, especially when an alias is reused | Find aliases reused across SELECT, WHERE and HAVING, and rename ambiguous ones before the upgrade |
| Stored views and materialized views | Stored definitions are analyzed under the new path and may fail or change behavior after the switch | Recreate or validate stored definitions on a test server running the target build |
| SQL generated by BI tools and ORMs | Generated queries often use the patterns above, and changed results can go unnoticed | Ask BI and ORM owners whether generated SQL was tested on 26.x, and include it in your query inventory |
| Silent result changes | Some queries still run but return different rows or types, which error monitoring will not catch | Compare row counts and checksums of key queries between the current and target builds |
| Build-specific affected list | The list of affected queries can change with the target build | Confirm the list against the 26.9 changelog and analyzer documentation before sign-off |
How the analyzer changes HAVING, JOIN and alias resolution
Query results can differ after the analyzer becomes mandatory because the old query analysis and the new analyzer do not resolve every query the same way. The old analysis rewrote some HAVING clauses into WHERE filters, which could change what was filtered before aggregation. It also resolved aliases in its own order. The analyzer applies a single set of rules, so a query that returned correct results for years can return different rows, fail to resolve, or change column meaning without any error. The risk is silent drift, not just failures, which is why result comparison matters more than a clean run.
Three categories of queries deserve the first review. The first is HAVING clauses that reference non-aggregate expressions, which the old rewrite sometimes accepted. The second is multi-table JOIN queries that use table aliases or qualify columns in ways the new resolution may read differently. The third is queries that reuse an alias name already used for a column, a table or another expression, because the analyzer may bind the name to a different target. This page does not give counts of affected queries, so the reader should confirm each category against the analyzer documentation and the release notes for the target version rather than assume a fixed list.
Treat each category as a test target. Collect representative queries from dashboards, scheduled jobs and application code, including those with HAVING, multi-table joins and repeated aliases, then run them on a ClickHouse 26.x pre-production cluster with production-like data and compare row counts and values with the current output. For HAVING cases, the setting analyzer_compatibility_allow_non_aggregate_in_having is a migration aid that replicates the old rewrite, so it can show whether a difference comes from that behavior. Confirm the setting's exact scope and removal plans in the analyzer documentation before relying on it.
What to prepare before testing on 26.x
Four inputs decide whether the analyzer change is a short test or a production incident. Gather them before anyone edits a query, because each one answers a different question: which SQL runs, where it runs, what you can roll back to, and which consumers depend on it.
Inventory queries from logs and system tables
Pull the distinct SQL text that has actually run, from query logs and system tables, over a window long enough to include month-end jobs and rare reports. Group statements by normalized form and record how often each one runs and which user issues it. Flag every query that uses HAVING, JOIN, or column aliases defined in the SELECT list, since these are the patterns most likely to change behavior under the new analyzer.
Build a pre-production cluster
Stand up a non-production cluster on the target 26.x line with a copy of representative data, including the largest partitions and the table shapes you run in production. Replaying the inventory against it shows which results differ and which queries fail outright. Ask the team to confirm that the test cluster uses the same server settings, users and profiles as production, since defaults can mask differences.
Take and test-restore a backup
Before any version change, take a full backup and restore it to a spare cluster, then run the inventory queries against the restored data. A backup that has never been restored may not restore when you need it. Record the restore duration and any errors, because that record becomes the baseline for a rollback decision later.
List embedded SQL consumers
List every application, dashboard, alerting rule and scheduled job that sends SQL to ClickHouse, including BI tools, ETL scripts and saved queries in notebooks. For each one, record the owner, the query source, and whether it can be changed without a release. Identify consumers that build SQL dynamically, because their statements may not appear in the query log until they run.
Confirm the change window and owners
Name one owner for each consumer on the list and agree who signs off when a result differs between the old and new analysis. Confirm the support window of the target version against the security policy before choosing a line to test on. Decide in advance which differences block the change and which need a documented fix.
How to test queries on 26.x before the 26.9 cutover
Replaying production traffic is the closest test you can run without touching live users. Capture the queries your applications, dashboards and scheduled jobs send over a representative period, then run them against a pre-production cluster that runs the target 26.x line. The ClickHouse production FAQ recommends a pre-production environment with representative data and queries, so the replay is only as useful as the data underneath it. Plan the replay so that failures are attributed to specific queries, not just counted.
- Capture from query logs: Collect statements from the query log on your current cluster, including the user, client and normalized query text, so you can group failures by application.
- Preserve parameters and settings: Keep per-query settings, profiles and default database context, because an alias or HAVING clause can behave differently under a different default.
- Keep the data representative: Restore a recent backup or sample of the production partitions the queries touch, rather than an empty schema with a few rows.
- Replay on the target version only after a baseline: Run the same set on the current production version first, so that any error on 26.x can be compared with a known good result.
- Record errors by query fingerprint: Group failing statements so that a single rewritten dashboard query does not hide a wider pattern.
Confirm with your own team which query sources are excluded from capture, such as ad hoc analyst sessions, and test those separately.
A query that runs without an error can still return different results. Comparison testing runs the same statement under the current analyzer behavior and the target behavior, then checks row counts, column names and values. This matters most for HAVING clauses that reference non-aggregated columns, JOIN conditions that depend on alias resolution, and queries that reuse an alias name for a different expression. Store the outputs in a form you can diff, and decide in advance what counts as a match for floating-point and ordering-sensitive results.
- Compare row counts first: Counts are cheap to check and catch most filtering changes, such as a predicate that moved or stopped applying.
- Diff column names and types: Alias resolution changes can rename output columns, which breaks downstream parsers and BI tools even when values match.
- Use deterministic ordering: Add explicit ORDER BY clauses in the test copy so that differences reflect semantics, not result order.
- Hash result sets for large outputs: For wide results, compare aggregate checksums per query rather than storing full output.
- Review every mismatch manually: Some differences are intended fixes, so each one needs a recorded decision, not an automatic pass or fail.
Confirm with the ClickHouse analyzer documentation which constructs changed in your target version, and check the release changelog for the exact list before you define expected differences.
The analyzer_compatibility_allow_non_aggregate_in_having setting is a migration aid, available since ClickHouse 26.7. It makes the analyzer replicate the old HAVING-to-WHERE rewrite, so you can separate queries that depend on that behavior from queries that fail for other reasons. Use it as a diagnostic tool: run the failing queries with and without the setting, and treat any query that only passes with it as a rewrite you must fix in application code. The setting is a bridge, not a permanent design, and the goal is to remove the dependency.
- Set it per session first: Apply the setting in a test session or profile so that the change stays scoped to the replay, not the whole cluster.
- Classify each query: Mark queries as passing unchanged, passing only with the setting, or failing both ways. The middle group is your rewrite list.
- Rewrite dependent HAVING clauses: Move filters that do not depend on aggregates into WHERE, and make grouping explicit so the intent is visible to the next reader.
- Do not ship the setting by default: Decide when it is removed, and record that date with the application owners, so the workaround does not become permanent.
- Retest after each rewrite: Run the replay again so that fixes do not introduce new differences.
Confirm with your application owners which dependent queries are generated by tools or BI layers, since those often need a change in the tool configuration rather than in SQL.
Sequence for moving to 26.9 without surprises in production
This sequence moves a ClickHouse estate onto 26.9 by surfacing analyzer-dependent queries before any production node changes, then leaves a tested restore point and a defined rollback boundary in place. Each step closes with a check you can record.
Confirm the supported target version
Platform or database owners check the ClickHouse security policy and the GitHub releases page to confirm which 26.x line you will target and whether it is current and supported. Record the exact build you intend to run, along with the changelog entries for 26.7 through 26.9 that touch the analyzer, HAVING, JOIN and aliases. Done when the target build and its changelog review are written into the change record and approved by the owner of the production cluster.
Inventory SQL across applications and jobs
Data platform engineers collect every query source: dashboards, scheduled jobs, ingestion scripts, saved views, materialized view definitions and application code that reads ClickHouse. Query logs in system tables help identify what actually runs, but confirm coverage with the teams that own each consumer, since some queries run only at month end. Done when you hold a list of query sources, owners and the SQL text, with any query using HAVING, JOIN or column aliases flagged for review.
Build a representative test cluster
Platform engineers stand up a pre-production cluster on the target version, using the same table definitions and a data sample that reflects production volume, skew and time ranges. The ClickHouse production guidance calls for a pre-production environment with representative data and queries, so confirm the sample with the data owners before you rely on it. Done when schema parity with production is verified and the test cluster accepts the full inventory of queries without infrastructure errors.
Replay queries and diff the results
Engineers run the inventoried queries on the old production version and the 26.9 test cluster, then compare row counts, values, column names and error codes. Differences in HAVING filters, join results and alias resolution are the most likely findings, so review them with the query owners rather than accepting the new output by default. Done when every query either matches, has an agreed explanation, or is logged as a defect with an owner.
Fix or pin affected queries
Query owners rewrite the queries that changed behavior so they return the intended result on the analyzer. Where a rewrite cannot ship in time, the setting analyzer_compatibility_allow_non_aggregate_in_having is available from 26.7 as a migration aid for HAVING behavior, and it should be treated as a temporary bridge with a removal plan. Done when the replay passes with the revised SQL or the documented setting, and each pinned query has a named owner and a review date.
Take a backup and test the restore
The platform team takes a full backup of the affected tables with BACKUP, stores it outside the cluster, and restores it to a spare environment. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, and replication does not protect against human error. Done when the restored data matches row counts and checksums from the source and the restore procedure is written down.
Upgrade one replica, then the rest
Operators upgrade a single replica in production, then the remaining replicas and shards, following the order in the ClickHouse replication guide so that each shard keeps a healthy copy available. Confirm that Keeper has quorum before and after each restart, and that a recovered replica resyncs missing data from a healthy peer. Done when every replica reports the target version and replication queues drain without persistent errors.
Run post-upgrade checks and set the rollback boundary
After the rollout, teams rerun the replay suite against production, check dashboards and scheduled jobs, and watch error rates for analyzer-related failures. Record the point after which a downgrade is no longer practical, which usually follows the first use of new features or new data written in a format the old version cannot read, and document the backup and procedure that cover that window. Done when the checks pass, the rollback boundary is signed off by the cluster owner, and the restore procedure remains tested.
What buyers ask before upgrading ClickHouse for the analyzer change
How much effort does the analyzer migration take?
The effort depends on how many queries your organization runs, how many applications and dashboards depend on them, and how much data each query scans. Start by counting the distinct query shapes in your query logs, since many dashboards repeat the same few patterns. Ask any partner to size the work from that inventory rather than from a headcount guess. Also confirm whether your schemas, materialized views and scheduled jobs use HAVING, JOIN or alias patterns that changed, because those need review even when the application code is unchanged.
What drives the cost of the analyzer work up or down?
Cost follows the inventory of affected queries, the number of teams that own them, and how much representative data your test environment can hold. Shared analytics platforms with many owners take longer because every owner must confirm results. Clean, documented query sources reduce review time. Ask for a quote that separates the query inventory, the test cluster setup, the query rewrites, the owner sign-off and the final regression run, and that states which items are one-time and which recur with each new release.
How long does testing on ClickHouse 26.x take?
No duration has been established for your environment, so confirm it against your own query inventory and data volume. Testing typically has three phases: replaying captured queries against a 26.x test cluster loaded with representative data, reviewing any result or error differences with each query owner, and rerunning the corrected set after fixes. Ask your team or a partner to state the expected duration for each phase and the criteria that end testing. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, so plan that environment before the schedule.
Can our own team do the analyzer migration internally?
Often yes, if your team already runs ClickHouse, can read query logs and has access to the owners of each dashboard and application. The work is less about ClickHouse administration and more about tracing which queries are affected and confirming that results are still correct for the business. Teams without a test cluster, without ClickHouse query experience or with many unowned queries usually benefit from outside help for the inventory and testing. Compare your internal capacity against the query inventory and the owner time it needs before deciding.
What does the migration need from our team?
The work needs four things from your side: query logs covering a representative period, a test cluster on 26.x with a copy or sample of production-like data, a named owner for each dashboard, report and application that runs queries, and a sign-off process for changed results. Confirm that query logs are retained long enough to cover month-end and other periodic jobs. Ask whether your retention policy allows copying the data you need to the test cluster, since that can raise access and privacy questions.
Does the compatibility setting remove the migration work?
No. The analyzer_compatibility_allow_non_aggregate_in_having setting, available since ClickHouse 26.7, makes the analyzer keep the old HAVING-to-WHERE rewrite as a migration aid. It can keep some queries running while you fix them, but it does not confirm that every query returns the results your business expects, and it does not cover every incompatibility. Treat it as a bridge that lets you stage the work, and test each query on the new analyzer behavior before you rely on it. Check the ClickHouse analyzer documentation for the current list of affected patterns.
When should we remove the analyzer compatibility setting?
Remove the setting only after every affected query has been tested without it and its owner has signed off on the results. Keeping it indefinitely hides differences that will matter when the setting is no longer available. Record which queries depended on it, track them in your change process, and schedule the removal as a separate change with its own test run and rollback point. Confirm the exact removal timing for your version and your ClickHouse release plan with the ClickHouse documentation and your own upgrade calendar.
What drives the effort of the analyzer migration
The effort in this migration sits in five cost components, and each has a different owner. Query discovery comes first: someone must collect the SQL that actually runs, from application repositories, scheduled jobs, dashboards and ad hoc analyst sessions, because a query that is never found is never tested. The platform or data engineering team usually pays for this, using query logs and the system tables on the current cluster. The test environment comes next, and its cost depends on how closely it mirrors production data volumes, settings profiles and user permissions. Result comparison is where analyzer differences surface, since a query can return different rows or fail outright on 26.x. Expect the comparison harness to be built once and reused, not rebuilt for each query.
The fixes themselves fall to the teams that own each artifact. Application developers change queries and ORM-generated SQL, analytics engineers change dashboard definitions and saved queries, and data owners must sign off that a changed result still means the same thing for the business. That last step is the one most often underestimated, because a query that now runs cleanly can still return a different number. Ask any partner quoting this work to show how they will separate one-time testing from recurring work: the discovery, harness build, and first full pass are one-time costs, while reviewing new or changed queries after each release line is recurring. The recurring share depends on how often teams add SQL and whether a review gate exists in their change process. Confirm with your own engineers how many query sources exist before accepting any estimate.
How DeployOpen works on this
DeployOpen treats the mandatory analyzer in ClickHouse 26.9 as a query compatibility project before it is an upgrade project. We inventory the queries your applications and dashboards run, test them on a ClickHouse 26.x environment with representative data, and hand over the fixes and evidence your team needs to move forward.
How we scope
- Inventory of query sources, including applications, dashboards, scheduled jobs and ad hoc analyst SQL
- Written scope naming the ClickHouse versions, environments and query sets covered by the engagement
- Agreed list of known analyzer incompatibilities to check, such as HAVING, JOIN and alias behavior
- Confirmation of which migration aids and settings you are allowed to use, and for how long
How we build
- Replay of representative queries on a pre-production cluster running a 26.x release with comparable data
- Side-by-side comparison of results and error output between the current and analyzer-based behavior
- Rewrites of affected queries, with each change reviewed and linked to the test case that found it
- Use of analyzer_compatibility_allow_non_aggregate_in_having only where a documented rewrite is not yet practical
How we hand over
- Compatibility report listing each failing query, its cause, the fix applied and the test status
- Updated query repository or code changes delivered to your version control system
- Runbook for repeating the query replay before each upgrade within your own team
- Documented open items and the owner your team should confirm for each one
How we support
- Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts
- Ongoing support, upgrades and continuous testing are available as a retainer
- Support can cover re-running the query replay when new ClickHouse stable releases are evaluated
- Quotes follow from the written scope, so ask for the query set size and environments to be listed
