INSERT … RETURNING can return rows ClickHouse never stored

I inserted a thousand rows into a table with CHECK (x < 500). RETURNING sent back rows 0 to 599. The table held 0 to 399.

So the client was told about two hundred rows that do not exist, and a hundred of them — 500 to 599 — break the table’s own constraint and could never have existed. The insert did fail with an exception, so nothing was hidden. But if your code reads the returned rows as a receipt, it has just recorded data the database rejected.

That one experiment explains how ClickHouse’s new INSERT … RETURNING (SELECT …) works, and most of what follows:

RETURNING taps the insert stream just before the table’s storage. It tells you what the server was about to write, not what it wrote.

On the happy path those are the same, and the feature is excellent. This post covers both: what it is great at, and the cases where the tap and the table disagree.

I asked for this feature in 2019, as issue #7206; the seven-year story is its own post. It is in 26.11, written by Alexey Milovidov in PR #126476. I tested a pre-release 26.11 build with about 150 INSERT … RETURNING statements over the native protocol and HTTP, with several restricted users and quotas and inserts of up to ten million rows. Method at the end.

How it works, in one picture

The PR puts a fork into the insert pipeline right before the destination’s sink. One copy of each block goes on to the table; the other copy feeds your RETURNING query, which reads it the way a materialized view reads an insert. Inside that query, the destination table’s name means the rows being inserted — after defaults are filled — not the table on disk.

CREATE TABLE t (id UUID DEFAULT generateUUIDv4(), x UInt32, created DateTime DEFAULT now())
ENGINE = MergeTree ORDER BY x;

INSERT INTO t (x) RETURNING (SELECT x, id, created) VALUES (1), (2);

1	69fd1645-c26b-4bb9-b40d-0a931f224704	2026-10-29 08:25:28
2	c80a3087-6c02-4316-8fa2-f7078dccb3d7	2026-10-29 08:25:28

The syntax has three rules. The clause goes after the column list and before the data. The parentheses are mandatory — there is no RETURNING *. And SETTINGS goes after the RETURNING (…) clause and before VALUES or FORMAT, or inside the RETURNING query itself. SETTINGS before RETURNING is a syntax error, and RETURNING after VALUES gives a confusing parse error that mentions WaitForAsyncInsert.

What it is genuinely great at

Generated values match what was stored. I inserted a thousand rows into a table with DEFAULT generateUUIDv7(), DEFAULT rand64() and MATERIALIZED now64(6), then read the table back: all thousand returned rows were identical to the stored ones, and generateUUIDv4() checked out the same way. That was the original use case — read back what the server computed — and it is now one round trip. It even tells the truth about conversions: insert 256 into a UInt8 and RETURNING shows 0, because that is what was written.

Two details matter for this use case. SELECT * inside RETURNING leaves out MATERIALIZED and ALIAS columns, exactly like an ordinary SELECT, so name them explicitly. EPHEMERAL columns cannot be returned at all.

The destination is the stream, not the table. With seven rows already in t, inserting two more:

INSERT INTO t (x) RETURNING (SELECT count(), max(x) FROM t) VALUES (20), (21);

2	21

So you can aggregate, filter, rank and join what you just inserted — WHERE, window functions, ORDER BY … LIMIT, joins to dimension tables — without isolating “your” rows by hand. A SQL LIMIT only limits the output: a million-row INSERT … SELECT with RETURNING (SELECT x LIMIT 0) still committed a million rows, and so did another with WHERE 0.

It is cheap. A ten-million-row INSERT … SELECT, three runs each, server-side time and memory from system.query_log; the client discarded the returned rows (--format Null):

Variant	Avg time	Avg memory
plain `INSERT … SELECT`	383 ms	290 MiB
`RETURNING (SELECT count())`	377 ms	318 MiB
`RETURNING (SELECT x, s)`, every row	374 ms	313 MiB
`RETURNING (SELECT x ORDER BY x DESC LIMIT 10)`	360 ms	327 MiB

Time is within noise; memory grows 8–13%. Shipping the rows to a real client costs what any result set costs: two million rows sent from stdin and all two million returned over the native protocol took about 0.9 s end to end, with no deadlock — the risk the PR spends a paragraph on. Separately, three 500,000-row runs with trace-level server logs streaming over the same connection all completed and stored every row.

The security model is tight. This is where I expected holes and found few:

