为什么SQLite CTE JOIN中的RANDOM（）行为与其他RDBMS不同？

一尘不染

为什么SQLite CTE JOIN中的RANDOM（）行为与其他RDBMS不同？

sql

RANDOM()公用表表达式（CTE）联接中的值在SQLite中的行为不正常。

SQL：

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

示例SQLite结果：

n   r                       n   r
1   7058971975145008000     1   8874103142384122000
1   1383551786055205600     2   8456124381892735000
2   2646187515714600000     1   7558324128446983000
2   -1529979429149869800    2   7003770339419606000

每列中的随机数都不同。但是CROSS JOIN重复行-
因此我希望每列中有两对相同的数字-PostgreSQL，Oracle
11g和SQL Server
2014中就是
这种情况（使用基于行的种子时）。

PostgreSQL / Oracle 11g / SQL Server 2014示例结果：

n   r                   n   r
1   0.117551110684872   1   0.117551110684872
1   0.117551110684872   2   0.221985165029764
2   0.221985165029764   1   0.117551110684872
2   0.221985165029764   2   0.221985165029764

问题

可以解释SQLite中的行为吗？是虫子吗？
有没有办法让CTE中的表B（基于同一CTE中的表A）具有附加的一列随机生成的数字，这些数字在用在CTE中时将保持不变JOIN？

阅读 147

2021-05-30

共1个答案

一尘不染

您的问题相当漫长而棘手-不是一个问题。但是，这很有趣，我学到了一些东西。

这个说法是不正确的：

SQL Server为RAND（）函数分配一个随机种子：在SELECT中使用时，它仅被种子一次，而不是每一行。

SQL Server具有运行时常量功能的概念。这些是从已编译查询中拉出的函数，并且在查询开始时针对 每个表达式
执行一次。最突出的示例是getdate()（和相关的日期/时间函数）和rand()。

如果运行，您可以很容易地看到这一点：

select rand(), rand()
from (values (1), (2), (3)) v(x);

每列具有相同的值，但列之间的值不同。

大多数数据库（包括SQLite）对rand()/都有更直观的解释random()。（作为个人说明，在每行上返回相同值的“随机”函数非常违反直觉。）每次调用该函数时，您将获得一个不同的值。对于SQL
Server，通常使用使用以下表达式newid()：

select rand(), rand(), rand(checksum(newid()))
from (values (1), (2), (3)) v(x);

至于第二个问题，看来 SQLite实现了递归CTE。所以这就是您想要的：

WITH tbl1(n) AS (
      SELECT 1 UNION ALL SELECT 2
     ),
     tbl2(n, r) AS (
       SELECT n, RANDOM()
       FROM tbl1
       union all
       select *
       from tbl2
       where 1=0
      )
SELECT *
FROM tbl2 t1 CROSS JOIN tbl2 t2;

我没有看到这种情况的文档，因此使用后果自负。这是一个DB小提琴。

而且，据记录，这似乎也适用于SQL Server。我刚刚学到了一些东西！

编辑：

正如评论中所建议的，实现可能并不总是发生。它似乎适用于同一级别的两个引用：

WITH tbl1(n) AS (
      SELECT 1 UNION ALL SELECT 2),
     tbl2(n, r) AS (
       SELECT n, RANDOM()
       FROM tbl1
       union all
       select *
       from tbl2
       where 1=0
      )
SELECT t2a.r, count(*)
FROM tbl2 t2a left JOIN
     tbl2 t2b
     on t2a.r = t2b.r
GROUP BY t2a.r;

2021-05-30