[MDEV-33339] DATE_SUB bug ? Created: 2024-01-31  Updated: 2024-01-31  Resolved: 2024-01-31

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.10.7, 10.11.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Brun Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux (Debian) - MacOS (brew)


 Description   

Hi,

I use DATE_SUB function to compute "age" of person.

When I use the follow on Mariadb 10.11.6 :

select DATE_SUB("2023-08-11 12:00:00", INTERVAL TO_DAYS("2022-01-01 12:00:00") DAY)

The result is OK :

0001-08-10 12:00:00

When I try on same Mariadb server with :

select DATE_SUB("2023-08-11 12:00:00", INTERVAL TO_DAYS("2023-01-01 12:00:00") DAY)

The result is WRONG (in my opinion):

NULL

On server Mysql 5.7.9, 8.0 ,
the result for "select DATE_SUB("2023-08-11 12:00:00", INTERVAL TO_DAYS("2023-01-01 12:00:00") DAY)" is :

0000-00-00 12:00:00

Can you investigate on this potential bug ?

Thank you !

Eric

 Comments   
Comment by Sergei Golubchik [ 2024-01-31 ]

This is not a bug, documentation says (https://mariadb.com/kb/en/datetime/):

MariaDB stores values that use the DATETIME data type in a format that supports values between 1000-01-01 00:00:00.000000 and 9999-12-31 23:59:59.999999.

DATETIME values below 1000-01-01 are not supported, but it seems that up to 0001-01-01 the function still works. Zero year is definitely incorrect and you get NULL.

Note that it's the same or MySQL: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

You're using unsupported values of DATETIME, they aren't guaranteed to work either in MariaDB or in MySQL.

You can workaround it by always keeping the second date in the 1000-2000 year range, and then subtracting 1000 from the age.

Or use TIMESTAMPDIFF which was specifically created for that

Comment by Brun [ 2024-01-31 ]

Thank you very much for your explanation and your speed !

Sorry for wrong ticket declaration.

Thank you again !

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