/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse::Storage

NAME

DBIO::ClickHouse::Storage - ClickHouse storage driver for DBIO (MySQL wire protocol)

VERSION

version 0.900004

DESCRIPTION

ClickHouse storage driver for DBIO. Extends DBIO::Storage::DBI with ClickHouse-specific behavior.

ClickHouse is a column-oriented OLAP database that speaks the MySQL wire protocol on server port 9004. There is no native DBD::ClickHouse on CPAN, so this storage sits on top of DBD::mysql or DBD::MariaDB over that wire protocol. DBD::mysql is the standard: it sends no SQL on connect at all, so it works against a stock ClickHouse server -- and therefore ClickHouse Cloud -- with no server-side accommodation, verified live against 25.3.14.14 (the full suite over a dbi:mysql: DSN). Connect it with mysql_enable_utf8mb4 => 1 or character data comes back as bytes. DBD::MariaDB is supported for servers whose config you control: it sends SET character_set_server on every connect and therefore needs custom_settings_prefixes registered on the server -- impossible on ClickHouse Cloud -- but decodes character data unconditionally. See "Transport: the MySQL wire protocol" in DBIO::ClickHouse and docs/adr/0001-mysql-wire-protocol-transport.md.

Unlike other drivers, this storage is not resolved by DSN autodetection: a ClickHouse DSN is dbi:MariaDB:... and is indistinguishable from a real MySQL DSN. It is registered under the ClickHouse driver name for the component / -ch shortcut path, and must be pinned explicitly (see DBIO::ClickHouse).

METHODS

_determine_supports_insert_returning

Returns 0 -- ClickHouse has no INSERT ... RETURNING.

_prefetch_autovalues

Generates the client-side UUID for every is_auto_increment UUID column the insert did not supply, so that the value is sent rather than retrieved. See "PRIMARY KEYS AND IDENTITY".

Core merges the returned hash into the row before building the statement and once more into what insert hands back, which is what makes $row->id the value this process generated.

_generate_uuid

my $uuid = $storage->_generate_uuid;

Returns a fresh RFC 4122 version 4 UUID in the canonical hyphenated form, from 16 bytes of Crypt::URandom entropy -- the same shape ClickHouse's generateUUIDv4() produces, generated on this side of the wire.

This is the override point for a different identity scheme (UUIDv7 for a time-ordered sorting key, an application-wide id service): subclass the storage and return your own value.

last_insert_id

Always throws a DBIO::Exception. ClickHouse has no identity column and no last_insert_id, so there is nothing to return; core reaches this hook when a primary key was left unset, and the honest answer is a message saying how to set it rather than a fabricated or undef key silently landing in $row->id.

Note that the row has been inserted by the time this throws -- there is no transaction to undo it with. See "PRIMARY KEYS AND IDENTITY".

_insert_bulk

$storage->_insert_bulk($source, \@columns, \@rows);

Writes the whole dataset as one multi-row INSERT, rendered by "insert_multi" in DBIO::ClickHouse::SQLMaker. This is where a void-context $rs->populate(\@rows) ends up.

Core's implementation prepares a single-row statement and runs it once per row via execute_for_fetch; on ClickHouse that would write one part per row (see "INSERTS AND BATCHING"). One statement also removes the reason core wraps the loop in a txn_scope_guard -- with nothing to make atomic across statements, the guard could only add the refusal from "txn_rollback" on top of whatever error actually occurred, so this path takes no guard.

The per-row consistency checks core performs (every row must repeat the same literal SQL, because there is only one prepared statement) do not apply either: each row is rendered on its own. What is still checked, in insert_multi, is that every row fills the same column list.

_prep_for_execute

Renders the statement as core does, then refuses -- before anything is sent -- a value that the server would store as something else than was written. The cases:

  • A string that is not a number, bound to a numeric column. The server stores it as NULL, without an error, in a Nullable integer column ('', 'abc', '5x', ' 5', and also '1.5' and '1e3', which are numbers but not integers), and a Float or Decimal column does the same for a value with a blank around it. Measured on a stock server through DBD::mysql (conformance case bind-empty-string). The driver throws instead, naming table and column and never the value. The test is the server's own number format per type: an integer column takes [+-]?\d+, a float column the decimal forms with an optional exponent and inf/nan, a decimal column the same without them. '007' is a number and goes through.

    The column is the one the bind belongs to (its type read from the column info, Nullable(...) and LowCardinality(...) looked through). It applies to insert, insert_multi and update -- the statements that store a value; a search condition stores nothing and is left to the server. A reference (literal SQL) and undef are not looked at.

  • An integer outside the range of its column's type. 300 or -3 at a UInt8, 128 at an Int8, 2**64 at a UInt64: the server stores NULL in a Nullable column and a wrapped-around number (300 becomes 44) in any other, without an error. Measured on a stock server through DBD::mysql. No session setting turns that into an error -- input_format_null_as_default, input_format_values_interpret_expressions, input_format_values_accurate_types_of_literals, insert_null_as_default change nothing, and the template settings only make it worse (the value wraps to 0, or every insert fails) -- so the driver checks the range of every Int8 .. Int256 / UInt8 .. UInt256 column (portable names and extra => { unsigned => 1 } included) before sending, and throws, naming table and column and never the value.

