ClickHouse リリース 26.10

1WITH [
2    'https://WWW.ClickHouse.com/docs',
3    'https://GitHub.com/ClickHouse/ClickHouse',
4    'https://www.anthropic.com/news'
5] AS urls
6SELECT
7    arrayMap(x -> upper(firstSignificantSubdomain(domainWithoutWWW(lower(x)))), urls) AS with_lambda,
8    arrayMap(lower | domainWithoutWWW | firstSignificantSubdomain | upper, urls) AS with_composition
9FORMAT Vertical;

1Row 1:
2──────
3with_lambda:      ['CLICKHOUSE','GITHUB','ANTHROPIC']
4with_composition: ['CLICKHOUSE','GITHUB','ANTHROPIC']

1WITH ['ClickHouse', 'Anthropic', 'OpenAI'] AS companies
2SELECT
3    arrayMap(x -> substring(upper(x), 1, 3), companies) AS with_lambda,
4    arrayMap(upper | substring(_, 1, 3), companies) AS with_composition
5FORMAT Vertical;

1Row 1:
2──────
3with_lambda:      ['CLI','ANT','OPE']
4with_composition: ['CLI','ANT','OPE']

1WITH
2    [49.99, 189.00, 19.50] AS prices,
3    [2, 1, 4] AS quantities
4SELECT
5    arrayMap(multiply(_1, _2) | toString | concat('$', _), prices, quantities) AS line_totals;

1┌─line_totals─────────────┐
2│ ['$99.98','$189','$78'] │
3└─────────────────────────┘

1WITH
2    ['Keyboard', 'Monitor', 'Mouse'] AS products,
3    [49.99, 189.00, 19.50] AS prices,
4    [2, 1, 4] AS quantities
5SELECT
6    arrayMap(_1 * _2, prices, quantities) AS line_totals,
7    arrayMap(concat(_1, ' x', toString(_2)), products, quantities) AS order_lines
8FORMAT Vertical;

1Row 1:
2──────
3line_totals: [99.98,189,78]
4order_lines: ['Keyboard x2','Monitor x1','Mouse x4']

1EXPLAIN SYNTAX run_query_tree_passes = 1
2SELECT arrayMap(
3    lower | domainWithoutWWW | firstSignificantSubdomain | upper,
4    ['https://WWW.ClickHouse.com/docs']
5) AS sites;

1SELECT arrayMap(lambda(tuple(__composed_arg_1), upper(firstSignificantSubdomain(domainWithoutWWW(lower(__composed_arg_1))))), _CAST(['https://WWW.ClickHouse.com/docs'], 'Array(String)')) AS sites
2FROM system.one AS __table1

1CREATE TABLE events (id UInt64) 
2ORDER BY id;

1CREATE USER ingest IDENTIFIED WITH no_password;
2GRANT INSERT, SELECT ON default.events TO ingest;

1CREATE QUOTA ingest_limit
2FOR INTERVAL 1 minute MAX written_rows = 100000, query_inserts = 250
3TO ingest;

1INSERT INTO events 
2SELECT number 
3FROM numbers(60000);

1Received exception:
2Code: 201. DB::Exception: Quota for user `ingest` for 60s has been exceeded: written_rows = 120000/100000. Interval will end at 2026-10-23 14:00:00. Name of quota template: `ingest_limit`. (QUOTA_EXCEEDED)

1SELECT quota_name, written_rows, max_written_rows, query_inserts, max_query_inserts
2FROM system.quotas_usage
3WHERE quota_name = 'ingest_limit'
4FORMAT Vertical;

1Row 1:
2──────
3quota_name:        ingest_limit
4written_rows:      120000
5max_written_rows:  100000
6query_inserts:     2
7max_query_inserts: 250

1WITH [2,5,10,9,7] AS values
2
3SELECT
4    arrayMinIndex(values) AS min_index,
5    arrayMaxIndex(values) AS max_index,
6    arrayMaxIndex(x -> length(x), ['a', 'abc', 'ab', 'abcd']) AS longest_index,
7    arrayRemoveAt(values, 2) AS remove_second,
8    arrayRemoveAt(values, -1) AS remove_last
9FORMAT Vertical;

