Custom shuffle function stopped working in 9.6

Hi

2017-02-11 17:37 GMT+01:00 Alexander Farber <alexander.farber@gmail.com>:

Good evening,

after switching to 9.6.2 from 9.5.3 the following custom function has
stopped working:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$func$
SELECT array_agg(letters.x) FROM
(SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters;
$func$ LANGUAGE sql STABLE;

In 9.5.3 it was shuffling characters:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
words_shuffle
---------------
{c,d,b,a,e,f}
(1 row)

But in 9.6.2 it has stopped doing so:
words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
words_shuffle
---------------
{a,b,c,d,e,f}
(1 row)

Any suggestions for a better shuffling function please?

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$func$
SELECT array_agg(letters.x) FROM
(SELECT x FROM UNNEST(in_array) x ORDER BY RANDOM()) letters;
$func$ LANGUAGE sql STABLE;

there was some optimisations for faster expression evaluation - probably
this is one effect of this optimisation.

generally SRF function should not be used in target list - now when we have
LATERAL join, it is not necessary

Regards

Pavel

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

I don't use an array, but perhaps you can adapt to this function which
works in 9.6.1

CREATE OR REPLACE FUNCTION public.scramble(text)
RETURNS text AS
$BODY$
DECLARE
p_in ALIAS FOR $1;
v_out TEXT DEFAULT '';
v_mod TEXT;
v_len INT4;
v_ctr INT4;
v_pos INT4;
v_array CHAR[];

BEGIN

v_ctr = 1;
WHILE v_ctr <= LENGTH(p_in) LOOP
v_array[v_ctr] = NULL;
v_ctr := v_ctr +1;
END LOOP;

v_ctr = 1;
WHILE v_ctr <= LENGTH(p_in) LOOP

v_pos := INT4(random() * 100);
IF v_pos > LENGTH(p_in) OR v_array[v_pos] IS NOT NULL THEN
CONTINUE;
END IF;

v_mod := substring(p_in from v_pos for 1);

v_array[v_pos] := '*';
v_out := v_out || v_mod;
v_ctr := v_ctr +1;

END LOOP;

RETURN v_out;
END;
-- SAMPLE CALL
-- SELECT scramble('Honor');
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.scramble(text)
OWNER TO postgres;

On Sat, Feb 11, 2017 at 12:17 PM, Alexander Farber <
alexander.farber@gmail.com> wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 02/11/2017 09:17 AM, Alexander Farber wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

postgres=> select version();
version
-------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit
(1 row)

postgres=> select * from unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
d
c
a
f
e
b
(6 rows)

postgres=> select * from unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
b
d
e
c
a
f
(6 rows)

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/11/2017 09:17 AM, Alexander Farber wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

So back to your original question:

CREATE OR REPLACE FUNCTION public.words_shuffle(in_array character
varying[])
RETURNS character varying[]
LANGUAGE sql
STABLE
AS $function$
SELECT array_agg(letters.x) FROM
(SELECT * FROM UNNEST(in_array) x ORDER BY RANDOM()) letters;
$function$

postgres=> select * from words_shuffle(ARRAY['a','b','c','d','e','f']);
words_shuffle
---------------
{d,f,a,e,c,b}
(1 row)

postgres=> select * from words_shuffle(ARRAY['a','b','c','d','e','f']);
words_shuffle
---------------
{c,d,a,e,f,b}
(1 row)

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/11/2017 11:36 AM, Adrian Klaver wrote:

On 02/11/2017 09:17 AM, Alexander Farber wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

postgres=> select version();
version
-------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit
(1 row)

postgres=> select * from unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
d
c
a
f
e
b
(6 rows)

postgres=> select * from unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
b
d
e
c
a
f
(6 rows)

I can duplicate issue on 9.6.2.

jack=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit
(1 row)

jack=# select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

jack=# select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

Jack

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

words=> select version();
version

----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
--------
a
b
c
d
e
f
(6 rows)

At the same time this advice from
http://stackoverflow.com/questions/42179012/how-to-shuffle-array-in-postgresql-9-6-and-also-lower-versions
works, don't know why though:

words=> select array_agg(u order by random())
words-> from unnest(array['a','b','c','d','e','f']) u;
array_agg
---------------
{d,a,f,c,b,e}

On 02/11/2017 10:51 AM, Alexander Farber wrote:

At the same time this advice from
http://stackoverflow.com/questions/42179012/how-to-shuffle-array-in-postgresql-9-6-and-also-lower-versions
works, don't know why though:

words=> select array_agg(u order by random())
words-> from unnest(array['a','b','c','d','e','f']) u;
array_agg
---------------
{d,a,f,c,b,e}

To future proof your code follow the advice shown in the doc snippet in
the first answer to your SO question. This is what I showed in my
answers to your questions.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2017-02-11 19:51 GMT+01:00 Alexander Farber <alexander.farber@gmail.com>:

At the same time this advice from
http://stackoverflow.com/questions/42179012/how-to-
shuffle-array-in-postgresql-9-6-and-also-lower-versions
works, don't know why though:

words=> select array_agg(u order by random())
words-> from unnest(array['a','b','c','d','e','f']) u;
array_agg
---------------
{d,a,f,c,b,e}

There is a change in plan

postgres=# explain analyze verbose select * from
unnest(ARRAY['a','b','c','d','e','f']) order by random();
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
Sort (cost=4.57..4.82 rows=100 width=40) (actual time=0.054..0.056 rows=6
loops=1)
Output: unnest, (random())
Sort Key: (random())
Sort Method: quicksort Memory: 25kB
-> Function Scan on pg_catalog.unnest (cost=0.00..1.25 rows=100
width=40) (actual time=0.029..0.033 rows=6 loops=1)
Output: unnest, random()
Function Call: unnest('{a,b,c,d,e,f}'::text[])
Planning time: 0.125 ms
Execution time: 0.119 ms

postgres=# explain analyze verbose select
unnest(ARRAY['a','b','c','d','e','f']) order by random();
QUERY PLAN

------------------------------------------------------------------------------------------------
ProjectSet (cost=0.02..0.54 rows=100 width=40) (actual time=0.032..0.037
rows=6 loops=1)
Output: unnest('{a,b,c,d,e,f}'::text[]), (random())
-> Sort (cost=0.02..0.03 rows=1 width=8) (actual time=0.020..0.021
rows=1 loops=1)
Output: (random())
Sort Key: (random())
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.01 rows=1 width=8) (actual
time=0.006..0.006 rows=1 loops=1)
Output: random()
Planning time: 0.100 ms
Execution time: 0.072 ms

