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,

Again, I can only suspect index on estimated_date here.

Also, the mysql server version is 5.5.12.

  • 3
    Unless the table is being fed with new rows between and during running the 3 queries, this cannot happen! Feb 16, 2012 at 7:42
  • 6
    Are you certain that you are doing a select count(*) and not select count(estimated_date)? These two will return different results as NULLs are ignored if that is the only thing you are counting.
    – Steven Schroeder
    Feb 16, 2012 at 7:49
  • 6
    I'm not sure whether the following will work in MySQL, but can you try running: SELECT COUNT(*),SUM(CASE WHEN estimated_date IS NULL THEN 1 ELSE 0 END),SUM(CASE WHEN estimated_date IS NOT NULL THEN 1 ELSE 0 END) from s_p - which should get all of the counts in one go. Feb 16, 2012 at 7:50
  • 1
    Are these the exact queries you're running?
    – gbn
    Feb 16, 2012 at 8:24
  • 4
    Also, if this is MyISAM, can you run CHECK TABLE on it? Considering the wildly larger full row count, I'd guess a DELETE went insane somewhere.
    – Naltharial
    Feb 16, 2012 at 8:55

5 Answers 5


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

  • The bug appears when the DATE or DATETIME is defined as NOT NULL. In the question here, the column is defined as nullable. This bug however, is another reason to run MySQL in strict mode only. Feb 16, 2012 at 20:57
  • I have updated the original post to show the current values in estimated_date column. It does not have 0000-00-00 or empty strings "". Feb 17, 2012 at 12:21
  • 1
    @yper or a reason to pick a different DBMS...
    – ErikE
    Feb 19, 2012 at 1:58
  • 1
    @ErikE: That, sometimes, is not a choice. And you'll always find reasons to pick anotehr DBMS, whatever one you are working with. Feb 19, 2012 at 2:01
  • FYI ToadSQL shows 0000-00-00 00:00:00 as {null}, further muddying the waters! What a nightmare. FTR we don't have an index on our problem column. This is on 5.6.15-log.
    – sming
    Mar 4, 2016 at 15:40


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


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:


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.


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:


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;


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 !!!


Try the query

select * from s_p where estimated_date is null and estimated_date is not null limit 5;
  • I don't think you understand what the question is.
    – Kemal Fadillah
    Feb 16, 2012 at 8:15
  • 2
    The above query would show the mis behaving rows from which you can find the solution.
    – Naveen Kumar
    Feb 16, 2012 at 8:23
  • 1
    If that query returns any rows, I'd be seriously worried about the integrity of your data.
    – Naltharial
    Feb 16, 2012 at 8:48
  • @Naltharial Its not my data, Question above gives weird output.
    – Naveen Kumar
    Feb 16, 2012 at 9:37
  • mysql> select * from s_p where estimated_date is null and estimated_date is not null limit 5; Empty set (0.00 sec) Feb 17, 2012 at 12:22

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"

  • 2
    0 is never equal to NULL. Empty string ('') is not the same as NULL either, unless you are working with Oracle. Feb 16, 2012 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.