MySQL Bugs: #66653: leap year date is stored as '0000-00-00'

Bug #66653	leap year date is stored as '0000-00-00'
Submitted:	2 Sep 2012 18:19	Modified:	5 Sep 2012 19:47
Reporter:	Milton Mobley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.25.0	OS:	Windows (W7 Home Premium)
Assigned to:		CPU Architecture:	Any

Description:
When I load the date '1854-02-29' (a leap year date), it is stored as '0000-00-00' with no error reported. The previous server version reported an invalid date error in this case.

I am using MySQL 5.5.25.0 as distributed in the XAMPP package, and the MySQL
Python-based connector I found in the MySQLWorkbench source.

I did not find any references to leap year dates in the FAQ.  What can I do
to overcome this problem?

How to repeat:
CREATE TABLE some_table (
...
date_text VARCHAR(20),
date_value DATE,
...
);

INSERT INTO some_table(...,date_text,date_value,...)
VALUES(...,'2/29/1854','1854-02-29',...);

SELECT * from some_table WHERE date_value = 0;

Putting a print statement into the MYSQL connector shows the value returned by
the server is '0000-00-00'.

Suggested fix:
Change the code to accept leap year dates, which I gather have been in use since the 1500's.

This should be fairly simple to fix, but I will be amazed if no one ever reported it before.

According to http://en.wikipedia.org/wiki/List_of_leap_years "1854" is not a leap year!  Did you do a miscalculation?

But 5.1+ behaves different from 5.0 (all in 'empty' sql-mode). In 5.1 the invalid date truncates to "0". In 5.0 returns an error (and I believe that 5.1+ behaviour is correct and consistent with the concept of strict/nonstrict sql_modes).

5.0:

CREATE TABLE some_table (
date_text VARCHAR(20),
date_value DATE);

INSERT INTO some_table(date_text,date_value)
VALUES('2/29/1854','1854-02-29');
-- Error Code: 1292
-- Incorrect date value: '1854-02-29' for column 'date_value' at row 1

SELECT * FROM some_table WHERE date_value = 0; 
-- empty set

5.1+:

CREATE TABLE some_table (
date_text VARCHAR(20),
date_value DATE);

INSERT INTO some_table(date_text,date_value)
VALUES('2/29/1854','1854-02-29');
-- 1 row(s) affected, 1 warning(s)

SELECT * FROM some_table WHERE date_value = 0;
/*
date_text  date_value  
---------  ------------
2/29/1854  0000-00-00  
*/

Peter
(not a MySQL/Oracle person)

You are right that 1854 is not actually a leap year, I apologize to all.

Thank you for the report.

Please check if you don't set SQL MODE ALLOW_INVALID_DATES and if you check warnings. By default (with empty SQL MODE) MySQL produces warning like "Warning (Code 1265): Data truncated for column 'f1' at row 1" and inserts 0000-00-00 date. This is not a bug.