Thread: [GENERAL] Custom shuffle function stopped working in 9.6

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

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

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

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

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&a=commitdiff&h=9118d03a8

            regards, tom lane

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