Bug #64343 Count Distinct Giving Different Results Depending on Index

Submitted: 15 Feb 2012 16:48 Modified: 16 Feb 2012 4:19
Reporter: Ben Murphy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5.20 OS:MacOS
Assigned to: CPU Architecture:Any

[15 Feb 2012 16:48] Ben Murphy
Description:
If you have two count(distinct x) expressions in a query then you will get back different (incorrect) results if you have an index on both of the the distinct columns.

How to repeat:
mysql> create table test(a int, b int);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test(a,b) values(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(a,b) values(1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(a,b) values(1, 3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(a,b) values(1, 4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(a,b) values(1, 5);
Query OK, 1 row affected (0.00 sec)

mysql> select SQL_NO_CACHE count(distinct a), count(distinct b) from test;
+-------------------+-------------------+
| count(distinct a) | count(distinct b) |
+-------------------+-------------------+
|                 1 |                 5 |
+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> alter table test add index (a,b);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select SQL_NO_CACHE count(distinct a), count(distinct b) from test;
+-------------------+-------------------+
| count(distinct a) | count(distinct b) |
+-------------------+-------------------+
|                 5 |                 5 |
+-------------------+-------------------+
1 row in set (0.01 sec)
[15 Feb 2012 17:20] Valeriy Kravchuk
Looks like a duplicate of bug #62504. Please, check.
[15 Feb 2012 17:25] Ben Murphy
it's a duplicate of that bug

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.