Re: BUG #18430: syntax error when using aggregate function in where clause of subquery - Mailing list pgsql-bugs

From Eric Atkin
Subject Re: BUG #18430: syntax error when using aggregate function in where clause of subquery
Date
Msg-id CABt5tOV8x2aV8Afr-paa8SY1Wm6-xzzj+iX1Zp73mvvjAniM_g@mail.gmail.com
Whole thread Raw
In response to Re: BUG #18430: syntax error when using aggregate function in where clause of subquery  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Perfect. I had tried the `= ANY` construct but got the same syntax error and mistakenly assumed it was a problem with the parser rather than my misreading of the `ANY (array)` docs (9.24.3).
Thank you both for your help.

On Fri, Apr 12, 2024 at 1:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #18430: syntax error when using aggregate function in where clause of subquery
Next
From: Noah Misch
Date:
Subject: Re: FSM Corruption (was: Could not read block at end of the relation)