Bug #8143 A date with value 0 is treated as a NULL value
Submitted: 26 Jan 2005 13:00 Modified: 15 Aug 2006 3:19
Reporter: a b Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.8-standard-log OS:Linux (SuSE Linux 8.2)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[26 Jan 2005 13:00] a b
Description:
A table is defined with non-null date field. When this date field has the value 0 (i.e. 0000-00-00), it is treated as a null value in select operations.

How to repeat:
drop table if exists nulltest;
create table nulltest (
        id integer not null primary key auto_increment,
        datum date not null
);

insert into nulltest (datum) values (0);
select * from nulltest where datum is null;
-- in the previous line the error occurs: the single row of the table is selected
select * from nulltest where datum = 0;
delete from nulltest where datum is null;
-- delete does not show this erroneous behaviour
delete from nulltest where datum = 0;
[26 Jan 2005 13:07] MySQL Verification Team
Verified with 4.1.10-debug-log
[23 May 2005 13:56] Michael Widenius
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug but instad intended behaviour.
See the manual section http://dev.mysql.com/doc/mysql/en/comparison-operators.html
for more information (search after IS NULL)
[24 May 2005 12:32] Michael Widenius
I reopened this as I missed the fact that DELETE now is not consistent to SELECT and this should be fixed at least in 5.1
[2 Jun 2006 11:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7205
[23 Jul 2006 10:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9460
[3 Aug 2006 16:47] Magnus BlÄudd
Pushed to 5.0.25
[4 Aug 2006 3:34] Paul DuBois
Noted in 5.0.25 changelog.

For ODBC compatibility, MySQL supports use of WHERE col_name IS NULL
for DATE or DATETIME columns that are NOT NULL, to allow column
values of '0000-00-00' or '0000-00-00 00:00:00' to be selected.
However, this was not working for WHERE clauses in DELETE
statements.
[14 Aug 2006 20:54] Konstantin Osipov
Pushed into 5.1.12
[15 Aug 2006 3:19] Paul DuBois
Noted in 5.1.12 changelog.
[27 Oct 2006 13:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14477

ChangeSet@1.2610, 2006-10-27 18:08:50+05:00, ramil@mysql.com +3 -0
  Fix for bug #23412: delete rows with null date field
  
  Backport of the fix for bug #8143: A date with value 0 is treated as a NULL value