The byte 0x1A needs no refusal; see "_render_bind" in DBIO::ClickHouse::SQLMaker.

update

delete

my $rv = $storage->update($source, \%values, \%condition);
my $rv = $storage->delete($source, \%condition);

Issue the ALTER TABLE ... UPDATE / ALTER TABLE ... DELETE mutation rendered by DBIO::ClickHouse::SQLMaker, and return -1.

-1 is DBI's "number of rows is unknown", and that is the honest answer here. A mutation reports nothing about how many rows it matched: the server acknowledges the ALTER, not the row set, so the affected-rows count on the wire is 0 whether the mutation changed a million rows or none. Every other driver returns a real count from $rs->update / $rs->delete; this one cannot, and returns a value that is visibly not a count rather than a plausible-looking 1 or a 0 that would read as "nothing matched".

So:

my $n = $rs->search({ stale => 1 })->delete;   # -1, always
if ($n > 0) { ... }                            # never true -- do not write this

Count first if you need the number ($rs->count before the mutation), or count after it -- the default mutations_sync = 1 means the rows really are gone by the time the statement returns.

The row-level path is the reason the value is -1 and not '0E0': DBIO::Row::update throws "row not found" on a 0 return and "updated more than one row" above 1, so a truthful "unknown" has to sit between them. $row->update and $row->delete therefore behave normally -- but note that they cannot detect a row that is no longer there. A mutation whose WHERE matches nothing is a successful mutation.

_dbio_sql_maker_option_names

Adds mutations_sync to the connect-info attributes that are routed to the SQL maker instead of to DBI->connect, which is what makes ->connect($dsn, $user, $pass, { mutations_sync => 2 }) work. See "MUTATIONS".

The setting is rendered into each statement rather than held on the storage, so the SQL maker is where it belongs -- and unlike core's storage-option list (which DBIO::Storage::DBI captures into a file-scoped lexical at compile time), the SQL-maker list is looked up as a method on the live storage and can therefore be extended by a driver at all.

_adjust_select_args_for_complex_prefetch

Drops the ClickHouse dialect attributes (final, prewhere, limit_by -- see "CLICKHOUSE RESULTSET ATTRIBUTES" in DBIO::ClickHouse::SQLMaker) from the outer half of a complex-prefetch rewrite. Core copies the whole attribute hash into both halves, but the outer query selects from the generated subquery, where FINAL and PREWHERE are not valid at all and LIMIT n BY would apply to the joined result instead of the table rows. The inner half keeps them.

_set_operation_keyword

my $keyword = $storage->_set_operation_keyword('except');   # EXCEPT DISTINCT

Spells union, intersect and except with an explicit DISTINCT, which is what those three mean in ANSI SQL and not what the bare keyword means on ClickHouse. The other three -- union_all, intersect_all, except_all -- are already spelled the ClickHouse way by core's base map and reach it through $self->next::method. Nothing is refused: this engine has all six. See "SET OPERATIONS" for the settings behind it and the 22.9 version floor.

A static class fact, as ADR 0039 section 7 requires: no $dbh, no version probe, because "_set_operation" in DBIO::ResultSet bakes the keyword into the query at construction time.

_random_function

my $func = $storage->_random_function;   # rand()

The function "rand" in DBIO::ResultSet orders by. rand(), and not core's inherited RANDOM() (ClickHouse has no such function), and not randCanonical() (its Float64 buys nothing at this hook's only call site and costs a 22.11 version floor). See "RANDOM ORDERING", including why randConstant() -- the variant that would silently stop shuffling -- is the wrong answer.

A static class fact: no $dbh, no version probe.

_exec_txn_begin

_exec_txn_commit

