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. ** Bug watch added: MySQL Bug System #65053 http://bugs.mysql.com/bug.php?id=65053 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1111611 Title: Mysql distinct count returns wrong value in some circumstances To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/mysql-5.5/+bug/1111611/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
