Bug #60105 Zero dates defined as NOT NULL shows strange behavior with IS [NOT] NULL
Submitted: 10 Feb 2011 22:39 Modified: 4 Mar 2011 1:21
Reporter: Gavin Towey Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.44, 5.5.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: date, null, zero

[10 Feb 2011 22:39] Gavin Towey
Description:
When creating a date or datetime column with NOT NULL in the definition, dates with '0000-00-00' match *both* IS NULL and IS NOT NULL.

When the column is created with NULL option, dates with '0000-00-00' behave differently.

How to repeat:
test> create table d ( d date not null default '0000-00-00');
Query OK, 0 rows affected (0.12 sec)

test> insert into d values ();
Query OK, 1 row affected (0.00 sec)

test> select * from d where d IS  NULL;
+------------+
| d          |
+------------+
| 0000-00-00 | 
+------------+
1 row in set (0.00 sec)

test> select * from d where d IS NOT NULL;
+------------+
| d          |
+------------+
| 0000-00-00 | 
+------------+
1 row in set (0.00 sec)

test> drop table d;
Query OK, 0 rows affected (0.43 sec)

test> create table d ( d date null default '0000-00-00');
Query OK, 0 rows affected (0.17 sec)

test> insert into d values ();
Query OK, 1 row affected (0.00 sec)

test> select * from d where d IS NOT NULL;
+------------+
| d          |
+------------+
| 0000-00-00 | 
+------------+
1 row in set (0.00 sec)

test> select * from d where d IS  NULL;
Empty set (0.00 sec)

Suggested fix:
a date of '0000-00-00' should return true when used with IS NULL
[11 Feb 2011 4:24] Valeriy Kravchuk
Verified with current mysq-5.5 on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.10-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  create table d ( d date not null default '0000-00-00');
Query OK, 0 rows affected (0.10 sec)

mysql>  insert into d values ();
Query OK, 1 row affected (0.00 sec)

mysql>  select * from d where d IS  NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from d where d IS  NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from d where d IS  NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from d where d IS NOT NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
[4 Mar 2011 1:21] Omer Barnir
This 'dual' behavior in regarding to '000-00-00' dates and NULL / NOT NULL is by design and documented in 
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is-null