From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eric Atkin <eatkin@certusllc.us>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18430: syntax error when using aggregate function in where
 clause of subquery
In-reply-to: 
 <CABt5tOXt=N5oenV6k36RBuOV37-MwFs-obLg2ZdcPSpS_FRJdA@mail.gmail.com>
References: <18430-f06d523d31f29af7@postgresql.org>
 <cd61a2c86437acce0624280e3998bcacd5f48cc9.camel@cybertec.at>
 <CABt5tOXt=N5oenV6k36RBuOV37-MwFs-obLg2ZdcPSpS_FRJdA@mail.gmail.com>
Comments: In-reply-to Eric Atkin <eatkin@certusllc.us>
	message dated "Fri, 12 Apr 2024 12:35:46 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <483375.1712949255.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 12 Apr 2024 15:14:15 -0400
Message-ID: <483376.1712949255@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/483376.1712949255%40sss.pgh.pa.us>
Precedence: bulk

Eric Atkin <eatkin@certusllc.us> writes:
> It seems the aggregate function call should be evaluated at the outer la=
yer
> (where it would not be bad syntax) and then is a constant for the inner
> query where clause where an array would be allowed.

Correct, but the problem is not with the array_agg call, it's with
your use of IN.  In the first place, IN requires parens around its
righthand side.  But that only gets us past "syntax error":

regression=3D# SELECT
    city,
    (SELECT count(*) FROM delivery WHERE driver_id IN (array_agg(driver.id=
)))
AS deliveries
FROM driver
GROUP BY city
;
ERROR:  operator does not exist: integer =3D integer[]
LINE 3: ...   (SELECT count(*) FROM delivery WHERE driver_id IN (array_...
                                                             ^
HINT:  No operator matches the given name and argument types. You might ne=
ed to add explicit type casts.

That's because what's inside the parens is supposed to be either a
sub-select or a list of things directly comparable to the LHS.
To do what you're after, you need to use the "scalar =3D ANY(array)"
construct:

regression=3D# SELECT
    city,
    (SELECT count(*) FROM delivery WHERE driver_id =3D any (array_agg(driv=
er.id)))
AS deliveries
FROM driver
GROUP BY city
;
 city | deliveries =

------+------------
(0 rows)

IN and =3DANY are more-or-less equivalent when the RHS is a sub-select,
but not for an array RHS.

There's nothing particularly wrong with Laurenz's construction,
but it's not necessary to split it up like that.

			regards, tom lane