Mysql distinct count returns wrong value in some circumstances

[Dave L]

Under some circumstances, an SQL query containing count(distinct(item)) with a "where" clause returns half the value it should (rounded up to an integer if applicable).

I have been unable to determine the exact qualities of the data that cause this situation to occur, however the test case attached reproduces the problem reliably.

Example of expected output from the attached script (Server is Ubuntu 10.04)
dave@hp:~$ mysql -u davetest -p -h 10.0.3.85 < ~/dumps/testdump6.sql
Enter password:
count(distinct(testtab.foo))
111
count(testtab.foo)
111
count(distinct(testtab.foo))
111

Example of incorrect output from Ubuntu 12.10 / 12.04
dave@hp:~$ mysql -u davetest -p < ~/dumps/testdump6.sql
Enter password:
count(distinct(testtab.foo))
56
count(testtab.foo)
111
count(distinct(testtab.foo))
111

The same client machine was used for both tests. The problem is also seen with the php5-mysql client.

ProblemType: Bug
DistroRelease: Ubuntu 12.10
Package: mysql-server 5.5.29-0ubuntu0.12.10.1
ProcVersionSignature: Ubuntu 3.5.0-22.34-generic 3.5.7.2
Uname: Linux 3.5.0-22-generic x86_64
ApportVersion: 2.6.1-0ubuntu10
Architecture: amd64
Date: Thu Jan 31 16:40:31 2013
InstallationDate: Installed on 2013-01-21 (10 days ago)
InstallationMedia: Ubuntu 12.10 "Quantal Quetzal" - Release amd64 (20121017.5)
Logs.var.log.daemon.log:

MarkForUpload: True
MySQLConf.etc.mysql.conf.d.mysqld.safe.syslog.cnf:
 [mysqld_safe]
 syslog
MySQLVarLibDirListing: False
PackageArchitecture: all
SourcePackage: mysql-5.5
UpgradeStatus: No upgrade log present (probably fresh install)

[Robie Basak (racb)]

Thank you for taking the time to report this bug and helping to make Ubuntu better.

I've confirmed that I can reproduce this in Precise, Quantal and Raring. It certainly looks wrong to me. But with it being MySQL I'm half expecting someone to tell me that this is expected behaviour for some obscure reason.

So two things we can do from here:

1) Verify that this behaviour is in fact a bug.

2) Build the latest upstream version without packaging, check if the behaviour is still the same, and report upstream if appropriate.

[Dave L (westy3)]

I see the same behaviour on mysql 5.5.28 on Cygwin. So the issue is not Ubuntu-specific. The comparison therefore is between Mysql5.1 and Mysql5.5. 5.1 was available in the 12.04 repository as a non-default install, but it has subsequently been removed.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[pwaring (launchpad-pwaring)]

I can confirm this bug occurs for me in 12.04 with the following mysql version:

mysql Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2

I did a bit of Googling and found this bug, which might be related:

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

The final comment suggests there may be a problem with composite indexes - i.e. those consisting of more than one column. I took out both of the indexes and created the table again, using the following SQL:

CREATE TABLE `testtab` (
  `foo` int(11) NOT NULL AUTO_INCREMENT,
  `bar` char(3) NOT NULL,
  PRIMARY KEY (`foo`)
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=latin1;

Re-running the SELECT queries, I now get 111 returned. I tried creating the table again, this time just deleting the idx_listfieldmashup index (i.e. the composite one) and again got the results I expected.

I'm not sure why the composite index would cause this problem, but I hope it's a good pointer for where to look further.

[Morgan Tocker (morgo)]

I can confirm that this bug is fixed by upstream in 5.5.36 (latest). I believe it might be http://bugs.mysql.com/bug.php?id=68749

Here's the results of the testcase showing expected output:

morgo@Rbook:~/sandboxes/msb_5_5_36$ ./use < ~/Desktop/testdump6.sql.txt
count(distinct(testtab.foo))
111
count(testtab.foo)
111
count(distinct(testtab.foo))
111