RETURNING needs SELECT on the destination. A user with only INSERT cannot run even RETURNING (SELECT 1).
Column-level grants are enforced: returning a column you cannot SELECT fails with ACCESS_DENIED.
A column whose DEFAULT calls dictGet or joinGet on a secret source needs those grants for the plain insert already, so RETURNING leaks nothing new.
Destinations that read other tables — Distributed, Alias, Buffer with a destination table, a materialized view, and the remote() and cluster() table functions — are refused with NOT_IMPLEMENTED, because the underlying tables’ access checks would not apply to the substituted stream. null() and file() work; INSERT INTO FUNCTION needs the CREATE TEMPORARY TABLE grant.
Errors found while analysing the RETURNING query (an unknown column, reading the destination twice, a scalar subquery over the stream) abort before anything is committed.
system.query_log records both written_rows and result_rows, so you can audit what was returned.

Where the tap and the table disagree

Failed inserts: rows that were never written

The opening experiment, exactly:

CREATE TABLE v (x UInt64, CONSTRAINT c CHECK x < 500) ENGINE = MergeTree ORDER BY x;

INSERT INTO v RETURNING (SELECT x) SELECT number FROM numbers(1000)
SETTINGS max_insert_block_size = 100, min_insert_block_size_rows = 100, max_block_size = 100,
         max_insert_threads = 1, max_threads = 1;

600 rows returned, then VIOLATED_CONSTRAINT; 400 committed. The tap sits before the constraint check. With several insert threads the numbers changed from run to run (600 returned and 200 committed; 500 and 100); over HTTP with one thread, 500 and 400. I reported this upstream as #128175.

A ClickHouse insert has never been atomic across blocks, so a partial commit is not new. Two things are. The client now receives rows for data that was never written. And a limit on the returned output can now stop the write:

max_result_rows = 150 with result_overflow_mode = 'throw', a thousand rows in blocks of a hundred: the client got 100 rows, then TOO_MANY_ROWS_OR_BYTES, and nothing was committed.
The same throw with insert block squashing turned off (min_insert_block_size_rows = 0) and one thread committed 400 rows before the limit tripped. Which blocks survive depends on block layout.
result_overflow_mode = 'break' does the opposite: output stopped at 200 rows (the limit is checked per block, so it overshoots 150), no error, and all thousand rows were committed. The result is silently truncated while the insert completes.
A result_rows = 1000 quota let 1,000 rows out, then threw QUOTA_EXCEEDED; 1,000 of 2,000 rows were committed.
A MEMORY_LIMIT_EXCEEDED thrown by an ORDER BY inside RETURNING (10 million rows, a 100 MiB limit) left 2.2 million rows committed. A full ORDER BY with external sorting allowed completed and committed all ten million.
KILL QUERY in the middle of the output left 5,000 rows committed and the server fine.

Retries: ids that were never stored

Insert deduplication makes retries safe in ClickHouse. With RETURNING, a deduplicated retry hands you a fresh id that does not exist:

CREATE TABLE du (x UInt32, id UUID DEFAULT generateUUIDv4()) ENGINE = MergeTree ORDER BY x
SETTINGS non_replicated_deduplication_window = 100;

INSERT INTO du (x) RETURNING (SELECT x, id) SETTINGS insert_deduplication_token = 'retry-1' VALUES (1);
INSERT INTO du (x) RETURNING (SELECT x, id) SETTINGS insert_deduplication_token = 'retry-1' VALUES (1);

1	363a8630-de56-4ef2-b599-90dd1dfcbb3a
1	b46d4e0f-b1da-4184-a825-fff935d8f0fb

The table holds only the first. The second insert succeeded, was deduplicated, and still reported a row with a newly generated UUID; RETURNING (SELECT count()) likewise reports the full count for an insert that wrote nothing. The PR says it plainly: deduplication and other engine transformations are not taken into account. If you retry with a deduplication token, generate ids on the client, where a retry sends the same value. Reported upstream as #128174.

The same “before the engine” rule shows up without any failure. ReplacingMergeTree and SummingMergeTree return the pre-merge rows — with optimize_on_insert, three rows returned and one stored — and rows already past their TTL are returned like any others.

HTTP: 200 with the error at the bottom

The constraint experiment over HTTP, run twice on identical input:

	Status	Rows in body	Error in body	Committed
