Re: BUG #14737: Wrong PL/pgSQL behaviour

Frank Gard <frank(at)familie-gard(dot)de> writes:
> The problem here is not that RANDOM() has different values for each row.
> This is exactly the expected behaviour. But if I do a numerical FOR loop
> iterating the range from 1 to p_anzahl, and every time I do an UPDATE
> which increments a "counter", then after the loop, the counter should
> equal to p_anzahl (and nothing else).

The flaw in that argument is the assumption that each execution of UPDATE
updates exactly one row. But since RANDOM() is recomputed at each row,
what you really have is a stochastic decision whether to update that row,
and so the UPDATE could well update more or fewer than one row overall.

> In addition, the result should not differ at all between the two variants of
> the function. What makes, semantically, the difference? There's none at all!

The point is exactly that a WHERE condition is (notionally) computed at
each table row, and that includes re-evaluating any functions it calls.
The DBMS can often optimize away some of that computation, but in this
case it cannot because of the volatility of RANDOM().

The SQL standard is pretty explicit that this is the intended semantics
--- it says "SC is effectively evaluated for each row of T". (In the 2011
edition, this is in 14.14 <update statement: searched>, general rule 5.)

regards, tom lane