Re: behavior of GROUP BY with VOLATILE expressions

On Fri, Jul 19, 2024 at 7:20 AM Paul George <p(dot)a(dot)george19(at)gmail(dot)com> wrote:

>
> I wanted to surface a discussion in [1] regarding the expected behavior of
> GROUP BY with VOLATILE expressions. There seems to be a discrepancy between
> how volatile functions (RANDOM(), also confirmed with TIMEOFDAY()) and
> subqueries are evaluated in groups. In the examples below, volatile
> functions do not always appear to be evaluated per-call (evidenced by
> looking at EXPLAIN or results) whereas scalar subqueries always appear to
> be independently evaluated.
>
> Based on the docs, "A query using a volatile function will re-evaluate
> the function at every row where its value is needed," it seems that the
> handling of subqueries is correct and that each call to RANDOM() should be
> evaluated (not the current behavior). But, what is correct/anticipated?
>
>
The observed behaviors are unlikely to change. Prior discussions can be
found regarding this:

https://www.postgresql.org/message-id/flat/CZHAF947QQQO.27MAUK2SVMBXW%40nmfay.com

David J.