No-ops -- there is no BEGIN / COMMIT to send. The public txn_begin / txn_commit keep working (and keep core's transaction_depth bookkeeping correct), they simply do not make anything atomic. The first of the two to run in an interpreter warns once. See "TRANSACTIONS".

txn_do

my @rows = $storage->txn_do(sub { ... }, @args);

Runs the coderef without a BEGIN/COMMIT bracket (warns once), passing @args through and preserving calling context. If the coderef dies, the original exception propagates unchanged -- there is no rollback that could mask it -- preceded by a loud warning that everything the block managed to write is permanent.

The block is never retried: core retries a txn_do whose connection went stale, which is only safe because the transaction guarantees the failed attempt left nothing behind. Without a transaction a retry would re-apply whatever the first attempt already wrote.

txn_rollback

Always throws a DBIO::Exception via throw_exception: ClickHouse cannot undo anything, and silently accepting a rollback would leave the caller believing the changes are gone while they are still in the table.

Core's transaction_depth is unwound first, so the storage is not left believing it is inside a transaction after the throw. See "TRANSACTIONS".

txn_scope_guard

my $guard = $storage->txn_scope_guard;   # or $schema->txn_scope_guard

Returns a DBIO::ClickHouse::Storage::TxnScopeGuard: core's guard with its DESTROY replaced by one that carps instead of rolling back. Taking one is still allowed -- core takes them on your behalf all over the ORM -- it simply cannot make anything atomic, and says so when it unwinds.

$guard->rollback is untouched and still throws. See "TRANSACTIONS".

_exec_svp_begin

_exec_svp_release

_exec_svp_rollback

ClickHouse has no savepoints (and no transactions); all three throw a DBIO::Exception via throw_exception.

_ping

Liveness check via SELECT 1 on the active dbh.

clickhouse_version

my $v = $storage->clickhouse_version;

Returns the ClickHouse server version string via SELECT version().

_mask_error_values

my $text = $storage->_mask_error_values($text, \@values, $statement);

The one seam through which the text of the engine passes before it is thrown ("_mask_error_values" in DBIO::Storage::DBI). ClickHouse writes the value it choked on into its message -- also a bound one, because the MySQL-wire driver puts the bind values into the statement on the client and the engine quotes what it received -- in sentences of its own (Cannot parse string 'x' as Int32, Column values:, the statement after In scope, the plan after while executing). The text goes through "mask_error_details" in DBIO::ClickHouse::Util first, which knows those sentences and keeps the error code, the type, the column, the constraint and the position; then through core's masking of the bound values, which catches the values of at least four characters wherever else they stand.

Core calls it for every error of a handle, of a statement run through _dbh_execute and of a bulk populate; update, delete and the multi-row insert of this driver run through the same path. $dbh->errstr keeps the raw text.

dbio_deploy_class

my $class = $storage->dbio_deploy_class;   # DBIO::ClickHouse::Deploy

Names the native deploy class, which is how "deploy" in DBIO::Schema finds it: core probes $storage->can('dbio_deploy_class') and, when the storage answers, runs $class->new(schema => $schema)->install instead of its own generic path.

The declaration is what makes $schema->deploy fail loud. Undeclared, core falls through to "deploy" in DBIO::Storage::DBI, whose statement loop wraps every statement in a try/catch that carps the error as a warning and continues with the next statement, then reports success regardless. On this engine that is a realistic outcome rather than a theoretical one -- a CREATE TABLE whose type or sorting key ClickHouse rejects fails on its own while its neighbours succeed -- and the caller would be handed a true return value over a half-deployed schema. Routing through DBIO::ClickHouse::Deploy runs the same DDL through "_execute_ddl" in DBIO::Deploy::Base, which lets the first failure propagate and never reaches the statements behind it.

That is a loud failure, not a clean one: ClickHouse has no transactional DDL (see "TRANSACTIONS" and ADR 0003), so the tables created before the failing statement stay created either way. The difference is being told which statement broke, instead of finding out later.

The class name is only a name here -- core requires it at deploy time -- so declaring it does not pull DBIO::ClickHouse::DDL, DBIO::ClickHouse::Introspect or DBIO::ClickHouse::Diff into every connection.

deployment_statements

my $sql = $storage->deployment_statements($schema);

Returns the ClickHouse-native install DDL for $schema, by delegating to $schema->clickhouse_install_ddl (see "clickhouse_install_ddl" in DBIO::ClickHouse, which renders through DBIO::ClickHouse::DDL). The remaining arguments core passes -- $type, $version, $dir, $sqltargs -- are ignored: the DDL is rendered from the schema itself and has no dialect or version variants.

This is the SQL-inspection entry point, not the deploy path: $schema->deploy routes through "dbio_deploy_class" and renders the same DDL there.

Unlike the MySQL and PostgreSQL storages there is deliberately no fallback for a schema without the component -- it throws. The generic path keys off sqlt_type, and sqlt_type is the DBD driver name, which for a ClickHouse connection is MariaDB / mysql. Anything that resolves against that name is MySQL DDL: InnoDB engines, FOREIGN KEY, AUTO_INCREMENT -- all of which ClickHouse rejects -- and none of it carries the ENGINE and ORDER BY that a ClickHouse CREATE TABLE cannot do without. Falling through would not degrade gracefully, it would deploy the wrong database's schema, so the missing component is reported as the error it is.

CLICKHOUSE SEMANTICS

ClickHouse shares the wire protocol with MySQL but almost none of the semantics:

  • No transactions and no savepoints. txn_begin / txn_commit are no-ops and "txn_do" runs its coderef unwrapped (each warns once), while "txn_rollback" and the savepoint hooks below throw. See "TRANSACTIONS" and docs/adr/0003-no-transactions-policy.md.

  • No SELECT ... FOR UPDATE -- disabled in DBIO::ClickHouse::SQLMaker.

  • No INSERT ... RETURNING -- "_determine_supports_insert_returning" returns 0.

  • No auto-increment / last_insert_id. The primary key is the MergeTree ORDER BY sorting key; surrogate identity is done with UUIDs. See "PRIMARY KEYS AND IDENTITY".

  • UPDATE / DELETE are asynchronous mutations (ALTER TABLE ... UPDATE/DELETE), non-transactional and applied in the background by rewriting parts. "update" / "delete" render and wait for them; neither returns a row count. See "MUTATIONS".

  • Identifier quoting is the backtick ` (MySQL-wire compatible).

Native DDL generation lives in DBIO::ClickHouse::DDL and is reachable through "deployment_statements"; introspection, diff and desired-state deploy live in DBIO::ClickHouse::Introspect, DBIO::ClickHouse::Diff and DBIO::ClickHouse::Deploy, reached via "dbio_deploy_class".

PRIMARY KEYS AND IDENTITY

ClickHouse has no identity columns, no sequences and no last_insert_id. There is also no INSERT ... RETURNING, so nothing the server computes during an insert can be read back in the same round-trip. That fixes the policy:

a row's primary key is always the value the client sent. This driver never hands back a database-generated key, because it has no way to learn one.

UUID primary keys

The one surrogate identity ClickHouse offers natively is a generated UUID, and that is what is_auto_increment => 1 means here -- but only on a UUID-family column (uuid, guid, uniqueidentifier):

__PACKAGE__->add_columns(
  id   => { data_type => 'uuid', is_auto_increment => 1 },
  url  => { data_type => 'varchar', size => 2048 },
);
__PACKAGE__->set_primary_key('id');

my $hit = $schema->resultset('Hit')->create({ url => '/a' });
$hit->id;   # the UUID this process generated and sent

Two halves, deliberately kept in sync:

  • Client side. "_prefetch_autovalues" generates an RFC 4122 version 4 UUID (from Crypt::URandom) for every such column that the insert did not supply, before the statement is built. It goes out as an ordinary bind value, so $row->id afterwards is exactly what was written -- no retrieval, no extra round-trip, no guess.

  • Server side. DBIO::ClickHouse::DDL emits DEFAULT generateUUIDv4() on the same column. Because the client always supplies a value, that default never fires for a DBIO insert; it is the safety net for rows written by anything else (clickhouse-client, a Kafka engine, a INSERT ... SELECT), so the column still fills itself.

Supplying the column explicitly always wins -- the generator only fills in what the insert left unset.

Supplying it as literal SQL is a different thing, and does not work with create:

$rs->create({ id => \'generateUUIDv7()', url => '/a' });   # throws

A literal is computed by the server, so it lands in exactly the hole this driver cannot climb out of: the row is written, and there is no RETURNING and no last_insert_id to learn the resulting key from. create promises a usable row object, so it throws (from "last_insert_id") rather than handing back the unevaluated literal ref. Use "populate" in DBIO::ResultSet in void context when you want the server to compute keys -- it never needs them back.

Anything else must be supplied

is_auto_increment => 1 on a non-UUID column is refused by the DDL layer (there is no server-side generator to call), so a table with such a key can only come from hand-written DDL, and an insert that leaves such a primary key unset cannot be completed honestly. The identity-retrieval hook "last_insert_id" therefore throws instead of returning a fabricated or undef key.

Note also that retrieve_on_insert costs a full extra SELECT here -- there is no RETURNING to piggyback on -- and that this follow-up read is not reliable under async_insert (see "INSERTS AND BATCHING"). Introspection deliberately never sets it.

INSERTS AND BATCHING

An INSERT into a MergeTree table writes a new part, which background threads then merge. Row-at-a-time inserts create one part per row and are the standard way to bring a ClickHouse table to its knees; the engine wants few, large inserts. So:

  • populate is one statement. "_insert_bulk" -- the method $rs->populate(\@rows) in void context ends in -- renders the whole dataset as a single multi-row INSERT INTO t (...) VALUES (...), (...), ... through "insert_multi" in DBIO::ClickHouse::SQLMaker, rather than core's "prepare once, execute per row". One statement, one part, one round-trip.

  • Prefer populate over a loop of create. Each create is its own INSERT and its own part.

  • Very large datasets must be chunked by the caller. The statement carries one placeholder per cell, so a big enough populate will hit the server's max_allowed_packet. Nothing here splits it; feed populate in slices of a few tens of thousands of rows.

async_insert / wait_for_async_insert

ClickHouse can do the batching itself: with async_insert the server collects incoming inserts in a buffer and flushes them as one part. That turns a stream of small inserts into acceptable write behaviour without restructuring the application. Both are session settings, so they belong in the connect information:

MyApp::Schema->connect($dsn, $user, $pass, {
  on_connect_do => [
    'SET async_insert = 1',
    'SET wait_for_async_insert = 1',
  ],
});

wait_for_async_insert decides what the INSERT statement means:

  • 1 (the ClickHouse default) -- the statement returns once the buffer was flushed. Slower, but an insert error still reaches the client as an exception, and a subsequent SELECT sees the rows.

  • 0 -- fire and forget. The statement returns as soon as the server has buffered the data. Nothing downstream can be trusted: a failure during the flush is never reported to the client, and a read immediately after the write may not see the rows. Only set it where losing an insert silently is acceptable.

This driver does not set either of them, and does not second-guess them: they are a durability trade-off only the application can make.

MUTATIONS

$rs->update, $rs->delete, $row->update and $row->delete all work, and all of them issue something that is not DML:

ALTER TABLE `hit` UPDATE `hits` = ? WHERE `id` = ? SETTINGS mutations_sync = 1
ALTER TABLE `hit` DELETE WHERE `id` = ? SETTINGS mutations_sync = 1

ClickHouse has no UPDATE and no DELETE statement; changing stored rows is a mutation, an asynchronous background job that rewrites every data part containing a matching row. "MUTATIONS" in DBIO::ClickHouse::SQLMaker renders the statement -- including the row-level form, where the WHERE clause is the row's primary key, exactly as on any other driver.

Four consequences the ORM cannot hide:

  • A mutation is not a transaction. It is not atomic with anything around it -- not with the statement before it, not with the one after it, and (for a multi-part table) not internally: other readers can see a partly-applied mutation. There is nothing to roll it back with. See "TRANSACTIONS".

  • A mutation is heavyweight. A single-row $row->update rewrites whole parts, not one row. It is a schema-maintenance operation wearing DML clothing: fine for a correction or a GDPR erasure, wrong for a per-request write path. If rows need to change often, model it instead -- append the new version and read through a ReplacingMergeTree (or FINAL, see "final" in DBIO::ClickHouse::SQLMaker), which is what the engine is built for.

  • Neither call returns a row count. See "update" below.

  • An unrestricted $rs->delete deletes everything, rendered as WHERE 1 -- a mutation must carry a filter expression, so the clause cannot simply be omitted the way DELETE FROM t omits it.

mutations_sync: synchronous by default

Left alone, a mutation is fire-and-queue: the statement returns immediately and the rows change at some later point. That breaks the one thing every piece of ORM code assumes -- that a write is visible to the read after it. So this driver appends SETTINGS mutations_sync = 1 to every mutation, which makes the statement wait until the mutation has been applied on the server it was sent to.

Override it at connect time:

MyApp::Schema->connect($dsn, $user, $pass, { mutations_sync => 2 });

2 waits for all replicas (needed when reads may land on a different replica than the write), 0 restores ClickHouse's asynchronous default, and undef emits no SETTINGS clause at all so a session-level or profile-level setting is the one that applies. Values are validated when the SQL maker is built. The full option is documented in "mutations_sync" in DBIO::ClickHouse::SQLMaker, the reasoning in docs/adr/0005-mutations-sync-by-default.md.

The default is not free: waiting is exactly as slow as the part rewrite it waits for. It buys predictable ORM semantics, not throughput -- which is the same reason the second bullet above says not to put mutations in a hot path.

TRANSACTIONS

ClickHouse has no transactions, so this storage cannot honour DBIO's transaction API. Rather than pretending, it splits the API in two:

  • Everything that only promises atomicity is a no-op, so ordinary schema code keeps working: txn_begin and txn_commit send nothing to the server ("_exec_txn_begin" / "_exec_txn_commit"), "txn_do" runs its coderef without a BEGIN/COMMIT bracket, and a scope guard ("txn_scope_guard") neither brackets nor rolls back. Each of the two emits a one-time warning per interpreter (carp_once) saying that nothing is atomic. Core's transaction_depth bookkeeping is kept intact, so nesting stays predictable.

  • Everything that claims work was undone throws: "txn_rollback" always throws, because a silent no-op there would be an active lie -- the caller would believe the changes are gone while every statement is in fact still in the table. $guard->rollback is the same statement written on a guard, and throws for the same reason. This is also why a failing "txn_do" carps loudly (every time, not once) that nothing was rolled back before re-throwing the original exception unchanged.

Scope guards

DBIO::Storage::TxnScopeGuard is not just a user-facing tool -- core takes one on your behalf in delete_all, update_all, multi-create ($rs->create with related rows), cascade delete, and populate in non-void context or with related data. Its DESTROY rolls back, and that path is structurally indistinguishable from a caller writing $guard->rollback.

"txn_scope_guard" therefore hands out DBIO::ClickHouse::Storage::TxnScopeGuard, which is core's guard with DESTROY replaced: it unwinds transaction_depth and carps that nothing was rolled back, rather than throwing from inside someone else's stack unwind. So:

  • The guarded operations run -- unguarded in effect, exactly like "txn_do". A failure part-way through delete_all leaves the rows it already deleted deleted. That is true of every mutation on this engine, guard or no guard.

  • The original exception arrives untouched. Core's DESTROY catches the refusal from "txn_rollback" and folds it into the in-flight error -- mutating a DBIO::Exception in place to "Transaction aborted: <original Rollback failed: ...">, once per guard on the stack. A delete_all inside a caller's own guard nests three deep, so the real error used to arrive behind three such prefixes. It no longer does.

  • Every abnormal unwind carps (not once -- each is its own partial write), naming the only fact that matters: nothing was rolled back. A guard that reaches ->commit normally is silent.

  • $guard->rollback still throws. Inherited unchanged from core, it routes to "txn_rollback".

One bulk path takes no guard at all any more: "_insert_bulk" issues a single multi-row statement, which no rollback could improve on, so a void populate of plain column data never touches the transaction machinery -- no warning, and an insert error propagates as itself. (A populate carrying related rows still falls back to per-row create, and is guarded like any other multi-create.)

See docs/adr/0003-no-transactions-policy.md.

EXISTS QUERIES

"results_exist" in DBIO::ResultSet and its two siblings work on this driver: the storage declares the supports_exists_query capability (ADR 0037) and inherits core's rendering of "_exists_value_sql" in DBIO::Storage::DBI unchanged, because ClickHouse accepts it as written:

SELECT CASE WHEN EXISTS (SELECT 42 FROM `hit` `me` WHERE `url` = ?)
       THEN 1 ELSE 0 END

ClickHouse documents EXISTS(subquery) as an operator that returns 0 or 1, not merely a predicate, and its own example selects two of them into a select list of a FROM-less SELECT -- which is exactly the shape above. It needs ClickHouse 21.11 or newer ("Support EXISTS(subquery)"); CASE WHEN ... THEN ... ELSE ... END is older than that and is lowered internally to multiIf().

The subquery may not be correlated

The one restriction, and it is the engine's, not this driver's: ClickHouse's EXISTS documentation states that references to main query tables and columns are not supported in a subquery. So "results_exist_as_query" in DBIO::ResultSet is usable here as a standalone scalar subquery, but not in the correlated role core's own documentation suggests for it -- $rs->correlate(...)->results_exist_as_query builds a fragment this engine will reject. (Newer servers carry a allow_experimental_correlated_subqueries setting; nothing here relies on it.) Use "has_rows" in DBIO::ResultSet or a join if you need the correlation.

The declaration itself is a class fact and needs no connection, so the statement is rendered without one, like every other engine-specific fragment this storage answers for.

SET OPERATIONS

All six of DBIO::ResultSet's set operations work on this driver -- nothing is refused -- but three of them are spelled differently here, through the "_set_operation_keyword" in DBIO::Storage::DBI hook (ADR 0039):

$rs->union          # UNION DISTINCT       (not UNION)
$rs->union_all      # UNION ALL
$rs->intersect      # INTERSECT DISTINCT   (not INTERSECT)
$rs->intersect_all  # INTERSECT ALL
$rs->except         # EXCEPT DISTINCT      (not EXCEPT)
$rs->except_all     # EXCEPT ALL

ClickHouse has every one of these operators. What it does not share is what the bare keyword means. In ANSI SQL, UNION, INTERSECT and EXCEPT without a modifier are the duplicate-eliminating forms; on ClickHouse a bare keyword carries no fixed meaning of its own at all -- it is resolved from a session setting:

  • A bare UNION is an error by default. union_default_mode ships as the empty string, and the UNION documentation states that "if you use UNION with union_default_mode setting to empty string, it will throw an exception" -- the default was deliberately changed to throw in 21.1. Core's ANSI keyword would not merely mean the wrong thing here, it would not run.

  • A bare INTERSECT or EXCEPT means ALL. intersect_default_mode and except_default_mode both default to 'ALL', which is why the EXCEPT documentation warns that "the result of EXCEPT can contain duplicate rows. Use EXCEPT DISTINCT if this is not desirable". Core's ANSI keyword would run and quietly hand back duplicates from an operation that promised distinct rows -- the silent kind of wrong, and the reason this is a spelling and not a refusal.

Spelling all six explicitly also makes the emitted statement independent of those three settings: a deployment that sets union_default_mode = 'ALL' in its profile does not thereby turn $rs->union into a UNION ALL behind the application's back.

Version floor

UNION ALL is as old as ClickHouse; UNION DISTINCT arrived with union_default_mode in 21.1; INTERSECT and EXCEPT in 21.10. The ALL / DISTINCT modifiers on INTERSECT and EXCEPT, and the two settings that go with them, arrived in 22.9 -- before that the parser accepted INTERSECT and EXCEPT with no modifier at all. So four of the six operations as this driver spells them need ClickHouse 22.9 or newer. The last release without them, 22.8 LTS, left support in 2023.

That floor is unconditional on purpose. The hook must answer without a connection (ADR 0039 section 7) -- the keyword is baked into the query at construction time -- so it cannot ask which server version it is talking to. A driver that hedged downwards would emit the ANSI spelling, which is wrong on every ClickHouse version, in order to be right on none.

RANDOM ORDERING

"rand" in DBIO::ResultSet works on this driver, ordering by rand() through the "_random_function" in DBIO::Storage::DBI hook:

$schema->resultset('Artist')->rand(3);
# SELECT ... FROM `artist` `me` ORDER BY rand() LIMIT 3

Core's inherited default is RANDOM(), which ClickHouse does not have. Its random functions are rand (alias rand32), rand64, randCanonical, randConstant and the distribution family (randUniform, randNormal, ...) -- there is no random among them, so inheriting the default would throw an unknown-function error naming RANDOM. Loud rather than silent, but still an outage for a feature this engine has.

Why rand() and not randCanonical()

rand() returns a UInt32; randCanonical() returns the Float64 in [0, 1) that the name RANDOM() suggests elsewhere. The hook has exactly one consumer in core -- "rand" in DBIO::ResultSet, which drops the string into an order_by literal and does nothing else with it -- so the value is never selected, never compared against a threshold and never handed to the caller. Only the induced order is observable, and a uniform UInt32 orders rows exactly as uniformly as a uniform Float64 does.

What differs is the price. randCanonical arrived in 22.11; rand has been there since 1.1. Choosing the float would put a version floor under $rs->rand to buy a property nothing at the call site can observe -- unlike "SET OPERATIONS", where the floor buys a statement that is otherwise wrong. Should a caller ever need this hook's value as a number (a WHERE rand() < 0.01 sampling predicate, say), that is a different hook and the floor gets paid then.

The one visible consequence of UInt32: it has 2**32 distinct values, so past roughly 77,000 rows a tie is more likely than not, and tied rows sort in an unspecified order relative to each other. For a random pick that is not a defect.

It is evaluated per row -- which is the thing that had to be checked

This hook is worth nothing if the engine computes the function once per statement instead of once per row: ORDER BY over a per-statement constant is a silent non-shuffle -- the same rows in the same order, no error anywhere. (That is a real finding elsewhere in this family, on Sybase ASE.) ClickHouse documents the opposite, in three independent places:

  • rand is a regular function, and regular functions "work as if they are applied to each row separately (for each row, the result of the function does not depend on the other rows)".

  • Random numbers are the documented exception to constant folding: "Almost all functions return a constant for constant arguments. The exception is functions that generate random numbers." now() is named right there as the counter-example that is treated as constant within one query -- rand() explicitly is not.

  • ClickHouse ships the per-statement variant as a separate, separately named function, randConstant(): "a single random value that remains constant across all rows in the current query execution". The non-shuffle has to be asked for by name; it cannot be walked into.

Common subexpression elimination does apply: two textually identical rand() calls in one query are one expression, evaluated once per row, which is what the optional ignored argument (rand(1)) exists to break. It does not touch $rs->rand, which emits exactly one call.

Unverified against a live server -- none was reachable when this was written -- so the above is ClickHouse's documented behaviour, not observed behaviour.

Cost

ORDER BY rand() sorts the whole result set before the LIMIT, which on a MergeTree table of any size is the expensive way to take a few rows. ClickHouse's cheap alternative is SAMPLE, which needs a sampling expression in the table's own definition and which this driver deliberately does not implement -- use the literal-SQL escape hatch if you need it.

QUERY PLANS

"explain" in DBIO::ResultSet works here through core's inherited implementation. This storage does not override "_explain_query" in DBIO::Storage::DBI, and the absence is a decision, not an omission: ADR 0040 names ClickHouse as one of the engines the base fits, and that was checked here rather than taken on trust.

$schema->resultset('Artist')->search({ name => 'x' })->explain;
# EXPLAIN SELECT `me`.`artistid`, ... FROM `artist` `me` WHERE `name` = ?

ClickHouse's EXPLAIN takes an optional type:

EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ANALYZE | ESTIMATE
         | TABLE OVERRIDE] [setting = value, ...] SELECT ...

and the type this hook wants is the one you get by omitting it. A bare EXPLAIN is EXPLAIN PLAN: the query execution plan, one String row per plan line in a single column named explain. That is exactly what core's base implementation produces, so an override could only re-spell what is already right. All four assumptions ADR 0040 section "the finding" lists hold on this engine -- obtaining the plan is one statement, it is executed with the query's own binds, it returns rows, and those rows are the plan.

The other types answer other questions, and none of them is what explain promises: AST and SYNTAX dump the parsed / rewritten query rather than its execution, QUERY TREE the analyzer's tree, PIPELINE the processor graph below the plan, ESTIMATE a row/mark/part count (and it is the one type whose result is not a single explain column), TABLE OVERRIDE a schema validation -- and ANALYZE runs the query, which explain must not.

So nothing here is silent the way SQLite's bare EXPLAIN is, where the base implementation is accepted and answers with VM bytecode. On ClickHouse the base implementation asks for the plan and gets the plan.

The row contents are engine-specific, as on every engine (ADR 0040 section 2): they are ClickHouse plan lines (Expression, ReadFromMergeTree, ...) whose format has changed between releases -- 26.7 turned the pretty / actions / compact rendering on by default. Read them; do not parse them.

Unverified against a live server, like "RANDOM ORDERING": established from the EXPLAIN documentation, not from a running ClickHouse.

ACCESS PATH: DBD::MYSQL AND DBD::MARIADB

Since core's ADR 0059 a declared storage is held against the DBD its DSN names: when "register_driver" in DBIO::Storage::DBI maps that DBD to a storage class this one is not related to by inheritance, the DSN is taken to lead through another driver, and that is reported through "_driver_mismatch" in DBIO::Storage::DBI -- a warning by default, an exception under "driver_strict" in DBIO::Schema. Core registers mysql and MariaDB for the MySQL storages.

The transport of this driver is another driver's DBD (docs/adr/0001-mysql-wire-protocol-transport.md), and the storage deliberately does not inherit that driver's class (docs/adr/0002-standalone-dist-not-mysql-subclass.md). So the storage declares both DBDs as its own through core's "register_access_path_dbd" in DBIO::Storage::DBI: they are accepted in silence, also under driver_strict, and nothing else is -- a dbi:Pg: DSN on a ClickHouse schema is still reported by core.

It is decided from the DBD name alone -- no connection, no query. Whether the server behind a dbi:mysql: DSN really is a ClickHouse is not asked here; a DSN cannot tell ("The storage must be pinned explicitly" in DBIO::ClickHouse). t/07-transport-dbd.t pins the behaviour and the declaration.

SEE ALSO

AUTHOR

DBIO Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.