[MDEV-18159] Arithmatic operations on SYSDATE yields in-consistent results Created: 2019-01-07  Updated: 2019-01-23  Resolved: 2019-01-23

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 5.5, 10.0, 10.1, 10.2.14, 10.2, 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Kiran Kuppili Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None


 Description   

select DATE(SYSDATE()), DATE(DATE('2018-11-01')-1);
– Results in invalid date 2018-11-00

select DATE(SYSDATE()), DATE(DATE('2018-11-30')+1);
– Results in NULL

Both the cases should have returned NULL or yield expected dates as 2018-10-31 and 2018-12-01 respectively

 Comments   
Comment by Alice Sherepa [ 2019-01-07 ]

Thanks for the report!
Reproducible on 5.5-10.3

MariaDB [test]> select  DATE(DATE('2018-11-01')-1);
 
+----------------------------+
 
| DATE(DATE('2018-11-01')-1) |
 
+----------------------------+
 
| 2018-11-00                 |
 
+----------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select  DATE(DATE('2018-11-30')+1);
 
+----------------------------+
 
| DATE(DATE('2018-11-30')+1) |
 
+----------------------------+
 
| NULL                       |
 
+----------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
Warning (Code 1292): Incorrect datetime value: '20181131'
 
 
 
MariaDB [test]> select  DATE(DATE('2018-11-01')-interval(1)DAY);
 
+-----------------------------------------+
 
| DATE(DATE('2018-11-01')-interval(1)DAY) |
 
+-----------------------------------------+
 
| 2018-10-31                              |
 
+-----------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select DATE(DATE('2019-01-01')-3);
 
+----------------------------+
 
| DATE(DATE('2019-01-01')-3) |
 
+----------------------------+
 
| NULL                       |
 
+----------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
Warning (Code 1292): Incorrect datetime value: '20190098'

Comment by Sergei Golubchik [ 2019-01-23 ]

This is not a bug. A DATE value in a numeric context is converted to a number. In your case, 20181101 and 20181130. After your arithmetic operations you get 20181100 and 20181131. The first can be converted back to a DATE (unless you set sql_mode to NO_ZERO_IN_DATES), the second cannot.

For correct date arithmetic use DATE_ADD

See:

Generated at Thu Feb 08 08:41:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.