Re: Wrong results with grouping sets - Mailing list pgsql-hackers

On Thu, Jul 18, 2024 at 8:31 AM Richard Guo <guofenglinux@gmail.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