主页 / dba / 问题 / 80374
Accepted
Doug Lane
Asked: 2014-10-17 07:49:17 +0800 CST

随机数和连接类型的意外结果

  • 772

我有一个简单的脚本，它获取四个随机数（1 到 4），然后返回以获取匹配的 database_id 号。当我使用 LEFT JOIN 运行脚本时，我每次都会返回四行（预期结果）。但是，当我使用 INNER JOIN 运行它时，我得到了不同数量的行——有时是两行，有时是八行。

从逻辑上讲，应该没有任何区别，因为我知道 database_ids 1-4 的行存在于 sys.databases 中。并且因为我们从具有四行的随机数字表中进行选择（而不是加入它），所以返回的行永远不会超过四行。

这在 SQL Server 2012 和 2014 中都会发生。是什么导致 INNER JOIN 返回不同数量的行？

/* Works as expected -- always four rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
LEFT JOIN sys.databases d ON rando.RandomNumber = d.database_id;


/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Also returns a varying number of rows */

WITH rando AS (
  SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
  FROM sys.databases WHERE database_id <= 4
)

SELECT r.RandomNumber, d.database_id
FROM rando AS r
INNER JOIN sys.databases d ON r.RandomNumber = d.database_id;
sql-server t-sql

2 个回答

  1. 在网站上一位更聪明的人插话之前，这可能会提供一些见解。

    我将随机结果放入一个临时表中，并且无论连接类型如何，我始终会得到 4 个结果。

    /* Works as expected -- always four rows */

DECLARE @Rando table
(
    RandomNumber int
);

INSERT INTO
    @Rando
(
    RandomNumber
)
-- This generates 4 random numbers from 1 to 4, endpoints inclusive
SELECT
    1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
FROM
    sys.databases
WHERE
    database_id <= 4;

SELECT
    *
FROM
    @Rando AS R;

SELECT
    rando.RandomNumber
,   d.database_id
FROM 
    @Rando AS rando
    LEFT JOIN 
        sys.databases d 
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;


/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM 
    @Rando AS rando
    INNER JOIN 
        sys.databases d 
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;

/* Also returns a varying number of rows */

WITH rando AS 
(
    SELECT * FROM @Rando AS rando
)
SELECT r.RandomNumber, d.database_id
FROM 
    rando AS r
    INNER JOIN 
        sys.databases d 
        ON r.RandomNumber = d.database_id
ORDER BY 1,2;

    如果我比较您的第二个查询和带有表变量的变体之间的查询计划，我可以看到两者之间存在明显差异。红色的 XNo Join Predicate对我的穴居人开发者大脑来说真的很奇怪

    在此处输入图像描述

    如果我将查询的随机位消除为常量 1 % (4)，我的计划看起来会更好，但是计算标量被消除了，这让我更仔细地观察

    在此处输入图像描述

    它在连接后计算随机数的表达式。无论这是预期的，我仍然留给网站上的内部向导，但至少这就是为什么您在加入中获得可变结果的原因。

    2014

    对于那些在家玩的人，上面的查询计划是从 2008 R2 实例生成的。2014 年的计划看起来有所不同，但计算标量操作在加入后仍然存在。

    这是使用常量表达式的 2014 年的查询计划

    在此处输入图像描述

    这是使用 newid 表达式的 2014 实例的查询计划。

    在此处输入图像描述

    这显然是设计使然，此处连接问题。感谢@paulWhite 知道它的存在。

    • 12
  2. Best Answer

    通过添加额外的 SELECT，它将计算标量评估更深入地推入计划并提供连接谓词，顶部的计算标量然后引用较早的计算标量。

    SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id

|--Compute Scalar(DEFINE:([Expr1071]=[Expr1070]))

|--Compute Scalar(DEFINE:([Expr1070]=(1)+abs(checksum(newid()))%(4)))

    仍在深入研究为什么要等这么晚才这样做，但目前正在阅读 Paul White 的这篇文章 ( https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html ) . 也许这与 NEWID 不是确定性的事实有关？

    • 9

相关问题