Repository navigation
Pin the dump's TIMESTAMP zone in the loader's input() declaration - #1578
Merged
minguyen9988 merged 1 commit intoNov 6, 2026
Merged
Conversation
A MySQL Shell dump writes TIMESTAMP values as text in the zone the dump records. The dump's @.json carries "tzUtc": true, so that text is UTC. The loader passed the field through to the ClickHouse client unchanged, and a zone-less DateTime64(p) is parsed in the AMBIENT zone of the load: the client zone under --use_client_time_zone 1, else the server's. The same dump therefore stored different instants on two hosts. Measured on ClickHouse 24.8.14 with the dump's text 2018-04-05 20:44:35.772929 and 2018-12-01 00:18:03.545683, reading toInt64() of the stored value: declaration TZ=UTC TZ=America/Chicago TZ=Europe/London DateTime64(6) 1522961075772929 1522979075772929 1522957475772929 DateTime64(6) 1543623483545683 1543645083545683 1543623483545683 DateTime64(6,'UTC') 1522961075772929 1522961075772929 1522961075772929 DateTime64(6,'UTC') 1543623483545683 1543623483545683 1543623483545683 The zone-less April reading moves by the 5-hour summer offset and the December reading by 6 hours; the pinned declaration stores one instant everywhere, and it is the instant the text names. timestamp_input_datatype(datatype, timezone) pins the zone the loader resolved, from the dump or from --source_timezone, into the declaration. A datatype that already names a zone is returned untouched, so --clickhouse_datetime_timezone still decides that column, and a second pass cannot drift. The emitted INSERT reads the text in the named zone and the target column's own type keeps that instant. The defect showed up as a checksum divergence between a source table and its replica whose row counts matched: the replica held the same instants as the source only when the load and the source agreed on a zone. Loading the same dump under the declaration that names the dump's zone stores the source's instants on every host, whatever the ambient zone is. Verified in an isolated sandbox on this head: the loader suite 17 passed; specs validated 94, citations 1423, Validation PASSED. Formal-Verify: result=pass; fingerprint=574f88f0f8830a78584cd78d285c5b979bacdc0307124ebb452698e6fc6652b2 Co-Authored-By: Claude Code <noreply@anthropic.com>
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
What
A MySQL Shell dump writes TIMESTAMP values as text in the zone the dump records. The dump's @.json carries "tzUtc": true, so that text is UTC. load_data_mysqlshell passed the field through to the ClickHouse client unchanged, and a zone-less DateTime64(p) is parsed in the AMBIENT zone of the load: the client zone under --use_client_time_zone 1, else the server's. The same dump therefore stored different instants on two hosts.
This pins the resolved zone into the input() declaration.
Measured
ClickHouse 24.8.14, the dump's text 2018-04-05 20:44:35.772929 and 2018-12-01 00:18:03.545683, reading toInt64() of the stored value:
The zone-less April reading moves by the 5-hour summer offset and the December reading by 6 hours. The pinned declaration stores one instant everywhere, and it is the instant the text names.
How
timestamp_input_datatype(datatype, timezone) appends the resolved zone to a zone-less declaration:
Why it matters
It surfaced as a checksum divergence between a source table and its replica where the row counts matched: the replica held the source's instants only when the load and the source agreed on a zone. Loading the same dump under the declaration that names the dump's zone stores the source's instants on every host, whatever the ambient zone is.
Tests
Whole-Change-Verification: ran=the loader unit suite on the full branch head (sink-connector/python/db_load/tests/test_loader_timestamp_input_zone.py, 17 passed in 2.46s) plus the four spec suites (lean 4/4, dafny 4/4, tla 4/4, hypothesis 36/36, all pass); result=pass
Specs
specs/lean, specs/dafny, specs/tla and specs/hypothesis cover the changed logic, following the spec style already in this tree: the zone model, the ambient-zone dependence, and the measured vectors above. The hypothesis spec runs the model against the real functions transcribed line for line; the other three model it.
Notes
Created on behalf of the team; the maintainers own the merge.