1Row 1:
2──────
3min_index:     1
4max_index:     3
5longest_index: 4
6remove_second: [2,10,9,7]
7remove_last:   [2,5,10,9]

1WITH
2    ['Mon', 'Tue', 'Wed', 'Thu', 'Fri'] AS days,
3    [18.5, 21.2, 16.9, 24.3, 22.0] AS temps
4SELECT
5    days[arrayMinIndex(temps)] AS coldest,
6    days[arrayMaxIndex(temps)] AS warmest,
7    arrayRemoveAt(days, -1) AS without_friday;

1┌─coldest─┬─warmest─┬─without_friday────────────┐
2│ Wed     │ Thu     │ ['Mon','Tue','Wed','Thu'] │
3└─────────┴─────────┴───────────────────────────┘

1WITH map('env', 'prod', 'region', 'eu-west-1', 'team', 'payments') AS labels
2SELECT
3    mapRemove(labels, 'team') AS without_team,
4    mapEntries(labels) AS entries,
5    mapContainsKeyValue(labels, 'env', 'prod') AS is_prod
6FORMAT Vertical;

1Row 1:
2──────
3without_team: {'env':'prod','region':'eu-west-1'}
4entries:      [('env','prod'),('region','eu-west-1'),('team','payments')]
5is_prod:      1

1SELECT
2    sumMap(views, country) AS views_by_country,
3    argMaxMap(page, views, country) AS top_page_by_country
4FROM VALUES('country String, page String, views UInt32',
5    ('UK', '/docs', 120), ('UK', '/blog', 340),
6    ('US', '/docs', 910), ('US', '/pricing', 450),
7    ('DE', '/blog', 75));

1┌─views_by_country─────────────┬─top_page_by_country──────────────────────┐
2│ {'DE':75,'UK':460,'US':1360} │ {'DE':'/blog','UK':'/blog','US':'/docs'} │
3└──────────────────────────────┴──────────────────────────────────────────┘

1SELECT 7 / 2, -7 / 2, 7 / 2.0, toTypeName(7 / 2);

1┌─divide(7, 2)─┬─divide(-7, 2)─┬─divide(7, 2.)─┬─toTypeName(divide(7, 2))─┐
2│          3.5 │          -3.5 │           3.5 │ Float64                  │
3└──────────────┴───────────────┴───────────────┴──────────────────────────┘

1SET sql_compatible_division_for_integers = 1;
2
3SELECT 7 / 2, -7 / 2, 7 / 2.0, toTypeName(7 / 2);

1┌─divide(7, 2)─┬─divide(-7, 2)─┬─divide(7, 2.)─┬─toTypeName(divide(7, 2))─┐
2│            3 │            -3 │           3.5 │ UInt8                    │
3└──────────────┴───────────────┴───────────────┴──────────────────────────┘

1SET sql_compatible_division_for_integers = 1;
2
3SELECT 1 / 0;

1Received exception:
2Code: 153. DB::Exception: Division by zero: In scope SELECT divideExact(1, 0) AS `divide(1, 0)`. (ILLEGAL_DIVISION)

1SELECT count() AS repeat_address_sales_2024
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4  AND date < '2025-01-01'
5  AND (postcode1, postcode2, addr1, addr2) IN
6  (
7      SELECT postcode1, postcode2, addr1, addr2
8      FROM uk_price_paid
9      WHERE date < '2024-01-01'
10  )
11SETTINGS max_threads = 2,
12         max_memory_usage = '4Gi',
13         max_bytes_before_external_set = 0;

1┌─repeat_address_sales_2024─┐
2│                    558458 │
3└───────────────────────────┘
4
51 row in set. Elapsed: 5.777 sec. Processed 60.90 million rows, 966.34 MB (10.54 million rows/s., 167.27 MB/s.)
6Peak memory usage: 1.96 GiB.

1SELECT count() AS repeat_address_sales_2024
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4  AND date < '2025-01-01'
5  AND (postcode1, postcode2, addr1, addr2) IN
6  (
7      SELECT postcode1, postcode2, addr1, addr2
8      FROM uk_price_paid
9      WHERE date < '2024-01-01'
10  )
11SETTINGS max_threads = 2,
12         max_memory_usage = '512Mi',
13         max_bytes_before_external_set = 0;

