UNIX_TIMESTAMP returns 0 in TiFlash for DATETIME values after 2038 while TiDB returns the extended timestamp

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE test.t_unix_timestamp (
    id INT PRIMARY KEY,
    dt DATETIME NOT NULL
);

INSERT INTO test.t_unix_timestamp VALUES
    (1, '2100-01-01 00:00:00');

ALTER TABLE test.t_unix_timestamp SET TIFLASH REPLICA 1;

-- Wait until AVAILABLE=1 before continuing.
SELECT TABLE_SCHEMA, TABLE_NAME, AVAILABLE, PROGRESS
FROM information_schema.tiflash_replica
WHERE TABLE_SCHEMA = 'test'
  AND TABLE_NAME = 't_unix_timestamp';

SET time_zone = '+00:00';

-- Evaluated by TiDB after a TiKV point get.
SET SESSION tidb_isolation_read_engines = 'tikv';

SELECT /*+ read_from_storage(tikv[t]) */
       id, dt, UNIX_TIMESTAMP(dt)
FROM test.t_unix_timestamp AS t
WHERE id = 1;

-- Evaluated in a TiFlash MPP Projection.
SET SESSION tidb_isolation_read_engines = 'tiflash';
SET SESSION tidb_allow_mpp = 1;
SET SESSION tidb_enforce_mpp = 1;

SELECT /*+ read_from_storage(tiflash[t]) */
       id, dt, UNIX_TIMESTAMP(dt)
FROM test.t_unix_timestamp AS t
WHERE id = 1;

EXPLAIN FORMAT = 'brief'
SELECT /*+ read_from_storage(tiflash[t]) */
       UNIX_TIMESTAMP(dt)
FROM test.t_unix_timestamp AS t
WHERE id = 1;

-- A constant expression evaluated by TiDB also returns the extended value.
SELECT UNIX_TIMESTAMP('2100-01-01 00:00:00');

Related issue: #3171. That issue was reported against v5.2.1. This report focuses on the current cross-engine semantic mismatch after TiDB extended the valid UNIX_TIMESTAMP range in pingcap/tidb#44003.

2. What did you expect to see? (Required)

UNIX_TIMESTAMP(DATETIME) should return the same result regardless of whether the expression is evaluated by TiDB/TiKV or pushed down to TiFlash.

With time_zone = '+00:00', all three expressions should return:

4102444800

TiDB follows the MySQL 8.0.28+ range and accepts Unix timestamps through 3001-01-18 23:59:59.999999. Therefore, 2100-01-01 00:00:00 is in range.

3. What did you see instead (Required)

TiDB/TiKV returns the expected value:

id  dt                   UNIX_TIMESTAMP(dt)
1   2100-01-01 00:00:00  4102444800

The constant expression also returns:

UNIX_TIMESTAMP('2100-01-01 00:00:00')
4102444800

However, when the column expression is pushed down to a TiFlash MPP Projection, TiFlash returns 0:

id  dt                   UNIX_TIMESTAMP(dt)
1   2100-01-01 00:00:00  0

The relevant plan shape is:

TableReader root
└─ExchangeSender mpp[tiflash]
  └─Projection mpp[tiflash]   unix_timestamp(dt)
    └─TableFullScan mpp[tiflash]

The implementations currently use different valid ranges:

• TiDB accepts values through year 3001 in goTimeToMysqlUnixTimestamp:
  https://github.com/pingcap/tidb/blob/ae18096e023780bb56bfce33698abec0d4640d0a/pkg/expression/builtin_time.go#L4248-L4260
• TiFlash rejects every epoch second greater than std::numeric_limits<Int32>::max(), so values after 2038-01-19 03:14:07 UTC become 0:
  https://github.com/pingcap/tiflash/blob/7d70c1ce4d09cc018080a638e6a450018ff0e388/dbms/src/Functions/FunctionsConversion.cpp#L145-L160

The same TiFlash helper is shared by the integer and decimal variants, so both UnixTimestampInt and UnixTimestampDec are affected.

This is a correctness issue: projections, filters, and aggregations involving UNIX_TIMESTAMP on DATE/DATETIME values after 2038 can return different results depending on the selected storage engine.

4. What is your TiFlash version? (Required)

TiFlash version: 8.5.4-20260713-4b2815a-5-g7d70c1ce4d
TiFlash git hash: 7d70c1ce4d09cc018080a638e6a450018ff0e388

TiDB version: 8.5.6
TiDB git hash: ae18096e023780bb56bfce33698abec0d4640d0a

Start in dbms/src/Functions/FunctionsConversion.cpp at the shared helper used by UnixTimestampInt and UnixTimestampDec, then compare its range handling with TiDB's goTimeToMysqlUnixTimestamp implementation. Reproduce the issue using the SQL statements and TiFlash MPP Projection plan described here. Done means DATE/DATETIME values after 2038 produce the same valid timestamp in TiFlash and TiDB for both variants.