Bug #21787 COUNT(*) + ORDER BY + LIMIT returns wrong result

• Submitted: 22 Aug 2006 15:46
• Modified: 19 Sep 2006 16:32
• Reporter: Georg Richter
• Status: Closed
• 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, [e-mail] +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, [e-mail] +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.