| Bug #65053 | Incorrect select results when using index and complicated select count(distinct( | ||
|---|---|---|---|
| Submitted: | 20 Apr 2012 10:52 | Modified: | 26 Apr 2012 8:53 |
| Reporter: | Romans Heimanis | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.5.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | count, distinct, INDEX, SELECT | ||
[20 Apr 2012 10:52]
Romans Heimanis
[20 Apr 2012 10:54]
Romans Heimanis
The sql file that implement the test table and failing selects
Attachment: testcase.sql (text/x-sql), 10.44 KiB.
[20 Apr 2012 10:55]
Romans Heimanis
When no index is used, everything works ok. Also everything works ok with 5.0 versions of mysql
[20 Apr 2012 11:47]
MySQL Verification Team
Please test against the 5.5.23 server. Thanks.
[20 Apr 2012 12:50]
Romans Heimanis
same thing
[20 Apr 2012 12:56]
Valeriy Kravchuk
I've got the following results: macbook-pro:5.5 openxs$ bin/mysql -uroot test < ~/Downloads/testcase.sql count( distinct( user_id ) ) 16 count( distinct( ip ) ) 1 count( distinct( ip ) ) count( distinct( user_id ) ) 1 16 So, where is the bug here?
[20 Apr 2012 12:59]
Romans Heimanis
In my case: test2 ~ # mysql -prootpass test < testcase.sql count( distinct( user_id ) ) 16 count( distinct( ip ) ) 1 count( distinct( ip ) ) count( distinct( user_id ) ) 16 16 also will attach my.cnf
[20 Apr 2012 13:00]
Romans Heimanis
my.cnf config
Attachment: my.cnf (application/octet-stream, text), 2.15 KiB.
[20 Apr 2012 13:04]
Romans Heimanis
gcc 4.5.3, default cmake rules
[22 Apr 2012 15:27]
Valeriy Kravchuk
I do not see any problem with 5.5.21 on Mac OS X: macbook-pro:mysql-5.5.21-osx10.5-x86_64 openxs$ bin/mysql -uroot test < ~/Downloads/testcase.sql count( distinct( user_id ) ) 16 count( distinct( ip ) ) 1 count( distinct( ip ) ) count( distinct( user_id ) ) 1 16 So, what operating system do you use to run MySQL and, if you built from source code, please, specify exact commands used to build it.
[23 Apr 2012 8:32]
Romans Heimanis
Linux 3.2.12, default compilation options.
[24 Apr 2012 15:50]
MySQL Verification Team
I couldn't repeat on Windows and on Centos: +------------------------------+ | count( distinct( user_id ) ) | +------------------------------+ | 16 | +------------------------------+ 1 row in set (0.00 sec) +-------------------------+ | count( distinct( ip ) ) | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec) +-------------------------+------------------------------+ | count( distinct( ip ) ) | count( distinct( user_id ) ) | +-------------------------+------------------------------+ | 1 | 16 | +-------------------------+------------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+--------------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------------+ | innodb_version | 1.1.8 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.23-log | | version_comment | MySQL Community Server (GPL) by Remi | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+--------------------------------------+ 7 rows in set (0.01 sec)
[26 Apr 2012 8:53]
Romans Heimanis
Maybe i can give you the access to our dedicated (test) server so you can see?
[18 Nov 2012 10:27]
Vilnis Termanis
Test case (reproduced wth 5.5.17 but not 5.5.28)
Attachment: count_bug.sql (application/octet-stream, text), 1.23 KiB.
[18 Nov 2012 10:37]
Vilnis Termanis
I've come across what I think is the same problem (see attached test case): Using COUNT() with a composite index (DISTINCT not required) produces an incorrect result with a high enough number of rows in the database table. But I can only reproduce it when: - Using 5.5.17 (e.g. 5.5.28 does not seem to be affected) - Adding a composite index to an existing/populated table. With a new table (or after running OPTIMIZE TABLE) the COUNT() values match up again. However, other than maybe Bug #12713907 (5.5.25) or #13444084 (5.5.27), I don't see anything obvious between the mentioned versions which should result in behaviour changing (and incorrect results no longer being produced).
