Gotcha - SQL count() query with LIMIT clause
Bumped into an interesting feature today. Well, whether it’s a feature or a gotcha appears to be in the eye of the beholder. And, I’ll tell ya, for this particular beholder, it was a gotcha.
I’ve got some code that builds a SQL query from a variable set of parameters. Most parameters are involved with building a dynamic WHERE clause. Other parameters are for pagination, setting a LIMIT clause on the query that eventually gets built. Another parameter is whether to make the query a COUNT() query or whether to actually pull rows from the db.
In the case where I build a COUNT() query, I was carelessly allowing a zero-based LIMIT clause - something like LIMIT 0,10 - to be appended to the query. No sweat, I figured, since I really only need the single-row containing the count I am requesting.
However, it turns out that this LIMIT was actually be applied *before* the COUNT()-ing, not after. So, my COUNT() was completely wrong, often returning 0.
There’s an hour of my life - spent in furious frustration - that I’ll never get back.
Onward!
