Bug #866 COUNT(1) with LIMIT clause returns all rows found

• Submitted: 17 Jul 2003 9:46
• Modified: 17 Jul 2003 10:12
• Reporter: Patrick O'Lone
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.13
• OS: Linux (Linux)
• CPU Architecture: Any

[17 Jul 2003 9:46] Patrick O'Lone

Description:
When performing a SELECT COUNT(1) FROM table LIMIT 0,10, COUNT() will return all rows found instead of 10. It use to function based on the LIMIT clause itself, so COUNT(1) would return 10.

How to repeat:
CREATE TABLE foo (

   bar INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

);

insert into foo values ();
insert into foo values ();
insert into foo values ();

select count(1) from foo limit 0,1;

Output:

+----------+
| count(1) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

[17 Jul 2003 10:12] Indrek Siitan

This is completely expected behaviour. LIMIT is applied as a last thing in processing the query to 
limit the rows returned. The COUNT(*) statement is parsed before the LIMIT, so it takes all rows in 
the table into account.