1Elapsed: 0.978 sec. Processed 7.93 million rows, 122.04 MB (8.10 million rows/s., 124.75 MB/s.)
2
3Received exception:
4Code: 241. DB::Exception: Query memory limit exceeded: would use 537.51 MiB (attempt to allocate chunk of 63.96 MiB), maximum: 512.00 MiB: While executing CreatingSetsTransform. (MEMORY_LIMIT_EXCEEDED)

1SELECT count() AS repeat_address_sales_2024
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4  AND date < '2025-01-01'
5  AND (postcode1, postcode2, addr1, addr2) IN
6  (
7      SELECT postcode1, postcode2, addr1, addr2
8      FROM uk_price_paid
9      WHERE date < '2024-01-01'
10  )
11SETTINGS max_threads = 2,
12         max_memory_usage = '512Mi',
13         max_bytes_before_external_set = '128Mi';

1┌─repeat_address_sales_2024─┐
2│                    558458 │
3└───────────────────────────┘
4
51 row in set. Elapsed: 3.185 sec. Processed 60.90 million rows, 966.48 MB (19.12 million rows/s., 303.44 MB/s.)
6Peak memory usage: 203.11 MiB.

1SetsSpilledToDisk:          1
2ExternalSetWritePart:       4
3ExternalSetCompressedBytes: 470342352

1SELECT
2    county,
3    district,
4    town,
5    postcode1,
6    count() AS sales,
7    avg(price) AS average_price
8FROM uk_price_paid
9GROUP BY county, district, town, postcode1
10SETTINGS use_query_cache = 0
11FORMAT Null;

10 rows in set. Elapsed: 0.289 sec. Processed 30.45 million rows, 331.39 MB (105.20 million rows/s., 1.14 GB/s.)
2Peak memory usage: 170.67 MiB.
3
40 rows in set. Elapsed: 0.269 sec. Processed 30.45 million rows, 331.39 MB (113.29 million rows/s., 1.23 GB/s.)
5Peak memory usage: 171.81 MiB.
6
70 rows in set. Elapsed: 0.255 sec. Processed 30.45 million rows, 331.39 MB (119.19 million rows/s., 1.30 GB/s.)
8Peak memory usage: 177.98 MiB.

10 rows in set. Elapsed: 0.242 sec. Processed 30.45 million rows, 331.39 MB (125.90 million rows/s., 1.37 GB/s.)
2Peak memory usage: 128.49 MiB.
3
40 rows in set. Elapsed: 0.181 sec. Processed 30.45 million rows, 331.39 MB (168.49 million rows/s., 1.83 GB/s.)
5Peak memory usage: 124.16 MiB.
6
70 rows in set. Elapsed: 0.171 sec. Processed 30.45 million rows, 331.39 MB (178.37 million rows/s., 1.94 GB/s.)
8Peak memory usage: 127.35 MiB.

1CREATE TABLE uk_price_paid_by_date
2ENGINE = MergeTree
3ORDER BY date
4AS SELECT date, price FROM uk_price_paid;
5
6OPTIMIZE TABLE uk_price_paid_by_date FINAL;

1SELECT
2    date,
3    count() AS sales,
4    round(avg(price)) AS average_price
5FROM uk_price_paid_by_date
6GROUP BY date
7ORDER BY date DESC
8LIMIT 10
9SETTINGS
10    enable_group_by_top_k_dynamic_filtering = 0,
11    use_query_condition_cache = 0,
12    use_query_cache = 0;

1┌───────date─┬─sales─┬─average_price─┐
2│ 2025-07-31 │    26 │        300021 │
3│ 2025-07-30 │    39 │        316864 │
4│ 2025-07-29 │    92 │        344986 │
5│ 2025-07-28 │   220 │        317645 │
6│ 2025-07-27 │     4 │        242998 │
7│ 2025-07-25 │   926 │        316243 │
8│ 2025-07-24 │   388 │        358814 │
9│ 2025-07-23 │   437 │        347647 │
10│ 2025-07-22 │   373 │        332035 │
11│ 2025-07-21 │   662 │        337260 │
12└────────────┴───────┴───────────────┘

1SelectedRows:                     1728263
2SelectedBytes:                    10369578
3TopKGranulesSkippedByPrimaryKey:  3529

