ClickHouse 26.4 New Features Lab

---

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

📋 Overview

ClickHouse 26.4 is a SQL-ergonomics release: NATURAL JOIN and VALUES clauses cut JOIN boilerplate, two new array primitives (arrayTranspose and arrayAutocorrelation) bring matrix and time-series utilities to SQL, and a trio of text helpers (obfuscateQuery, highlight, non-constant printf) round out the string toolkit. The release also makes automatic JOIN spilling to grace hash join the default behavior.

🎯 Key Features

1. NATURAL JOIN + VALUES Table Expression — SQL-standard join syntax and inline tables
2. arrayTranspose + arrayAutocorrelation — matrix transpose and time-series autocorrelation
3. obfuscateQuery + highlight + per-row printf — text transformation toolkit

🚀 Quick Start

Prerequisites

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

Setup and Run

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

./01-natural-join-values.sh
./02-array-functions.sh
./03-string-text-functions.sh

📚 Feature Details

1. NATURAL JOIN + VALUES Table Expression (01-natural-join-values)

New Features: - NATURAL JOIN — automatically matches on all same-named columns and deduplicates them in the result. (#99840) - VALUES (...) AS t(col, ...) — SQL-standard VALUES clause usable as a table expression in FROM. (#100143)

Test Content: - VALUES as the source of a SELECT, inside CTEs, and as the right side of a join - NATURAL JOIN between two tables sharing one column - Column-count comparison vs explicit JOIN ... ON - NATURAL JOIN driven entirely by VALUES literals - Country-code lookup pattern (replace temp tables with inline VALUES) - Multi-column NATURAL JOIN (region + category)

Key Learning Points: - NATURAL JOIN makes joins concise but is implicit — readers must inspect the schema to know what is being joined - Use it for ad-hoc analytics; prefer explicit JOIN ... USING (col) in production for clarity - VALUES works as a real table expression — supports ORDER BY, LIMIT, joins - Combine VALUES with CTEs to inline small reference tables without DDL

Use Cases: - Quick lookups against a small static mapping (country → name, currency → rate) - Demos and tutorials (no CREATE TABLE needed) - Migration from PostgreSQL / SQL Server scripts that already use VALUES - Reducing JOIN boilerplate in star-schema queries

---

2. arrayTranspose + arrayAutocorrelation (02-array-functions)

New Features: - arrayTranspose(matrix) — transpose a 2-D array (rows ↔ columns). (#101214) - arrayAutocorrelation(arr [, max_lag]) — normalized autocorrelation at each lag from 0 up to max_lag. (#94776)

Test Content: - 2×3 → 3×2 transpose, square transpose, single-row → column-vector - Round-trip identity arrayTranspose(arrayTranspose(M)) = M - Pivot use case: rows-of-records → columns-of-fields - Autocorrelation of a linear ramp, constant array, and random noise - Detecting weekly seasonality (period 7) in a synthetic 28-day series - Custom max_lag parameter - Combined: per-row autocorrelation after transposing a stack of signals

Key Learning Points: - arrayTranspose expects a "rectangular" array of arrays — all inner arrays must be the same length - Element type is preserved (numbers stay numbers, strings stay strings) - arrayAutocorrelation returns lag 0 as 1.0 (perfect self-correlation) - Constant arrays produce NaN autocorrelation (zero variance, undefined denominator) - A strong peak at lag k indicates periodicity with period k

Use Cases: - Matrix-style data manipulation in SQL (no ARRAY JOIN ceremony) - Quick pivot from row-store to column-store views - Detecting seasonality in time-series stored as arrays - Time-series quality checks (random noise should yield near-zero autocorrelation) - Mathematical and statistical pipelines

---

3. obfuscateQuery + highlight + Non-Constant printf (03-string-text-functions)

New Features: - obfuscateQuery(s) — replace identifiers and literals with deterministic random substitutes, preserving SQL structure. (#98305) - highlight(text, terms [, open_tag, close_tag]) — wrap each occurrence of any term in HTML tags (default <em>/</em>). (#99131) - printf now accepts a non-constant format string, allowing per-row formats. (#98991)

Test Content: - Basic obfuscateQuery on simple and complex SQL - Determinism check (same input → same output) - Batch obfuscation of three different queries - highlight with default and custom tags, case-insensitive matching - Search-results pipeline using highlight on real article text - Per-row format strings (decimal, hex, octal, zero-padded) - Mixed-format logging output - Combined pipeline: filter → highlight → format

Key Learning Points: - obfuscateQuery is deterministic — same query always obfuscates to the same output (good for de-duplicating query patterns in logs) - highlight does ASCII case-insensitive matching; for international text combine with caseFoldUTF8 (added in 26.3) - Non-constant printf enables data-driven formatting — useful for log rendering and report generation - highlight automatically merges overlapping matches

Use Cases: - Safely sharing slow-query reports without exposing PII or sensitive identifiers - Building search-results UIs (highlight matched terms in snippets) - Generating reports / logs with mixed numeric formats per row - Query-pattern deduplication for capacity planning

---

🔧 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-4
./stop.sh
./stop.sh --cleanup

📂 File Structure

26.4/
├── README.md                     # This document
├── 00-setup.sh                   # ClickHouse 26.4 installation script
├── 01-natural-join-values.sh     # NATURAL JOIN + VALUES test runner
├── 01-natural-join-values.sql    # NATURAL JOIN + VALUES SQL
├── 02-array-functions.sh         # arrayTranspose + arrayAutocorrelation runner
├── 02-array-functions.sql        # Array functions SQL
├── 03-string-text-functions.sh   # obfuscateQuery + highlight + printf runner
└── 03-string-text-functions.sql  # String/text functions SQL

🆕 What's New in 26.4

• Automatic JOIN spilling to grace hash join when memory limit is reached (default behavior)
• Arrow Flight SQL support
• NATURAL JOIN syntax
• VALUES as a table expression in FROM
• arrayTranspose and arrayAutocorrelation
• obfuscateQuery, highlight, non-constant printf
• PostgreSQL-compatible EXTRACT units — EPOCH, DOW, DOY, ISODOW, ISOYEAR, WEEK, CENTURY, DECADE, MILLENNIUM
• SQL-standard compound intervals — INTERVAL '1:30' HOUR TO MINUTE
• hasPhrase / matchPhrase — phrase search (continuous sequences of tokens)
• commit_order projection index — keep data in insertion order
• MergeTree skip-index for JSON columns — bloom_filter, tokenbf_v1, ngrambf_v1, text on JSONAllPaths
• Bucketed serialization for Map columns — 2-49× faster single-key lookups
• stem function GA — was previously experimental
• Map/JSON values in dictionary attributes
• auto_statistics_types defaults to 'minmax, uniq' — automatic stats on new tables
• Date + Time addition produces DateTime/DateTime64
• Quotas by normalized-query-hash — limit per query-pattern
• SET TIME ZONE as an alias for SET session_timezone
• Text index GA stays enabled regardless of compatibility setting
• Experimental: aiGenerate function for OpenAI / Anthropic LLM calls from SQL

🔍 Additional Resources

• Release Presentation: ClickHouse 26.4
• Release Video: YouTube
• ClickHouse Documentation: docs.clickhouse.com

📝 Notes

• All features verified on ClickHouse 26.4.3.37
• Each script can be executed independently
• Test data is generated within each SQL file
• Cleanup is commented out for inspection

📄 License

MIT — free to learn from and modify.

---

Happy Learning! 🚀

---

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

./01-natural-join-values.sh
./02-array-functions.sh
./03-string-text-functions.sh