In second case, the random function is called only once, and result is
multiplied.

Maybe it is bug, because volatile functions should be evaluated every time

Regards

Pavel

Alexander Farber <alexander.farber@gmail.com> writes:

after switching to 9.6.2 from 9.5.3 the following custom function has
stopped working:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$func$
SELECT array_agg(letters.x) FROM
(SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters;
$func$ LANGUAGE sql STABLE;

Expansion of SRFs in the targetlist now happens after ORDER BY.
So the ORDER BY is sorting a single dummy row and then the unnest
happens after that. See

https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=9118d03a8

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Feb 11, 2017 at 5:37 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
...

after switching to 9.6.2 from 9.5.3 the following custom function has
stopped working:
CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
Any suggestions for a better shuffling function please?

I've seen several sugestions and hints, but seem no one sugested the
classical shuffling algorithm. Even when of the solutions seems to be
not guaranteed to stop.

An easy way to shuffle is swap every element with a random one from
its position to the start or end ( NOT a random one on the array, this
will give you N^N combinations on an N element array, which does not
evenly divide the N! permutations on an array ( see at end ) ) ( of
course even my version is not going to give you that given random() is
not perfect, but it will be a bit better ).

Not having access to a server I've just tried this on 9.3 on sqlfiddlle:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$$
declare
a varchar[]:=in_array;
n integer:=array_length(a,1);
tmp varchar;
r integer;
begin
for i in reverse n..2 loop
r := floor(random()*i) + 1;
tmp=a[i]; a[i]=a[r]; a[r]=tmp;
end loop;
return a;
end
$$
LANGUAGE plpgsql volatile

As you can see I do it from the end swapping it with elements from the
start ( this way I swap i in the range 1..i, instead of i, n wich is a
little harder to debug ). I stop at 2 because element 1 can only be
swapped with itself. I've marked it volatile as it returns different
things each time you call it. My tests show it working, but it may
have some problems with the type conversions, as I'm not used to do
this kind of code in plpgsql, but you can get the idea.

Francisco Olarte.

P.S.:
-- shufflings of three elements, with any or from its pos to the end:

Swapping with any element in the array
0,0,0: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,0)=> cab
0,0,1: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,1)=> bca
0,0,2: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,2)=> bac
0,1,0: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,0)=> cba
0,1,1: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,1)=> acb
0,1,2: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,2)=> abc
0,2,0: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,0)=> bca
0,2,1: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,1)=> abc
0,2,2: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,2)=> acb
1,0,0: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,0)=> cba
1,0,1: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,1)=> acb
1,0,2: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,2)=> abc
1,1,0: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,0)=> cab
1,1,1: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,1)=> bca
1,1,2: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,2)=> bac
1,2,0: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,0)=> acb
1,2,1: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,1)=> bac
1,2,2: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,2)=> bca
2,0,0: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,0)=> acb
2,0,1: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,1)=> bac
2,0,2: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,2)=> bca
2,1,0: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,0)=> abc
2,1,1: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,1)=> cab
2,1,2: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,2)=> cba
2,2,0: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,0)=> bac
2,2,1: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,1)=> cba
2,2,2: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,2)=> cab
F(abc) = 4
F(acb) = 5
F(bac) = 5
F(bca) = 5
F(cab) = 4
F(cba) = 4
Swapping from its own position in the array to the end ( last can be
omitted, of course )
0,1,2: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,2)=> abc
0,2,2: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,2)=> acb
1,1,2: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,2)=> bac
1,2,2: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,2)=> bca
2,1,2: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,2)=> cba
2,2,2: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,2)=> cab
F(abc) = 1
F(acb) = 1
F(bac) = 1
F(bca) = 1
F(cab) = 1
F(cba) = 1

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general