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

[JaySon-Huang]

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:
  

  tiflash/dbms/src/Functions/FunctionsConversion.cpp

  Lines 145 to 160 in 7d70c1c

  	bool getUnixTimeStampHelper(UInt64 packed, UInt64 & ret) const
  	{
  	try
  	{
  	time_t epoch_second = getEpochSecond(MyDateTime(packed), *timezone_info.timezone);
  	if (!timezone_info.is_name_based)
  	epoch_second -= timezone_info.timezone_offset;
  	if (epoch_second <= 0)
  	{
  	ret = 0;
  	return false;
  	}
  	else if unlikely (epoch_second > std::numeric_limits<Int32>::max())
  	{
  	ret = 0;
  	return false;

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