SQLite中CTE JOIN的RANDOM()行为异于其他RDBMS的原因及解决方法

解答 #

1. SQLite的行为解释：不是Bug，是设计选择

SQLite对CTE的默认处理逻辑是延迟计算（按需执行），而非像其他数据库那样默认将CTE结果物化（保存为临时表）。也就是说，每次在查询中引用tbl2这个CTE时，SQLite都会重新执行一遍SELECT n, RANDOM() FROM tbl1语句——这意味着每次引用都会重新生成随机数，而非复用第一次生成的结果。

在你的交叉连接操作中，tbl2被引用了两次（t1和t2），且SQLite在执行过程中会为每一行的生成重新计算CTE内容，最终就会得到多组不同的随机数。这是SQLite为了轻量性和性能做出的设计决策，并非Bug。

2. 让随机数列在JOIN时保持不变的方法

要实现和其他数据库一致的效果，你需要强制SQLite将CTE结果物化，也就是把CTE的结果保存为临时数据，后续所有引用都复用这些值。这里有几种可行方案：

方法一：使用MATERIALIZED关键字（SQLite 3.35.0+）

从SQLite 3.35.0版本开始，支持显式指定MATERIALIZED关键字强制物化CTE：

WITH tbl1(n) AS (SELECT 1 UNION ALL SELECT 2),
     tbl2(n, r) AS MATERIALIZED (SELECT n, RANDOM() FROM tbl1)
SELECT * FROM tbl2 t1 CROSS JOIN tbl2 t2;

这样tbl2只会被计算一次，生成的随机数会被保存下来，后续JOIN操作都会复用这些值。

方法二：使用临时表（兼容所有版本）

如果使用的是较老版本的SQLite，可以先把CTE结果插入临时表，再进行JOIN操作：

CREATE TEMP TABLE tbl2 AS
WITH tbl1(n) AS (SELECT 1 UNION ALL SELECT 2)
SELECT n, RANDOM() FROM tbl1;

SELECT * FROM tbl2 t1 CROSS JOIN tbl2 t2;

-- 用完后可删除临时表（可选）
DROP TABLE tbl2;

方法三：用LIMIT -1触发物化（老版本技巧）

在CTE的查询语句末尾加上LIMIT -1（SQLite中LIMIT -1表示返回所有行），这个操作会触发SQLite对CTE进行物化：

WITH tbl1(n) AS (SELECT 1 UNION ALL SELECT 2),
     tbl2(n, r) AS (SELECT n, RANDOM() FROM tbl1 LIMIT -1)
SELECT * FROM tbl2 t1 CROSS JOIN tbl2 t2;

这个技巧适用于不支持MATERIALIZED关键字的旧版本SQLite。

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