Re: BUG #18430: syntax error when using aggregate function in where clause of subquery

"The aggregate expression as a whole is then an outer reference
for the subquery it appears in, and acts as a constant over any one
evaluation of that subquery."

It seems the aggregate function call should be evaluated at the outer layer
(where it would not be bad syntax) and then is a constant for the inner
query where clause where an array would be allowed.

Thank you for your working example. There is often more than one way to
write any particular query. My version and yours seem to mean the same
thing, but one is arguably more readable. At the very least, is this not a
documentation bug?

On Fri, Apr 12, 2024 at 11:45 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Fri, 2024-04-12 at 17:14 +0000, PG Bug reporting form wrote:
> > SELECT
> > city,
> > (SELECT count(*) FROM delivery WHERE driver_id IN array_agg(
> driver.id))
> > AS deliveries
> > FROM driver
> > GROUP BY city
> > ;
> >
> > This produces:
> >
> > ERROR: syntax error at or near "array_agg"
> > LINE 3: ...(SELECT count(*) FROM delivery WHERE driver_id IN
> array_agg(...
>
> This not a bug, but bad syntax.
>
> Write
>
> SELECT city,
> (SELECT count(*)
> FROM delivery
> WHERE driver_id = ANY (drivers)) AS deliveries
> FROM (SELECT city,
> array_agg(driver.id) AS drivers
> FROM driver
> GROUP BY city) AS q;
>
> Yours,
> Laurenz Albe
>