Add --dump-schema option to clickhouse-client and clickhouse-local - #114098

@groeneai, please check this one. If you won't have any major findings, please tag someone from the ClickHouse team to review this PR.

Reviewed. One blocker, then some smaller notes.

Blocker: views and materialized views are not dependency-ordered, so the dump does not replay

orderTablesByDependencies orders by system.tables.loading_dependencies_*, but that column is empty for View and MaterializedView. The view-to-source edge is only recorded in the reverse direction, on the source table's dependencies_table. So every view lands in dependency level 0 alongside its own source table, and the (database, name) tie-break decides the output order. Whenever a view sorts before its source table by name, the dump is emitted in an unreplayable order.

Reproduced on master (26.8.1.1), realistic names, no cycle involved:

CREATE DATABASE analytics;
CREATE TABLE analytics.raw_events (ts DateTime, uid UInt64) ENGINE = MergeTree ORDER BY ts;
CREATE MATERIALIZED VIEW analytics.daily_agg (day Date, cnt UInt64) ENGINE = SummingMergeTree ORDER BY day
    AS SELECT toDate(ts) AS day, count() AS cnt FROM analytics.raw_events GROUP BY day;

loading_dependencies_table is [] for both daily_agg and raw_events; raw_events.dependencies_table is ['daily_agg']. d sorts before r, so the dump emits CREATE MATERIALIZED VIEW analytics.daily_agg first, and replaying it into a fresh instance gives:

Code: 60. DB::Exception: Unknown table expression identifier 'analytics.raw_events'
in scope SELECT toDate(ts) AS day, count() AS cnt FROM analytics.raw_events GROUP BY day. (UNKNOWN_TABLE)

Same failure for a plain CREATE VIEW whose source sorts after it. Ordering base tables before views fixes it: I replayed a fixture with an inner-table MV, a TO MV and a plain view in that order and it applied cleanly, with SELECT returning correct rows through all three.

Two things fall out of this:

• The TO target is not covered by either dependency column. For CREATE MATERIALIZED VIEW ... TO tgt, both loading_dependencies_table and dependencies_table are empty on the view and on tgt, so nothing orders the view after its target, and replaying the view first fails with Table d.z_target does not exist. system.tables.target_database/target_table do carry it, so that edge is available.
• The test passes by name luck, not by dependency tracking. In 04836, the fixture is base, mv, dict. mv has empty loading_dependencies_table, so it sits in level 0 with base, and base happens to sort first alphabetically, which satisfies the base_line -lt mv_line assertion. Rename base to z_base (or the MV to a_mv) and the assertion fails. dict is the only table in the fixture whose ordering is actually exercised, since a dictionary is the one object type here that does populate loading_dependencies_*. Worth adding a case where the view sorts before its source.

Smaller notes

• A user table literally named .inner.foo is silently dropped from the dump. CREATE TABLE d.\.inner.mytable` (...)succeeds on master, and theNOT startsWith(name, '.inner.')filter then excludes it, so the dump is quietly incomplete rather than wrong. Matching the implicit-storage naming more precisely (the real inner tables are.inner_id., and .inner.` only for the pre-UUID form) would narrow it. Low priority, but it is a silent omission.
• The test only ever invokes $CLICKHOUSE_LOCAL, never $CLICKHOUSE_CLIENT, so the programs/client/Client.cpp path and its processConfig() validation are untested. The two entry points have separately written validation blocks, so a divergence between them would not be caught.
• On the case-insensitive-filesystem guard you flagged: escapeForFileName percent-escapes every non-word-character byte but leaves ASCII letters as-is, so db and DB do collide after escaping and the guard is doing real work. It reads correct to me.

Given the blocker I have not tagged a reviewer yet. Ping me once the ordering is fixed and I will route it.

@groeneai Fixed, thanks for the very thorough repro — I reproduced your exact case locally (analytics.raw_events/analytics.daily_agg) and confirmed both the failure before the fix and a clean replay after it.

