主页 / dba / 问题 / 209084
Accepted
Heinzi
Asked: 2018-06-09 01:53:20 +0800 CST

SQL Server 将 A <> B 拆分为 A < B OR A > B，如果 B 是不确定的，则会产生奇怪的结果

  • 772

我们在 SQL Server 中遇到了一个有趣的问题。考虑以下重现示例：

CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');

SELECT s_guid FROM #test
WHERE s_guid = '7E28EFF8-A80A-45E4-BFE0-C13989D69618'
  AND s_guid <> NEWID();

DROP TABLE #test;

小提琴

请暂时忘记s_guid <> NEWID()条件似乎完全没用——这只是一个最小的重现示例。由于NEWID()匹配某个给定常量值的概率极小，因此每次都应评估为 TRUE。

但事实并非如此。运行此查询通常返回 1 行，但有时（非常频繁，10 次中超过 1 次）返回 0 行。我已经在我的系统上使用 SQL Server 2008 复制了它，您可以使用上面链接的小提琴（SQL Server 2014）在线复制它。

查看执行计划表明查询分析器显然将条件拆分为s_guid < NEWID() OR s_guid > NEWID()：

查询计划截图

...这完全解释了为什么它有时会失败（如果第一个生成的 ID 小于给定的 ID 而第二个大于给定的 ID）。

是否允许 SQL Server 计算A <> B为A < B OR A > B，即使其中一个表达式是不确定的？如果是，它记录在哪里？还是我们发现了错误？

有趣的是，AND NOT (s_guid = NEWID())产生相同的执行计划（和相同的随机结果）。

当开发人员想要有选择地排除特定行并使用时，我们发现了这个问题：

s_guid <> ISNULL(@someParameter, NEWID())

作为“捷径”：

(@someParameter IS NULL OR s_guid <> @someParameter)

我正在寻找文档和/或错误确认。该代码并不是所有相关的，因此不需要解决方法。

sql-server optimization

