Bug #21705 | COUNT(DISTINCT field) with GROUP BY forces incorrect behaviour of LIMIT | ||
---|---|---|---|
Submitted: | 17 Aug 2006 22:50 | Modified: | 7 Sep 2006 10:02 |
Reporter: | Sergey Smirnov | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 4.1.21/4.1/5.0/5.1BK | OS: | Linux (Linux) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
Tags: | distinct, group by.limit |
[17 Aug 2006 22:50]
Sergey Smirnov
[17 Aug 2006 22:54]
Sergey Smirnov
Slightly more intestering query, returns only 1 result: select t1.id, COUNT(DISTINCT t2.value) CNT from __test1 t1 inner join __test2 t2 on t2.id = t1.id group by t1.id limit 3 While the same one without a limit returns 3 correctly: select t1.id, COUNT(DISTINCT t2.value) CNT from __test1 t1 inner join __test2 t2 on t2.id = t1.id group by t1.id
[17 Aug 2006 23:21]
Sergey Smirnov
Sorry, forgot to add one more thing. Adding "SQL_CALC_FOUND_ROWS" right after the SELECT seems to fix the problem. So the query below works correctly: select SQL_CALC_FOUND_ROWS t1.id, COUNT(DISTINCT t2.value) CNT from __test1 t1 inner join __test2 t2 on t2.id = t1.id group by t1.id limit 3
[18 Aug 2006 1:24]
MySQL Verification Team
Thank you for the bug report. I was able to repeat for 4.1/5.0/5.1 source tree: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.27-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select C -> COUNT(DISTINCT t1.ID) CNT -> from __test1 t1 -> inner join __test2 t2 on t2.id = t1.id -> group by t1.id -> limit 3; +-----+ | CNT | +-----+ | 1 | | 1 | | 1 | +-----+ 3 rows in set (0.00 sec) --------------------------------XXXX-------------------------------- miguel@hegel:~/dbs/4.1> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.22-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select -> COUNT(DISTINCT t1.ID) CNT -> from __test1 t1 -> inner join __test2 t2 on t2.id = t1.id -> group by t1.id -> limit 3; +-----+ | CNT | +-----+ | 1 | | 1 | +-----+ 2 rows in set (0.01 sec) --------------------------------XXXX-------------------------------- Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.25-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select -> COUNT(DISTINCT t1.ID) CNT -> from __test1 t1 -> inner join __test2 t2 on t2.id = t1.id -> group by t1.id -> limit 3; +-----+ | CNT | +-----+ | 1 | | 1 | +-----+ 2 rows in set (0.03 sec) --------------------------------XXXX-------------------------------- Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select -> COUNT(DISTINCT t1.ID) CNT -> from __test1 t1 -> inner join __test2 t2 on t2.id = t1.id -> group by t1.id -> limit 3; +-----+ | CNT | +-----+ | 1 | | 1 | +-----+ 2 rows in set (0.03 sec)
[22 Aug 2006 22:43]
dAniel hAhler
JFI: I cannot reproduce it with 5.0.22-Debian_0ubuntu6.06-log, but found it, while searching for a bug like this (which I also cannot reproduce). See http://forums.b2evolution.net/viewtopic.php?t=8951
[25 Aug 2006 18:48]
Sveta Smirnova
There is same problem with GROUP_CONCAT: see bug #21824
[7 Sep 2006 10:02]
Georgi Kodinov
Retested with the latest 4.1-BK. Appears fixed (most probably by the fix for bug #21787).