sql - 使用 `newid()` 将单行子查询与列交叉连接会导致每一行都有不同的 GUID

Question

摘要

类似的查询

SELECT *
       FROM elbat t
            CROSS JOIN (SELECT newid() guid) x;

在 SQL Server 中，每行都有一个不同的 GUID，而不是在整个结果中每行都有一个共同的 GUID。如何为结果的所有行设置一个 GUID(不使用变量或(临时)表)？

设置

考虑 SQL Server 数据库中的下表。

CREATE TABLE elbat
             (id integer);

INSERT INTO elbat
            VALUES (1);
INSERT INTO elbat
            VALUES (2);
INSERT INTO elbat
            VALUES (3);
INSERT INTO elbat
            VALUES (4);
INSERT INTO elbat
            VALUES (5);
INSERT INTO elbat
            VALUES (6);

让我们运行以下查询。

SELECT *
       FROM elbat t
            CROSS JOIN (SELECT newid() guid) x;

这是 db<>fiddle和 SQL Fiddle看看它的实际效果。

问题

令我惊讶的是，结果每一行都有不同的 GUID。例如:

 id | guid                                
 -: | :-----------------------------------
  1 | ad146af7-9ebd-4521-a440-47c7dea6a1d4
  2 | ce24fbb8-af64-480c-8c46-1e03187642c5
  3 | 14509451-9b1d-49e9-8da2-c691947ae805
  4 | 37a86339-e352-486f-b541-92798540599f
  5 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
  6 | d491275b-4ebb-461b-94e2-93b47e7d2348

这让我很困惑。我希望每一行在整个结果集中都有相同的 GUID。例如:

 id | guid                                
 -: | :-----------------------------------
  1 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
  2 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
  3 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
  4 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
  5 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
  6 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8

我当然明白，GUID 会因调用而异。但我不明白为什么当我交叉加入单个 GUID 并且没有将 newid() 调用放在投影列列表中时它会逐行变化。

其他信息

我在 fiddle 平台上的所有可用版本以及本地 Microsoft SQL Server 2014 (12.0.2269.0 (X64), Express) 上都尝试了这个。结果在任何地方都是一样的(当然只是 GUID 发生了变化)。

质疑我对连接的理解，我还在其他 DBMS 上用等效的设置和查询做了一些测试。

• (DDL 和 DML 都保持不变。)

• Postgres:

  SELECT *
         FROM elbat t
              CROSS JOIN (SELECT uuid_generate_v4() guid) x;
  

  SQL Fiddle

• 甲骨文:

  SELECT *
         FROM elbat t
              CROSS JOIN (SELECT sys_guid() guid
                                 FROM dual) x;
  

  db<>fiddle

• MariaDB:

  SELECT *
         FROM elbat t
              CROSS JOIN (SELECT uuid() guid) x;
  

  db<>fiddle

• MySQL:

  SELECT *
         FROM elbat t
              CROSS JOIN (SELECT uuid() guid) x;
  

  SQL Fiddle

所有这些其他 DBMS 都会产生我实际期望的结果——结果的所有行中都有一个公共(public) GUID。

我还尝试更改查询。不过没用。

• 将子查询放在 CTE 中。
• 我尝试从子查询中的物理表中进行选择，而不是使用 TOP 和主键从没有 FROM 的 SELECT .
• 使用隐式交叉连接(FROM elbat, (SELECT newid() ...))。
• 使用 CROSS APPLY。

查看文档，我在任何地方都找不到这种行为。

问题

为什么 SQL Server 的行为与所有其他(经过测试的)DBMS 不同(在这方面)，有没有办法按预期获得结果(不使用变量或(临时)表)？

(注意:我知道我可以使用用 newid() 初始化的变量并将其放在投影列中。但是当我试图避免这样的变量时，问题实际上出现了。我实际上想为 "Order table randomly but with exceptions" 寻找一个无变量、仅查询的解决方案。)

Answer 1

我对 SQL Server 的行为感到非常惊讶。我没有意识到它一遍又一遍地重新评估这些子查询。我怀疑原因是优化:cross join中的表达式实际上移动到了读取数据的节点，所以函数被一遍又一遍地调用。

无论如何，我认为这是错误的。这样的优化应该认识到 newid() 是一个 volatile 函数并进行相应的调整。

经过一些实验，我发现子查询中的 order by 确实会导致它只被评估一次。所以，这就是你想要的:

select *
from elbat cross join
     (select top (1) newid() as guid
      order by guid
     ) x;

另一个符合您期望的版本:

select *
from elbat cross join
     (select max(newid()) as guid
     ) x;

顺便说一句，后一个版本也可以在 select 中使用:

select *, (select max(newid())) as guid
from elbat ;

在这种情况下，我希望对每一行评估一次子查询。去图吧。