Re: Wrong results with grouping sets

On Thu, Jul 18, 2024 at 8:31 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> I am confused. Does the SQL standard explicitly define or standardize
> the behavior of grouping by volatile expressions? Does anyone know
> about that?

Just for the record, multiple instances of non-volatile grouping
expressions should always be evaluated only once. As an example,
consider:

create function f_stable_add(a integer, b integer) returns integer as
$$ begin return a+b; end; $$ language plpgsql stable;

explain (verbose, costs off)
select f_stable_add(a, b) as c1,
f_stable_add(a, b) as c2,
f_stable_add(a, b) as c3
from t t1 group by c1, c2;
QUERY PLAN
----------------------------------------------------------------------------
HashAggregate
Output: (f_stable_add(a, b)), (f_stable_add(a, b)), (f_stable_add(a, b))
Group Key: f_stable_add(t1.a, t1.b)
-> Seq Scan on public.t t1
Output: f_stable_add(a, b), a, b
(5 rows)

In this regard, the patched version is correct on handling subqueries
in grouping expressions, whereas the master version is incorrect.

Thanks
Richard