PostgreSQL 9.4与10中order by random()的差异及数组排序疑问

你遇到的这个版本差异确实是预期行为，核心原因是PostgreSQL 9.6版本对array()构造函数的查询优化逻辑做了调整，咱们来拆解一下：

为什么两个版本结果不同？ #

• PostgreSQL 9.4.15（及更早版本）：
  array(SELECT ...)构造函数会严格按照子查询的输出顺序来生成数组。你写的子查询select generate_series(1,10) order by random()会先把1-10随机排序，再把结果塞进数组，所以得到的是随机顺序的数组，比如{7,1,10,6,2,8,9,4,5,3}。

• PostgreSQL 10.4（及9.6+版本）：
  从9.6开始，查询优化器会把array(SELECT ...)这种写法重写为等价的array_agg()聚合查询。而array_agg()默认不会保留子查询中的ORDER BY子句——优化器认为子查询里的排序对聚合结果没有必要，直接用了generate_series(1,10)的自然有序输出，所以得到的是{1,2,3,4,5,6,7,8,9,10}。

如何在新版本中得到随机排序的数组？ #

如果需要在9.6+版本里实现旧版本的随机数组效果，有两种可靠的写法：

1. 直接使用array_agg()并指定排序
   把排序逻辑整合到聚合函数里，优化器不会忽略这个排序：

   select array_agg(x order by random()) from generate_series(1,10) x;
   

2. 阻止优化器重写子查询
   通过给子查询添加OFFSET 0（这是一个不影响结果但会让优化器认为子查询不能被轻易重写的小技巧）：

   select array(select generate_series(1,10) order by random() offset 0);
   

这两种写法在10.4及更高版本中都会返回随机排序的数组，和9.4的行为一致。

内容的提问来源于stack exchange，提问作者mpapec