MySQL IS NULL / IS NOT NULL Misbehaving

countMySQL

Please have look at this table:

mysql> desc s_p;

+-------------------------+------------------+------+-----+---------+----------------+    
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| id                      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| s_pid                   | int(10) unsigned | YES  | MUL | NULL    |                |
| sm_id                   | int(10) unsigned | YES  | MUL | NULL    |                |
| m_id                    | int(10) unsigned | YES  |     | NULL    |                |
| created                 | datetime         | YES  |     | NULL    |                |
| s_date                  | datetime         | YES  |     | NULL    |                |
| estimated_date          | datetime         | YES  | MUL | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

Now Have a look at these queries:

mysql> select count(*) from s_p where estimated_date is null;
+----------+
| count(*) |
+----------+
|   190580 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from s_p where estimated_date is not null;
+----------+
| count(*) |
+----------+
|    35640 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from s_p;
+----------+
| count(*) |
+----------+
|  1524785 |
+----------+

The counts above are not matching. While as per my understanding:

Count with IS NULL and Count with IS NOT NULL should be equal to count when queried
without where clause.

Any idea on whats happening here?

===================================================

Update on 17th Feb 2012

Since, I found that a lot of people are asking about the kind of values estimated_date currently has. Here is the answer:

mysql> select distinct date(estimated_date) from s_p;

+----------------------+
| date(estimated_date) |
+----------------------+
| NULL                 |
| 2012-02-17           |
| 2012-02-20           |
| 2012-02-21           |
| 2012-02-22           |
| 2012-02-23           |
| 2012-02-24           |
| 2012-02-27           |
| 2012-02-28           |
+----------------------+
9 rows in set (0.42 sec)

As you can see above estimated_date either has NULL or a valid datetime values. There are no zeros or empty strings "".

Can this(original issue) happen if the index on estimated_date has some problem/s?

===================================================

Update on 18th Feb 2012

Here is the show create table output:

 | s_p | CREATE TABLE `s_p` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned DEFAULT NULL,
  `sm_id` int(10) unsigned DEFAULT NULL,
  `m_id` int(10) unsigned DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `estimated_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `sm_id` (`sm_id`),
   KEY `estimated_date_index` (`estimated_date`) USING BTREE,
  ) ENGINE=InnoDB AUTO_INCREMENT=1602491 DEFAULT CHARSET=utf8 |

Again, I can only suspect index on estimated_date here.

Also, the mysql server version is 5.5.12.

Best Answer

Do you have some zero dates? Datetime values of 0000-00-00 00:00:00 are considered by MySQL to simultaneously satisfy is null and is not null:

steve@steve@localhost > create temporary table _tmp (a datetime not null);
Query OK, 0 rows affected (0.02 sec)

steve@steve@localhost > insert into _tmp values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1264): Out of range value for column 'a' at row 1
steve@steve@localhost > select a from _tmp where a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

steve@steve@localhost > select a from _tmp where a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

See: http://bugs.mysql.com/bug.php?id=940

This is classified as "not a bug". They suggest a workaround: use strict mode, which will convert the insertion warning into an error.

Having said all that, this alone can't explain the wild variation in the results you're getting (the sum of the is null and is not null counts should exceed the unrestricted count)...

Related Question