BUG #15800: Order by random in functions - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15800
Logged by:          Alessio Gennari
Email address:      alessio.gennari78@gmail.com
PostgreSQL version: 10.8
Operating system:   Linux
Description:

Hi there, I found a strange behavior in order by random() statement between
Postgres version 9 and the following:

in version 9.5 (select version(): PostgreSQL 9.5.12 on x86_64-pc-linux-gnu
(Debian 9.5.12-1.pgdg80+1), compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2,
64-bit) this statement returns element (id_card_type) randomly ordered:

select jsonb_array_elements('[{"id":0,"id_card_type":128},
{"id":1,"id_card_type":258}, {"id":2,"id_card_type":130},
{"id":3,"id_card_type":130}, {"id":4,"id_card_type":130},
{"id":5,"id_card_type":130}, {"id":6,"id_card_type":130},
{"id":7,"id_card_type":134}, {"id":8,"id_card_type":262},
{"id":9,"id_card_type":262}, {"id":10,"id_card_type":262},
{"id":11,"id_card_type":262}, {"id":12,"id_card_type":263},
{"id":13,"id_card_type":263}, {"id":14,"id_card_type":263},
{"id":15,"id_card_type":263}, {"id":16,"id_card_type":266},
{"id":17,"id_card_type":266}, {"id":18,"id_card_type":266},
{"id":19,"id_card_type":266}, {"id":20,"id_card_type":142},
{"id":21,"id_card_type":142}, {"id":22,"id_card_type":142},
{"id":23,"id_card_type":142}, {"id":24,"id_card_type":147},
{"id":25,"id_card_type":150}, {"id":26,"id_card_type":150},
{"id":27,"id_card_type":150}, {"id":28,"id_card_type":150},
{"id":29,"id_card_type":150}, {"id":30,"id_card_type":154},
{"id":31,"id_card_type":158}, {"id":32,"id_card_type":158},
{"id":33,"id_card_type":158}, {"id":34,"id_card_type":158},
{"id":35,"id_card_type":158}, {"id":36,"id_card_type":287},
{"id":37,"id_card_type":287}, {"id":38,"id_card_type":287},
{"id":39,"id_card_type":287}, {"id":40,"id_card_type":166},
{"id":41,"id_card_type":169}, {"id":42,"id_card_type":171},
{"id":43,"id_card_type":171}, {"id":44,"id_card_type":171},
{"id":45,"id_card_type":171}, {"id":46,"id_card_type":172},
{"id":47,"id_card_type":185}, {"id":48,"id_card_type":185},
{"id":49,"id_card_type":185}, {"id":50,"id_card_type":185},
{"id":51,"id_card_type":186}, {"id":52,"id_card_type":186},
{"id":53,"id_card_type":186}, {"id":54,"id_card_type":186},
{"id":55,"id_card_type":186}, {"id":56,"id_card_type":192},
{"id":57,"id_card_type":195}, {"id":58,"id_card_type":195},
{"id":59,"id_card_type":195}, {"id":60,"id_card_type":195},
{"id":61,"id_card_type":196}, {"id":62,"id_card_type":197},
{"id":63,"id_card_type":197}, {"id":64,"id_card_type":197},
{"id":65,"id_card_type":197}, {"id":66,"id_card_type":198},
{"id":67,"id_card_type":198}, {"id":68,"id_card_type":198},
{"id":69,"id_card_type":198}, {"id":70,"id_card_type":198},
{"id":71,"id_card_type":199}, {"id":72,"id_card_type":200},
{"id":73,"id_card_type":201}, {"id":74,"id_card_type":206},
{"id":75,"id_card_type":206}, {"id":76,"id_card_type":206},
{"id":77,"id_card_type":206}, {"id":78,"id_card_type":207},
{"id":79,"id_card_type":207}, {"id":80,"id_card_type":207},
{"id":81,"id_card_type":207}, {"id":82,"id_card_type":207},
{"id":83,"id_card_type":209}, {"id":84,"id_card_type":211},
{"id":85,"id_card_type":218}, {"id":86,"id_card_type":218},
{"id":87,"id_card_type":218}, {"id":88,"id_card_type":218},
{"id":89,"id_card_type":219}, {"id":90,"id_card_type":219},
{"id":91,"id_card_type":219}, {"id":92,"id_card_type":219},
{"id":93,"id_card_type":219}, {"id":94,"id_card_type":220},
{"id":95,"id_card_type":226}, {"id":96,"id_card_type":226},
{"id":97,"id_card_type":226}, {"id":98,"id_card_type":226},
{"id":99,"id_card_type":226}, {"id":100,"id_card_type":229},
{"id":101,"id_card_type":229}, {"id":102,"id_card_type":229},
{"id":103,"id_card_type":229}, {"id":104,"id_card_type":229},
{"id":105,"id_card_type":233}, {"id":106,"id_card_type":234},
{"id":107,"id_card_type":234}, {"id":108,"id_card_type":234},
{"id":109,"id_card_type":234}, {"id":110,"id_card_type":238},
{"id":111,"id_card_type":238}, {"id":112,"id_card_type":238},
{"id":113,"id_card_type":238}, {"id":114,"id_card_type":240},
{"id":115,"id_card_type":243}, {"id":116,"id_card_type":244},
{"id":117,"id_card_type":119}, {"id":118,"id_card_type":249},
{"id":119,"id_card_type":250}, {"id":120,"id_card_type":250},
{"id":121,"id_card_type":250}, {"id":122,"id_card_type":250},
{"id":123,"id_card_type":250}, {"id":124,"id_card_type":253},
{"id":125,"id_card_type":254}, {"id":126,"id_card_type":126},
{"id":127,"id_card_type":126}, {"id":128,"id_card_type":126},
{"id":129,"id_card_type":126}, {"id":130,"id_card_type":126},
{"id":131,"id_card_type":255}, {"id":132,"id_card_type":255},
{"id":133,"id_card_type":255},
{"id":134,"id_card_type":255},{"id":135,"id_card_type":255}]'::jsonb)->>'id_card_type'
order by random()

While in version 9.6 (PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit) and above (I tested it
in Postgres 10.8) the query return elements not ordered but in the same
sequence as it is in json array.

Is it a bug or an expected behavior?

Very kind regards.

Alessio