1CREATE TABLE uk_price_paid_columns_cache
2ENGINE = MergeTree
3ORDER BY (postcode1, postcode2, addr1, addr2)
4SETTINGS min_bytes_for_wide_part = 0,
5         min_rows_for_wide_part = 0
6AS SELECT postcode1, postcode2, addr1, addr2, price FROM uk_price_paid;

1SELECT count() AS house_sales
2FROM uk_price_paid_columns_cache
3WHERE addr1 LIKE '%HOUSE%'
4SETTINGS use_columns_cache = 0,
5         use_query_cache = 0,
6         use_query_condition_cache = 0,
7         log_queries = 1,
8         log_profile_events = 1,
9         log_comment = 'sorted-columns-cache:off-1';

1┌─house_sales─┐
2│      904871 │
3└─────────────┘

1SYSTEM FLUSH LOGS;
2
3SELECT
4    log_comment AS run,
5    query_duration_ms AS queryTime,
6    ProfileEvents['ColumnsCacheHits'] AS cacheHits,
7    ProfileEvents['ColumnsCacheMisses'] AS cacheMisses,
8    ProfileEvents['SelectedRows'] AS selectedRows
9FROM
10(
11    SELECT *
12    FROM system.query_log
13    WHERE type = 'QueryFinish'
14      AND startsWith(log_comment, 'sorted-columns-cache:')
15      AND log_comment != 'sorted-columns-cache:warmup'
16    ORDER BY query_start_time_microseconds DESC
17    LIMIT 6
18)
19ORDER BY query_start_time_microseconds
20FORMAT Markdown;

1SELECT
2    concat(a.first_name, ' ', a.last_name) AS actor,
3    top_titles.name AS title,
4    top_titles.year AS year,
5    round(top_titles.rank, 1) AS rating
6FROM actors AS a
7JOIN LATERAL
8(
9    SELECT m.id, m.name, m.year, m.rank
10    FROM roles AS r
11    INNER JOIN movies AS m ON r.movie_id = m.id
12    WHERE r.actor_id = a.id
13      AND m.rank IS NOT NULL
14    GROUP BY m.id, m.name, m.year, m.rank
15    ORDER BY m.rank DESC, m.year DESC, m.id
16    LIMIT 3
17) AS top_titles ON true
18WHERE (a.first_name, a.last_name) IN
19    (('Tom', 'Hanks'), ('Meryl', 'Streep'), ('Denzel', 'Washington'))
20ORDER BY actor, rating DESC, year DESC, top_titles.id
21SETTINGS allow_experimental_lateral_join = 1;

1┌─actor─────────────┬─title──────────────────┬─year─┬─rating─┐
2│ Denzel Washington │ Glory                  │ 1989 │    8.1 │
3│ Denzel Washington │ Much Ado About Nothing │ 1993 │    7.5 │
4│ Denzel Washington │ Antwone Fisher         │ 2002 │    7.4 │
5│ Meryl Streep      │ Manhattan              │ 1979 │    8.2 │
6│ Meryl Streep      │ Deer Hunter, The       │ 1978 │    8.1 │
7│ Meryl Streep      │ Adaptation.            │ 2002 │    7.9 │
8│ Tom Hanks         │ Saving Private Ryan    │ 1998 │    8.3 │
9│ Tom Hanks         │ Green Mile, The        │ 1999 │    8.1 │
10│ Tom Hanks         │ Toy Story 2            │ 1999 │    8.1 │
11└───────────────────┴────────────────────────┴──────┴────────┘

1CREATE TABLE page_views
2(
3    path String,
4    views UInt64
5);
6
7INSERT INTO page_views VALUES
8    ('/docs/getting-started', 1200),
9    ('/docs/sql-reference/functions', 850),
10    ('/blog/clickhouse-release-26-10', 2400),
11    ('/pricing', 600),
12    ('/about', 300);

1CREATE TABLE page_categories
2(
3    pattern String,
4    category String
5);
6
7INSERT INTO page_categories VALUES
8    ('/docs/%', 'Documentation'),
9    ('/blog/%', 'Blog'),
10    ('/pricing', 'Pricing');

