BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19834
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 19rc1
Operating system: Linux x86_64; platform-independent (reproduced on
Description:
**Description:**
Commit 27c844034f8 made eager aggregation refuse to push partial aggregation
below a join clause that contains a volatile function. The guard uses
`contain_volatile_functions()`, which deliberately does **not** recurse into
SubPlan nodes. A volatile function inside a *correlated SubPlan* in the join
clause therefore still escapes the guard: eager aggregation is applied, the
join filter (and the SubPlan, and the volatile function) is evaluated once
per group instead of once per joined row, changing both the number of side
effects and the query result.
**Reproduction (self-contained SQL):**
```sql
CREATE TABLE e1(a int, b int);
CREATE TABLE e2(a int, b int);
INSERT INTO e1 SELECT g%10, g FROM generate_series(1,1000) g;
INSERT INTO e2 SELECT g%10, g FROM generate_series(1,1000) g;
ANALYZE e1; ANALYZE e2;
CREATE SEQUENCE s;
-- volatile DIRECTLY in the join clause: fix works, no eager aggregation,
900 evaluations
SET enable_eager_aggregate = on;
SELECT count(*) FROM e1 JOIN e2 ON e1.a = e2.a AND nextval('s')%2 = 0;
-- volatile INSIDE a correlated SubPlan: eager aggregation is applied anyway
SET enable_eager_aggregate = on;
SELECT count(*) FROM e1 JOIN e2 ON e1.a = e2.a AND (SELECT nextval('s')%2) =
0;
SET enable_eager_aggregate = off;
SELECT count(*) FROM e1 JOIN e2 ON e1.a = e2.a AND (SELECT nextval('s')%2) =
0;
```
**Actual behavior** (observable via `SELECT last_value FROM s` after each
statement, or `EXPLAIN (ANALYZE)` showing `Partial HashAggregate` and `Join
Filter ... loops=9`):
- direct volatile form: join filter evaluated 900 times (fix works);
- SubPlan form with eager aggregation **on**: join filter/SubPlan evaluated
**9 times** (once per group after partial aggregation);
- SubPlan form with eager aggregation **off**: 900 times.
So the same query silently returns a different result and performs a
different number of volatile side effects depending on the (default-on)
eager aggregation setting, and the count of side effects is wrong by 100x in
this example.
**Root cause:** the volatile check for eager aggregation
(src/backend/optimizer/util/relnode.c, guard added by 27c844034f8) uses
`contain_volatile_functions()`, which does not descend into SubPlan nodes.
**Expected behavior:** a join clause whose evaluation can invoke volatile
functions — including via a SubPlan — should suppress eager aggregation
below that join, same as the direct form.
Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
Thanks for the report. This follows up on [1], where we fixed volatile
join clauses. contain_volatile_functions() does not look into SubPlans,
so a volatile function in a correlated sub-select still gets through.
The query in the report does not show it, because its sub-select is
uncorrelated and runs once as an initplan.
The attached patch also refuses SubPlans, like we do when moving HAVING
to WHERE. I think it should go back to 19.
[1] https://postgr.es/m/CAGRkXqSc_qtpuPm34qoS1z0LVx4+Hc+JcJkbDEVC=z2ai4VQ1w@mail.gmail.com
Thanks,
Shihao
join clauses. contain_volatile_functions() does not look into SubPlans,
so a volatile function in a correlated sub-select still gets through.
The query in the report does not show it, because its sub-select is
uncorrelated and runs once as an initplan.
The attached patch also refuses SubPlans, like we do when moving HAVING
to WHERE. I think it should go back to 19.
[1] https://postgr.es/m/CAGRkXqSc_qtpuPm34qoS1z0LVx4+Hc+JcJkbDEVC=z2ai4VQ1w@mail.gmail.com
Thanks,
Shihao
Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
shihao zhong writes: > Thanks for the report. This follows up on [1], where we fixed volatile > join clauses. contain_volatile_functions() does not look into SubPlans, > so a volatile function in a correlated sub-select still gets through. > The query in the report does not show it, because its sub-select is > uncorrelated and runs once as an initplan. > The attached patch also refuses SubPlans, like we do when moving HAVING > to WHERE. I think it should go back to 19. I do not think this is a bug, so I don't think we should limit query optimization to work around it (especially in such a brute-force way). We have traditionally understood that a sub-select can be used to hide or disable the volatility of a function. For example, these clauses behave quite differently: WHERE x = random() WHERE x = (SELECT random()) So the fact that contain_volatile_functions() doesn't descend into SubPlans is intentional, and subverting that intention in just one place does not seem to me like a good idea. Maybe there is something to be done here, but this bug report shows no awareness of that context. regards, tom lane
Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Tom,
> For example, these clauses behave quite differently:
If I understand correctly, you mean the following case,
If I understand correctly, you mean the following case,
A table t with 100 rows,
SELECT count(*) FROM t WHERE (SELECT random()) < 0.5;
returns either 100 or 0, because the sub-select runs only once.
SELECT count(*) FROM t WHERE (SELECT random()) < 0.5;
returns either 100 or 0, because the sub-select runs only once.
I think technically that is one of the correct answers....It is just a
result the caller would almost never see otherwise.
I am okay to revert the patch..
Thanks,
Shihao
Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
shihao zhong writes: > If I understand correctly, you mean the following case, > A table t with 100 rows, > SELECT count(*) FROM t WHERE (SELECT random()) < 0.5; > returns either 100 or 0, because the sub-select runs only once. Right. > I think technically that is one of the correct answers....It is just a > result the caller would almost never see otherwise. > I am okay to revert the patch.. To be clear, I'm fine with 27c844034 as it stands. I just don't think it'd be consistent with our past practice to extend the volatility check into subplans. regards, tom lane