The following bug has been logged on the website:
Bug reference: 18430
Logged by: Eric Atkin
Email address: eatkin@certusllc.us
PostgreSQL version: 16.2
Operating system: Arch Linux
Description:
CREATE TABLE driver (
id INTEGER PRIMARY KEY,
city TEXT
);
CREATE TABLE delivery (
id INTEGER PRIMARY KEY,
driver_id INTEGER REFERENCES driver(id)
);
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(...
I assume this is because aggregate functions are not normally allowed in a
where clause. However, my reading of the last paragraph of Section 4.2.7 of
the version 16 docs (quoted below) leads me to believe there should be an
exception to that constraint when a subquery occurs in an outer query select
list and refers to one of its variables. Is it possible there is a bug in
the parser causing it to not be aware of this exception?
Perhaps a join of delivery would be a simpler way to write this query in
this trivial example, but I think the subquery approach is more reasonable
in the real world case I've derived it from. In any case, I think postgres
should behave as documented even if I can only poorly demonstrate the
issue.
Thank you,
Eric Atkin
4.2.7 Aggregate Expressions
...
When an aggregate expression appears in a subquery (see Section 4.2.11 and
Section 9.23), the aggregate is normally evaluated over the rows of the
subquery. But an exception occurs if the aggregate's arguments (and
filter_clause if any) contain only outer-level variables: the aggregate then
belongs to the nearest such outer level, and is evaluated over the rows of
that query. 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. The restriction about appearing only in the
result list or HAVING clause applies with respect to the query level that
the aggregate belongs to.
