MySQL Bugs: #25445: LIMIT returns incorrect result when used with LEFT JOIN, GROUP BY, and DISTINCT

Bug #25445	LIMIT returns incorrect result when used with LEFT JOIN, GROUP BY, and DISTINCT
Submitted:	6 Jan 2007 2:44	Modified:	6 Jan 2007 3:08
Reporter:	Jared Luxenberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Linux (Ubuntu (Debian))
Assigned to:		CPU Architecture:	Any

Description:
When running a LIMIT query with a LEFT JOIN, GROUP BY, and a DISTINCT function, the LIMIT seems to apply to the records obtained _before_ they are grouped, not after.

How to repeat:
Try the following DDL (my results shown):

select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.24a-Debian_9-log | 
+----------------------+
1 row in set (0.00 sec)

create table tmp (id int, type varchar(10));

insert into tmp values (1, 'foo');
insert into tmp values (2, 'foo');
insert into tmp values (3, 'foo');
insert into tmp values (4, 'foo');
insert into tmp values (5, 'foo');

insert into tmp values (6, 'bar');
insert into tmp values (7, 'bar');
insert into tmp values (8, 'bar');
insert into tmp values (9, 'bar');
insert into tmp values (10, 'bar');

create table tmp_properties (id int, title varchar(25));
insert into tmp_properties values (1, 'foo_title');

# the following query behaves as expected; both types 'foo' and 'bar' are returned
select type, count(distinct(tmp.id)) count from tmp group by type limit 0,2;

+------+-------+
| type | count |
+------+-------+
| bar  |     5 | 
| foo  |     5 | 
+------+-------+
2 rows in set (0.01 sec)

# the following query does not behave as I expect.
# it appears to apply the limit _before_ applying the group by
# only one record is returned, instead of 2
select type, count(distinct(tmp.id)) count from tmp left join tmp_properties tp on tp.id = tmp.type group by type limit 0,2; 

+------+-------+
| type | count |
+------+-------+
| foo  |     2 | 
+------+-------+
1 row in set (0.02 sec)

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

mysql> select type, count(distinct(tmp.id)) count from tmp group by type limit 0,2;
+------+-------+
| type | count |
+------+-------+
| bar  |     5 |
| foo  |     5 |
+------+-------+
2 rows in set (0.05 sec)

mysql> select type, count(distinct(tmp.id)) count from tmp left join tmp_properties tp
    -> on tp.id = tmp.type group by type limit 0,2;
+------+-------+
| type | count |
+------+-------+
| bar  |     5 |
| foo  |     5 |
+------+-------+
2 rows in set (0.06 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql>