Bug #4832 IS NULL on a date field treats '0000-00-00' as NULL

• Submitted: 30 Jul 2004 17:50
• Modified: 30 Jul 2004 19:24
• Reporter: Indrek Siitan
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23, 4.0, 4.1
• OS: Any (any)
• CPU Architecture: Any

[30 Jul 2004 17:50] Indrek Siitan

Description:
IS NULL condition on a date field defined with NOT NULL (regardless it being indexed or not) picks 
up '0000-00-00' values.

How to repeat:
mysql> create table datetest ( id int not null auto_increment primary key, datefld date not null );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into datetest (datefld) values (now()),(now()),(now()),('0000-00-00'),('0000-00
-00'),('0000-00-00');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select count(*) from datetest where datefld is null;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from datetest where datefld is not null;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from datetest;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

[30 Jul 2004 19:24] Sergei Golubchik

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

BUG#940