Обсуждение: BUG #4780: Aggregate functions are unaware of LIMIT clauses in SELECTs

The following bug has been logged online:

Bug reference:      4780
Logged by:          Ted Holzman
Email address:      tholzman@fhcrc.org
PostgreSQL version: 8.3.7
Operating system:   SuSE Linux 10.3
Description:        Aggregate functions are unaware of LIMIT clauses in
SELECTs
Details:

Hi,

I don't know if this is a bug or an arcane feature, but AGGREGATE functions
don't appear to respond to LIMIT clauses.  For example:

The untouched results of a query.

experiments=# select generate_series from generate_series(1,10);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)


The LIMITed results of a query.

experiments=# select generate_series from generate_series(1,10) limit 3;
 generate_series
-----------------
               1
               2
               3
(3 rows)


The SUM (aggregated) results of a limited query:


experiments=# select sum(genera
te_series) from generate_series(1,10) limit 3;
 sum
-----
  55
(1 row)

I was expecting the sum to be 6.

              ==Ted

"Ted Holzman" <tholzman@fhcrc.org> wrote:

> AGGREGATE functions don't appear to respond to LIMIT clauses.

Not a bug.  LIMIT affects how many rows are in the result set the
LIMIT qualifies.

> select sum(generate_series)
>   from generate_series(1,10) limit 3;
>  sum
> -----
>   55
> (1 row)
>
> I was expecting the sum to be 6.

The LIMIT is applied to the final result set, which is only one row,
so the LIMIT has nothing to do.  If you wanted to limit how many rows
went into the aggregate function, you'd need to do something like
this:

select sum(generate_series)
  from (select generate_series(1,10) limit 3) x;
 sum
-----
   6
(1 row)

-Kevin