Root cause was exactly what you found: loading_dependencies_* is empty for View/MaterializedView, so both landed in dependency level 0. The fix combines every dependency signal system.tables exposes — loading_dependencies_* (dictionaries), inverted dependencies_* (a materialized view's source, recorded only on the source row), target_database/target_table (the TO target you flagged as uncovered, explicit or implicit), and — since the server doesn't track this for plain views at all (confirmed: DROP TABLE on a view's source succeeds even with the view still referencing it) — a best-effort scan of each view's stored SELECT text for other dumped tables' names.

Also fixed the .inner.foo false-positive you flagged: a table is now only excluded when it's confirmed to be a live materialized view's own target with the generated-name shape, not merely name-shaped like one.

Test (04836_client_dump_schema.sh) is substantially strengthened: renamed the fixture so alphabetical order can't accidentally pass the assertion, added plain views, a view-on-view chain, an explicit TO target, the .inner.foo case, and a clickhouse-client invocation alongside the existing clickhouse-local ones (your test-coverage note). Each new case is round-tripped through an actual replay, not just checked for line order.

One thing I want to flag rather than claim silently: for plain-view-on-plain-view (and view-on-table) ordering, there's no server-tracked signal at all, so I'm relying on a text scan of the resolved, server-formatted SELECT (which is reliably fully-qualified even when the CREATE VIEW wasn't). It's reliable for straightforward FROM/JOIN references but can in principle miss one hidden behind a subquery, CTE, or table function — happy to discuss if you'd rather that residual case fail loudly instead of best-effort.

Correction to my earlier caveat: I did reproduce the case-collision guard — mounted a case-sensitive APFS volume to stand in for a Linux source, created Foo/foo there, dumped with --dump-schema-dir:

Code: 36. DB::Exception: Cannot dump to `--dump-schema-dir`: databases Foo, foo would all be written to the same file on a case-insensitive filesystem. (BAD_ARGUMENTS)

Note: the guard isn't destination-aware — it fires unconditionally regardless of the actual target filesystem, by design (portably detecting case-sensitivity isn't worth it for a dump meant to be replayed elsewhere anyway).

Answering your question directly: I would not pick either contract, because the text scan does not only under-collect. It also over-collects, and that direction is worse: a spurious edge fabricates a dependency cycle, and checkNoCyclicDependencies() then aborts the entire dump with INFINITE_LOOP for a schema that has no cycle at all. So the current behaviour is not "graceful in the miss direction"; it can refuse to dump a perfectly ordinary database.

Two distinct false-positive classes, both reproduced on master (26.8.1.1) by running the predicate at SchemaDumper.cpp:262 against real server-produced as_select text.

1. Substring, not identifier, matching. find(qualified_name) matches inside a longer identifier, so any table whose qualified name is a prefix of another's yields a bogus edge. Ordinary names are enough:

CREATE TABLE analytics.metrics_source    (a UInt64) ENGINE=MergeTree ORDER BY a;
CREATE TABLE analytics.metrics_daily_raw (a UInt64) ENGINE=MergeTree ORDER BY a;
CREATE VIEW  analytics.metrics       AS SELECT * FROM analytics.metrics_daily_raw;
CREATE VIEW  analytics.metrics_daily AS SELECT * FROM analytics.metrics_source;

metrics matches analytics.metrics_daily inside its own SELECT, and metrics_daily matches analytics.metrics inside its own SELECT, so the two views become mutually dependent and the dump fails. Neither view references the other. Confirmed against the server's own text rather than a reimplementation:

SELECT v.name, o.name FROM system.tables v CROSS JOIN system.tables o
WHERE v.engine='View' AND NOT (v.name=o.name)
  AND position(v.as_select, concat(o.database,'.',o.name)) > 0

metrics         metrics_daily        <-- bogus
metrics         metrics_daily_raw
metrics_daily   metrics              <-- bogus
metrics_daily   metrics_source

2. String literals. A qualified name inside a literal matches too, and this class survives any word-boundary tightening, so tightening the match is not a fix:

CREATE VIEW d.audit_orders AS SELECT a, 'd.audit_users'  AS src FROM d.orders;
CREATE VIEW d.audit_users  AS SELECT a, 'd.audit_orders' AS src FROM d.users;

Same outcome: fake mutual dependency, whole dump refused.

Suggested direction, which removes your question rather than answering it: collect referenced tables from the parsed AST instead of the text. ClientBase.cpp already includes the parsers, and an ASTTableIdentifier walk resolves exactly the cases the scan cannot see. Verified with EXPLAIN AST:

SELECT * FROM (SELECT * FROM d.base2)             -> TableIdentifier d.base2
WITH c AS (SELECT * FROM d.base2) SELECT * FROM c -> TableIdentifier d.base2
SELECT a, 'd.audit_users' AS src FROM d.orders    -> Literal, not an identifier

So subqueries and CTEs (the residual you flagged) resolve correctly, and both false-positive classes disappear. Better still, getDependenciesFromCreateQuery in src/Databases/DDLDependencyVisitor.h is the extractor the server itself uses for this purpose, and it also covers table functions and dictGet; it takes a ContextPtr, so if that is awkward for a client talking to a remote server, a local identifier walk is the fallback. One caveat if you hand-roll it: a CTE alias also parses as a TableIdentifier (WITH orders AS (SELECT 1) SELECT * FROM orders gives TableIdentifier orders), so skip names bound by an enclosing WITH, or you reintroduce a spurious edge.

Smaller notes:

• The test cannot catch either class today: every fixture name is aaa_*/zzz_*, so no qualified name is a substring of another, and there is no literal case. A prefix-collision pair like the one above is worth adding, since the suite currently passes with this present.
• Same shape as the .inner. case you fixed: .tmp.inner. is still filtered by name at SchemaDumper.cpp:189, and a user table named .tmp.inner.x is creatable on master (verified), so it would be silently dropped from the dump.

Thanks for the ordering and .inner.foo fixes, and for confirming the case-collision guard. Since this one blocks a plain dump, I am holding off on tagging a ClickHouse team reviewer again; ping me once it is resolved and I will route it.

@groeneai Fixed — you're right that the substring scan was worse than the original bug, and your two repro classes were exactly the right proof. Replaced it with a proper parse: as_select is reparsed with ParserSelectWithUnionQuery (same grammar the server used to produce it), and every ASTTableIdentifier in the resulting tree is matched against the known table set by (database, name) equality instead of text search.

Your CTE-alias caveat turned out to be free: I confirmed empirically that a CTE alias reference stays unqualified in server-formatted SQL (WITH c AS (SELECT * FROM d.base2) SELECT * FROM c stores as_select with FROM c untouched, while d.base2 is fully qualified), so filtering to identifiers with a non-empty database part excludes CTE aliases without needing to track WITH-bound names separately — that alone also kills the string-literal false positive, since a literal parses as ASTLiteral, never ASTTableIdentifier.

Went with the local AST-walk fallback you flagged, not getDependenciesFromCreateQuery — confirmed it takes a ContextPtr, which a remote clickhouse-client connection doesn't have.

Verified against both your exact repros (metrics/metrics_daily_raw prefix collision, audit_orders/audit_users literal collision) directly against the built binary — both now dump cleanly with no INFINITE_LOOP, correct ordering preserved for the genuine dependencies. Also fixed .tmp.inner_id.*/.tmp.inner.* the same way as the .inner.foo case: only excluded when traceable to a live materialized view's own uuid/name via system.tables.uuid, so a literal user table with that prefix stays in the dump.

Test now covers both false-positive classes, a genuine view-on-view/view-on-table chain (round-tripped through actual replay), and the .tmp.inner. literal case.

The AST walk is the right call, and I verified all three things you claimed. The empty-database-part filter (SchemaDumper.cpp:321) does make the CTE-alias case free. getDependenciesFromCreateQuery does take a ContextPtr (DDLDependencyVisitor.h:26), so the local walk is correct for a remote client. Both of my round-2 false-positive classes are now dead by construction.

One new blocker, same class as round 1.

A materialized view with two or more sources is ordered by only one of them.

The AST scan is gated if (row.engine != "View") continue; (SchemaDumper.cpp:302), so MV rows never reach it, and an MV's ordering rests entirely on the inverted dependents edge. That edge carries at most one source, because the server records mv_from_dependency as a single std::optional<StorageID> (DDLDependencyVisitor.h:18), not a set.

Measured on master 26.8.1.1:

CREATE VIEW d.zzz_view AS SELECT id, b FROM d.base;
CREATE MATERIALIZED VIEW d.aaa_join_mv ENGINE = MergeTree ORDER BY id AS
  SELECT s.id AS id, s.a AS a, v.b AS b FROM d.src AS s INNER JOIN d.zzz_view AS v ON s.id = v.id;

dependencies_table is ['aaa_join_mv'] on d.src only; d.zzz_view gets no edge. MV and view both land in level 1, the (database, name) tie-break emits aaa_join_mv first, and replay fails:

Code: 60. DB::Exception: Unknown table expression identifier 'd.zzz_view' ... (UNKNOWN_TABLE)

Emitting the view first replays cleanly and data flows through the MV, so it is the ordering and not the fixture. Reproduces the same way with UNION ALL instead of the join, and with a TO-target MV.

Two precisions so you do not over-fix, both measured:

• It is specifically the second and later sources. A single-source MV reading a view does get its edge, so the tracked-source path is fine.
• It only bites when the untracked source outranks level 0. An MV over two plain tables replays fine, since both sources sort into level 0 anyway. The shape that breaks is: two or more sources, at least one of them a view (or anything else carrying its own dependencies) that sorts after the MV by name.

Fix direction: the scan you already wrote is the right tool, just gated too narrowly. Dropping the engine != "View" gate so MV rows are scanned too gives the MV its missing edge from its own as_select, which is populated for MVs including TO-target ones (I checked that on every shape above). The known_tables and self-reference guards at SchemaDumper.cpp:324 already cover the spurious-edge classes, and the TO-target edge is separate and already correct.

The test cannot catch this: 04836_client_dump_schema.sh has no JOIN and no UNION, and every MV in it reads zzz_source, a plain MergeTree table. Worth adding an MV that joins a table and a view, and one over UNION ALL with a view, each with the second source named so it sorts after the MV, round-tripped through an actual replay. Both should fail against the current head.

Holding the reviewer routing one more round, since your condition was no major findings. Ping me once this is in and I will route it.

@groeneai Fixed — good catch, and the precision on when it does/doesn't bite (second-and-later sources, only when the untracked one outranks level 0) made this trivial to scope correctly.

Went with your exact fix direction: widened the if (row.engine != "View") gate to also let MaterializedView rows through the AST scan. Confirmed empirically before touching anything: as_select for an MV never mentions its own name, implicit storage, or TO target (verified on a TO-target MV too — identical as_select to the non-TO case), so there's no conflict with the already-correct target_database/target_table edge. A duplicate edge to the already-tracked single source is harmless — TablesDependencyGraph::addDependencies collects into an unordered_set before storing.

Verified against your exact JOIN repro (d.src/d.zzz_view/d.aaa_join_mv) directly against the built binary: zzz_view now dumps before aaa_join_mv, replay succeeds. Test now covers both the JOIN and UNION ALL shapes, each with the untracked source named to sort after the MV, round-tripped through an actual insert+select (confirmed the UNION ALL view-sourced branch not firing on insert is a separate, pre-existing ClickHouse MV limitation — only the table-sourced branch is expected to populate — not something this fix touches).

Round-4 blocker is discharged. I verified the widened gate at SchemaDumper.cpp:302 against my exact JOIN repro and the UNION-with-a-view shape: zzz_view now lands at level 1 and both MVs at level 2, the dump replays with RC=0, and data flows through both MVs. Negative control: restoring the old engine != "View" gate puts both MVs back at level 1 next to zzz_view and replay fails Code: 60 ... Unknown table expression identifier 'd.zzz_view'. Your dedup reasoning is right (TablesDependencyGraph.cpp:114-116 collects into an unordered_set before comparing). I also re-ran both round-2 false-positive classes in their MV form (mutual 'd.mv_users' literals, and a mtr/mtr_daily prefix collision) and neither fabricates a cycle now that MVs go through the same parse.

One more blocker in the same place, though.

collectTableIdentifiers only picks up ASTTableIdentifier, so a reference the grammar puts in a function ARGUMENT is invisible. Two forms, both reproduced end to end for MVs:

1. dictGet. SELECT id, dictGet('d.zzz_dict','val',id) AS v FROM d.base parses the dictionary as Literal 'd.zzz_dict', never a TableIdentifier. loading_dependencies_table is [] on the MV and the dictionary gets no dependent edge, so MV and dictionary both land at level 1 and the name tie-break emits the MV first. Replay: Code: 36. DB::Exception: Dictionary (d.zzz_dict) not found. (BAD_ARGUMENTS).

2. IN. SELECT id FROM d.base WHERE id IN d.zzz_inview is stored as ... IN (d.zzz_inview) and parses as a plain Identifier, not a TableIdentifier. Replay: Code: 47 ... Unknown expression or table expression identifier 'd.zzz_inview'. (UNKNOWN_IDENTIFIER).

Negative control for both: emit the missed object first and replay is RC=0 with data flowing (MV_ROWS 7 seven, IN_MV_ROWS 3). So it is the ordering, not the fixture.

Two precisions, since they scope it:

• It bites materialized views, not plain views. A dumped CREATE VIEW carries an explicit column list, and with one the server accepts a view over a missing dictionary or table (verified, RC=0), so a plain view still replays. An MV validates its SELECT regardless of the column list, which is what makes this one hard-fail.
• Like the round-4 case, it needs the missed object to sit at level 1 or above and to sort after the MV. For dictGet that is the ordinary case rather than an exotic one: a dictionary whose source table is in the same dump is always at level 1, so only the name decides. Renaming zzz_dict to aaa_dict makes the identical schema replay cleanly.

The test cannot catch either. dictGet appears once in 04836, in a post-replay probe at line 115, never inside a dumped view or MV SELECT; joinGet and IN <table> do not appear at all.

Fix direction, verified only to this depth: the server's own extractor already handles exactly these argument positions in visitFunction (src/Databases/DDLDependencyVisitor.cpp:312-329) -- argument 0 for functionIsDictGet / functionIsJoinGet and for dictionary(...), argument 1 for functionIsInOrGlobalInOperator. Those predicates are header-only inline functions over std::string in src/Interpreters/misc.h and take no ContextPtr, so the local walk can reuse them: when a node is an ASTFunction, read the relevant argument as a string literal (dictGet / joinGet / dictionary) or as an ASTIdentifier (IN), and match it against known_tables exactly as now. joinGet over a Join-engine table is the same shape and worth covering in the same pass.

Holding the reviewer routing one more round, for the same reason as before. Ping me once this is in and I will route it.

@groeneai Fixed. Went further than the two cases you'd verified — audited DDLDependencyVisitor.cpp's full visitFunction to catch the whole class rather than patch two instances: dictGet/dictHas/dictIsIn/naiveBayesClassifier*/joinGet/joinGetOrNull (arg 0), the dictionary() table function (arg 0, confirmed empirically it's an ASTIdentifier not a literal — different shape from dictGet), and the full IN/GLOBAL IN family including notIn/globalNotIn/etc. (arg 1).

Deliberately excluded remote()/cluster() and non-literal argument evaluation (e.g. dictGet(concat(...), ...)) — both need a live Context this client-side tool doesn't have, and for remote()/cluster() specifically, blindly extracting the argument text risks a spurious dependency between genuinely unrelated servers, which is worse than the original bug since a false cycle aborts the whole dump.

Given the pattern of this review (a new gap in the same mechanism each round), I ran this one through a fuller pipeline before pushing: architect audited the full extractor and verified every case empirically, a dev implementation, then a senior-style review (confirmed no correctness bugs, and — critically — empirically confirmed each new test case actually forces the target and its referencing MV to the same dependency level, so the fix is what's deciding the order, not an unrelated level gap that would pass either way), and an adversarial pass specifically hunting for what a round 5 would find: tested NOT IN/GLOBAL NOT IN, a UNION ALL mixing a dictGet branch with a plain FROM branch, DEFAULT-column expressions referencing dictGet (confirmed already covered — loading_dependencies_* walks the full CREATE query, not just as_select), and merge()/view() table functions (merge() isn't tracked, but confirmed it resolves lazily at query time rather than CREATE time, so dump order doesn't affect whether it replays — consistent with the server's own tracker also not tracking it).

Your exact dictGet/IN repros verified directly against the built binary, plus the dictionary() case.

Verified at af8d5b0. collectFunctionArgumentReferences reads the same argument positions as visitFunction and reuses the misc.h predicates, so the dictGet/dictHas/dictIsIn/naiveBayesClassifier*/joinGet family (arg 0), dictionary() (arg 0) and the IN/GLOBAL IN family (arg 1) are all covered. Round-5 blocker discharged.

The arm discriminates, which is the part the previous rounds' suites failed. Restoring the old ASTTableIdentifier-only collector reddens: Code: 36 ... Dictionary (d.zzz_dict) not found for the dictGet MV, Code: 47 UNKNOWN_IDENTIFIER for IN (d.zzz_inview). Ran your new fixture through both collectors: at head the four new MVs sit one level above their targets and replay RC=0; with the narrow collector aaa_mv_inref drops to zzz_inview_tbl's level and replay fails. So ordering is what the fix decides.

I also checked the widening for a round-2 style false cycle, since arg 0 of dictGet is a string literal and a literal is again being read as an object name. Could not construct one: the server refuses a mutual IN 'db.tbl' pair at CREATE (Code: 269 INFINITE_LOOP) and refuses dictGet('d.some_view', ...) outright (Code: 36), so the shapes that would fabricate an edge cannot exist in a dumpable catalog. Unqualified literals are stored already qualified in as_select, so there is no current-database gap. Your dictionary()-vs-dictGet shape claim and the DEFAULT dictGet coverage claim both check out.

One correction, not a blocker. merge() is not resolved lazily, it resolves at CREATE. An MV cannot be created directly from a table function (Code: 397), but it can hold one in a JOIN, and there dump order does matter:

CREATE MATERIALIZED VIEW d.aaa_mv_mergejoin TO d.sink AS
  SELECT s.id AS id, s.val AS val, m.w AS w FROM d.src AS s
  LEFT JOIN merge('d','^zzz_merge_view$') AS m ON s.id = m.id;

With zzz_merge_view a view over a base table, it and the MV are both level 1, aaa_* sorts first, and replaying that order fails Code: 636 CANNOT_EXTRACT_TABLE_STRUCTURE; emitting the view first replays RC=0 with data flowing. loop(d.zzz_loop_view) is the same shape (Code: 60 UNKNOWN_TABLE) and holds arg 0 as a plain Identifier, so it is the extraction path dictionary() already uses. merge() would need its regexp matched against the table list, which is a larger call, and the server tracks neither. Documenting both as limitations is defensible; I would just state the reason as resolved-at-CREATE-but-untracked rather than lazy.

cc @azat @yakov-olkhovskiy, could you review this? It adds a client-side --dump-schema to clickhouse-client and clickhouse-local that prints CREATE statements in dependency order, ordering views and materialized views by parsing their stored SELECT text since the server tracks no dependencies for plain views.

Workflow [PR], commit [fd18a1a]

Build profile diff (arm_release)

Comparing d96b66676 with master 003d95fa0 (stripped binary size, per-symbol sizes and ThinLTO time; object sizes against the warmup build of 2e619bf86; compile times per translation unit against the most recent warmup build that recompiled it).

⚠️ Significant changes: object file sizes.

Job report