Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl

shihao zhong <zhong950419(at)gmail(dot)com> 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