pingcap / pingcap/tidb

Inconsistent Result for FROM_UNIXTIME with IF and LEAST Functions

Open
#67,014 3 comments 0 reactions 0 assignees View on GitHub
affects-7.1 affects-7.5 affects-8.1 affects-8.5 contribution severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

It seems that the query produces different results based on the table structure. Specifically, the result of FROM_UNIXTIME differs when the column type is changed, despite the query itself remaining identical. This inconsistency is unexpected since the difference between the two queries should not affect the result in such a significant manner.

### 1. Minimal reproduce step (Required)

```sql

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

-- Create table with BOOL column and PRIMARY KEY constraint
CREATE TABLE t0(c0 BOOL ZEROFILL , c1 TEXT(232) , PRIMARY KEY(c0));
INSERT INTO t0 VALUES (0.3969229027546298, false);

-- Query 1:
SELECT FROM_UNIXTIME(SUM(b'101010')) AS c0
FROM t0 AS tom0
GROUP BY tom0.c0
HAVING IF(1, LEAST(X'68656C6C6F', 1), 1);

-- Drop table and recreate with different column types
DROP TABLE t0;
CREATE TABLE t0(c0 tinyint(1) unsigned zerofill, c1 text COLLATE "utf8mb4_bin");
INSERT INTO t0 VALUES (0.3969229027546298, false);

-- Query 2:
SELECT FROM_UNIXTIME(SUM(b'101010')) AS c0
FROM t0 AS tom0
GROUP BY tom0.c0
HAVING IF(1, LEAST(X'68656C6C6F', 1), 1);

```

### 2. What did you expect to see? (Required)
I expected both queries to return the same result, as the only difference between them is the column type. The query logic, which includes functions like FROM_UNIXTIME, SUM, IF, and LEAST, should not be affected by the column type.

### 3. What did you see instead (Required)
```sql

Output for Query 1:

+----------------------------+
| c0 |
+----------------------------+
| 1970-01-01 00:00:42.000000 |
+----------------------------+
1 row in set (0.00 sec)
Output for Query 2:

+----------------------------+
| c0 |
+----------------------------+
| 1970-01-01 00:00:00.000000 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
```
### 4. What is your TiDB version? (Required)

```sql
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.5
Edition: Community
Git Commit Hash: 1fa258b833ff113883beeba40bc130be7ce66610
Git Branch: HEAD
UTC Build Time: 2026-03-12 08:34:59
GoVersion: go1.25.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.