MySQL IS NULL / IS NOT NULL Misbehaving?

Question

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.

Answer 1

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)...

Answer 2

@ypercube:

I was recently asked if I thought the regression bug "SELECT COUNT(DISTINCT) crashes InnoDB when WHERE operand is in Primary Key or Unique Index" could be at the root of this.

Here is my reply (originally here):

http://www.chriscalender.com/?p=315&cpage=1#comment-1460

I don’t think this is the same bug. This bug is more about the crashing, and requires a SELECT COUNT (DISTINCT) specifically, plus the WHERE operand is in the Primary Key or Unique index.

Your bug/issue does not have the DISTINCT, it is not crashing, and the index on the datetime column is not a primary key nor unique. However, it is a bit strange off the cuff, so I did some searching though, and ran across this bug, which seems more likely to be involved/related:

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

Actually, it is designated as “not a bug”, but it shows/describes how you can run into strange behavior when you have dates/datetimes with ’0000-00-00′ and using IS NULL and IS NOT NULL.

I wonder if you have any of these ’0000-00-00′ rows which could be affecting the counts?

Note the Dev who comments in the bug report mentions this page too:

If that is not it, I’d certainly recommend upgrading and trying this on the latest 5.5, which is 5.5.21 (as of 2/22/2012), since it has been 9 months (and 9 releases) since 5.5.12 was released.

Note you should be able to dump the table (and data) and import it into another test instance, just to test it out. That way you do not affect a production machine, and you can have a test instance set up in minutes.

Then, should that that not make a difference still, you’d be in a position to test some other items, such as perhaps convert the table to MyISAM to see if the issue is a global one, or just specific to InnoDB.

Or, I noticed the index on ‘estimated_date’ was:

KEY estimated_date_index (estimated_date) USING BTREE

Note the “USING BTREE”. Perhaps try it without the USING BTREE and see if you still see the same behavior. (Or remove the index altogether just to test .. it will all help narrow down the issue).

Hope this helps.

Answer 3

I see something interesting in the table layout that cries out 'I don't feel like counting'. What I am about to say is only a hunch.

You ran this query before

select distinct date(estimated_date) from s_p;

Run it as a COUNT/GROUP BY

select count(1) rowcount,date(estimated_date) from s_p group by date(estimated_date);

You shold get the definitive counts you were looking for.

Yet, why would the counts for NULL and NOT NULL compute correctly? Again, this is just an educated guess.

You have the column estimated_date indexed. Here is what I want you to try:

SHOW INDEX FROM s_p;
SHOW INDEX FROM s_p;
SHOW INDEX FROM s_p;
SHOW INDEX FROM s_p;

That's not a typo. I want you run SHOW INDEX FROM s_p; four(4) times. Look at the Cardinality column. Since the table s_p in InnoDB, I expect the Cardinality column to be different each time. Why?

InnoDB get the Cardinality value by estimating it (NO PUN INTENDED) by counting via the BTREE page entries. Check your system variable innodb_stats_on_metadata. It should be enabled. If it already is enabled, disable it and rerun your original queries to see if improves things. DO THIS ONLY AS A LAST RESORT !!!

So instead of these queries:

select count(*) from s_p where estimated_date is null;
select count(*) from s_p where estimated_date is not null;

Try

select count(estimated_date) from s_p;

This should give you the count of rows with a non-null estimated_date.

Another approach you may want to experiment with this brute force query using the ISNULL function:

select count(*) rowcount,isnull(estimated_date) IsItNull
from s_p group by isnull(estimated_date);

I hope these suggestions help !!!

Answer 4

Try the query

select * from s_p where estimated_date is null and estimated_date is not null limit 5;

Answer 5

This is expected. For a column that is nullable a 0==NULL="" and so on. So the first check actually returns rows where no date was set or its perceived analogous to "0/NULL"