Thread: Select count with offset returns nothing.
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?
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...
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>
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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
My question is why would you put an offset in a query designed to return a row count without grouping and ordering?
> Date: Fri, 5 Aug 2011 22:51:24 +1200
> Subject: [GENERAL] Select count with offset returns nothing.
> From: timuckun@gmail.com
> To: pgsql-general@postgresql.org
>
> 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?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> Subject: [GENERAL] Select count with offset returns nothing.
> From: timuckun@gmail.com
> To: pgsql-general@postgresql.org
>
> 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?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
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