Bug #8617 SQL_CALC_FOUND_ROWS with rollup , affect limit
Submitted: 19 Feb 2005 2:06 Modified: 21 Mar 2005 20:04
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:
Assigned to: Igor Babaev CPU Architecture:Any

[19 Feb 2005 2:06] Martin Friebe
Description:
according to the doc limit is applied after rollup. This seems to work, but it breaks, when using SQL_CLAC_FOUND_ROWS.

see example

How to repeat:
create table x (a integer, b integer);
insert into x values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);

# this is fine
 select  SQL_CALC_FOUND_ROWS  a, sum(b) from x group by a  limit 1;
+------+--------+
| a    | sum(b) |
+------+--------+
|    1 |      4 |
+------+--------+
1 row in set (0.00 sec)

# this is also fine
select   a, sum(b) from x group by a with rollup  limit 1;
+------+--------+
| a    | sum(b) |
+------+--------+
|    1 |      4 |
+------+--------+
1 row in set (0.01 sec)

# and this returns the rollup, even so its clearly outside the limit
 select  SQL_CALC_FOUND_ROWS  a, sum(b) from x group by a with rollup  limit 1;
+------+--------+
| a    | sum(b) |
+------+--------+
|    1 |      4 |
| NULL |     12 |
+------+--------+
2 rows in set (0.00 sec)

Suggested fix:
-
[18 Mar 2005 4:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23168
[20 Mar 2005 7:55] Igor Babaev
The fix for this bug will appear in 4.1.11 and 5.0.4 (or even in 5.0.3)
The chanset for the fix for 4.1 was merged into 5.0.

ChangeSet
  1.2134 05/03/17 20:18:19 igor@rurik.mysql.com +3 -0
  olap.test:
    Added a test case for bug #8617.
  sql_select.cc:
    Fixed bug #8617.
    Queries with ROLLUP and LIMIT n returned more than n rows
    if SQL_CALC_FOUND_ROWS was used.
[21 Mar 2005 20:04] Paul Dubois
Noted in 4.1.11, 5.0.4 changelogs.