Re: Wrong results with grouping sets - Mailing list pgsql-hackers
| From | Paul George |
|---|---|
| Subject | Re: Wrong results with grouping sets |
| Date | |
| Msg-id | CALA8mJqNeiexX3t_G555_m=nOMS9261zQ_W8r+6LRRbt3q8CLg@mail.gmail.com Whole thread Raw |
| In response to | Re: Wrong results with grouping sets (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>) |
| Responses |
Re: Wrong results with grouping sets
|
| List | pgsql-hackers |
Thanks for the work!
> Since a subquery is a volatile expression, each of its instances
should be evaluated separately.This seems like a valid point, though "query 2" below which groups over a RANDOM() column and outputs an additional RANDOM() column a potential, albeit contrived, counter-example? [NOTE: this was done on Postgres 16.3] I've included a few different combinations of GROUP BYs.
-- setup
create table t as (select 0 x);
analyze t;
-- query 1: base --> multiple evaluations of RANDOM(), col0 != col1
postgres=# select x, random() col0, random() col1 from t group by x;
x | col0 | col1
---+---------------------+--------------------
0 | 0.07205921113992653 | 0.9847359546402477
(1 row)
x | col0 | col1
---+---------------------+--------------------
0 | 0.07205921113992653 | 0.9847359546402477
(1 row)
-- query 2: group by one volatile column --> single evaluation of RANDOM(), col0 == col1
postgres=# select x, random() col0, random() col1 from t group by x, col0;
x | col0 | col1
---+--------------------+--------------------
0 | 0.7765600922298943 | 0.7765600922298943
(1 row)
x | col0 | col1
---+--------------------+--------------------
0 | 0.7765600922298943 | 0.7765600922298943
(1 row)
-- query 3: group by both volatile columns --> multiple evaluations of RANDOM() again, col0 != col1
postgres=# select x, random() col0, random() col1 from t group by x, col0, col1;
x | col0 | col1
---+---------------------+--------------------
0 | 0.07334303548896548 | 0.6528967617521189
(1 row)
x | col0 | col1
---+---------------------+--------------------
0 | 0.07334303548896548 | 0.6528967617521189
(1 row)
--
Related to your point about the unexpected asymmetry in single vs multiple evaluations of subquery plans, I'm curious if the pair of subqueries in both examples below should be considered equivalent? The queries output the same results and the subqueries differ only in output name. With this patch, they're considered equivalent in the first query but not in the second. [NOTE: this was done on a branch with the patch applied]
-- query 1: alias outside subquery
test=# explain (verbose, costs off) select x, (select 1) col0, (select 1) col1 from t group by x, col0;
QUERY PLAN
-----------------------------------------------------
Group
Output: t.x, (InitPlan 1).col1, (InitPlan 1).col1
Group Key: t.x
InitPlan 1
-> Result
Output: 1
-> Sort
Output: t.x
Sort Key: t.x
-> Seq Scan on public.t
Output: t.x
(11 rows)
QUERY PLAN
-----------------------------------------------------
Group
Output: t.x, (InitPlan 1).col1, (InitPlan 1).col1
Group Key: t.x
InitPlan 1
-> Result
Output: 1
-> Sort
Output: t.x
Sort Key: t.x
-> Seq Scan on public.t
Output: t.x
(11 rows)
...compared to...
-- query 2: alias inside subquery
test=# explain (verbose, costs off) select x, (select 1 col0), (select 1 col1) from t group by x, col0;
QUERY PLAN
-----------------------------------------------------
Group
Output: t.x, (InitPlan 1).col1, (InitPlan 2).col1
Group Key: t.x
InitPlan 1
-> Result
Output: 1
InitPlan 2
-> Result
Output: 1
-> Sort
Output: t.x
Sort Key: t.x
-> Seq Scan on public.t
Output: t.x
(14 rows)
QUERY PLAN
-----------------------------------------------------
Group
Output: t.x, (InitPlan 1).col1, (InitPlan 2).col1
Group Key: t.x
InitPlan 1
-> Result
Output: 1
InitPlan 2
-> Result
Output: 1
-> Sort
Output: t.x
Sort Key: t.x
-> Seq Scan on public.t
Output: t.x
(14 rows)
-Paul-
pgsql-hackers by date: