Bug #113595 Incorrect query result of function TIMESTAMPADD
Submitted: 9 Jan 2024 14:38 Modified: 9 Jan 2024 15:15
Reporter: Ye Shiyang Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.35 OS:Any
Assigned to: CPU Architecture:Any
Tags: DATE function, TIMESTAMPADD

[9 Jan 2024 14:38] Ye Shiyang
Description:
In theory, the result of sql should have the same result of sql2.

However,The results returned by these two query statements are different.

CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE);
INSERT INTO t0(c0) VALUES(1);

--sql1

SELECT f1 FROM (SELECT ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) AS f1 FROM t0) as t WHERE f1; 

+-------+
| f1    |
+-------+
| -2022 |
+-------+
1 row in set (0.00 sec)

--sql2

SELECT f1 FROM (SELECT ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) AS f1, ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) IS TRUE AS flag FROM t0) as t WHERE flag=1; 
+-----------------+
| f1              |
+-----------------+
| -20230417999999 |
+-----------------+
1 row in set (0.01 sec)

How to repeat:
DROP DATABASE IF EXISTS database18;
CREATE DATABASE database18;
USE database18;
CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE);
INSERT INTO t0(c0) VALUES(1);

SELECT f1 FROM (SELECT ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) AS f1 FROM t0) as t WHERE f1; 

SELECT f1 FROM (SELECT ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) AS f1, ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) IS TRUE AS flag FROM t0) as t WHERE flag=1; 

Suggested fix:
I think sql1 and sql2 should return the same value.
[9 Jan 2024 15:02] MySQL Verification Team
Hi Mr. eric,

Thank you for your bug report.

However, you just keep reporting the same bug, over and over again .......

Here are the obvious results of your constant repetitions :

SELECT f1 FROM (SELECT ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) AS f1 FROM t0) as t;
+-----------------+
| f1              |
+-----------------+
| -20230417999999 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT f1 FROM (SELECT ((t0.c0) - (TIMESTAMPADD(MICROSECOND, 4 , '2023-04-18'))) AS f1 FROM t0) as t where f1;
+-------+
| f1    |
+-------+
| -2022 |
+-------+
1 row in set (0.00 sec)

Duplicate.
[9 Jan 2024 15:15] Ye Shiyang
I apologize for reporting duplicate bugs. But can you tell me why this is a duplicate bug? Because the timestampadd function has a different structure from the if and ifnull reported earlier.