Horton

Re: Wrong results with grouping sets

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: Paul George <p.a.george19@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-07-18T01:17:02Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove the RTE_GROUP RTE if we drop the groupClause

  2. Mark expressions nullable by grouping sets

  3. Introduce an RTE for the grouping step

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