Bug #8143 A date with value 0 is treated as a NULL value

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;

Verified with 4.1.10-debug-log

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)

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

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

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

Pushed to 5.0.25

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.

Pushed into 5.1.12

Noted in 5.1.12 changelog.

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