ClickHouse 26.5 New Features Lab

---

A hands-on laboratory for learning and testing ClickHouse 26.5 new features. This directory focuses on verified and working features newly added in ClickHouse 26.5 (released 2026-05-21).

📋 Overview

ClickHouse 26.5 sharpens the SQL surface for introspection and ergonomics: the new filesystem() table function lets you query directory trees as tables, higher-order functions accept bare function names (cutting lambda boilerplate), and tokenizeQuery / highlightQuery expose the lexer and parser to SQL itself. Number-theory primitives (isPrime, isProbablePrime) round out a release packed with developer-experience improvements.

🎯 Key Features

1. filesystem() Table Function — query directories, file metadata, and contents as a SQL table
2. Bare Function Names + isPrime / isProbablePrime — arrayMap(negate, [1,2,3]) and primality testing
3. tokenizeQuery + highlightQuery — lexer/parser introspection for SQL strings

🚀 Quick Start

Prerequisites

• macOS (with Docker Desktop)
• oss-docker environment

Setup and Run

cd local/releases/26.5
./00-setup.sh

./01-filesystem-function.sh
./02-higher-order-primes.sh
./03-tokenize-highlight-query.sh

📚 Feature Details

1. filesystem() Table Function (01-filesystem-function)

New Feature: A new filesystem('path') table function exposes the contents of a directory (relative to user_files) as a SQL table with one row per entry. Columns include name, type, size, depth, modification_time, content, and full Unix permission flags. (#53610)

Test Content: - Creating sample files via INSERT INTO FUNCTION file(...) - Schema inspection (DESCRIBE filesystem('.')) - Listing all entries with metadata - Files-only filter + size formatting - Reading file contents inline - Extension and permission filtering - Aggregations over the filesystem - Recently-modified files (last hour) - Pairing filesystem() discovery with file() data reads - Unix permission audit string rendering

Key Learning Points: - filesystem() reads relative to /var/lib/clickhouse/user_files (user_files_path config) - The content column gives you the entire file body as a Nullable(String) — be careful with large files - Directory entries have size = NULL; real files have a size and content - 22 columns are available (name, type, size, depth, mtime, permissions, etc.) - Combine with file() table function to discover then process files in one query

Use Cases: - Operational queries against staged ingest files - Auditing user_files directory (sizes, ages, permissions) - Cataloging data lake snapshots stored on local disk - Building self-service file browsers backed by SQL - Identifying stale or oversized staged files for cleanup

---

2. Bare Function Names + isPrime / isProbablePrime (02-higher-order-primes)

New Features: - Higher-order functions (arrayMap, arrayFilter, arrayCount, arraySum, etc.) now accept a bare function name instead of requiring a lambda. arrayMap(negate, [1,2,3]) is equivalent to arrayMap(x -> negate(x), [1,2,3]). (#101033) - isPrime(n) — exact primality test for UInt64 (#104234) - isProbablePrime(n [, rounds]) — Miller-Rabin probabilistic test for up to UInt256, with configurable confidence (default 25 rounds, FP rate < 10⁻¹⁵)

Test Content: - Old vs new syntax (x -> negate(x) vs negate) - arrayFilter(isPrime, [...]) — predicate as bare function - isPrime on small numbers - isProbablePrime on UInt128 and UInt256 values (including Mersenne primes) - Configurable rounds parameter for confidence tuning - arrayCount(isPrime, range(2,100)) and arraySum(arrayFilter(isPrime, ...)) - Mixed lambda + bare function usage

Key Learning Points: - Any single-argument function with the right signature can be used bare - Bare-function form is shorter and avoids accidental variable shadowing in nested lambdas - isPrime is exact only for UInt64; for wider types use isProbablePrime - isProbablePrime returns 0 for "definitely composite" and 1 for "probably prime" - More rounds = higher confidence at the cost of CPU; the default is sufficient for almost all use cases

Use Cases: - Cleaner functional-style SQL - Number-theory experiments, cryptographic preliminaries - Validating user-provided primes (e.g. RSA parameter checks) - Generating prime-numbered IDs, hash-table sizes

---

3. tokenizeQuery + highlightQuery (03-tokenize-highlight-query)

New Features: - tokenizeQuery(s) returns Array(Tuple(start, end, token_type)) — the lexer's token stream for a SQL string (BareWord, Number, Whitespace, StringLiteral, …) - highlightQuery(s) returns Array(Tuple(start, end, category)) — the parser's syntax-highlight ranges with semantic categories (keyword, identifier, function, number, string, …) - (#101054)

Test Content: - tokenizeQuery on simple and complex queries - Token-by-token decomposition via ARRAY JOIN - Token-type frequency analysis - highlightQuery basics (SELECT, CREATE TABLE) - Building a per-token highlight breakdown - Side-by-side comparison: lexer-level (many tokens) vs parser-level (semantic spans) - Identifier extraction from a query corpus (real use case) - Keyword frequency across a query log

Key Learning Points: - tokenizeQuery is finer-grained: every whitespace, operator, and bare word is its own token - highlightQuery collapses tokens into semantic units (a function call is one span) - Tuple offsets are byte positions — slice the original string with substring(q, t.1 + 1, t.2 - t.1) - These functions enable SQL-driven query-log analysis without any external parser - Categories from highlightQuery include: keyword, identifier, function, number, string

Use Cases: - Custom query-log analytics (identifier inventories, keyword usage) - Detecting unusual queries by token-shape signature - Building schema-aware autocomplete or linting in custom tooling - Pre-processing for SQL-similarity / clustering pipelines

---

🔧 Management

ClickHouse Connection Info

• Web UI: http://localhost:8123/play
• HTTP API: http://localhost:8123
• TCP: localhost:9000
• User: default (no password)

Useful Commands

cd ../../oss-docker
./status.sh
./client.sh 8123
docker logs clickhouse-26-5
./stop.sh
./stop.sh --cleanup

📂 File Structure

26.5/
├── README.md                          # This document
├── 00-setup.sh                        # ClickHouse 26.5 installation script
├── 01-filesystem-function.sh          # filesystem() runner
├── 01-filesystem-function.sql         # filesystem() SQL
├── 02-higher-order-primes.sh          # Bare names + primes runner
├── 02-higher-order-primes.sql         # Bare names + primes SQL
├── 03-tokenize-highlight-query.sh     # tokenizeQuery / highlightQuery runner
└── 03-tokenize-highlight-query.sql    # tokenizeQuery / highlightQuery SQL

🆕 What's New in 26.5

• filesystem() table function
• Bare function names in higher-order functions
• tokenizeQuery / highlightQuery for SQL introspection
• isPrime / isProbablePrime number-theory primitives
• regexpPosition (with PostgreSQL-compatible regexpInstr / regexp_instr aliases)
• prettyPrintJSON function
• STRING_AGG case-insensitive alias for groupConcat
• Negative LIMIT BY — LIMIT -2 BY id selects last N rows per group
• Multi-path Tuple / Array JSONPath in JSON_VALUE, JSON_EXISTS, JSON_QUERY
• url_base setting for relative URL resolution in url() / URL engine
• CREATE OR REPLACE MATERIALIZED VIEW with atomic-swap semantics
• SYSTEM PAUSE VIEW for refreshable materialized views
• system.zookeeper_watches system table
• AvroConfluent output format (was input-only)
• Paimon table engine with incremental snapshot tracking
• Independent subquery use_query_cache with query_cache_for_subqueries setting
• Web UI syntax highlighting in play.html
• Experimental Web Terminal at /webterminal (browser-based clickhouse-client over WebSocket)
• Experimental WASM UDFs can now be declared DETERMINISTIC
• Spill-to-grace-hash auto-enabled at max_bytes_ratio_before_external_join = 0.5

🔍 Additional Resources

• Release Presentation: ClickHouse 26.5
• Release Video: YouTube
• Web Terminal Demo: play.clickhouse.com/webterminal?user=play
• ClickHouse Documentation: docs.clickhouse.com

📝 Notes

• All features verified on ClickHouse 26.5.1.882
• Each script can be executed independently
• Test data is generated within each SQL file
• Cleanup is commented out for inspection
• The filesystem() test creates files in user_files — remove with docker exec clickhouse-26-5 rm /var/lib/clickhouse/user_files/demo_* if desired

📄 License

MIT — free to learn from and modify.

---

Happy Learning! 🚀

For questions, see the main clickhouse-hols README.

---

cd local/releases/26.5
./00-setup.sh

./01-filesystem-function.sh
./02-higher-order-primes.sh
./03-tokenize-highlight-query.sh