MySQL Bugs: #21705: COUNT(DISTINCT field) with GROUP BY forces incorrect behaviour of LIMIT

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

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

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

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

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)

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

There is same problem with GROUP_CONCAT: see bug #21824

Retested with the latest 4.1-BK. Appears fixed (most probably by the fix for bug #21787).