Re: BUG #14737: Wrong PL/pgSQL behaviour

Hi David,

thanks for your instant reply. Unfortunately, I disagree your rating that
this behaviour is not a bug.

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).

My function simulates throwing the dice for p_anzahl times, counting how
often each of the dots you have as the result, and returns the distribution
of the results (… times 1 dot, … times 2 dots, and so on) as a table.

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!

For your convenience, here I add the diff between the two variants:
--- variant1.sql 2017-07-07 22:40:44.308024705 +0200
+++ variant2.sql 2017-07-07 22:40:36.975977660 +0200
@@ -13,6 +13,7 @@
SELECT * FROM ttb_histogramm ORDER BY wert
;
v_ergebnis tp_histogramm;
+ v_zufall INTEGER;
BEGIN
EXECUTE c_drop;
EXECUTE c_create;
@@ -20,11 +21,12 @@
INSERT INTO ttb_histogramm( wert, anzahl ) VALUES ( v_wert, 0 );
END LOOP;
FOR v_anzahl IN 1 .. p_anzahl LOOP
+ v_zufall := p_von + FLOOR( ( 1 + p_bis - p_von ) * RANDOM() );
UPDATE ttb_histogramm
SET
anzahl = anzahl + 1
WHERE
- wert = p_von + FLOOR( ( 1 + p_bis - p_von ) * RANDOM() )
+ wert = v_zufall
;
END LOOP;
FOR r_histogramm IN c_histogramm LOOP

Thanks a lot,
Frank.

Am 07.07.2017 um 18:28 schrieb David G. Johnston:
> On Fri, Jul 7, 2017 at 9:10 AM, <frank(dot)von(dot)postgresql(dot)org(at)familie-gard(dot)de <mailto:frank(dot)von(dot)postgresql(dot)org(at)familie-gard(dot)de>>wrote:
>
> Bug reference: 14737
> Logged by: Frank Gard
> Email address: frank(dot)von(dot)postgresql(dot)org(at)familie-gard(dot)de <mailto:frank(dot)von(dot)postgresql(dot)org(at)familie-gard(dot)de>
> PostgreSQL version: 9.6.3
> Operating system: Debian GNU/Linux
>
>
> ​Not a bug - RANDOM() is a volatile function so it gets evaluated once per row.
>
> UPDATE ttb_histogramm
> SET
> anzahl = anzahl + 1
> WHERE
> wert = p_von + FLOOR( ( 1 + p_bis - p_von ) * RANDOM() )
>
>
> Unfortunately this is not the case. When calling it
> multiple times, it returns numbers smaller and greater, and always different
> values for each call. Very strange (to me)!!!
>
>
> ​Which is the symptom one will see if, for every row, the value of random is different.
> ​
>
> When I change my function a little bit, writing the random number into an
> INTEGER variable, and using this variable within my UPDATE statement,
> everything works fine:
>
>
> ​Then this is what you should do.
>
> ​David J.
>