ClickHouse 26.8 LTS

---

Released 2026-08-30 · LTS · verified here against 26.8.2.7

26.8 is an LTS release and a large one — the changelog runs to 21 backward incompatible changes, 46 new features and 89 performance improvements. This lab takes three of them that you can see working in a single container, and that change how you write queries rather than how fast they run.

Lab	Feature	Why it is here
01	Pipe operators \|>	The biggest syntax change in years. Queries read in the order data flows
02	GROUPS window frame mode	The third frame mode SQL defines, and the one ClickHouse was missing
03	japanese, chinese, icu tokenizers	The difference between a working Korean text index and a useless one

Quick start

./00-setup.sh              # brings up ClickHouse 26.8 via local/oss-docker
./01-pipe-operators.sh
./02-groups-window-frame.sh
./03-cjk-tokenizers.sh

Each NN-*.sh pipes the matching .sql into clickhouse-client. Read the SQL — the comments are the lab.

---

01 · Pipe operators

FROM trips
|> WHERE n > 2
|> AGGREGATE sum(n) AS total GROUP BY city
|> ORDER BY total DESC

SQL reads in an order nobody writes in. You start at SELECT, jump back to FROM, then to WHERE, then return to the select list once you know what you are grouping by. Pipe syntax lets the text run in the order the data flows.

The operators. The parser will list them if you give it something it does not recognise:

WHERE, SELECT, EXTEND, SET, DROP, AS, AGGREGATE, DISTINCT,
ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, and the JOIN family

Three of those have no classic equivalent and are worth knowing:

EXTEND expr AS name	add a column, keep everything else — no need to re-list the columns
SET col = expr	overwrite a column in place
DROP col	remove a column

The practical argument is not elegance, it is append-only editing. Every prefix of a pipe chain is a complete query:

FROM trips;
FROM trips |> WHERE n >= 5;
FROM trips |> WHERE n >= 5 |> AGGREGATE sum(n) AS total GROUP BY city;
FROM trips |> WHERE n >= 5 |> AGGREGATE sum(n) AS total GROUP BY city |> ORDER BY total DESC;

Each line is the previous one plus a step. Nothing above ever has to be edited. Building a query stops being a cycle of jumping back to the top.

It is sugar. EXPLAIN SYNTAX rewrites a pipe chain into the classic form:

SELECT city, sum(n) AS total FROM (SELECT * FROM (SELECT * FROM trips)
WHERE greater(n, 2)) GROUP BY city

Same AST, same plan, same performance. Nothing to weigh up except readability — and on a single-table lookup the classic form is still shorter, which is worth saying out loud before someone rewrites a codebase.

!!! note EXPLAIN SYNTAX itself changed in 26.8: it now returns one String record with embedded newlines instead of one record per line. If you had something parsing its output, that is a breaking change.

---

02 · GROUPS window frame mode

SQL defines three ways to size a window frame. ClickHouse had two.

Mode	Counts
ROWS	physical rows
RANGE	rows whose value is within the given distance
GROUPS	peer groups — sets of rows sharing the same ORDER BY value

The difference only shows up when there are ties, gaps, or both. Values 1, 5, 5, 100 with BETWEEN 1 PRECEDING AND 1 FOLLOWING:

n	ROWS	RANGE	GROUPS
1	2	1	3
5	3	2	4
5	3	2	4
100	2	1	3

RANGE collapses at the gap — nothing is within ±1 of 100, so the frame is the row itself. GROUPS does not care how far away 100 is, only that it is the next distinct value.

Where it is the correct answer. Ranked results with ties. "Compare each score against the ranks either side of it" is a GROUPS question — you mean the neighbouring rank, not a score one point away and not a fixed row count:

player  score  GROUPS neighbours      ROWS neighbours
a       100    [a,b,c,d]              [a,b]
b       100    [a,b,c,d]              [a,b,c]
c       100    [a,b,c,d]              [b,c,d]
d        80    [a,b,c,d,e,f]          [c,d,e]

GROUPS returns the same complete answer for all three tied players. ROWS slices arbitrarily through the tie and gives each of them a different one.

The detail that surprises people. Under GROUPS and RANGE, CURRENT ROW is not one row — it is every row tied with it:

-- three rows all equal to 7
count() OVER (ORDER BY n GROUPS BETWEEN CURRENT ROW AND CURRENT ROW)  -- 3
count() OVER (ORDER BY n ROWS   BETWEEN CURRENT ROW AND CURRENT ROW)  -- 1

On evenly spaced data RANGE and GROUPS are the same function, which is why the distinction is easy to miss until it produces a wrong number.

---

03 · japanese, chinese and icu tokenizers

For anyone indexing Korean, Japanese or Chinese, this is the release that makes text indexes usable.

What used to happen to Korean:

unicodeWord  ['클','릭','하','우','스','벡','터','검','색','은','빠','르','다']
asciiCJK     ['클','릭','하','우','스','벡','터','검','색','은','빠','르','다']
chinese      ['클릭하우스','벡터','검색은','빠르다']
icu(ko)      ['클릭하우스','벡터','검색은','빠르다']

Thirteen tokens for four words. An index built on per-syllable tokens matches almost everything and prunes almost nothing — the worst of both. Korean does put spaces between words, which is why this is easy to get wrong: it looks like a space-splitting tokenizer should have been fine.

Building the index. The tokenizer argument is a function call:

INDEX idx body TYPE text(tokenizer = icu('ko')) GRANULARITY 1   -- correct
INDEX idx body TYPE text(tokenizer = 'icu', locale = 'ko')      -- rejected

Two things about icu worth knowing before you tune it.

