Re: [BUGS] BUG #14737: Wrong PL/pgSQL behaviour - Mailing list pgsql-bugs

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_anzahlIN 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.von.postgresql.org@familie-gard.de
<mailto:frank.von.postgresql.org@familie-gard.de>>wrote:
>
>     Bug reference:      14737
>     Logged by:          Frank Gard
>     Email address:      frank.von.postgresql.org@familie-gard.de <mailto:frank.von.postgresql.org@familie-gard.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.
>