Thread: Bug #503: case and LIMIT not working together

Carl Anderson (candrsn@mindspring.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
case and LIMIT not working together

Long Description
PostreSQL 7/1/3 i686-pc-linux-gnu compiled by GCC 3.0

when I count ( case ... ) LIMIT

The count is from the entire table not from the LIMIT

in the below example the count is returned with the
same value in all three statements

Sample Code
select count( case when b='T' then 1 else null) from test limit 50;

select count( case when b='T' then 1 else null) from test;

select count(*) from test where b = 'T';

No file was uploaded with this report

On Tue, 30 Oct 2001 pgsql-bugs@postgresql.org wrote:

> Carl Anderson (candrsn@mindspring.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> case and LIMIT not working together
>
> Long Description
> PostreSQL 7/1/3 i686-pc-linux-gnu compiled by GCC 3.0
>
> when I count ( case ... ) LIMIT
>
> The count is from the entire table not from the LIMIT
>
> in the below example the count is returned with the
> same value in all three statements
>
> Sample Code
> select count( case when b='T' then 1 else null) from test limit 50;
>
> select count( case when b='T' then 1 else null) from test;
>
> select count(*) from test where b = 'T';

I don't see that this is a bug.  Limit affects the output rows.  Since
select count() has only one row of output, it seems to me that its
the correct interpretation of the query.

If you want to grab 50 rows and then count on those I think you want
something more like (although I don't know if it works in 7.1):
select count(*) from (select * from test limit 50) t where b='T';

pgsql-bugs@postgresql.org writes:
> case and LIMIT not working together

> when I count ( case ... ) LIMIT
> The count is from the entire table not from the LIMIT
> in the below example the count is returned with the
> same value in all three statements

> select count( case when b='T' then 1 else null) from test limit 50;

This is not a bug, this is your misunderstanding of what LIMIT does.

LIMIT applies to the output rows of the SELECT it's attached to,
not to the rows scanned to produce the output rows.  Since a select
count() will only have one output row, the LIMIT is irrelevant.

In 7.1 and later you can do something like

select count(...) from
(select * from test limit 50) as ss;

to apply the LIMIT before the aggregation step.

            regards, tom lane