From: | Eric Atkin <eatkin(at)certusllc(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18430: syntax error when using aggregate function in where clause of subquery |
Date: | 2024-04-12 23:22:48 |
Message-ID: | CABt5tOV8x2aV8Afr-paa8SY1Wm6-xzzj+iX1Zp73mvvjAniM_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Eric Atkin <eatkin(at)certusllc(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2024-04-13 17:15:28 | Re: FSM Corruption (was: Could not read block at end of the relation) |
Previous Message | Tom Lane | 2024-04-12 19:14:15 | Re: BUG #18430: syntax error when using aggregate function in where clause of subquery |