MySQL Bugs: #53078: SELECT WITH LIMIT PROBLEM

Bug #53078	SELECT WITH LIMIT PROBLEM
Submitted:	22 Apr 2010 19:03	Modified:	28 Apr 2010 20:32
Reporter:	jonathan touchette	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.45-community	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	bug, count, limit, problem, SELECT

Description:
When I do :

SELECT COUNT(*) FROM `MY TABLE` LIMIT 1;

It seems to react as 

(SELECT COUNT(*) FROM `MY TABLE`) LIMIT 1

               Result : number of rows in `MY TABLE`

instead of

SELECT (COUNT(*) FROM `MY TABLE` LIMIT 1)

               Result : return 1 if there is any record into `MY TABLE`

How to repeat:
run this example in a table that has more than 5 records.

SELECT COUNT(*) FROM `MY TABLE` LIMIT 5;

Suggested fix:
Change the priority order of the LIMIT keyword.

TEMPORARY FIX FOR LIMIT 1:

SELECT 1 FROM `MY TABLE` [WHERE FIELD = VALUE] LIMIT 1;

Return 1 if a record exists

But there isn't a temporary fix for LIMIT > 1:

SELECT COUNT(*) FROM `MY TABLE` [WHERE FIELD = VALUE] LIMIT 5;

Sorry, but LIMIT is applied last, to the final result set, even after HAVING and ORDER BY clauses. This is documented (see http://dev.mysql.com/doc/refman/5.1/en/select.html) and nobody is going to change this.

You have to use some other, more creative SQL instead of:

mysql> select count(*) from mysql.user limit 1;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

Like these maybe:

mysql> select 1 from mysql.user having count(*) > 0;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from mysql.user having count(*) >= 5;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from mysql.user having count(*) >= 10;
Empty set (0.00 sec)

Statements above answers the questions like: "Are there more than N rows in the table?"

SOLUTION:

SELECT * FROM `MY TABLE` [WHERE field = value ...] LIMIT 5;

SELECT FOUND_ROWS();

/*If there is more than 5 rows, it will return 5 otherwise, it will return the number of rows */