PapayaSoft - Phuket web development

Gotcha – SQL Count() Query With LIMIT Clause

| Comments

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.