Hi, This is High 3 from my list in [1].
Eager aggregation can put a partial aggregate below a join whose
clause has a volatile function. The function then runs once per
group, not once per joined row. 3a08a2a8b4f fixed the same thing for
volatile functions inside the aggregate.
create table f as select 1 as k from generate_series(1, 100);
create table dm as select 1 as k;
analyze f, dm;
create sequence sq;
select count(*) from f join dm on f.k = dm.k and nextval('sq') > 0
group by dm.k;
select last_value from sq;
The join has 100 rows. 18 calls nextval 100 times, master calls it
once. With random() < 0.5 in place of nextval, 18 returns a count
near 50, and master returns 100 or no row at all. The same happens
with where nextval('sq') > 0, a condition with no columns.
0001 does not build a grouped rel when one of the rel's join clauses
is volatile. A volatile filter on the aggregated table alone is still
allowed, it runs below the partial aggregate. 0002 adds a test. The
patches apply to master and REL_19_STABLE.
Related discussion in [2].
[1]
https://postgr.es/m/cagrkxqsfdjqzp_kwrz7dv6lhx-csrkdnynnxgntrscenqtk...@mail.gmail.com
[2]
https://postgr.es/m/ca+tgmob7ism9ykrm44vjuduacchw-fy54mv5njptztl9uny...@mail.gmail.com
Thanks,
Shihao
v1-0002-Test-eager-aggregation-with-a-volatile-join-claus.patch
Description: Binary data
v1-0001-Don-t-use-eager-aggregation-below-volatile-clause.patch
Description: Binary data
