Asking ClickHouse for more

In 2019, getting three literal rows meant UNION ALL of single-row SELECTs or building arrays and unfolding them with ARRAY JOIN. PostgreSQL has VALUES (1, 'one'), (2, 'two') as a table. I opened #5984 asking for that.

The maintainer’s reply the next day changed the shape: not new top-level syntax but a table function taking the structure as a string, like file and url: VALUES('a UInt64, s String', (1, 'one'), ...). A contributor asked why not infer types from the first row; the objection was that (1, 'one'), (256, 'two') would give UInt8 and then not fit. The proposed fallback was the common supertype across rows, as array literals do. I preferred an optional TABLE name(types...) prefix; the verdict was that it was nicer but adds special syntax, and could still be implemented later.

PR #6209 (“VALUES list”, dimarub2000) was merged on 2019-08-01, three weeks after the issue. TableFunctionValues.cpp is present in the 19.14 release tag and in none of the 19.13 tags I checked, so 19.14 is where it first shipped.

Checked on 26.4, the structure string is optional; without it columns are named c1, c2 and types are inferred as the common type:

SELECT *, toTypeName(c1), toTypeName(c2) FROM VALUES((1, 'one'), (256, 'two'));
-- c1 is UInt16 for both rows, c2 is String

The PostgreSQL form works as a subquery source (SELECT * FROM (VALUES (1, 'a'), (2, 'b')) returns two rows on 26.4), but a standalone VALUES (...) statement is still an error. For inline data from text, see the format table function and generating test data.

Permalink · GitHub #5984

ClickHouse has substring for strings but nothing for slicing digits out of a number. The third and fourth digits of 1234567890 meant toUInt32(substring(toString(n), 3, 2)), which is tolerable once and miserable when an identifier carries meaning by position and you need several slices per query. I asked in 2020 for digits(n, offset, length), counting from the left, and mentioned a reversed variant.

The first reply called it a very special case; after I described the identifier use case a maintainer said “That’s reasonable” and suggested division and remainder by a power of ten instead of a trip through strings. The issue was labelled easy task and sat for six years. In June 2026 a first-time contributor asked to take it, and PR #109012 merged on 2026-07-13, shipping in 26.7. The changelog entry: digits(n, offset[, length]) returns the number starting at the 1-indexed offset with length digits, or everything to the end if length is omitted. I found no reversed variant.

26.4 predates it, so the function is unknown there. The two spellings that work without it:

SELECT toUInt32(substring(toString(1234567890), 3, 2)) AS via_string,
       intDiv(1234567890, pow(10, 6)) % 100 AS via_arithmetic;

34	34

The arithmetic form needs you to know the digit count, and that bookkeeping is the reason to want a function.

Permalink · GitHub #9077

input_format_allow_errors_num lets a CSV or TSV load tolerate up to N bad rows, but ClickHouse then skipped them silently: you did not know how many were dropped, let alone which. Finding them meant rerunning with a limit of zero and fixing one row per trip. I asked in 2020 for the rejected rows to be reported to stdout or stderr.

A maintainer replied the same day with a better design: a fallback table with one String column, plus a rejected-row counter in ProfileEvents. The idea then sat for years. What shipped is neither: PR #40516, “Record errors while reading text formats (CSV, TSV)”, merged 2022-09-14 and released in 22.9, adds input_format_record_errors_file_path, which writes every rejected row to a file with its row number and the reason; errors_output_format picks the file format (CSV by default). The issue itself stayed open until 2026-06-12, when someone noticed it had been implemented all along.

On 26.4, with 2,oops and four,80 as the bad rows:

SELECT * FROM file('events.csv', 'CSV', 'user_id UInt64, duration_ms UInt32')
SETTINGS input_format_allow_errors_num = 10,
         input_format_record_errors_file_path = 'rejected.csv';

The load returns the good rows, and rejected.csv holds a timestamp, database and table when known, the row offset, the parser’s reason and the raw text. The ProfileEvents counter and any stdout option I did not find.

Permalink · GitHub #9157

EmbeddedRocksDB overwrites a row when you insert an existing key, which makes “last value per key” a one-line point read, fed by a materialized view. In 2021 the engine accepted only one primary key column, so a real key like (sensor_id, metric) failed with BAD_ARGUMENTS. A user offered a workaround, a single Tuple column built by DEFAULT, which I called clever but still a workaround; a later commenter noted dictionaries refuse a Tuple attribute, so it cannot back a complex-key dictionary.

The issue carried the easy task label. PR #33917 by usurai was opened on 2022-01-22, five weeks after my request, and merged on 2026-01-09. The 26.1 changelog lists it: “Support multiple columns as primary key in EmbeddedRocksDB.” Key columns are serialised in creation order into the RocksDB key.

On 26.4 this works:

CREATE TABLE last_point (sensor_id UInt32, metric String, ts DateTime, value Float64)
ENGINE = EmbeddedRocksDB PRIMARY KEY (sensor_id, metric);

Two cautions: “last” means last written, not latest ts, so late data overwrites newer data; and per the PR design, a WHERE that does not fix every key column scans the whole table.

Permalink · GitHub #32819

Not every issue I filed deserves its own post. Four small ones, most substantial first.

