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:
None 
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
Description:
Using COUNT(DISTINCT field) in select combined with GROUP BY and LIMIT seems to effect how LIMIT works. Instead of limiting the rows after the grouping it seems to limit the rows before the grouping. Otherwise I can't explain the results received.

How to repeat:
CREATE TABLE `__test1` (
  `id` int(11) unsigned NOT NULL default '0',
  `data` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `__test2` (
  `id` int(11) unsigned NOT NULL default '0',
  `value` smallint(3) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into __test1 values (1, 'some data'), (2, 'data for row 2'), (3, 'data for row 3');

insert into __test2 values (1, 3), (1, 5), (2, 10), (2, 15), (3, 17), (3, 20);

=============================================================================

select
COUNT(DISTINCT t1.ID) CNT
from __test1 t1
inner join __test2 t2 on t2.id = t1.id
group by t1.id
limit 3;

..returns only 1 row

select
COUNT(t1.ID) CNT
from __test1 t1
inner join __test2 t2 on t2.id = t1.id
group by t1.id
limit 3;

..returns 3 rows as expected
[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).