[MDEV-4667] DATE('string') incompability between mysql and mariadb Created: 2013-06-17  Updated: 2018-10-31  Resolved: 2013-07-03

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.31, 5.3.12
Fix Version/s: 10.0.5, 5.5.32, 5.3.13

Type: Bug Priority: Minor
Reporter: Jonas Reinhardt Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17563 Different results using table or view... Closed

 Description   

If you do SELECT date('string'); in MySQL you get NULL and if you do the same in MariaDB you get '0000-00-00';

Tried with MySQL ver 5.1.29, probably with all previous versions.
Perhaps it is a known change, but it's hard to search for

 Comments   
Comment by Elena Stepanova [ 2013-06-25 ]

We had various similar issues, since NULL vs 0000-00-00 inconsistency is all over the place, even inside the same version (e.g. compare DATE('string') and FROM_DAYS('string') in MySQL). However, I haven't found this exact problem, so I'm passing it over to Sergei for a verdict. If it's intentional, maybe we should document it here https://kb.askmonty.org/en/date-function/ , the same way as error handling is documented at https://kb.askmonty.org/en/unix_timestamp/.

CAST has the same difference:

MySQL:
CAST('string' AS DATE)
NULL

MariaDB:
CAST('string' AS DATE)
0000-00-00

Generated at Thu Feb 08 06:58:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.