Select count with offset returns nothing.

Hi,

I think problem is in OFFSET 15

It means return rows after row 15... because of SELECT COUNT(*)
FROM batches LIMIT 15 returns 1 row when you add OFFSET 15 - it returns
nothing... because of there is no more than 15 rows...

I am not sure u can do something else then to change library to remove
OFFSET in SELECT COUNT(*)

Kind Regrads,

Misa
2011/8/5 Tim Uckun <timuckun@gmail.com>

My question is why would you put an offset in a query designed to return a row count without grouping and ordering?

Tim Uckun <timuckun@gmail.com> writes:

I am using a library which is emitting SQL like this SELECT COUNT(*)
FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres
this query returns nothing (not even zero as a result). Presumably it
returns some valid value on mysql and other databases.

Other than hacking the library is there anything I can do?

Using offset 15 there is totally bogus since the count aggregate is
going to return just 1 row. Limit 15 is harmless but unnecessary and
misleading

The only way you're going to get the result is with offset 0 or offset
omitted.

Something like limit 15 offset 15 sounds like a paginator feature
trying to show page 2 of pages having 15 rows each.

Oddly enough SELECT count(*) FROM batches LIMIT 15 returns the full
count of the table. Presumably the count has to be done on a subquery

I do not find this odd :-)

HTH

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

On Fri, Aug 5, 2011 at 4:51 AM, Tim Uckun <timuckun@gmail.com> wrote:

I am using a library which is emitting SQL like this  SELECT COUNT(*)
FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres
this query returns nothing (not even zero as a result). Presumably it
returns some valid value on mysql and other databases.

Other than hacking the library is there anything I can do?

Oddly enough SELECT count(*) FROM batches LIMIT 15 returns the full
count of the table. Presumably the count has to be done on a subquery
right?

Since select count(*) will return one row, you're limit / offset are
in the wrong place. You want something like:

select count(*) from (select * from batches ordery by something limit
15 offset 15);

Yea I figured it would need a subquery. I filed a ticket with the
library. Hopefully they will fix it.

Tim Uckun <timuckun@gmail.com> writes:

I am using a library which is emitting SQL like this SELECT COUNT(*)
FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres
this query returns nothing (not even zero as a result). Presumably it
returns some valid value on mysql and other databases.

FWIW, I just tried it on mysql 5.5.2, and got results similar to
Postgres: the limit/offset are considered to apply to the single
aggregated row, not the aggregate's input rows as that library
seems to be expecting. I wonder what database they were using
for testing ...

regards, tom lane