ClickHouse 26.10: function composition, joins on any condition and a long upgrade checklist

Why it matters: 26.10 is a release to read before you install it. The changelog lists 37 backward-incompatible changes, and several of them stop a server from starting: a seccomp system-call filter is now on in the shipped configuration, disk definitions are validated, and tables or databases that older versions accepted are rejected at load time. The query side gets real additions too. Function composition with placeholders, a slice of Trino syntax, joins on any ON condition, LATERAL JOIN, and a GROUP BY ... LIMIT that skips granules through the primary key.

Sources: the official changelog, 26.10 section (released 2026-10-22; the build tested here, v26.10.2.103-stable, was published on 2026-10-24). There is no release blog post for 26.10 at the time of writing (clickhouse.com/blog/clickhouse-release-26-10 returns 404), only the release webinar recording, so this post quotes no feature counts. PR numbers link to ClickHouse/ClickHouse; the key PRs were checked against their descriptions. Snippets marked Run on 26.10.2 were executed with clickhouse local from the official 26.10.2.103 build and show its real output; items marked from the changelog were not run here.

Features worth trying

Function composition and placeholders

f | g composes functions, and _, _1, _2 are argument placeholders (#116029). It is resolved during analysis into an ordinary lambda, so it costs nothing at run time, and it makes higher-order array calls readable:

-- Run on 26.10.2
SELECT arrayMap(plus(_, 1) | multiply(_, 2), [1, 2, 3]) AS composed,   -- [4,6,8]
       arrayMap(_1 * 2 + 1, [1, 2, 3])                  AS placeholder; -- [3,5,7]

SELECT arrayFilter(lower | startsWith(_, 'err'),
                   ['ERROR: disk', 'warn', 'Err timeout']) AS errs;
-- ['ERROR: disk','Err timeout']

Inside these expressions | is composition, not bitwise OR. Use bitOr for the bitwise operation.

Trino syntax and SIMILAR TO

Parts of the Trino dialect now work in ordinary ClickHouse SQL (#124763): TRY_CAST, the ARRAY[...] constructor, the ARRAY(T) and MAP(K, V) type names, and aliases such as starts_with, slice, regexp_like, day_of_week and url_extract_host. SIMILAR TO arrives too (#101514). Fewer queries ported from Trino or Presto need rewriting.

-- Run on 26.10.2
SELECT TRY_CAST('42' AS Int32) AS ok, TRY_CAST('x42' AS Int32) AS bad,
       ARRAY[1, 2, 3] AS arr, starts_with('clickhouse', 'click') AS sw,
       'abc' SIMILAR TO '(a|x)bc' AS similar;
-- ok = 42, bad = NULL, arr = [1,2,3], sw = 1, similar = 1

Joins on any condition, and LATERAL

A JOIN whose ON has no equality between the two tables used to fail with INVALID_JOIN_ON_EXPRESSION. It now runs as a block nested loop join (#123642), controlled by allow_block_nested_loop_join (on by default). It is as slow as a CROSS JOIN with a filter, so it buys correctness, not speed. On 26.10.2 an INNER JOIN on e.duration_ms > s.max_ms worked even with the setting off; the LEFT JOIN is what depends on it:

-- Run on 26.10.2: 300,000 rows in events, 2 rows in sla
SELECT count() FROM events AS e LEFT JOIN sla AS s ON e.duration_ms > s.max_ms;
-- 300540
SELECT count() FROM events AS e LEFT JOIN sla AS s ON e.duration_ms > s.max_ms
SETTINGS allow_block_nested_loop_join = 0;
-- Code: 403 ... Cannot determine join keys in JOIN ON expression ... (INVALID_JOIN_ON_EXPRESSION)

The experimental LATERAL JOIN (#97833) lets a subquery in FROM reference the left side, the standard “top N per group” form. Only INNER and LEFT JOIN LATERAL ... ON true are supported; CROSS JOIN LATERAL and the comma form were syntax errors on 26.10.2.

-- Run on 26.10.2
SET allow_experimental_lateral_join = 1;
SELECT c.country, t.url, t.slowest
FROM (SELECT DISTINCT country FROM events) AS c
INNER JOIN LATERAL (
    SELECT url, max(duration_ms) AS slowest FROM events
    WHERE events.country = c.country
    GROUP BY url ORDER BY slowest DESC, url LIMIT 2
) AS t ON true
ORDER BY c.country, t.slowest DESC;
-- two rows per country, e.g. DE /p/8 4996, DE /p/16 4992, FR /p/27 4999, ...

The PR’s own benchmark has ROW_NUMBER() ... WHERE rn = 1 faster for top-1 per group, so this is about expressiveness.

Overlay databases and database SETTINGS

ENGINE = Overlay(db1, db2, ...) exposes the union of the tables of existing databases. Each table becomes an Alias to the table in the first listed database that has it (#121878). That is a cheap way to put a “patch” database in front of a base one:

-- Run on 26.10.2: base has metrics and sensors, patch has only sensors
CREATE DATABASE combined ENGINE = Overlay(patch, base);
SELECT name, engine FROM system.tables WHERE database = 'combined';
-- metrics Alias, sensors Alias
SELECT * FROM combined.sensors;   -- 1  new-hall  (patch wins over base's old-hall)

CREATE DATABASE analytics SETTINGS max_tables = 100 now works without ENGINE and created an Atomic database on 26.10.2 (#118423). A Cluster database engine forwards queries to a configured cluster database (#114417).

Memory: IN sets spill, query cache on disk

Sets built for IN (subquery) now spill to disk by default once the query uses half the memory available under the server or user memory limit (max_bytes_ratio_before_external_set = 0.5). The feature is generally available (#123860), and the defaults were confirmed in system.settings on 26.10.2. The ratio does not take max_memory_usage into account, so a query whose own limit is below that half can still fail before it spills; set the absolute threshold max_bytes_before_external_set below the query’s limit to make a large semi-join filter spill instead. Per-workload max_memory_before_spill triggers the same spilling for sorts, aggregation, DISTINCT and grace_hash joins (#119443).

The query cache can keep entries in a filesystem cache on disk (query_cache_on_disk_cache_name), where they survive restarts and, with a distributed filesystem cache, are shared between servers (#115382).

Smaller additions that save a workaround

All run on 26.10.2 unless noted:

• The -Map combinator takes plain columns, with the key last: sumMap(value, key) returned {'a':4,'b':2}, and argMaxMap(url, duration_ms, country) gave the slowest URL per country (#123449).
• avg and sum accept Interval types and return the same unit: the average of 0, 10, 20 and 30 seconds came back as 15, type IntervalSecond (#121458).
• New functions mapRemove, mapEntries, mapContainsKeyValue, arrayMinIndex, arrayMaxIndex, arrayRemoveAt, encodeQuotedPrintable and decodeQuotedPrintable. The ClickHouse-style names regrSlope, regrIntercept and others were added for the 26.9 regression aggregates.
• sql_compatible_division_for_integers = 1 makes 7 / 2 return 3 (type UInt8) and -7 / 2 return -3, truncating toward zero. The default remains 3.5 as Float64 (#124490).
• normalizedQueryHashUnordered ignores the order of select lists, GROUP BY keys and arguments, so SELECT a, b and SELECT b, a hash equal while normalizedQueryHash does not, which helps group query_log by pattern (#116345).
• SQLite is now an input and output format: a file written with FORMAT SQLite read back through file(..., SQLite) and opened in the sqlite3 CLI (#104510).
• From the changelog: system.query_log gains used_join_algorithms, used_join_kinds and spilled_to_disk (#113833), so spilling queries can be found across a fleet; traceView renders OpenTelemetry spans as a call tree (#116325); and min_unreserved_disk_space_for_merge stops large merges from taking the last free disk space (#115723).

Performance

GROUP BY ... LIMIT reads less (#121145). The aggregation’s top-K heap now publishes its boundary to the read step, which filters rows and skips whole granules through the primary key or a minmax index (enable_group_by_top_k_dynamic_filtering, on). Measured on 26.10.2 against the metrics table with 20 million rows and ORDER BY (metric, sensor_id, ts):

-- Run on 26.10.2, clickhouse local --print-profile-events
SELECT sensor_id, round(avg(value), 4) FROM metrics
GROUP BY sensor_id ORDER BY sensor_id LIMIT 5;

enable_group_by_top_k_dynamic_filtering	Rows read	Compressed bytes read	TopKGranulesSkippedByPrimaryKey
1 (default)	688,128	371,720	2,358 of 2,442
0	20,000,000	11,456,815	none

Results were identical. Grouping events by user_id, which is in neither the primary key nor a minmax index, read all 10 million rows either way: the win needs the grouping key in the sort key or in a minmax skip index.

From the changelog:

• Large hash tables for GROUP BY, uniqExact and JOIN use up to two times less memory (#125001).
• The query condition cache now serves conditions such as time >= now() - INTERVAL 10 DAY by rounding the current time onto a grid, so dashboard queries can now hit it (#115522).
• apply_string_filters_during_scan is on by default. LIKE, position, startsWith and endsWith from PREWHERE run inside the column scan, and non-matching strings are not copied (#115848).
• query_plan_join_shard_by_pk_ranges is on by default. A full_sorting_merge join of two MergeTree tables whose keys share a primary-key prefix runs as parallel merge joins over key ranges, several times faster and smaller in memory per the changelog (#117856).
• The text-index postings cache is on by default, bounded at 2 GiB. A four-token phrase query on a 1B-row table went from 19 s to 0.12 s (#120677).
• When max_threads is not set, a query is planned with its fair share of CPU slots rather than all cores, which raises throughput under concurrency (use_concurrency_control_for_max_threads, #125085). optimize_move_to_prewhere_if_final is now on as well (#121350).

All these defaults read 1 in system.settings on 26.10.2; they are the first switches to try if a query regresses.

Experimental

A columns cache for deserialized MergeTree columns (use_columns_cache, #96844), a LogsQL dialect (#113207), an embedded Iceberg REST catalog (#118924) and ipcrypt* IP encryption functions (#123680). The PCO numeric codec is beta (enable_pco_codec, #110613).

Plan before you upgrade

Every backward-incompatible entry in the 26.10 changelog is below, grouped by what it breaks.

1. The server may not start

• seccomp is on. The packaged config.xml and the Docker image set seccomp to trap (#119545). Without that file the default is log, as clickhouse local reported. In a sandbox that refuses the seccomp call, or with executable UDFs, set log or disabled first.
• Disk definitions are validated. An unknown element in storage_configuration, such as path in an S3 disk, now fails with UNKNOWN_ELEMENT_IN_CONFIG (#118440). Remove it, or set skip_check_for_incorrect_settings.
• Named collections. Overriding a stored key needs SHOW NAMED COLLECTIONS SECRETS, and allow_named_collection_override_by_default is obsolete (#123133). Tables that override a NOT OVERRIDABLE key stop the server, so recreate them first.
• Objects that no longer load. Drop or rewrite these before upgrading:
  • Remote, RemoteSecure or Cluster databases with an empty remote database name (#122675);
  • columns holding a zero-byte aggregate state, such as countResample over an empty range or argument-less corrMatrix (#119728);
  • types such as AggregateFunction(rank) (#122148);
  • window-function -State calls in keys, indexes or TTL (#122463).
• ZXC codec data becomes unreadable (CANNOT_DECOMPRESS). Re-encode it before the upgrade (#119097).

2. Clusters and security

• The pre-analyzer query path is gone for remote queries too (#120311). A mixed-version cluster can hit NOT_FOUND_COLUMN_IN_BLOCK, so set enable_analyzer = 1 on every server before upgrading any of them. The AST predicate push-down is removed and enable_optimize_predicate_expression is obsolete (#121465). See moving to the new analyzer.
• Settings constraints and allow_feature_tier apply to SQL-defined users, roles and profiles (#119472). A user whose stored settings violate them can no longer log in, and compatibility no longer applies refused values. Audit SQL-defined users first.
• New FILE grants. ATTACH TABLE ... FROM '<path>' needs READ ON FILE and WRITE ON FILE (#120582).
• MySQL. A MySQL dictionary made by CREATE DICTIONARY can no longer set enable_local_infile. Move it to a config file (#120972). Connections that set ssl_ca now verify the certificate chain and host name, so check your certificates (#119510).
• Row policies on a Distributed table read through Merge now fail with ILLEGAL_PREWHERE. Define the policy on the underlying tables instead (#125765).
• skip_unavailable_shards = 1 with every shard skipped now fails with ALL_CONNECTION_TRIES_FAILED instead of returning nothing. No setting restores the old behavior (#115775).

3. Results and types that change

• arrayCount returns UInt64. It printed UInt64 on 26.10.2, and UInt32 with array_count_legacy_uint32_result = 1 (#113023). During a rolling upgrade, enable the legacy setting on upgraded shards.
• arrayJoin is lowered to an ARRAY JOIN step, so rand() beside it is evaluated once per output row (#117882). On 26.10.2, uniqExact(rand()) over a 3-element arrayJoin gave 3. The changelog’s opt-out, arrayjoin_nondeterministic_functions_before_expansion, fails with UNKNOWN_SETTING on 26.10.2; legacy_array_join_function_nondeterministic_evaluation = 1 and compatibility = '26.9' work and gave 1. The same setting covers runningDifference, neighbor and rowNumberInBlock next to arrayJoin (#123143).
• SummingMergeTree with FINAL keeps all-zero rows (#115032). On 26.10.2 the query returned (1, 0) and (2, 4), and OPTIMIZE ... FINAL then removed (1, 0). Filter zero rows explicitly if a report relied on that.
• accurateCast to Nullable throws CANNOT_PARSE_TEXT on unparsable text, as tested; use accurateCastOrNull for the old result (#120674).
• A JSON null for a non-Nullable number with input_format_null_as_default = 0 now raises an error instead of storing 0, as tested; the default 1 still inserts 0 (#122137).
• A MATERIALIZED column computed from a TTL column is recomputed when the TTL fires, and a non-total expression makes the merge throw (#117815).
• ALTER ... ADD/MODIFY COLUMN honors data_type_default_nullable (#124597). Duplicate CONSTRAINT names are refused in new definitions (#121235).
• --ignore-error. clickhouse local now reports failed statements, as the test scripts here showed, and clickhouse-client exits with success; scripts that relied on its exit code should drop the flag (#119572).

4. Spilling, settings and system tables

• Join spilling. max_bytes_before_external_join is the spill trigger for every hash join, including grace_hash, which now needs a non-zero threshold. max_bytes_in_join is a hard cap again. Restore the old behavior with legacy_join_size_limits_trigger_spilling = 1 (#117356). See joins, memory and algorithms.
• system.metric_log uses the bucketed Map schema. The old table is renamed metric_log_0, and ALIAS columns keep ProfileEvent_* queries working. Keep the old layout with <schema_type>wide</schema_type> (#118318).
• System log tables are configured through system_tables.*. In opentelemetry_span_log, finish_date becomes event_date, and some tables fall back to a default TTL, so set ttl explicitly (#121563).
• Obsolete settings, accepted but ignored. On 26.10.2 these all showed is_obsolete = 1 in system.settings or system.merge_tree_settings:
  • validate_mutation_query: mutations are always validated (#121419);
  • query_plan_read_in_order, query_plan_optimize_prewhere and query_plan_aggregation_in_order (#121390);
  • use_compact_format_in_distributed_parts_names: set it to 1 and drain the queue before upgrading (#120186);
  • use_minimalistic_part_header_in_zookeeper and use_minimalistic_checksums_in_zookeeper (#122790);
  • s3_max_connections: use the disk_connections_* limits (#119550).
• Removed.
  • The zero-argument year() now fails, so use toYear(today()) (#120268).
  • system.codecs.is_experimental is gone, so filter on tier = 'Experimental' (#118523).
  • detectTonality is gone (#123327).

If you do one thing first, start 26.10 on a staging server with your production config.xml and metadata: seccomp, disk validation, named collections and the objects that no longer load all show up at startup. Measure the rest with compatibility = '26.9' and the steps in upgrading ClickHouse safely. The previous release is covered in ClickHouse 26.9.