3 个回答

  1. Best Answer

    是否允许 SQL Server 计算A <> B为A < B OR A > B，即使其中一个表达式是不确定的？

    这是一个有点争议的点，答案是有条件的“是”。

    我所知道的最好的讨论是在回答 Itzik Ben-Gan 的 Connect 错误报告Bug with NEWID and Table Expressions时给出的，该讨论已关闭，因为无法修复。Connect 已经停用，所以那里的链接指向一个网络档案。可悲的是，许多有用的材料因 Connect 的消亡而丢失（或变得更难找到）。无论如何，微软的 Jim Hogg 最有用的引述有：

    这触及了问题的核心——优化是否允许改变程序的语义？即：如果一个程序产生某些答案，但运行缓慢，查询优化器使该程序运行得更快，同时改变给定的结果是否合法？

    在大喊“不！”之前 （我个人也倾向于 :-)，考虑一下：好消息是，在 99% 的情况下，答案是相同的。所以查询优化是一个明显的胜利。坏消息是，如果查询包含副作用代码，那么不同的计划确实会产生不同的结果。NEWID() 就是这样一种具有副作用（不确定性）的“函数”，它暴露了差异。[实际上，如果你进行实验，你可以设计其他的 - 例如，对 AND 子句的短路评估：使第二个子句抛出一个算术被零除 - 不同的优化可能会在第一个子句之前执行第二个子句]这反映了Craig 在该线程其他地方的解释是，SqlServer 不保证标量运算符何时执行。

    所以，我们有一个选择：如果我们想在非确定性（副作用）代码存在的情况下保证某种行为——例如 JOIN 的结果遵循嵌套循环执行的语义——那么我们可以使用适当的选项来强制执行该行为——正如 UC 指出的那样。但是生成的代码将运行缓慢——这实际上是阻碍查询优化器的代价。

    综上所述，我们正在将查询优化器朝着 NEWID() 的“预期”行为的方向移动——以性能换取“预期的结果”。

    随着时间的推移，这方面的行为发生变化的一个例子是NULLIF 不能正确地使用非确定性函数，例如 RAND()。还有其他类似的情况，例如COALESCE使用子查询可能会产生意想不到的结果，这些情况也在逐步解决。

    吉姆继续说：

    关闭循环。. . 我已经和开发团队讨论过这个问题。最终我们决定不改变当前行为，原因如下：

    1) 优化器不保证标量函数的执行时间或次数。这是由来已久的信条。这是基本的“回旋余地”，它允许优化器有足够的自由来在查询计划执行方面获得显着改进。

    2）这种“每行一次的行为”并不是一个新问题，尽管它没有被广泛讨论。我们在 Yukon 版本中开始调整它的行为。但在所有情况下，很难准确地确定它的确切含义！例如，它是否适用于计算“途中”到最终结果的中间行？- 在这种情况下，它显然取决于所选择的计划。或者它是否仅适用于最终将出现在完成结果中的行？- 这里有一个讨厌的递归，我相信你会同意的！

    3) 正如我之前提到的，我们默认“优化性能”——这对 99% 的情况都有好处。可能会改变结果的 1% 的情况很容易发现 - 副作用的“功能”，例如 NEWID - 并且很容易“修复”（因此交易性能）。这再次默认为“优化性能”，是长期建立的，并被接受。（是的，这不是编译器为传统编程语言选择的立场，但就这样吧）。

    所以，我们的建议是：

    a) 避免依赖非保证时间和执行次数语义。b) 避免在表表达式深处使用 NEWID()。c) 使用 OPTION 强制执行特定行为（交易性能）

    希望这个解释有助于澄清我们将这个错误关闭为“不会修复”的原因。

    有趣的是，AND NOT (s_guid = NEWID())产生相同的执行计划

    这是规范化的结果，它发生在查询编译的早期。两个表达式都编译为完全相同的规范化形式，因此生成了相同的执行计划。

    • 23

  2. 这在此处记录（有点）：

    查询中指定的函数实际执行的次数可能因优化器构建的执行计划而异。一个示例是由 WHERE 子句中的子查询调用的函数。子查询及其函数的执行次数会随着优化器选择的不同访问路径而变化。

    用户定义的函数

    这不是查询计划将多次执行 NEWID() 并更改结果的唯一查询形式。这令人困惑，但实际上对于 NEWID() 用于密钥生成和随机排序非常重要。

    最令人困惑的是，并非所有非确定性函数的实际行为都是这样的。例如 RAND() 和 GETDATE() 每次查询只执行一次。

    • 11

  3. 对于它的价值，如果你看一下这个旧的 SQL 92 标准文档，关于不等式的要求在“ 8.2 <comparison predicate>”部分描述如下：

    1) 令X和Y为任意两个对应的<row value constructor element>。令XV和YV分别为X和Y所表示的值。

    [...]

    ii) 当且仅当 XV 和 YV 不相等时，“X <> Y”为真。

    [...]

    7) 令Rx和Ry为<comparison predicate>的两个<row value constructor>，令RXi和RYi分别为Rx和Ry的第i个<row value constructor element>。“Rx <comp op> Ry”为真、假或未知，如下所示：

    [...]

    b) “x <> Ry”为真当且仅当 RXi <> RYi 对于某些 i。

    [...]

    h) 当且仅当“Rx = Ry”为真时，“x <> Ry”为假。

    注意：为了完整性，我包括了 7b 和 7h，因为他们谈论的是<>比较——我认为行值构造函数与多个值的比较不会在 T-SQL 中实现，除非我只是严重误解了这句话的意思——这是很有可能的

    这是一堆令人困惑的垃圾。但是如果你想让垃圾箱继续潜水......

    我认为1.ii 是适用于这种情况的项目，因为我们正在比较“行值构造函数元素”的值。

    ii) 当且仅当 XV 和 YV 不相等时，“X <> Y”为真。

    基本上，如果 X 和 Y 表示的值不相等，则表示X <> Y为真。由于是对该谓词的逻辑等效重写，因此优化器使用它非常酷。X < Y OR X > Y

    <>该标准没有对与比较运算符任一侧的行值构造函数元素的确定性（或您得到的任何东西）相关的定义施加任何限制。用户代码有责任处理一侧的值表达式可能不确定的事实。

    • 5

相关问题