default	200	500	yes, appended at the end	400
`wait_end_of_query=1`	500	0	yes	400

A 200 followed by an exception in the body is how ClickHouse’s HTTP interface has always behaved once a result starts streaming. What is new is that an INSERT now streams a result. A client that judges success by the status code will read a failed, partly committed insert as a success. In this test, wait_end_of_query=1 turned the status into 500, but that is not a guarantee: the buffered result must fit in http_response_buffer_size, and with send_progress_in_http_headers the headers can go out before the query ends (HTTP response-code caveats). It never undoes the partial commit either. So always check the end of the body for an exception; treat the status code as a hint.

Surprises that are not failures

Materialized views are invisible. In my test, a count() of the view’s target table inside RETURNING was 0; right after the insert, every row was there. Do not use RETURNING to observe the work an insert triggers.
Row policies filter your own rows. A user restricted to tenant = 'a' who inserts a b row does not see it in RETURNING, although it is stored. Correct for security; startling the first time.
Unqualified names resolve in the current database. After USE db2, INSERT INTO db1.t RETURNING (SELECT * FROM t) returns the existing table db2.t, not the inserted rows. Qualify the destination, or leave out FROM.
Order is not guaranteed. With several insert threads, rows came back in a different order from the source — even from a deterministic numbers(). Add ORDER BY if order matters.
async_insert is silently turned off for these queries, as the PR documents. If you rely on server-side batching of many small inserts, adding RETURNING makes each one a synchronous insert again — and creates a part per insert.
UNION ALL with a constant branch is refused (“can be read only once”), because the FROM-less branch is treated as reading the destination too. SELECT 999 FROM system.one works.
Clients may need an upgrade. A 26.3 clickhouse-client rejects the query before sending it. On the server, native protocol revision 54495 is checked only when the client sends the data separately — from stdin, FROM INFILE, input(), or VALUES parsed by the client — and an older native driver gets NOT_IMPLEMENTED there. INSERT … SELECT and data inside the query are not gated, and HTTP works from any client.
Quotas count it as an insert. A user whose query_selects quota is spent can still read another table inside INSERT … RETURNING and get the rows back. A plain INSERT … SELECT from that table was already charged as an insert; what is new is that the data now reaches the client. The user still needs SELECT on the table, and result_rows quotas apply.

Rules for production

Treat the result as what the server meant to write. Without an exception it matches the table, except for deduplication and engine transforms. With an exception, some returned rows may not exist.
Over HTTP, always check the end of the body for an exception. wait_end_of_query=1 can turn a late failure into a 500, but only within its documented limits, so it is not a substitute.
Generate ids on the client when you retry with deduplication tokens.
Use it to read back computed values, not to observe materialized views or merges.
Qualify the destination inside RETURNING, or leave out FROM.
Keep RETURNING off high-rate small inserts that depend on async_insert.
Add ORDER BY when order matters. Remember that a result-row limit, quota or memory error in the RETURNING query can stop the insert part-way; a SQL LIMIT only shortens the output.

Back to the request

The 2019 issue asked for a way to see what the server wrote. For a successful insert into a plain MergeTree table, that is exactly what 26.11 delivers, at a cost that disappears into the noise of a ten-million-row insert. The price of that streaming design is the one thing a receipt must not do: when an insert fails or is deduplicated, the result can describe rows that do not exist.

That is a reasonable trade for a column store whose inserts were never transactions. It just needs saying out loud, which is what this post is for.

Method. ClickHouse 26.11.1.879, a pre-release build (the official clickhouse/clickhouse-server:head image, pinned by digest), as a single-node server in a disposable container; clickhouse-client 26.11 over the native protocol and curl over HTTP. About 150 INSERT … RETURNING statements in a dozen suites. Each failure case was compared against an ordinary INSERT where a baseline exists, and every surprising result was re-run on fresh tables. One apparent “committed despite an error” turned out to be a leftover row from an earlier step and was dropped. Gemini 4 Argon and GPT-6.1-sol proposed tests and reviewed the findings and this draft. Their tests found the phantom ids, the partial commit under throw, the UNION ALL cause and the name shadowing. Their review caught two places where I had merged separate experiments, and two claims I had not yet tested; those were re-run for this version. Feature: PR #126476, merged 2026-10-24. Reported upstream: #128175 (rows rejected by a constraint are returned) and #128174 (deduplicated retries return ids that are never stored).