1SELECT p.path, p.views, c.category
2FROM page_views AS p
3LEFT JOIN page_categories AS c ON p.path LIKE c.pattern
4ORDER BY p.views DESC;

1┌─path───────────────────────────┬─views─┬─category──────┐
2│ /blog/clickhouse-release-26-10 │  2400 │ Blog          │
3│ /docs/getting-started          │  1200 │ Documentation │
4│ /docs/sql-reference/functions  │   850 │ Documentation │
5│ /pricing                       │   600 │ Pricing       │
6│ /about                         │   300 │               │
7└────────────────────────────────┴───────┴───────────────┘

1ls -l **/*.parquet | 
2./clickhouse --input-format Freeform --query "DESCRIBE TABLE table"

1c0	String
2c1	Int64
3c2	String
4c3	String
5c4	Int64
6c5	Int64
7c6	String
8c7	Int64
9c8	Int64
10c9	String

1ls -l **/*.parquet | 
2./clickhouse --input-format Freeform --query "FROM table FORMAT Vertical"

1Row 1:
2──────
3c0: -rw-r--r--@
4c1: 1
5c2: markhneedham
6c3: staff
7c4: 2048074919 -- 2.05 billion
8c5: 30
9c6: Oct
10c7: 16
11c8: 23
12c9: benchmarks/amazon-columns-cache/data/amazon_reviews_2010.snappy.parquet
13
14Row 2:
15──────
16c0: -rw-r--r--@
17c1: 1
18c2: markhneedham
19c3: staff
20c4: 3013406813 -- 3.01 billion
21c5: 30
22c6: Oct
23c7: 16
24c8: 27
25c9: benchmarks/amazon-columns-cache/data/amazon_reviews_2011.snappy.parquet

1ls -l **/*.parquet |
2./clickhouse --input-format Freeform --query "
3    SELECT
4        c9 AS file,
5        formatReadableSize(c4) AS size,
6        parseDateTimeBestEffort(
7            format('{} {} {} {}:{}', c5, c6, toYear(today()), c7, c8)
8        ) AS modified
9    FROM table
10    ORDER BY c4 DESC
11    FORMAT Vertical"

1Row 1:
2──────
3file:     benchmarks/amazon-columns-cache/data/amazon_reviews_2011.snappy.parquet
4size:     2.81 GiB
5modified: 2026-10-30 16:27:00
6
7Row 2:
8──────
9file:     benchmarks/amazon-columns-cache/data/amazon_reviews_2010.snappy.parquet
10size:     1.91 GiB
11modified: 2026-10-30 16:23:00

1SELECT
2    year,
3    toInt64(count()) AS films,
4    round(avg(rank), 2) AS average_rating
5FROM movies
6WHERE year BETWEEN 1990 AND 1999
7GROUP BY year
8ORDER BY year
9INTO OUTFILE 'films.sqlite'
10SETTINGS output_format_sqlite_table_name = 'films_by_year';

1sqlite3 -header -column films.sqlite \
2  'SELECT * FROM films_by_year ORDER BY year LIMIT 5;'

1year  films  average_rating
2----  -----  --------------
31990  6098   5.33
41991  6068   5.69
51992  6580   5.53
61993  6900   5.55
71994  7502   5.43

1SELECT *
2FROM file('films.sqlite')
3ORDER BY year
4LIMIT 5;

1┌─year─┬─films─┬─average_rating─┐
2│ 1990 │  6098 │           5.33 │
3│ 1991 │  6068 │           5.69 │
4│ 1992 │  6580 │           5.53 │
5│ 1993 │  6900 │           5.55 │
6│ 1994 │  7502 │           5.43 │
7└──────┴───────┴────────────────┘

1CREATE TABLE amazon_reviews
2ENGINE = MergeTree
3ORDER BY tuple()
4AS SELECT *
5FROM file('benchmarks/amazon-columns-cache/data/amazon_reviews_{2010,2011}.snappy.parquet');

1SELECT count() AS matching_reviews
2FROM amazon_reviews
3WHERE review_body LIKE '%disappointed%'
4SETTINGS apply_string_filters_during_scan = 0,
5         use_columns_cache = 0,
6         use_query_cache = 0,
7         use_query_condition_cache = 0;

1┌─matching_reviews─┐
2│           293115 │
3└──────────────────┘