EXPORT PARTITION: hive destination shifts DateTime by the timezone offset

[Selfeer]

Version: ClickHouse 26.3.17 (Antalya) · Destination: S3, partition_strategy='hive'

Related to: #2074

Summary

When the source and destination DateTime columns have different timezones, EXPORT PARTITION
writes the wrong instant.

The partition value goes into the hive path as the source's wall-clock text, and the destination
then parses that text in its own timezone. The row lands offset by the difference between the two
— 9 hours for UTC → Asia/Tokyo.

INSERT SELECT on the exact same schemas gets it right.

---

Reproduce

SET allow_experimental_export_merge_tree_part = 1;

CREATE TABLE src (id Int64, ts DateTime('UTC'))
ENGINE = ReplicatedMergeTree('/clickhouse/tables/shard0/src', '{replica}')
ORDER BY tuple()
PARTITION BY toDate(ts);

-- destination is Tokyo, source is UTC
CREATE TABLE dst (id Int64, ts DateTime('Asia/Tokyo'))
ENGINE = S3('http://minio:9000/bucket/dst/', '<key>', '<secret>',
            filename='dst', format='Parquet', partition_strategy='hive')
PARTITION BY ts;

INSERT INTO src VALUES (1, '2024-03-05 15:00:00');

ALTER TABLE src EXPORT PARTITION ID '20240305' TO TABLE dst;

Now do the same thing with a plain INSERT SELECT, into an identical table, as a reference:

CREATE TABLE dst_insert (id Int64, ts DateTime('Asia/Tokyo'))
ENGINE = S3('http://minio:9000/bucket/dst_insert/', '<key>', '<secret>',
            filename='dst_insert', format='Parquet', partition_strategy='hive')
PARTITION BY ts;

INSERT INTO dst_insert SELECT * FROM src;

Compare them

SELECT toUnixTimestamp(ts) FROM src;          -- 1709650800
SELECT toUnixTimestamp(ts) FROM dst;          -- 1709618400   ← 9 hours earlier
SELECT toUnixTimestamp(ts) FROM dst_insert;   -- 1709650800   ← correct

SELECT DISTINCT _path FROM dst;               -- .../ts=2024-03-05 15:00:00/...
SELECT DISTINCT _path FROM dst_insert;        -- .../ts=2024-03-06 00:00:00/...

1709650800 − 1709618400 = 32400 s = exactly 9 hours, the Tokyo offset.

---

What is happening

source value:   2024-03-05 15:00:00 UTC   (epoch 1709650800)

INSERT SELECT   converts to Tokyo first  →  path  ts=2024-03-06 00:00:00  →  reads back 1709650800  ✅
EXPORT PARTITION  writes the UTC text    →  path  ts=2024-03-05 15:00:00  →  reads back 1709618400  ❌
                                                                              parsed as Tokyo, −9h

The hive partition value is stored as text in the object path. INSERT SELECT renders that text in
the destination column's timezone. EXPORT PARTITION renders it in the source's and never converts, so
the destination reads back a different point in time.

---

Why we think this is a bug

A timezone is a display attribute, not the value. DateTime stores an epoch. Converting
DateTime('UTC') → DateTime('Asia/Tokyo') must keep the same instant and only change how it is
printed. Here the instant itself moves.

It contradicts the feature's own contract. Export is specified to behave like
INSERT INTO dest SELECT * FROM src — that is exactly what verifyExportSchemaCastable documents. The
repro above runs both against identical schemas and identical data and gets two different answers, so
the feature fails its own stated equivalence. No judgement call about timezones is needed to call this
wrong.

It is silent. The export reports success. The data is readable and looks plausible; it is simply off
by the timezone offset. Nothing warns the user.