• A tuple IN that blamed the wrong thing (#34810). (a, b) IN (SELECT (c, d) ...) worked when the tuple types matched exactly and failed when integer widths differed, with “Number of columns in section IN doesn’t match. 2 at left, 1 at right”, which was false. The first reply took the message at its word and suggested rewriting the subquery; I asked why the other form worked, and the issue was reopened. It was a real defect, fixed by vdimir in PR #34836 (merged 2022-02-25, in 22.3) with the issue’s example as a regression test. The fix is better than the message I asked for: the query just evaluates. Confirmed on 26.4 with Tuple(UInt64, DateTime) against Tuple(UInt32, DateTime).
• SHOW CREATE TABLE on a table function (#34967). Declined: remote(...) builds a temporary Distributed table with no CREATE of its own, and can read many differently created tables. Read system.tables through remote instead. CREATE TABLE t AS numbers(5) then SHOW CREATE TABLE t works and keeps AS numbers(5); SHOW CREATE TABLE numbers(5) is a syntax error on 26.4. A reason I accept.
• A documentation suggestion (#1127, 2017). I proposed community-editable docs. A year later a maintainer pointed out the docs already lived in the repository, and every page has an edit link. I was glad to be wrong.
• Pasting multi-line SQL (#13967, 2020). In 20.6 each pasted line ran as its own query. The answer was -n (--multiquery) without -m. I closed it myself, and have not retested the default on a current interactive client.

A fifth issue, #20546, was a plain question about deleting a row in EmbeddedRocksDB; it is answered in passing in the EmbeddedRocksDB post.

Permalink · GitHub #34810

Not every request is a design argument. Three small ones, in the order I filed them.

• indices vs indexes (#37288). Both plurals are correct English and ClickHouse uses both: use_skip_indexes, but force_data_skipping_indices and system.data_skipping_indices. Type the wrong one and you get Unknown setting 'force_data_skipping_indexes': Maybe you meant [...] (checked on 26.4). A maintainer’s answer was to keep the old names and add aliases. Contributor msohail22 did that in PR #122565, merged 2026-09-29: allow_suspicious_indexes, ignore_data_skipping_indexes, force_data_skipping_indexes and secondary_indexes_enable_bulk_filtering now work, with the old names kept. It landed in the 26.10 development line, so it is not in 26.4.
• SETTINGS printed twice (#40590). In August 2022 the client echoed a query with both FORMAT and SETTINGS and printed the SETTINGS line twice. The reply came the same day: already fixed, by PR #38900 (“Avoid printing SETTINGS twice when formatting a query”, merged 2022-08-23, release 22.9). My build predated the fix. Closed.
• Schema inference for format (#64840). I asked the format function to infer a schema, then realised I had confused the scalar string formatter with the format table function, which already infers one. I closed it myself. The real snag in the same experiment, a null in the first row failing with ONLY_NULLS_WHILE_READING_SCHEMA on the 22.8 server I used, no longer happens: on 26.4 the column comes out as Nullable(String). I do not know which release changed that. See the format table function.

DESCRIBE format(JSONEachRow, '{"a":1,"b":null,"c":"x"}');

a   Nullable(Int64)
b   Nullable(String)
c   Nullable(String)

Permalink · GitHub #37288

Client code has to behave when ClickHouse says no: retry on a transient Keeper failure, page on TOO_MANY_PARTS, back off on MEMORY_LIMIT_EXCEEDED. Those branches are only tested if you can make the server fail that way, and for the interesting codes that means breaking Keeper or inserting until parts explode. throwIf only ever raised FUNCTION_THROW_IF_VALUE_IS_NON_ZERO (code 395). I asked for a function that throws the code I name.

On the issue, one maintainer asked whether a mock server or a real scenario could do it; I answered that building a real scenario for each error is impractical. Alexey Milovidov agreed with the need but suggested extending throwIf with an extra argument rather than adding a function, and required it to be off by default behind a setting: “we don’t want to have fake errors in normal environments.”

That shipped as PR #40319 (“Allow custom error code in throwIf”, rschu1ze), merged 2022-08-19, eighteen days after the issue, in release 22.9. Checked on 26.4: the third argument must be an Int8, Int16 or Int32 (so a literal needs toInt32()), and the setting allow_custom_error_code_in_throwif must be on.

SELECT throwIf(1, 'simulated', toInt32(252)) SETTINGS allow_custom_error_code_in_throwif = 1;
-- Code: 252. DB::Exception: Value passed to 'throwIf' function is non-zero ... (TOO_MANY_PARTS)

Without the setting, the three-argument form is rejected as a wrong argument count. For names to pick from, see the error codes snippet.

Permalink · GitHub #39793

SQL asks for the column list before the table, so an editor cannot complete column names while you type them. I was building a SQL editor with autocomplete and kept hitting exactly that wall. I asked for FROM events SELECT *: same semantics, same planner, only the clause order differs.

The thread had one sceptic who preferred a separate query language. Alexey Milovidov disagreed: ClickHouse may be the only DBMS you can give a wide user base raw SQL access to, thanks to RBAC, row-level security, quotas and resource metering, so SQL itself should be made friendlier rather than hidden behind another language. A third participant asked for tests on the awkward cases (subqueries, CTEs, joins, unions).

Contributor evillique implemented it in PR #41095 (“Add ‘FROM table SELECT column’ syntax”), merged 2022-12-02, which closed the issue the same day. It first shipped in 22.12, under three months after I filed it.

Checked on 26.4, the clauses after the select list stay where they were:

FROM events
SELECT event_type, count() AS n
GROUP BY event_type
ORDER BY n DESC;

WHERE goes after the SELECT list, not straight after the table name, and a bare FROM events without SELECT is an error, so this is not a PRQL-style pipe. It composes with subqueries. It is also a small step towards a SQL that reads in the order you think.

Permalink · GitHub #41018

DETACH TABLE takes a table out of service without deleting anything, and the table then vanishes from system.tables and from every inventory query built on it. Forget to attach it again on one node and nothing complains until a query fails with UNKNOWN_TABLE. Parts already had system.detached_parts; I asked for the same for tables: a system.detached_tables listing what is missing.

A community member answered the same day by pointing at an existing PR, #65400 by k-morozov, which merged on 2024-07-17 and shipped in 24.7. The issue stayed open because nobody linked the two; I noticed the merge in April 2025 and closed it myself.

The columns differ from my sketch (database, table, uuid, metadata_path, is_permanently). On 26.4 it needs an Atomic database; the default clickhouse local database cannot list detached tables.

CREATE DATABASE d ENGINE = Atomic;
CREATE TABLE d.t (x UInt8) ENGINE = MergeTree ORDER BY x;
CREATE TABLE d.u (x UInt8) ENGINE = MergeTree ORDER BY x;
DETACH TABLE d.t;
DETACH TABLE d.u PERMANENTLY;
SELECT database, table, is_permanently FROM system.detached_tables WHERE database = 'd' ORDER BY table;

d   t   0
d   u   1

A plain detach comes back after a restart; a permanent one does not. See also dropping detached parts and finding orphan tables.

Permalink · GitHub #65767

A skipping index is a bet on how values spread across granules: if every granule holds every value, a set index skips nothing and still costs insert time. You normally find out after building it, which on a large table is a long mutation. I asked for a _granule virtual column so one could measure first, for example the 99th percentile of distinct values per granule, and size max_size of a set index from it.

The issue sat for nine months and closed on 2025-06-26. The fix was not written for me: PR #82341 by amosbird added _part_granule_offset, the zero-based granule index within a part, and shipped in 25.7. It is per part, so group by _part too:

CREATE TABLE e (a UInt32, country String, url String) ENGINE = MergeTree ORDER BY a
SETTINGS index_granularity = 1000;
INSERT INTO e SELECT number, ['TR','DE','US'][number % 3 + 1], concat('/p/', toString(number % 300))
FROM numbers(100000);

SELECT count() AS granules, quantile(0.99)(c) AS p99_countries, quantile(0.99)(u) AS p99_urls
FROM (SELECT _part, _part_granule_offset AS g, uniq(country) AS c, uniq(url) AS u
      FROM e GROUP BY _part, g);

100   3   300

Every granule holds all 3 countries and up to 300 URLs, so on this data a set index would be exact but skip nothing. Use intDiv(_part_granule_offset, 10) to simulate GRANULARITY 10. See Data skipping indexes and EXPLAIN which indexes a query uses.

Permalink · GitHub #69924

Comparing tables of the same shape means a UNION ALL with the table name typed as a string literal in each branch, and a copy-pasted branch with a stale label gives a report that lies. I asked for a _table_name virtual column so the label comes from the table itself.

A community member called it a duplicate the same day, and I closed it the next. The original is #60300, opened by alexey-milovidov in February 2024: make _table work on every table, not just Merge tables. PR #63665 by wudidapaopao implemented it, merged 2025-07-29, released in 25.8.

So the feature exists under an older, better-fitting name (_table, next to _database and _part), not _table_name. Virtual columns are not in SELECT *, and next to an aggregate you must group by it:

CREATE TABLE a (x UInt8) ENGINE = MergeTree ORDER BY x;
CREATE TABLE b (x UInt8) ENGINE = MergeTree ORDER BY x;
INSERT INTO a VALUES (1),(2);
INSERT INTO b VALUES (3);

SELECT _table AS t, count() AS cnt FROM a GROUP BY _table
UNION ALL
SELECT _table, count() FROM b GROUP BY _table;

a   2
b   1

(Row order of a UNION ALL is not guaranteed.)

The lesson is about the tracker: search before filing, and a duplicate means someone on the other side already wanted it.

Permalink · GitHub #73806

system.mutations has create_time, parts_to_do, is_done and latest_fail_time, but not when a mutation ended, so “how long did last night’s mutations take?” had no answer in SQL. I asked for a finish_time DateTime column.

It was labelled easy task, and two PRs tried it. The first, #105255, closed unmerged after a contributor, ZelvaMan, asked the deciding question: must it survive a restart? Persisting means a file-format or Keeper change with upgrade and cleanup questions. The second, #113474 by nikitamikhaylov, reworked it to add no persistent state, merged 2026-08-20 and closed the issue; it shipped in 26.8. Semantics:

• 0 means not finished, or completion time unknown (entries loaded at startup, parts removed by DROP PARTITION).
• Non-replicated MergeTree stamps the time in memory when completion is observed.
• For ReplicatedMergeTree the value is per replica: the modification time of the replica’s mutation_pointer znode. For a cluster-wide time use max(finish_time) per mutation_id.

The column is absent on 26.4, the binary I have here, and present on 26.8 (not run here, so treat the query as intended usage):

SELECT mutation_id, command,
       dateDiff('second', create_time, finish_time) AS seconds
FROM system.mutations
WHERE is_done AND finish_time != toDateTime(0)
ORDER BY seconds DESC LIMIT 10;

The finish_time != 0 filter matters: after a restart old mutations honestly report unknown. See watch mutation progress.

Permalink · GitHub #89216

clickhouse-client read its configuration from ~/.clickhouse-client/config.xml, a dotfolder in the 1990s style. Newer tools follow the XDG Base Directory specification and keep configuration under ~/.config/<tool>/ or $XDG_CONFIG_HOME, which matters if you version-control or sync your config. I asked for the XDG location first, with the legacy path as fallback, and proposed ~/.config/clickhouse-client/config.xml.

It went fast. The maintainer answered “Good proposal, I’d like it!” the same night, the issue was labelled easy task, and a contributor’s PR #90306 merged on 2025-11-19, eight days after I filed it. It shipped in 25.12 with a different directory than I proposed: clickhouse, not clickhouse-client. The search order is ./.clickhouse-client/, then $XDG_CONFIG_HOME/clickhouse/ (default ~/.config/clickhouse/), then the legacy ~/.clickhouse-client/, then /etc/clickhouse-client/.

The path I proposed is ignored, which I confirmed on 26.4 with an unresolvable host as a probe:

H=$(mktemp -d); mkdir -p $H/.config/clickhouse
echo '<config><host>xdg-host.invalid</host></config>' > $H/.config/clickhouse/config.xml
HOME=$H clickhouse client -q "select 1"

Code: 198. DB::NetException: Not found address of host: xdg-host.invalid. (DNS_ERROR)

The same file under .config/clickhouse-client/ gives the default “Connection refused (localhost:9000)”, meaning it was never read.

Permalink · GitHub #89882

This one was about a dataset, not a pain. Primes are a famous sequence that needs no explanation in a talk or a benchmark, and ClickHouse already had numbers() for the integers. Computing primes in SQL is an ugly query (mine was a recursive CTE with trial division, which also hit the recursion-depth limit I filed as #90838). I asked for a system.primes table holding the first million primes, with an index column.

The maintainers did better. PR #92776 by nihalzp, “New table function primes and system.primes”, merged on 2026-01-27 and shipped in 26.2. Instead of a stored, bounded table it is virtual like numbers: primes are generated on demand, in ascending order, with no upper limit, plus a primes() table function taking a limit or an offset and a limit. I lost the index column and gained not storing anything. If you need the index, row_number() OVER () over a limited selection gives it back.

On 26.4:

SELECT prime FROM primes(3, 4);                                  -- 7, 11, 13, 17
SELECT prime FROM system.primes WHERE prime > toUInt64(1e15) LIMIT 1;

1000000000000037

My original request is one OFFSET away: SELECT prime FROM primes() LIMIT 1 OFFSET 999999 returns 15485863, the millionth prime.

Permalink · GitHub #90839

Tables and plain views had CREATE OR REPLACE; materialized views did not. Deployment scripts carried an existence check, a DROP and a CREATE, and that sequence is not atomic: between the drop and the create the view does not exist, so inserts arriving in the gap are never transformed into the target table. I asked for CREATE OR REPLACE MATERIALIZED VIEW [db.]name [ON CLUSTER c] [TO t] AS SELECT ... on grounds of consistency and scriptability. An earlier request, #5709, had been closed because its motivation was transactions, which were addressed by giving materialized views ALTER; mine was about scripts.

It took about four months. PR #100539 by il9ue merged on 2026-05-19 and shipped in 26.5. Its description showed the situation was odd: the statement was not unsupported, it was silently misparsed as an ordinary view, because the parser refused to consume MATERIALIZED after OR REPLACE. The machinery underneath already did the right thing: create a temporary view (and inner table when there is no TO), swap atomically with RENAME EXCHANGE, drop the old one.

Things worth knowing from the PR:

• The access check now requires CREATE MATERIALIZED VIEW, not CREATE VIEW, closing a privilege corner.
• Replacing a view that has an inner table creates a new, empty inner table; the old data goes with it.
• POPULATE re-populates on every replace.
• With a TO table, the replace swaps the view and leaves the target alone, which is the safe way to use it.
• OR REPLACE combined with IF NOT EXISTS is a syntax error.

26.4 predates the release and stops at the VIEW keyword:

CREATE OR REPLACE MATERIALIZED VIEW mv TO dst AS SELECT x + 1 AS x FROM src;

Code: 62. DB::Exception: Syntax error: failed at position 32 (VIEW) ...

For the broader picture see materialized views basics and adding a materialized view to an existing table.

Permalink · GitHub #94898

The client can echo your statement back after normalizing it with the server’s formatter: parentheses around set-operation arms, INTERVAL 1 MINUTE rewritten as toIntervalMinute(1). That echo is useful, but it appears directly under what you typed, in the same font, and in a scrollback of long queries the two blur into one block of SQL. I asked for a visible separator line between them and said the exact shape did not matter.

It took five months. A contributor’s PR #108888, “Add separator between user query and formatted query”, merged on 2026-07-02 and is in 26.7. The design differs from what I sketched, for the better: there is no fixed separator. The client and clickhouse-local got an option you set yourself:

clickhouse client --echo-formatted --echo-query-separator='-- ---- --'

Per the PR description, the string is printed before the formatted echo, it is empty by default so existing output does not change, and it only applies with --echo-formatted.

I could not run it here, since 26.4 predates the release; the behavior above comes from the PR. An empty default plus an option beats one separator for everybody, because people have strong feelings about what a separator should look like.

Permalink · GitHub #94899

CREATE DATABASE lite ENGINE = SQLite('/data/dim.db') failed in early 2022 if the file did not exist, so a setup script had to create it with another tool. I asked for ClickHouse to create it. A developer asked the fair question of what an empty file is for, since the engine could not create tables. My use case: a few small dimension tables that change often and need real UPDATE and DELETE, with a file as the only infrastructure. Someone suggested EmbeddedRocksDB; I replied it has no relational integrity.

I closed the issue myself that day, thinking an empty file made no sense without table creation; it was reopened. PR #34907, “Allow to open empty sqlite db if it does not exist”, by kssenii, merged on 2022-02-28 and shipped in 22.3.

The file part works. The part I needed does not. On 26.4:

CREATE TABLE lite.countries (code String, name String) ENGINE = SQLite('/data/dim.db', 'countries');
-- Code: 48. There is no CREATE TABLE query for DatabaseSQLite. (NOT_IMPLEMENTED)

Tables created with the sqlite3 tool are readable and writable from ClickHouse, but defining them is still a manual step, hence “partially implemented”.

Permalink · GitHub #33367

I expected every callable function to be listed in system.functions, the catalogue editors, linters and doc generators read. I hit a counter-example writing a query with date_add and timestamp_sub: both work when called and were absent from the table. I asked for it to be made complete.

A maintainer first explained that date_add and timestamp_add are aliases of plus, and timestamp_sub of minus; I answered that documented names should still be discoverable. Then rschu1ze found a whole category of the same problem: in the parser’s getFunctionLayer() there are functions and aliases that never reach the function registry. PR #48489 (“Register datediff and trim aliases in system.functions”), merged 2023-04-11 and shipped in 23.4, registered the ones that could be, and closed the issue.

He was explicit that date_add, timestamp_add and their relatives were not among them: they exist only in the parser, with no function behind them to alias, and a separate factory for such names “would be quite ugly”. Status: partially implemented.

Checked on 26.4, the registered aliases show up with their targets (date_diff to dateDiff, trim to trimBoth, ltrim to trimLeft), while a search for date_add, timestamp_add or timestamp_sub returns no rows. The parser rewrites them before the function layer sees them:

EXPLAIN SYNTAX SELECT date_add(DAY, 1, toDate('2024-01-01'));
-- SELECT plus(toDate('2024-01-01'), toIntervalDay(1)) FROM system.one

Any tool that trusts system.functions will mark them unknown. The same gap exists for table engines.

Permalink · GitHub #48260

I filed two naming requests the same day. #34426 asked to rename MergeTree’s PRIMARY KEY (to PRIMARY INDEX, or SORT INDEX), because it is not a key: it identifies nothing and enforces nothing, it is a sparse index over the sorted data. #34427 asked to rename or alias the table-level ORDER BY to SORT BY, because it controls on-disk layout and people read it as a promise about query result order.

#34427 got an answer within hours: a maintainer asked whether it was too late to change, noted Vertica uses ORDER BY the same way, and closed it the next morning with no change. #34426 never got a maintainer reply; it is open, labelled usability, comp-sql-syntax and external, with one comment, mine.

The point is easy to show:

CREATE TABLE a (k UInt32, j UInt32, v String) ENGINE = MergeTree PRIMARY KEY k ORDER BY (k, j);
INSERT INTO a VALUES (1, 1, 'x'), (1, 1, 'y');
SELECT count() FROM a WHERE k = 1;   -- 2

Neither SORT BY nor PRIMARY INDEX parses on 26.4. The one-sentence version: the primary key is the prefix of the sorting key that gets indexed, and neither is unique. See ORDER BY, PARTITION BY and how queries skip data.

Permalink · GitHub #34426

I had file paths and wanted the directory and the file name. ClickHouse has position(haystack, needle) for the first match but nothing for the last one or for all of them, so I asked for positions(haystack, needle) returning every index as an array, to take the last element.

I was asking the wrong question. Replies showed splitByChar('/', path)[-1], a regular expression with extract, and the existing basename(path), which is the plain answer. One user suggested a family of path-processing functions instead. I said a more intuitive positions would still be nice. The issue is open, labelled feature, comp-regular-function and external, with no pull request.

On 26.4 positions still does not exist (UNKNOWN_FUNCTION), and there is no dirname. multiSearchAllPositions reports the first position of each of several needles, not every position of one. To get all positions today, build the array:

SELECT arrayFilter(i -> substring('a/b/c', i, 1) = '/', range(1, length('a/b/c') + 1));   -- [2,4]

That is one lambda call per character, which a native function would avoid. For paths, use basename. See lesser-known ClickHouse functions and features.

Permalink · GitHub #35448

To spot stuck ingestion across hundreds of tables I needed the time each table last received data. max(event_time) per table is the wrong tool: it scans data, some tables have no time column, and it measures time in the data, not arrival time. I asked for last_insert_time in system.tables or system.parts.

The maintainer explained why it is hard: parts do not record insert times, and a trigger-style approach breaks with replication. A workaround from a user, system.part_log filtered on NewPart and DownloadPart, was rejected as incorrect, since a download (replica repair, fetched merged parts, network catch-up) is not an INSERT. I narrowed it to NewPart events, queried through a Distributed table over system.part_log so every replica is covered:

SELECT database, table, max(event_time)
FROM part_log_distributed
WHERE event_type = 'NewPart' AND event_date > now() - INTERVAL 5 DAY
GROUP BY database, table;

In April 2023 the maintainer replied that the trick “could suit as a workaround” and closed the issue. No column was added, so I treat the request as unmet.

The workaround has costs: part_log must be enabled, retention is limited, and a table silent for longer than the log keeps will not appear at all, which is exactly the case you are hunting. See inserts in the last 15 minutes and how often is a table inserted into.

Permalink · GitHub #36013

I asked for Python-style chained comparisons, so that a half-open range reads as a <= x < b and means a <= x AND x < b with x evaluated once. The motivation is the time-range filter: BETWEEN is closed on both ends, so it counts the row stamped exactly at the window’s end twice (once in each adjacent window), and everyone ends up writing the column name twice with >= and <. My example was five conditions across three columns collapsing into one chain.

The issue got the feature label, was assigned to a contributor in June 2022, and then went quiet. In July 2026 the repository’s automation unassigned him after more than a year of inactivity. No PR is linked; the issue is open.

What makes it more than a nicety is that ClickHouse accepts the chained form and does not do what Python does. It parses a <= x < b as (a <= x) < b, compares a 0/1 result with b, and carries on. Checked on 26.4:

SELECT 3 <= 2 < 3 AS chained, (3 <= 2) AND (2 < 3) AS python_meaning;
-- chained = 1, python_meaning = 0
SELECT 5 > 3 > 1;   -- 0, Python says True

On numeric columns this silently returns wrong rows: WHERE 1 <= duration_ms < 3 keeps every row, because both 0 and 1 are less than 3. With a DateTime and string bounds the type check happens to stop you with a TYPE_MISMATCH. Whether the fix is real chaining or just rejecting a comparison of a comparison, the status quo is a trap. Until then, write both conditions out. More small tools of this kind are in the lesser-known functions tutorial.

Permalink · GitHub #37576

The design argument is in What ClickHouse UDFs should be able to do. This page is only the history and status.

I asked for UDFs to carry an optional scope, a database in which they are callable without a prefix, so that grants on a database could cover its functions. Unscoped functions would stay global, so nothing existing would break. The privileges around functions today cover only creating and dropping them (CREATE FUNCTION, DROP FUNCTION), not calling them.

One maintainer replied on the day I opened it: grants for UDFs and views make little sense because ClickHouse has no object owners (definers). That is a real objection; my view is that it argues for designing the privilege model carefully, not for having none. It was the only maintainer reply. The issue carries the labels feature, comp-udf and external, has no assignee and no linked PR, and is open.

Status on 26.4: unchanged. A freshly created function is callable by anyone.

CREATE FUNCTION f AS (x) -> x * 2;
SELECT f(21);   -- 42

system.functions shows a UDF’s origin (SQLUserDefined or ExecutableUserDefined) and create_query, but no scope or access list. For how UDFs work today, see User-defined functions and executable scripts.

Permalink · GitHub #40982

Maintenance on a partitioned table is rarely “do this to partition 202401”; it is “do it to every partition older than three months” or “every partition with more than one part”. OPTIMIZE TABLE and the partition commands only take literal partitions, so you query system.parts to generate statements, paste them into a script and run that, once per node. KILL QUERY already has a WHERE. I asked for the same on OPTIMIZE, for example OPTIMIZE TABLE metrics WHERE partition_id < '202404', and said I did not care about the spelling.

The only comment, from a community member, generalised it better than I had: the same condition could drive DROP PARTITION and ATTACH PARTITION FROM. His caveat was atomicity, since a command that picks its own targets can act on a different set than the one it looked at. For idempotent OPTIMIZE that does not matter; for DROP it is a real design question. There has been no maintainer reply and no PR. Labels are feature and comp-mergetree; the issue is open.

On 26.4 a WHERE after the table name is a syntax error at parse time (Expected one of: ... ON, PARTITION, DRY RUN, FINAL, FORCE, DEDUPLICATE, CLEANUP ...). The workaround is still the generator:

SELECT format('OPTIMIZE TABLE {}.{} PARTITION ID \'{}\' FINAL;', database, table, partition_id)
FROM system.parts
WHERE database = currentDatabase() AND table = 'metrics' AND active AND partition_id < '202404'
GROUP BY database, table, partition_id
HAVING count() > 1
ORDER BY partition_id;

For a single partition see the optimize one partition snippet.

Permalink · GitHub #44082

system.functions now carries a description, syntax, arguments and examples per function, but system.table_engines is only capability flags. I wanted the syntax and parameters of each engine available from the server you are connected to, so that a client can complete an ENGINE = clause or offer console help without scraping the website. I opened the issue with “why not introduce table engines?”, not realising the table already existed, and sketched name, family, syntax and a parameters map.

The first reply, the same day, corrected me and gave the answer: keep the existing table and add a field with short documentation. That was the whole discussion. There is no PR; the issue has the labels feature, comp-system-table and external, and is open.

On 26.4 nothing has changed. The table has 74 engines and only these columns:

DESCRIBE system.table_engines;
-- name, supports_settings, supports_skipping_indices, supports_projections, supports_sort_order,
-- supports_ttl, supports_replication, supports_deduplication, supports_parallel_insert

The flags are still useful for questions like which engines accept a skipping index:

SELECT name FROM system.table_engines WHERE supports_skipping_indices ORDER BY name;

The request is small and waiting for someone to write the docstrings. The related gap in system.functions is the other side of the same completeness question.

Permalink · GitHub #50100

A materialized view created without TO gets a hidden inner table named .inner_id.<uuid>, and the data lives there. On a shared cluster, advice to “always use TO” does not stop anyone, so I asked for a setting, allow_implicit_materialized_view_creation, default 1, that an administrator could set to 0 in a profile to make CREATE MATERIALIZED VIEW without TO an error.

CREATE TABLE src (x UInt8) ENGINE = MergeTree ORDER BY x;
CREATE MATERIALIZED VIEW mv ENGINE = MergeTree ORDER BY x AS SELECT x FROM src;
SELECT name FROM system.tables WHERE database = currentDatabase() ORDER BY name;

.inner_id.14ddd52d-5250-424b-9a44-b629cc87113f
mv
src

The issue has two community replies and no maintainer response; it is open, labelled feature, comp-materialized-view and external, with no assignee or PR. The useful reply objected that refreshable materialized views make the implicit form convenient, since they can always be recomputed, and suggested restricting the guard to incremental views, perhaps with a separate CREATE STREAMING MATERIALIZED VIEW. That is a better design than my original.

On 26.4 no such setting exists. The enforcement has to live outside the server: review DDL, or scan system.tables for .inner_id. names. The explicit-target pattern is in Materialized views basics.

Permalink · GitHub #68109

The single clickhouse binary can install itself with sudo ./clickhouse install and then manage itself with start, stop, status and restart. It cannot undo the install. That bites when a trial box becomes a real deployment and you want the package-manager version or an older one, but the self-installed copy, its registered service and its test data are in the way. I asked for sudo ./clickhouse uninstall. When a maintainer asked why anyone would want that, I listed the reasons: switching to packages, installing an older version, a clean slate, recovering from broken directory permissions, and symmetry.

Nothing has happened. The issue is open, labelled feature, comp-build-system and external, with two comments (the question and my answer) and no assignee.

On 26.4 the subcommand list ends at restart, and running clickhouse uninstall just prints the list of valid subcommands:

clickhouse install [args]
clickhouse start [args]
clickhouse stop [args]
clickhouse status [args]
clickhouse restart [args]

Until it exists, treat clickhouse install as a one-way door: clickhouse stop, then remove by hand what the installer put on the system, and note what it did as it ran. Better, try builds in a throwaway VM or container. For switching versions without a system install, chctl local install/use/remove covers half the wish; see the version-pinning request.

Permalink · GitHub #79440

Query, MergeTree and server settings can all be inspected from SQL, with the effective value next to the default. Kafka is the odd one out: behavior comes from engine settings in CREATE TABLE plus librdkafka properties in a <kafka> section of the server config, a pass-through bag of keys. A misspelled key or the wrong file leaves the consumer on defaults and nothing shows which keys were accepted. I asked for system.kafka_settings with name, effective value, default, a changed flag and consumer/producer scope.

Nothing yet. The issue is open, labelled feature, comp-message-queues and external, with no comments.

The neighbour that exists is system.kafka_consumers, which shows per-consumer state: assignments, offsets, last poll and commit times, rebalance counters, exceptions and the raw librdkafka statistics in rdkafka_stat. That tells you what a consumer is doing, not what configuration it started with. On 26.4:

SELECT * FROM system.kafka_settings;

Code: 60. DB::Exception: Unknown table expression identifier 'system.kafka_settings' ... (UNKNOWN_TABLE)

This needs a server to go further, so I have not exercised it against a broker. There is no clean workaround; you infer effect from behavior and logs. See Kafka consumers status and operating Kafka engine tables. I still want the table, since a typo is easiest to make and hardest to see in exactly this layer.

Permalink · GitHub #86583

ClickHouse has today() and yesterday() but no tomorrow(). The workaround is one token, today() + 1, so this is a small request, and I said so. My argument is that a named function states intent (WHERE event_time < tomorrow()) and that a gap in a family of names is something people have to remember. I also padded the issue with a pitch about forecasting pipelines, which was mostly marketing.

Nothing from the maintainers. The issue is open with the labels feature, comp-datetime and external and no comments. An automated coding assistant opened PR #92054 the same month; it was closed without merging on 2026-01-30 after CI failures.

On 26.4 SELECT tomorrow() fails with UNKNOWN_FUNCTION. A SQL user-defined function gives you the name today, with the caveat that it will collide if a built-in ever arrives:

CREATE FUNCTION tomorrow AS () -> today() + 1;
SELECT tomorrow();

For what SQL UDFs can and cannot do, see what ClickHouse UDFs should be able to do. I am fine if this stays open: even a trivial function costs a review, a documentation page and a place in a namespace every user shares.

Permalink · GitHub #92041

When several materialized views write into one target table, a row does not say which view produced it. That hurts when debugging odd values, monitoring per-view throughput, reprocessing one bad source, or auditing a number. I asked for a _view virtual column, in the family of _table and _part, that resolves inside a view’s SELECT to the running view’s name.

Nothing so far. The issue is open with the labels feature, comp-materialized-view and external, no comments and no linked pull request.

On 26.4 using it in a view’s SELECT is rejected at CREATE time:

Code: 47. DB::Exception: Unknown expression identifier `_view` ... (UNKNOWN_IDENTIFIER)

The workaround is a source_view String column on the target and a literal in each view:

CREATE MATERIALIZED VIEW mv_cpu TO metrics_all AS
SELECT ts, sensor_id, metric, value, 'mv_cpu' AS source_view
FROM metrics WHERE metric = 'cpu';

It works, but it is manual: a view copy-pasted from another keeps the old string. On a server, system.query_views_log records what each view did per insert, which helps monitoring but not the provenance of an individual row. Basics of fan-in are in materialized views basics.

Permalink · GitHub #94901

I asked for an input format that reads the whitespace-aligned tables most shell tools print (ps aux, df, docker ps, ls -l): a run of spaces is one delimiter, the header row supplies the column names, and the last column is greedy so free-text tails like COMMAND survive. The goal is to make ps aux | clickhouse local -q "..." replace grep | awk | sort. The issue includes a roughly 150-line prototype whose row counts matched awk on ten real commands.

It is open, labelled feature, comp-formats and external, with no comments and no pull request. The general version, #34006 (schema inference for freeform text), is closed.

On 26.4 the nearest format, CustomSeparatedIgnoreSpacesWithNames with a space delimiter, throws BAD_ARGUMENTS, and Regexp cannot read the header. What works is splitting in SQL, which keeps the greedy tail with arraySlice:

SELECT f[1] AS user, toUInt32(f[2]) AS pid, toFloat64(f[3]) AS cpu,
       arrayStringConcat(arraySlice(f, 4), ' ') AS command
FROM (SELECT splitByWhitespace(line) AS f
      FROM file('/tmp/ps.txt', 'LineAsString', 'line String'))
WHERE f[1] != 'USER';

root  1   0.5  /sbin/init splash
me    22  3    sleep 10

That needs you to know the shape of ps in advance, which is what the request wanted to avoid. See also query inline data with the format table function.

Permalink · GitHub #112109

In a multi-tenant setup where every agent is a ClickHouse user, I needed the “mail slot” pattern: anyone may INSERT into a table, only the owner may SELECT. That wants GRANT INSERT ON t.reg TO ALL, but GRANT reads ALL as a role name and fails with UNKNOWN_ROLE. Meanwhile CREATE ROW POLICY, CREATE SETTINGS PROFILE, CREATE QUOTA and SET DEFAULT ROLE all accept TO ALL as a live set of every user, including users created later. I asked for the same on GRANT, or, if the asymmetry is deliberate, a sentence in the docs saying so.

It is open, labelled feature, comp-rbac and external, with no comments and no pull request.

The workaround is a role the application grants to every user it creates. Its weakness is silent drift: one missed grant and the role means “everyone created before the omission”, with no error. Catching it takes an audit diffing system.users against system.role_grants.

CREATE ROLE writers;
GRANT INSERT ON t.reg TO writers;
-- in whatever creates a user:
GRANT writers TO new_user;

Reproduced on 26.4. See users, roles and settings profiles.

Permalink · GitHub #115633

Columns and tables take COMMENT; constraints do not. When a CHECK fails, VIOLATED_CONSTRAINT prints the expression and the offending value but not what a valid value should look like. In a system where the schema is the API and callers, increasingly automated, learn what to send from SHOW CREATE TABLE, that is a real gap: one caller sent a UUID where a SHA-256 digest was required and could not tell from the error what to send instead. I asked for COMMENT on a constraint, a comment column in system.constraints, and the comment included in the error text.

A contributor said two days later that they would like to work on it; there is no pull request yet. The issue is open, labelled feature, comp-sql-syntax and external.

On 26.4 the syntax is rejected:

CREATE TABLE cc (x String, CONSTRAINT valid_x CHECK match(x, '^[a-z]+$') COMMENT 'lowercase only')
ENGINE = MergeTree ORDER BY x;
-- Code: 62. DB::Exception: Syntax error ... (COMMENT)

Until then, the only way to get the reason into the error is to put it in the constraint name, e.g. handle_lowercase_2_to_32_chars, which appears in every VIOLATED_CONSTRAINT message.

Permalink · GitHub #115933

The experimental Alias table engine gives a table a second name, and reads go through to the target. With row policies as the isolation boundary, the question is whose policies apply to a read through the alias. As I reported it, an alias with no policy of its own dropped the target’s, which is DEFINER behaviour without the SET DEFINER gate a DEFINER view needs. I proposed reusing the SQL SECURITY INVOKER | DEFINER clause views have had since 24.2, defaulting to INVOKER, so the safe behaviour is the default and not a special case.

The access-check bug itself was handled separately: #114596, “Fix target access checks for the Alias engine”, merged on 2026-08-24, the day the request was filed, and a reviewer asked that the target’s policies always apply. The SQL SECURITY request is open, labelled feature, comp-rbac and external, with no comments and no pull request.

On 26.4 the clause is still a syntax error, and the engine needs allow_experimental_alias_table_engine = 1:

SET allow_experimental_alias_table_engine = 1;
CREATE TABLE t (x UInt8) ENGINE = Memory;
CREATE TABLE a2 SQL SECURITY INVOKER ENGINE = Alias(t);
-- Code: 62. DB::Exception: Syntax error ... (SQL)

I did not test row-policy behaviour on 26.4. If you combine Alias with row policies, test it on your version. For the same mechanism on views, see users, roles and settings profiles.

Permalink · GitHub #116125

In 2019 ClickHouse got an experimental LIVE VIEW you could WATCH; it pushed a new result when the source table changed. The output carried a _version column but not what kind of change had happened. Issue #7881 asked for an _operation column next to it: +1 inserted, 0 updated, -1 deleted, so that dashboards, alerting and cache invalidators could react to changed or removed rows, not only new ones. The signed marker is the same row-and-cancellation model CollapsingMergeTree uses with sign.

Nothing happened, visibly. The issue has no comments; on 2022-04-26 it was closed without a word. GitHub records “completed”, but there is no PR and no _operation column ever appeared. Live View never left experimental status, which is the likely reason, though that is my inference.

The end of the story is documented: PR #88706, “Remove LIVE VIEW”, merged 2025-10-23, so the feature is gone in 25.11. The changelog warns that upgrading with a live view in place is not possible. WINDOW VIEW and WATCH were removed later, with streaming queries as the stated replacement direction (#114738).

On 26.4 the syntax no longer parses:

CREATE LIVE VIEW lv AS SELECT 1 FROM t;

Code: 62. DB::Exception: Syntax error: failed at position 13 (VIEW)

The need, “what changed since I last looked”, is still served by your own plumbing: a materialized view feeding a change table, or polling a monotonic column. If streaming queries land, a change feed with an operation marker is what I would ask for first.

Permalink · GitHub #7881

The argument for this request is in LIMIT could do more. This page is only the history and status.

I asked for a way to cap the rows read from one table inside a many-JOIN query, so you can sample while developing without wrapping each table in a subquery. My sketches were FROM events(100) and a LIMIT placed before the joins; a commenter suggested reusing SAMPLE.

The maintainer’s answer on 2022-09-04 was a careful no for now. FROM my_table(100) is too ambiguous because it looks like a table function; a second LIMIT before the joins “may be OK, but looks too surprising”. He floated TAKE 10, SAMPLE ANY 10 and PRQL integration, and concluded: “I would postpone implementation before more motivation for this task will appear.” In February 2025 another user pointed out that a parameterised view covers part of it, and the issue was closed the same day as not planned.

Nothing of the proposed syntax exists. On 26.4, SELECT * FROM numbers(100) LIMIT 3 ORDER BY number is still a syntax error. The parameterised view is the supported workaround:

CREATE VIEW head_of_events AS SELECT * FROM events LIMIT {n:UInt32};
SELECT count() FROM head_of_events(n = 10);   -- 10 rows, joinable like any table

Status: declined. Nobody argued the problem away; nobody agreed on a syntax. See also Feature requests worth making.

Permalink · GitHub #40965

CollapsingMergeTree trusts that its sign column holds only 1 or -1, but the column is an Int8. On a production system one client inserted a 2; the row could not collapse against anything, merges complained, and the error flooded the logs. The only exit I knew was copying the table without the bad rows. I proposed a Sign data type accepting only +1 and -1, or any way to refuse other values on insert.

Two replies the next day made it unnecessary. A user suggested CONSTRAINT c_sign CHECK sign IN (-1, 1), a feature I had forgotten. A ClickHouse developer pointed out that optimize_on_insert (on by default) collapses each block before writing it and throws if the sign is unexpected. The issue was closed in August 2021 with nothing implemented.

On 26.4 the engine alone rejects the row:

CREATE TABLE c1 (k UInt32, sign Int8) ENGINE = CollapsingMergeTree(sign) ORDER BY k;
INSERT INTO c1 VALUES (1, 1), (2, 2);
-- Code: 117. Incorrect data: Sign = 2 (must be 1 or -1). (INCORRECT_DATA)

That check depends on optimize_on_insert; a profile that disables the setting loses it. The constraint does not depend on any setting, and it is the line I put on every collapsing table.

Permalink · GitHub #24195

curl https://clickhouse.com/ | sh downloads the current master build, not a release. Run it twice a month apart and you get two different servers; I once built a cluster whose hosts were installed on different days and ran different builds, and it took days to find out why. I asked for versioned paths such as curl https://clickhouse.com/25.3.1 | sh, and later sketched a table: /stable, /stable/25.4, /lts/..., /nightly.

The thread was short. A maintainer (Algunenano) noted that release tarballs on packages.clickhouse.com already contain the all-in-one binary, another cross-linked a Slack discussion, and then it went quiet. The issue is open, labelled feature, comp-documentation and external, and the bare URL still serves master.

What changed is the tooling: the script now also installs clickhousectl (alias chctl), the official CLI, which does the version management I wanted. Per its README:

clickhousectl local install 26.8        # latest 26.8.x.x
clickhousectl local install 26.8.1.1760 # exact version
clickhousectl local use lts             # latest LTS channel
clickhousectl local list --remote       # what can be downloaded

The literal curl .../25.3.1 | sh form still does not exist, so the status is superseded, not implemented. For reproducibility, record the exact version (SELECT version(); mine reports 26.4.1.834) and install through clickhousectl or a release tarball, never the bare one-liner. The compatibility setting helps when comparing behavior, but does not replace running the old binary.

Permalink · GitHub #77431

Three of my requests ended in a comment rather than a pull request. Each taught me where to look.

• #84884, cluster() and clusterAllReplicas() should skip unavailable nodes. A down node fails the whole query (ALL_CONNECTION_TRIES_FAILED), exactly when you want to read system.server_settings from every node. Eight minutes after I opened it, den-crane asked whether I had tried SETTINGS skip_unavailable_shards = 1. I had not; I closed it within the hour. The setting defaults to 0, and 26.3 added max_skip_unavailable_shards_num and max_skip_unavailable_shards_ratio as a guard against silently answering from half a cluster. See compare settings across a cluster.
• #87468, enable_analyzer is logged as allow_experimental_analyzer. In system.query_log the Settings map showed the old name. novikd closed it with one line: enable_analyzer is an alias and the original name is what gets recorded. The alias is visible in system.settings.alias_for. Practical consequence: filter the query log on the old name.
• #92005, ttl_only_move_parts. I assumed a counterpart to ttl_only_drop_parts was missing. UnamedRus answered the next morning: TTL moves already work on whole parts, so there is nothing to opt into. A delete TTL is row-based, a move TTL is not. See tiered storage and TTL: delete only matching rows.

The cheap lesson: grep system.settings and system.merge_tree_settings with LIKE '%skip%' before filing. The less cheap one: if three of my requests hit features or behaviors that already exist, discoverability is itself a usability problem.

Permalink · GitHub #84884