MySQL Bugs: #19848: Date arithmetic can be dangerous, data disappears

Bug #19848	Date arithmetic can be dangerous, data disappears
Submitted:	16 May 2006 12:03	Modified:	17 May 2006 10:03
Reporter:	Andre Timmer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:
Assigned to:		CPU Architecture:	Any

Description:
Date arithmetic on dates with zero fields results in null values.
This can have unexpected results for a developer / is dangerous.

How to repeat:

drop table if exists test;

CREATE TABLE test (
  col1 date
) ENGINE=innodb DEFAULT CHARSET=utf8;

insert into test (col1) values ('1924-00-00');
insert into test (col1) values ('1924-02-02');
commit;

-- 2 rows and 2 date
select * from test;

update test set col1 = date_sub(col1, interval 1 day);

-- 2 rows and only 1 date
select * from test;

Suggested fix:
date_sub('1924-00-00', interval 1 day) should give 1924-01-01 or stay unchanged?

When doing an update on a million row table with mostly complete dates and some semi-dates the user will probably not found out that some if his / her precious data has disappeared.

Sorry, but this is clearly described in the manual (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html):

"Date arithmetic operations require complete dates and do not work with incomplete dates such as '2006-07-00' or badly malformed dates:

mysql> SELECT DATE_ADD('2006-07-00', INTERVAL 1 DAY);
        -> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
        -> NULL
"

So, it is not a bug.

You are totally right.

So whenever someone wants to do date arithmetic he or she has to start a complete investigation of the data in the date columns is used?!
If not the small price to pay is loss of data.

Having that said i know that the real solutions of course is to have MySQL run in a mode that doesn't allow this kind of data to be entered.