Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
Unless the table is being fed with new rows between and during running the 3 queries, this cannot happen! –  ypercube Feb 16 '12 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 '12 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. –  Damien_The_Unbeliever Feb 16 '12 at 7:50
1  
Are these the exact queries you're running? –  gbn Feb 16 '12 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 '12 at 8:55
show 7 more comments

migrated from stackoverflow.com Feb 16 '12 at 10:04

This question came from our site for professional and enthusiast programmers.

5 Answers

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

share|improve this answer
    
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. –  ypercube Feb 16 '12 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 "". –  user1213259 Feb 17 '12 at 12:21
    
@yper or a reason to pick a different DBMS... –  ErikE Feb 19 '12 at 1:58
    
@ErikE: That, sometimes, is not a choice. And you'll always find reasons to pick anotehr DBMS, whatever one you are working with. –  ypercube Feb 19 '12 at 2:01
add comment

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

share|improve this answer
add comment

Try the query

select * from s_p where estimated_date is null and estimated_date is not null limit 5;
share|improve this answer
    
I don't think you understand what the question is. –  Kemal Fadillah Feb 16 '12 at 8:15
2  
The above query would show the mis behaving rows from which you can find the solution. –  Naveen Kumar Feb 16 '12 at 8:23
    
If that query returns any rows, I'd be seriously worried about the integrity of your data. –  Naltharial Feb 16 '12 at 8:48
    
@Naltharial Its not my data, Question above gives weird output. –  Naveen Kumar Feb 16 '12 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) –  user1213259 Feb 17 '12 at 12:22
show 1 more comment

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

share|improve this answer
add comment

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"

share|improve this answer
1  
0 is never equal to NULL. Empty string ('') is not the same as NULL either, unless you are working with Oracle. –  ypercube Feb 16 '12 at 8:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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