Bug #30402 SELECT count(distinct) from mytable return 0
Submitted: 14 Aug 2007 8:35 Modified: 5 Dec 2012 19:43
Reporter: Bordas David Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.37 Community OS:Linux (RedHat 9.0)
Assigned to: CPU Architecture:Any
Tags: 5.0.37, count, distinct, error

[14 Aug 2007 8:35] Bordas David
Description:
Server version: 5.0.37 MySQL Community Server from official binaries tar.gz on RedHat 9.0.

Storage Engine : MyISAM.

Select count(distinct(my_key) from my_table could sometimes return 0 which is wrong...
Table is not empty, select count is ok, select distinct is ok, select count(distinct) is not ok !!
Some days, table will have more ou less rows and this will be ok, not today.

How to repeat:
Engine : MyISAM

Table load : none, just my select queries like above (every night)

>desc my_table :
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| my_key      | char(38)              | NO   | PRI |         |                |
| idincrem    | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
+-------------+-----------------------+------+-----+---------+----------------+

>select count(*) from my_table;
+----------+
| count(*) |
+----------+
|   552580 |
+----------+
1 row in set (0.00 sec)

>select * from my_table limit 10;
+---------------------------------------+----------+
| my_key                                | idincrem |
+---------------------------------------+----------+
| 0010200606061621462051808202300196218 |        1 |
| 0010200606061621501369308704204897718 |        1 |
| 0010200606061621512396608009604812539 |        1 |
| 0010200606061621530875408722800291139 |        1 |
| 0010200606061621550189408104000294041 |        1 |
| 0010200606061622012875908312504805588 |        1 |
| 0010200606061622211146208216700259669 |        1 |
| 0010200606061622481869808222904854167 |        1 |
| 0010200606061622562613508625404881438 |        1 |
| 0010200606061625140771008211700174474 |        1 |
+---------------------------------------+----------+
10 rows in set (0.00 sec)

> select distinct(my_key) from my_table limit 10;
+---------------------------------------+
| my_key                                |
+---------------------------------------+
| 0010200606061621462051808202300196218 |
| 0010200606061621501369308704204897718 |
| 0010200606061621512396608009604812539 |
| 0010200606061621530875408722800291139 |
| 0010200606061621550189408104000294041 |
| 0010200606061622012875908312504805588 |
| 0010200606061622211146208216700259669 |
| 0010200606061622481869808222904854167 |
| 0010200606061622562613508625404881438 |
| 0010200606061625140771008211700174474 |
+---------------------------------------+
10 rows in set (0.00 sec)

>select count(distinct(my_key)) from my_table;
+------------------------------+
| count(distinct(id_visiteur)) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

Dump table, drop table and reimport table will not fix this issue.
Thanks.
[14 Aug 2007 9:01] Sveta Smirnova
Thank you for the report.

But version 5.0.37 is a bit old and several DISTINCT bugs were fixed since. Additionally I can not repeat described behaviour with provided data. Please upgrade to current version 5.0.45, try with it and say us results.
[14 Aug 2007 9:46] Bordas David
Same datas (from same dump), same MySQL version, other serveur, the queries seems to be ok...
I'll try with 5.0.45 to see if this is still ok in test environnement.
If yes, i'll try to upgrade production serveurs soon and i'll keep you informed.
[14 Aug 2007 11:43] MySQL Verification Team
If the issue persist after the upgrade, please provide the dump of the
table and the my.cnf. Thanks in advance.
[21 Aug 2007 6:52] Bordas David
Hi,

I've just finished to upgrade to 5.0.45. Query is now working, this bug seems to be gone.
Thank you. :)
[21 Aug 2007 7:07] Sveta Smirnova
Thank you, Bordas, for the feedback.

The report closed as "Can't repeat" according to last comment.
[11 Aug 2012 23:51] Haluk Akin
We experience a similar problem with 5.5.25a.
MyISAM table as well.

It seems to particularly happen when each row is not distinct. If all the rows called up is distinct, then the result returns correct. But when there are duplicate rows, the result returns 0.

The table is 700million rows large, so it is a bit hard to do a dump or an index check.

But the interesting thing is, we have a slave with 5.5.25a and the count(distinct) works perfectly on the slave. The my.cnf files are almost identical on both server.

For now we decided to convert all such queries to GROUP BY statements.
[13 Aug 2012 11:01] Sveta Smirnova
Haluk,

thank you for the feedback. We could not repeat this bug at our side in year 2007, so we need repeatable test case if you claim this is MySQL bug.

