SQL从表中随机抽取15行（可重复）返回空结果，求排查

现有如下数据表结构及数据：

CREATE TABLE mytable 
(
    id     BIGINT,
    color  VARCHAR(10),
    food   VARCHAR(10),
    sport  VARCHAR(12),
    animal VARCHAR(10)
) DISTRIBUTE ON (id);

INSERT INTO mytable (id, color, food, sport, animal) VALUES
( 1, 'red',    'pizza',  'soccer',     'dog'),
( 2, 'blue',   'sushi',  'tennis',     'cat'),
( 3, 'green',  'tacos',  'basketball', 'horse'),
( 4, 'yellow', 'pasta',  'golf',       'rabbit'),
( 5, 'pink',   'pizza',  'swimming',   'fox'),
( 6, 'orange', 'burger', 'soccer',     'bear'),
( 7, 'black',  'ramen',  'hockey',     'wolf'),
( 8, 'purple', 'tacos',  'cricket',    'cat'),
( 9, 'white',  'salad',  'tennis',     'dog'),
(10, 'brown',  'pizza',  'running',    'deer'),
(11, 'cyan',   'curry',  'boxing',     'tiger'),
(12, 'gray',   'sushi',  'soccer',     'horse'),
(13, 'teal',   'burger', 'baseball',   'owl'),
(14, 'maroon', 'pasta',  'golf',       'dog'),
(15, 'navy',   'tacos',  'rugby',      'frog');

需求是从该表中可重复随机抽取15行，预期结果示例如下：

用户编写的SQL如下，执行后返回空结果：

SELECT
    g.i AS resample_id, 
    t.id AS original_id, 
    t.color, t.food,
    t.sport,
    t.animal
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY NULL) AS i,
           CAST((RANDOM() * 15) AS BIGINT) + 1 AS pick_id
    FROM mytable
    LIMIT 15
) g
JOIN mytable t ON t.id = g.pick_id
ORDER BY g.i;

问题原因 #

原SQL的核心问题在于子查询依赖mytable来生成15行数据，结合DISTRIBUTE ON (id)的MPP数据库特性（比如Greenplum），优化器的执行计划可能导致子查询无法稳定生成15行有效数据：

1. 当从分布式表中取数据时，LIMIT 15可能被推送到分片级别执行，导致每个分片仅返回少量数据，最终子查询的结果集行数不足15甚至为空。
2. ROW_NUMBER() OVER (ORDER BY NULL)的排序逻辑未定义，窗口函数在分布式环境下无法稳定生成连续的行号，进一步导致子查询结果异常。

修正后的SQL #

使用generate_series直接生成指定数量的序列行，确保子查询稳定输出15行数据，再关联原表完成随机抽样：

SELECT
    g.i AS resample_id, 
    t.id AS original_id, 
    t.color, t.food,
    t.sport,
    t.animal
FROM (
    SELECT 
        generate_series(1, 15) AS i,
        CAST((RANDOM() * 15) AS BIGINT) + 1 AS pick_id
) g
JOIN mytable t ON t.id = g.pick_id
ORDER BY g.i;

补充说明 #

• generate_series(1,15)会直接生成1到15的连续整数，作为抽样结果的序号resample_id，确保生成恰好15行。
• CAST((RANDOM() *15) AS BIGINT)+1生成1到15的随机整数，对应原表的id，实现可重复随机抽样。
• 该写法适用于支持generate_series的数据库（如PostgreSQL、Greenplum等）。

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