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: | |
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
[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