But having the fact copy on slave is working fine, this means most likely original table is corrupted and REPAIR TABLE and/or mysiamchk with repair option could solve the problem. Please try.
[14 Nov 2012 20:17] Edward Walker
I have a simple test case that hits this bug in 5.5.28.  After I submit this comment I will try to upload the myisam table files.

I installed the community version of 5.5.28 using the rpm for generic linux.

We actually hit this bug initially when we were using Innodb tables.  I use a myisam table for the test case here since myisam tables can be copied easily from one instance to another.

[root@testbox test]# uname -a
Linux testbox 2.6.35.14-106.fc14.x86_64 #1 SMP Wed Nov 23 13:07:52 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux

[root@testbox test]# mysql -S /tmp/bug.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28 MySQL Community Server (GPL)

[snip]

mysql> show create table efw_mysql_bug \G
*************************** 1. row ***************************
       Table: efw_mysql_bug
Create Table: CREATE TABLE `efw_mysql_bug` (
  `foobar` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> select count(distinct foobar) from efw_mysql_bug;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.17 sec)

mysql> select count(foobar) from (select distinct foobar from efw_mysql_bug) i;
+---------------+
| count(foobar) |
+---------------+
|         80473 |
+---------------+
1 row in set (8.72 sec)

mysql> select sum(case when foobar is null then 1 else 0 end) null_count, count(*) from efw_mysql_bug;
+------------+----------+
| null_count | count(*) |
+------------+----------+
|    2499716 |  2606621 |
+------------+----------+
1 row in set (0.63 sec)
[14 Nov 2012 20:36] Edward Walker
The files were too big to directly upload to this bug.

I uploaded bug-data-30402.tar.gz to ftp://ftp.oracle.com/support/incoming.

How to reproduce:

[root@testbox mysql]# cd /var/lib/mysql/test
[root@testbox test]# tar -xvf ~/bug-data-30402.tar.gz 
README
efw_mysql_bug.MYD
efw_mysql_bug.MYI
efw_mysql_bug.frm
[root@testbox test]# chown mysql:mysql efw_mysql_bug.*
[root@testbox test]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(distinct foobar) from efw_mysql_bug;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.15 sec)

mysql> select count(foobar) from (select distinct foobar from efw_mysql_bug) i;
+---------------+
| count(foobar) |
+---------------+
|         80473 |
+---------------+
1 row in set (8.69 sec)
[22 Nov 2012 19:17] Sveta Smirnova
Edward,

thank you for the feedback.

But I can not repeat described behavior with your table too:

mysql> select count(distinct foobar) from efw_mysql_bug;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                  80473 |
+------------------------+
1 row in set (0.57 sec)

mysql> select count(foobar) from (select distinct foobar from efw_mysql_bug) i;
+---------------+
| count(foobar) |
+---------------+
|         80473 |
+---------------+
1 row in set (4.55 sec)

Please run CHECK TABLE EXTENDED on this table and send us your configuration file.
[5 Dec 2012 19:20] Edward Walker
My MySql configuration is trivial, and CHECK TABLE EXTENDED said the table is OK.  FYI, I'm running on Fedora 14.

[root@testbox ~]# uname -a
Linux testbox 2.6.35.14-106.fc14.x86_64 #1 SMP Wed Nov 23 13:07:52 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux

[root@testbox ~]# cat /etc/my.cnf
[mysqld]
skip-networking
socket = /tmp/bug.sock

[root@testbox ~]# mysql -S /tmp/bug.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> check table efw_mysql_bug extended;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| test.efw_mysql_bug | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.42 sec)

mysql> select count(distinct foobar) from efw_mysql_bug;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.14 sec)

mysql> select count(foobar) from (select distinct foobar from efw_mysql_bug) i;
+---------------+
| count(foobar) |
+---------------+
|         80473 |
+---------------+
1 row in set (8.81 sec)

mysql> repair table efw_mysql_bug;
+--------------------+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| test.efw_mysql_bug | repair | status   | OK       |
+--------------------+--------+----------+----------+
1 row in set (0.90 sec)

mysql> select count(distinct foobar) from efw_mysql_bug;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.14 sec)

mysql> alter table efw_mysql_bug engine = innodb;
Query OK, 2606621 rows affected (40.34 sec)
Records: 2606621  Duplicates: 0  Warnings: 0

mysql> select count(distinct foobar) from efw_mysql_bug;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.97 sec)

