/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse::Diff::Column

NAME

DBIO::ClickHouse::Diff::Column - Diff operations for ClickHouse columns

VERSION

version 0.900004

DESCRIPTION

Column-level diff operations for ClickHouse: ALTER TABLE ... ADD COLUMN, ALTER TABLE ... DROP COLUMN and ALTER TABLE ... MODIFY COLUMN.

Columns of a brand-new table are emitted inline by DBIO::ClickHouse::Diff::Table (a ClickHouse table cannot be built up from nothing with ADD COLUMN -- it needs its engine and sorting key at CREATE time), so this module only ever sees tables present in both models.

MODIFY COLUMN, not a rebuild

MODIFY COLUMN covers almost every type change: widening an integer, String to FixedString, adding or removing Nullable, changing a Decimal scale. ClickHouse rewrites the affected parts as an asynchronous mutation -- the statement returns before the data has been converted, and system.mutations is where you watch it finish. So an upgrade that returns successfully means the change was accepted, not that it is done.

The changes MODIFY COLUMN cannot make are the ones that touch the sorting key, the partition key or the engine. Those never reach this module: they are refused up front by "check_rebuild_required" in DBIO::ClickHouse::Diff::Table.

Nullability is not a column property here

There is no SET NOT NULL / DROP NOT NULL to emit. ClickHouse columns are NOT NULL by default and nullability lives inside the type (Nullable(T)), so a nullability change is a type change and comes out as a single MODIFY COLUMN. The model's not_null field is derived from the type string by DBIO::ClickHouse::Introspect::Columns and is deliberately not compared -- it cannot differ unless data_type already did, and comparing it would only produce a second, redundant operation.

Nullable to NOT NULL is the one change the data can break

A MODIFY COLUMN from Nullable(T) to a non-nullable type fails in the background mutation as soon as one part holds a NULL -- and by then the metadata is already switched: the column is unreadable, every later mutation on the table hangs behind the broken one, and the NULLs can no longer be counted. The diff itself sees no data and renders the statement unchanged, so as a dry run (as_sql, summary) it stays data-free. Such an op instead answers "needs_null_check" and renders its precondition with "null_check_sql"; "_check_before_apply" in DBIO::ClickHouse::Deploy runs it before the first statement of the plan and refuses when it finds NULL rows. Besides, such a change is "lossy", so the plan runs only with allow_destructive.

What is compared, and what is left alone

Compared: data_type (the native ClickHouse type string) and default_value (the raw default expression).

changed_column carries one rule beyond core's desired-state contract: both sides here are introspected from a server, so a target without a default means "declared none", and a live plain DEFAULT is a change.

Not compared, on purpose:

  • comment -- DBIO::ClickHouse::DDL never emits column comments, so the desired side always reports none. Comparing it would drop every comment on the live database at the first upgrade.

  • compression_codec -- same reason. A hand-tuned CODEC(ZSTD(3)) is not something a portable schema prescribes, and clearing it silently would cost disk on every part written afterwards.

  • position -- ClickHouse can reorder with MODIFY COLUMN ... AFTER, but column order only affects SELECT *, and a reorder rewrites metadata for every part. Not worth an automatic mutation.

Type comparison is case-preserving

Core's "norm_type" in DBIO::Diff::Compare upper-cases before comparing, which is right for engines whose type strings are pure syntax. ClickHouse type strings carry string literals: Enum8('draft' = 1, 'live' = 2), DateTime64(3, 'UTC'). Upper-casing those would make 'draft' and 'Draft' the same type and silently swallow an enum rename. So "norm_clickhouse_type" collapses whitespace -- the only thing the server varies -- and leaves case alone.

Known phantom-diff sources

Both sides of a test-deploy-and-compare diff are introspected from a real server, so the native type strings are rendered by the same code and compare cleanly. One case is worth knowing about anyway:

  • Do not hand-build the target from column_info. The DDL layer collapses the whole varchar / char / text / json family onto String, and introspection can only report one of them back. A target model carrying portable DBIO type names rather than native ClickHouse ones will differ from the live side on every string column, forever. Build the target by deploying and introspecting, which is what DBIO::ClickHouse::Deploy does.

Bare Date / DateTime used to belong here: the DDL layer lower-cased data_type before its type-map lookup, so a column declared data_type => 'Date' deployed as Date32 and reappeared as a MODIFY COLUMN on every run. It now passes an exact native spelling through untouched, so a live Date column and a schema asking for Date agree. A Date-against-Date32 diff is a genuine type change today, and converges after one apply.

ATTRIBUTES

lossy_pairs

my %pairs = %DBIO::ClickHouse::Diff::Column::LOSSLESS_PAIRS;

The changes of the base type that MODIFY COLUMN carries out without loss, as the old->new keys (lower case) "column_change_lossy" in DBIO::Diff::Compare reads. The type is taken without its Nullable(...) and LowCardinality(...) wrappers and without its parameters:

  • Int8 < Int16 < Int32 < Int64 < Int128 < Int256, and the same ladder of the UInt types: a wider integer of the same signedness holds every value of the narrower one.

  • Date to Date32 (the range of Date lies within that of Date32) and DateTime to DateTime64 (whole seconds are exact).

Beyond the pairs, a type that stays the same is lossless when its parameters only grow ("column_change_lossy" in DBIO::Diff::Compare): Decimal(P, S) with the same scale and a larger precision (Decimal32 to Decimal256 are read as Decimal(9|18|38|76, S)), FixedString(N) with a larger N, DateTime64(P) with a larger precision and the same time zone, and Enum8/Enum16 whose elements are the old ones with the same values and more behind them (Enum8 to Enum16 included). LowCardinality(...) around the same type is no change of value, and a column that gains Nullable(...) keeps every value.

