From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eric Atkin <eatkin@certusllc.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18430: syntax error when using aggregate function in where clause of subquery
Date: Fri, 12 Apr 2024 15:14:15 -0400
Message-ID: <483376.1712949255@sss.pgh.pa.us> (raw)
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>
Eric Atkin <eatkin@certusllc.us> writes:
> 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.
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=# 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 = integer[]
LINE 3: ... (SELECT count(*) FROM delivery WHERE driver_id IN (array_...
^
HINT: No operator matches the given name and argument types. You might need 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 = ANY(array)"
construct:
regression=# SELECT
city,
(SELECT count(*) FROM delivery WHERE driver_id = any (array_agg(driver.id)))
AS deliveries
FROM driver
GROUP BY city
;
city | deliveries
------+------------
(0 rows)
IN and =ANY 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, eatkin@certusllc.us, 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: <483376.1712949255@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox