MySQL Bugs: #99013: An extra hour gets added to the timestamp when subtracting Interval 'n' days

Bug #99013	An extra hour gets added to the timestamp when subtracting Interval 'n' days
Submitted:	20 Mar 2020 19:20	Modified:	12 Jan 2021 20:50
Reporter:	Adarshdeep Cheema	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
1 extra hour gets added to the timestamp when subtracting Interval N days, with MySQL 8.0.19 JDBC driver version with MySQL 8.0.19 GA Community Server or MySQL 8.0.18 GA Community Server

This issue does not occur when using driver 5.1.46

How to repeat:
Create table :
CREATE TABLE partorder
(
   PNO char(2),
   SNO char(2),
   ORDERDATE date,
   RECEIVEDATE date
)
;

Metadata in table:

Select *  FROM  `PARTORDER`

PNO  SNO  ORDERDATE   RECEIVEDATE  
-----------------------------------
P1   S1   1990-04-30  1990-06-21   
P2   S1   1991-01-03  1991-01-21   
P2   S1   1991-02-28  1991-03-10   
P3   S1   1991-02-13  1991-06-05   
P4   S3   1990-08-08  1990-09-21   
P5   S5   1991-05-18  1991-06-04   
P6   S4   1990-11-30  1990-12-18   
P7   S2   1990-10-28  1991-01-05   
P7   S1   1991-04-30  1991-06-21   
P1   S5   1991-04-28  <null>       
P7   S4   1991-04-30  <null>     

Now run the following SQL using 8.0.19 JDBC driver 

SELECT
(
TIMESTAMP '2018-04-01 00:00:00' - interval(0) day) AS `Test`
FROM
`PARTORDER` where PNO = 'P1' and SNO ='S1'

Expected results:
Test
---------------------
2018-04-01 00:00:00.0

Actual Results:
Test
---------------------
2018-04-01 01:00:00.0

Hello Adarshdeep,

Thank you for the report and test case.
Observed with C/J 8.0.19.

regards,
Umesh

Posted by developer:
 
Added the following entry to the Connector/J 8.0.23 changelog: 

"The returned value for a TIMESTAMP was incorrect when a temporal interval expression was used in the SQL statement for the query."