Every other change is lossy, in doubt it is: Float32 to Float64 alters the decimal reading of a value, an integer to a float or a signed to an unsigned type rewrites it, String to FixedString and back, Nullable(T) to a non-nullable type (the NULLs become the zero value of the type) and every change inside Array, Tuple, Map and the other compound types.

Source: https://clickhouse.com/docs/sql-reference/statements/alter/column#modify-column (a changed type is converted as by the toType functions).

METHODS

norm_clickhouse_type

my $t = norm_clickhouse_type('Decimal(10,  2)');   # 'Decimal(10, 2)'

Normalizes a native ClickHouse type string for comparison: collapses runs of whitespace to a single space and trims the ends. undef becomes the empty string. Case is preserved; see "Type comparison is case-preserving".

changed_column

my @changed = changed_column($old_info, $new_info);

Returns the list of column fields that differ, or the empty list when the column is unchanged. data_type is compared with "norm_clickhouse_type", default_value through core's "changed_fields" in DBIO::Diff::Compare, plus the removal of a live plain DEFAULT the target no longer declares.

A column whose live side is MATERIALIZED, ALIAS or EPHEMERAL and whose target side prescribes no kind of its own is reported as unchanged. Such a column is computed, not stored as given, and this driver cannot render those clauses from a desired-state model; emitting a bare MODIFY COLUMN for it would strip the expression and turn a derived column into an empty one.

is_nullable_type

is_nullable_type('LowCardinality(Nullable(String))');   # true

True when the native type string is nullable at the top level, i.e. Nullable(T), optionally wrapped in LowCardinality(...). Nullable inside Array, Tuple or Map is not top-level and not reported.

diff

my @ops = DBIO::ClickHouse::Diff::Column->diff(
  $source_columns, $target_columns,
  $source_tables,  $target_tables,
  $storage,                                # optional, drives IF EXISTS
);

Compares the columns sections of two models, scoped to tables present in both tables sections. Members are keyed by column_name (the model stores them as an ordered arrayref). $storage threads through to the constructed ops; see "as_sql".

as_sql

Renders one ALTER TABLE statement. The column definition itself goes through "column_def" in DBIO::ClickHouse::DDL::Emit, so an added column is spelled exactly as the same column would be in a fresh CREATE TABLE.

MODIFY COLUMN restates the type and, when the target prescribes one, the default. A target that declares no default while the live column has a plain DEFAULT gets it removed: ClickHouse keeps the old expression when the clause is absent, so the removal is a statement of its own, MODIFY COLUMN ... REMOVE DEFAULT, emitted before a type change. A computed live column (MATERIALIZED, ALIAS, EPHEMERAL) is still left alone.

removes_default

True for an alter whose live column carries a plain DEFAULT the target does not: rendered as MODIFY COLUMN ... REMOVE DEFAULT ("alter_table_remove_default" in DBIO::ClickHouse::DDL::Emit). The rows keep what they were written with.

lossy

True for an alter that can alter or lose stored values, so that "apply" in DBIO::Deploy::Base runs it only with allow_destructive. The two data_type strings are taken apart -- Nullable(...) and LowCardinality(...) off, base type and parameters read -- and the base types are judged by "column_change_lossy" in DBIO::Diff::Compare with the pairs of "lossy_pairs". Besides:

  • Nullable(T) to a non-nullable type is lossy (NULL becomes the zero value of the type); a column that gains Nullable(...) is not;

  • a time zone that differs (DateTime, DateTime64) is lossy;

  • an Enum whose elements are not the old ones, same names with the same values, with more behind them is lossy;

  • a type whose parameters are not understood (Array, Tuple, Map, ...) is lossy unless it is spelled the same.

A change of the default alone keeps the type, and with it every value: not lossy. Other actions are not lossy.

needs_row_check

True for an add of a column that is not Nullable and has no default of its own: the server would fill the rows the table already holds with the type's zero value ('', 0), a value nobody wrote. "_check_before_apply" in DBIO::ClickHouse::Deploy asks "row_check_sql" before the first statement and refuses when the table holds rows. A column with a default (any kind) or a Nullable one needs no check.

row_check_sql

The precondition of a "needs_row_check" op: a read-only probe for one row of the table. The diff never runs it.

needs_null_check

True for a MODIFY COLUMN that turns a nullable column into a non-nullable one -- the only op whose success depends on the data. See "Nullable to NOT NULL is the one change the data can break".

null_check_sql

The precondition of a "needs_null_check" op: a SELECT count() of the rows whose column is NULL. Read-only; the diff never runs it.

summary

One indented line naming the column, its table, the target type, and whether it is added (+), dropped (-) or modified (~).

ADD COLUMN and DROP COLUMN each accept a guard from DBIO::ClickHouse::DDL::Emit; the flag is populated here from "should_emit_if_exists" in DBIO::Diff::Op on the op's stashed storage, so a re-run after a partial failure becomes a no-op rather than erroring on the already-applied statement. Two pitfalls are deliberate, not bugs:

  • MODIFY COLUMN never carries a guard: t/38 confirms ClickHouse has no ALTER TABLE ... MODIFY COLUMN IF [NOT] EXISTS form, so a re-apply genuinely has to repeat the MODIFY. The applier does not silently skip the step -- the danger is the opposite, that a type change silently survives.

  • ALTER TABLE ... ADD COLUMN IF NOT EXISTS swallows a column that already exists with a different type as a silent no-op. The flag here is only set when the F12 capability is on; the actual type-change safety net on a real type shift is the MODIFY COLUMN that this same diff will emit afterwards.

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.