Bug #21787 COUNT(*) + ORDER BY + LIMIT returns wrong result
Submitted: 22 Aug 2006 15:46 Modified: 19 Sep 2006 16:32
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 + 5.1 + 4.1 OS:Windows (Windows, Linux)
Assigned to: Timour Katchaounov CPU Architecture:Any

[22 Aug 2006 15:46] Georg Richter
Description:
SELECT COUNT with ORDER BY and LIMIT returns wrong result.

How to repeat:
create table t1 (a int);
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
#following select returns 7 (ok)
select count(*) c FROM t1 WHERE a > 0;
#following select returns 7 (ok)
select count(*) c FROM t1 WHERE a > 0 ORDER BY c;
#following select returns 7 (ok)
select count(*) c FROM t1 WHERE a > 0 LIMIT 3;

#following select returns 3 (bug!)
select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
[22 Aug 2006 16:56] MySQL Verification Team
Thank you for the bug report.

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.27-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+---+
| c |
+---+
| 7 |
+---+
1 row in set (0.00 sec)

mysql> 
-----------------------------XXX-----------------------------
mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.22-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+---+
| c |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

mysql> 
-----------------------------XXX-----------------------------
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+---+
| c |
+---+
| 3 | 
+---+
1 row in set (0.01 sec)

mysql> 
-----------------------------XXX-----------------------------
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+---+
| c |
+---+
| 3 | 
+---+
1 row in set (0.01 sec)

mysql>
[31 Aug 2006 11:47] 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/commits/11157

ChangeSet@1.2535, 2006-08-31 14:47:27+03:00, timour@lamia.home +3 -0
  Fix for BUG#21787: COUNT(*) + ORDER BY + LIMIT returns wrong result
  
  The problem was due to a prior fix for BUG 9676, which limited
  the rows stored in a temporary table to the LIMIT clause. This
  optimization is not applicable to non-group queries with aggregate
  functions. The fix disables the optimization in this case.
[31 Aug 2006 14:05] 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/commits/11166

ChangeSet@1.2535, 2006-08-31 17:04:00+03:00, timour@lamia.home +3 -0
  Fix for BUG#21787: COUNT(*) + ORDER BY + LIMIT returns wrong result
  
  The problem was due to a prior fix for BUG 9676, which limited
  the rows stored in a temporary table to the LIMIT clause. This
  optimization is not applicable to non-group queries with aggregate
  functions. The fix disables the optimization in this case.
[4 Sep 2006 14:28] Magnus BlÄudd
Pushed to 5.0.25
[6 Sep 2006 23:55] Jon Stephens
Documented this bugfix in the 4.1.22 and 5.0.25 changelogs, as this is a showstopper.

Set status to NDI pending push to 5.1.
[18 Sep 2006 21:37] Timothy Smith
Pushed to 5.1.12
[19 Sep 2006 16:32] Paul DuBois
Noted in 5.1.12 changelog.