MySQL Bugs: #115145: MySQL seems to lose precision when adding dates

Bug #115145	MySQL seems to lose precision when adding dates
Submitted:	28 May 2024 2:07	Modified:	28 May 2024 9:57
Reporter:	Wenqian Deng	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using the DATE_ADD function in MySQL, it appears that precision is lost for certain date values. Specifically, fractional seconds are being dropped in some cases.

How to repeat:
Set up the table:

CREATE TABLE t0 (c_0 TEXT, c_1 INT);
INSERT INTO t0 VALUES ('2389-04-27 07:05:39.100000', 105);
INSERT INTO t0 VALUES ('6389-08-27 07:05:39.000000', 10894);

Execute the following query:

SELECT (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) FROM t0;

It returns:

2389-08-10 07:05:39.100000
6419-06-25 07:05:39

The fractional seconds .000000 are dropped for the second value '6389-08-27 07:05:39.000000'.

Hi Mr. Deng,

Thank you for your bug report.

However, this is not a bug.

The function that you used can be applied only to DATE and DATETIME types. 

Next, you have mixed incompatible datatypes in your query. According to SQL Standard, such query should just return a very short error code. MySQL tries to do more by adopting the intermediary data type for two incompatible data types. 

That is a very good reason why you the result that you get.

Not a bug.

Mr. Deng,

In short, TEXT is not a data type that is equivalent to the DATE type.

MySQL cannot see that the first argument is a datetime value
without inspecting the first argument to DATE_ADD(), which is
a character string.

You can tell that the value is actually a datetime by using a CAST:

  SELECT DATE_ADD(CAST(t0.c_0 AS DATETIME(6)), INTERVAL t0.c_1 DAY) FROM t0;

Thank you, Roy.