mysql> select count(foobar) from (select distinct foobar from efw_mysql_bug) i;
+---------------+
| count(foobar) |
+---------------+
|         80473 |
+---------------+
1 row in set (12.62 sec)
[5 Dec 2012 19:43] Sveta Smirnova
Thank you for the feedback.

Verified as described using Linux 5.5.28 package and MySQL Sandbox with minimal configuration. Important: one should move files to datadir/dbname and only after start MySQL server. Also looks like I tested with debug BZR build before and could not repeat.
[5 Dec 2012 20:34] Edward Walker
Thanks.  Additional odd symptoms that might help with debugging:

mysql> create table efw_copy engine = myisam select * from efw_mysql_bug;
Query OK, 2606621 rows affected (1.23 sec)
Records: 2606621  Duplicates: 0  Warnings: 0

mysql> alter table efw_copy add column id int ;
Query OK, 2606621 rows affected (1.05 sec)
Records: 2606621  Duplicates: 0  Warnings: 0

mysql> set @id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update efw_copy set id = (@id := @id + 1);
Query OK, 2606621 rows affected (17.76 sec)
Rows matched: 2606621  Changed: 2606621  Warnings: 0

mysql> select count(distinct foobar) from efw_copy;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.29 sec)

mysql> select count(distinct foobar) from efw_copy where id <= 593237;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                  21184 |
+------------------------+
1 row in set (0.66 sec)

mysql> select count(distinct foobar) from efw_copy where id <= 593238;
+------------------------+
| count(distinct foobar) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.73 sec)
[8 Feb 2013 17:31] Denis Lamotte
Does anyone have a solution for this ?  
i have the same problem with the latest version on several serveur with ubuntu server. 
I can give the corresponding VM (300 Giga) if needed
[22 May 2013 20:42] Stephen Weiss
I also have this bug, running 5.5.30 on Debian squeeze (5.5.30-1~dotdeb.0-log).  The table is in InnoDB with some 63M rows and not all count distincts return 0 - it seems to only happen if the number of rows is sufficiently large.  Also, we have this on a database which is replicated from a 64-bit master to a 32-bit slave (which is used for backups).  On the 32 bit slave, oddly, the number of rows required to trigger the problem seems to be higher than on 64-bit.  On 64-bit, we have examples of failures at 8400 and 85000 rows - but on the 32-bit system, only the 85000 row example fails.

The failing query is:

SELECT count(DISTINCT obj_class, obj_id) from folderitems where  `f_id` = 1063738;

Simply writing to:

SELECT count(*) from (SELECT DISTINCT obj_class, obj_id) from folderitems where  `f_id` = 1063738) as a;

returns the correct result.  We have written workarounds using this for now, however performance is significantly slower and we would like to return to the simpler syntax.

The obj_id column is indexed, however obj_class is not.

We just upgraded from 5.0.51a after a long time spent in QA checking for issues, but this issue just went unnoticed because there really aren't so many entries in this table which return so many results.
[1 Nov 2013 15:48] Joris Van den Bogaert
I'm also having problems with select distinct on mysql 5.6.12:

An InnoDB table with a couple million rows. A distinct on one of the columns was always yielding 0. Using binary search I found that the bug occurs if the id is smaller than 500,000.

mysql> select count(distinct theme_author) from url_item where id < 499999;
+------------------------------+
| count(distinct theme_author) |
+------------------------------+
|                        36493 |
+------------------------------+
1 row in set (0.44 sec)

mysql> select count(distinct theme_author) from url_item where id < 500000;
+------------------------------+
| count(distinct theme_author) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.44 sec)

mysql> explain select count(distinct theme_author) from url_item where id < 499999;
+----+-------------+----------+-------+------------------------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys                                        | key     | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+------------------------------------------------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | url_item | range | PRIMARY,ndx_theme_author,ndx_theme_author_theme_name | PRIMARY | 8       | NULL | 1022302 | Using where |
+----+-------------+----------+-------+------------------------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(distinct theme_author) from url_item where id < 500000;
+----+-------------+----------+-------+------------------------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys                                        | key     | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+------------------------------------------------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | url_item | range | PRIMARY,ndx_theme_author,ndx_theme_author_theme_name | PRIMARY | 8       | NULL | 1022300 | Using where |
+----+-------------+----------+-------+------------------------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.03 sec)
[10 Mar 2014 14:31] Danillo Souza
We solved that bizarre problem by removing tmp_table_size from our my.cnf. It was set with an invalid value.