The locale is mandatory — tokens(s, 'icu') errors with "requires a mandatory locale argument". And it must be a constant; passing a column gives "Expected: const String, got: String".

And then it does not change the Korean result. ko, ko_KR, en, ja and zh all return identical tokens, because ICU's Korean word-break comes from a built-in dictionary rather than from the locale tag.

The limitation to plan around

SELECT count() FROM docs WHERE hasToken(body, '검색');   -- 0
SELECT id    FROM docs WHERE hasToken(body, '검색은');   -- 1

Korean particles (조사) stay glued to the noun. 검색은, 검색을 and 검색이 are three different tokens and none of them is 검색.

This is word segmentation, not morphological analysis. ICU finds word boundaries; it does not know that 은/을/이 are grammar rather than spelling. Options, in the order most people should consider them:

1. Search with the particle, if users type whole phrases.
2. Pre-process a second column with a Korean morphological analyser (mecab-ko, nori, kiwi) outside ClickHouse and index that with splitByString. You keep control and pay an ingest step.
3. Use ngrams for recall-first search and accept the index size.

What you should not do is assume icu('ko') behaves like a Korean analyser. It is a large improvement over per-syllable splitting, and it is not that.

The other two

For Chinese the dedicated tokenizer is better than ICU:

chinese  ['点击','之家','向量','搜索','很快']
icu(zh)  ['点','击','之家','向量','搜索','很快']

ICU splits 点击 into two characters. For Korean the two were identical. Test on your own corpus rather than picking by name.

japanese is listed in system.tokenizers but needs a dictionary supplied in the server configuration:

Code: 139. The Japanese tokenizer requires a dictionary configured under
<tokenizer><japanese> in the server configuration

so it does not work out of the box in this container. icu handles Japanese without extra configuration, which is the pragmatic choice for a mixed corpus.

---

Also in 26.8, not covered by these labs

Worth knowing about, in rough order of how likely they are to affect you:

max_insert_threads default 1 → auto	INSERT SELECT is now parallel by default. Backward incompatible, and the one most likely to change your ingest behaviour without you asking
Lightweight UPDATE patch parts, v2 format	Peak memory bounded by the largest equal-sort-key run instead of the whole patch
Date32 range widened	[1900-01-01, 2299-12-31] → [0000-01-01, 9999-12-31]
PostgreSQL / MaterializedPostgreSQL engines respect remote_url_allow_hosts	CREATE DATABASE now rejects disallowed hosts
IEJoin	Join algorithm for inequality comparisons
gini	Aggregate function for the Gini coefficient
notHas, array-of-integers subscript, MultiPoint	Small conveniences, all present and working
bigquery table function and BigQuery engine
aiSimilarity, aiFilter, aiRedact	LLM-backed functions; need an external provider, so out of scope for an offline lab
CREATE HANDLER	Custom HTTP handlers as DDL
parseQueryToJSON / formatQueryFromJSON	AST to JSON and back

Verified

Every query in the three .sql files was run against 26.8.2.7 in the container this lab sets up, and the outputs quoted above are from those runs. Two of them needed fixing along the way, and both are noted in the SQL: UNION ALL does not expose its column aliases to a trailing ORDER BY, and tokens() will not take a locale from a column.

---

./00-setup.sh              # local/oss-docker으로 26.8 기동
./01-pipe-operators.sh
./02-groups-window-frame.sh
./03-cjk-tokenizers.sh

FROM trips
|> WHERE n > 2
|> AGGREGATE sum(n) AS total GROUP BY city
|> ORDER BY total DESC

WHERE, SELECT, EXTEND, SET, DROP, AS, AGGREGATE, DISTINCT,
ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, 그리고 JOIN 계열

FROM trips;
FROM trips |> WHERE n >= 5;
FROM trips |> WHERE n >= 5 |> AGGREGATE sum(n) AS total GROUP BY city;
FROM trips |> WHERE n >= 5 |> AGGREGATE sum(n) AS total GROUP BY city |> ORDER BY total DESC;

SELECT city, sum(n) AS total FROM (SELECT * FROM (SELECT * FROM trips)
WHERE greater(n, 2)) GROUP BY city

player  score  GROUPS 이웃            ROWS 이웃
a       100    [a,b,c,d]              [a,b]
b       100    [a,b,c,d]              [a,b,c]
c       100    [a,b,c,d]              [b,c,d]
d        80    [a,b,c,d,e,f]          [c,d,e]

-- 값이 전부 7인 세 행
count() OVER (ORDER BY n GROUPS BETWEEN CURRENT ROW AND CURRENT ROW)  -- 3
count() OVER (ORDER BY n ROWS   BETWEEN CURRENT ROW AND CURRENT ROW)  -- 1

unicodeWord  ['클','릭','하','우','스','벡','터','검','색','은','빠','르','다']
asciiCJK     ['클','릭','하','우','스','벡','터','검','색','은','빠','르','다']
chinese      ['클릭하우스','벡터','검색은','빠르다']
icu(ko)      ['클릭하우스','벡터','검색은','빠르다']

INDEX idx body TYPE text(tokenizer = icu('ko')) GRANULARITY 1   -- 맞음
INDEX idx body TYPE text(tokenizer = 'icu', locale = 'ko')      -- 거부됨

SELECT count() FROM docs WHERE hasToken(body, '검색');   -- 0
SELECT id    FROM docs WHERE hasToken(body, '검색은');   -- 1

chinese  ['点击','之家','向量','搜索','很快']
icu(zh)  ['点','击','之家','向量','搜索','很快']

Code: 139. The Japanese tokenizer requires a dictionary configured under
<tokenizer><japanese> in the server configuration