if(ts >9999999999, ts/1000, ts) wrong

[database-on-line]

Bug Report

when if result have decimal,the compare result is wrong

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `articles_of_video_day` (
  `clicks_of_today` int(4) NOT NULL,
  `ts` bigint(4) NOT NULL,
  KEY `ts_index` (`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

insert into articles_of_video_day values(0,1725292800),(0,1725292800);

select from_unixtime( if(`ts` >9999999999, ts/1000, `ts`), '%Y-%m-%d %H:%i:%s') from articles_of_video_day;

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

+--------------------------------------------------------------------------+
| from_unixtime( if(`ts` >9999999999, ts/1000, `ts`), '%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------------------------+
| 2024-09-03 00:00:00                                                      |
| 2024-09-03 00:00:00                                                      |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]> select from_unixtime( if(`ts` >9999999999, ts/1000, `ts`), '%Y-%m-%d %H:%i:%s') from articles_of_video_day;
+--------------------------------------------------------------------------+
| from_unixtime( if(`ts` >9999999999, ts/1000, `ts`), '%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------------------------+
| 1970-01-02 11:46:39                                                      |
| 1970-01-02 11:46:39                                                      |
+--------------------------------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

MySQL [test]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1690 | DECIMAL value is out of range in '(9, 4)' |
| Warning | 1690 | DECIMAL value is out of range in '(9, 4)' |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)

you can see the currect result by this(cast(ts/1000 as signed))

MySQL [test]> select from_unixtime( if(`ts` >9999999999, cast(ts/1000 as signed), `ts`), '%Y-%m-%d %H:%i:%s') from articles_of_video_day;
+------------------------------------------------------------------------------------------+
| from_unixtime( if(`ts` >9999999999, cast(ts/1000 as signed), `ts`), '%Y-%m-%d %H:%i:%s') |
+------------------------------------------------------------------------------------------+
| 2024-09-03 00:00:00                                                                      |
| 2024-09-03 00:00:00                                                                      |
+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

v7.5.1,v8.1.1,
asktug url: https://asktug.com/t/topic/1031711/10

[pcqz]

related to #46786

[pcqz]

/sig execution

[seiya-annie]

/report community

[windtalker]

It seems the root cause of TiDB tries to add cast(issuedb.articles_of_video_day.ts, decimal(9, 4) BINARY) is in the create table statement, it use ts bigint(4), which I think TiDB will interpreter as the max ts's length is 4.
If I use the following statement to create table, it will get correct result

CREATE TABLE `articles_of_video_day` (
  `clicks_of_today` int NOT NULL,
  `ts` bigint NOT NULL,
  KEY `ts_index` (`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin