MySQL Bugs: #50005: select count(*) with LIMIT key word doesn't return expected # of results

Bug #50005	select count(*) with LIMIT key word doesn't return expected # of results
Submitted:	30 Dec 2009 19:00	Modified:	7 Jan 2010 12:13
Reporter:	Martijn Verburg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.41	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	limit, SELECT

Description:
Sample queries:

This doesn't quite work:

select count(*) from IkasanWiretap limit 0, 5;

Returns 24 (total rows in table)

This does work (brings back first 5 rows as expected):

select * from IkasanWiretap limit 0, 5;

This is a fairly annoying problem for ORM mapping tools such as hibernate who execute the equivalent of this query.

How to repeat:
Sample queries:

This doesn't quite work:

select count(*) from IkasanWiretap limit 0, 5;

Returns 24 (total rows in table)

This does work (brings back first 5 rows as expected):

select * from IkasanWiretap limit 0, 5;

Suggested fix:
count(*) should be applied after the limit is applied

Please, check with a newer version, 5.1.41, and inform about the results.

I'll check with 5.1.41 shortly, also this fails to return anything (e.g.  Returns null):

select count(*) from IkasanWiretap limit 1, 20;

Not much idea in aking people to try other versions.  That is only wasting time. MySQL evaluates LIMIT last like that and always did.

SELECT VERSION(); -- 5.1.41-community
SELECT COUNT(*) FROM test; -- returns 3
SELECT COUNT(*) FROM test LIMIT 2; -- returns 3
SELECT COUNT(*) FROM test LIMIT 1; -- returns 3
-- but
SELECT COUNT(*) FROM test LIMIT 0;
/* returns
COUNT(*)
--------
*/
-- note: 0 rows returned due to "LIMIT 0"

-- Obviously LIMIT is applied as the very last operation here. SELECT COUNT(*) (or SELECT COUNT(columnname)) with no LIMIT returns a result set with one row and LIMITing result set with a LIMIT clause >= 0 does nothing. Only if LIMIT is 0 the LIMIT clause has any effect at all as it will then return 0 rows in result set. I do not know what is correct according to standards - but MySQL version is irrelevant as all versions do like this and always did.

Peter
(not a MySQL person)

Of course I meant: "LIMITing result set with a LIMIT clause >= 1 does nothing".

Thanks Peter, I did test it with 5.1.41 and I get the same results.  I've updated the version field in this report to match.  As an aside, I don't think LIMIT is std ANSI SQL?  Therefore it's behaviour is perhaps up to whatever MySQL deems it to be correct, but the current behaviour it seems counter intuitive/inconsistent to me?

LIMIT is not in the standard.
But I've seen a similar functionality (using a different syntax) in the next sql standard draft. The semantics there matches our LIMIT. That is, LIMIT M,N takes rows from M to M+N in what the query would return without LIMIT.

As SELECT COUNT(*) returns 1 row, adding LIMIT 5 or LIMIT 20 won't change its result, you'll still get the very same one row.

OK in that case I'll need to chat to the Hibernate guys, they've been using this under false assumptions methinks.  Thanks all for looking into this, it was an interesting one to run across

In SQL:2008 (which is now official not draft)
one can say "FETCH FIRST 50 ROWS ONLY" and the
effect is similar to MySQL's behaviour with LIMIT.

I remember that SQL Server has a parallel/an alternative to LIMIT.  I do not remember details, but it is more flexible/usable as you can LIMIT both 'from top' and 'from bottom'.

A workaround in MySQL could be (for instance) a subquery in the FROM-clause like

SELECT COUNT(*) FROM (SELECT * FROM the_table LIMIT y);

.. not elegant, probably not efficient, but functional.

For those tracking this, I've opened a bug report with the Hibernate team (haven't tried with other ORM technologies) here at http://opensource.atlassian.com/projects/hibernate/browse/HHH-4761

Cheers,
Martijn