主页 / dba / 问题 / 267156
Accepted
sepupic
Asked: 2020-05-15 10:01:59 +0800 CST

使用计算表达式返回意外结果的 GROUPING SETS

  • 772

这里我们有两个类似grouping sets 的查询，使用 whereSELECT子句包含一些聚合计算的表达式：

SELECT RN10, RN10 / 10, COUNT(*) FROM 
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM 
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

它的计划在这里：第一个查询计划

和

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM 
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM 
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

对应的计划在这里：第二个查询计划

这两个查询首先计算一些聚合表达式，RN10 / 10在第一种情况和SUBSTRING(RN,3,99)第二种情况下，然后在SELECT子句中使用相同的表达式，但正如第一个计划所示，它在第一个查询中重新计算，而不是在第二个查询中。

结果，我们NULL在第一个结果集中有 s，这是非常出乎意料的：

结果

有人可以解释为什么第一个查询进行两次计算（一次在聚合中，一次在 final 中select），而第二次只计算一次？

sql-server execution-plan

1 个回答

  1. Best Answer

    我将使用一个更简单的示例，可以清楚地看到预期结果是什么。

    CREATE TABLE Queen
(
   FirstName        VARCHAR(7),
   Surname          VARCHAR(7)
); 

INSERT INTO Queen
    (FirstName, Surname)
VALUES
    ('Brian',   'May'),
    ('Freddie', 'Mercury'),
    ('John',    'Deacon'),
    ('Roger',   'Taylor')
;

    查询 1

    SELECT Surname,
       NULL AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)

    查询 1 结果

    +---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | NULL           |     1 |
| May     | NULL           |     1 |
| Mercury | NULL           |     1 |
| Taylor  | NULL           |     1 |
| NULL    | D              |     1 |
| NULL    | M              |     2 |
| NULL    | T              |     1 |
+---------+----------------+-------+

    查询 2

    SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) ) 
ORDER BY SurnameInitial, Surname

    查询 2 结果

    尽管在 SQL Server 中首先排序，但具有 as 的行排ORDER BY SurnameInitial在最后。NULLSurnameInitialNULL

    +---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | D              |     1 |
| May     | M              |     1 |
| Mercury | M              |     1 |
| Taylor  | T              |     1 |
| NULL    | NULL           |     1 |
| NULL    | NULL           |     2 |
| NULL    | NULL           |     1 |
+---------+----------------+-------+

    查询 1 和 2应该返回相同的结果。问题是 SQL Server 决定将其视为以下 SQL

    WITH GrpSets AS
(
SELECT Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)
)
SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       Count
FROM GrpSets

    这对我来说就像一个错误（跟踪标志 8605 表明损坏已经在初始查询树表示中完成）。错误报告。

    查询 3

    SELECT Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(FirstName,1)) )

    查询 3 个结果

    +---------+------------------+-------+
| Surname | FirstNameInitial | Count |
+---------+------------------+-------+
| NULL    | B                |     1 |
| NULL    | F                |     1 |
| NULL    | J                |     1 |
| NULL    | R                |     1 |
| Deacon  | NULL             |     1 |
| May     | NULL             |     1 |
| Mercury | NULL             |     1 |
| Taylor  | NULL             |     1 |
+---------+------------------+-------+

    Query3 不符合对列和引用该列的表达式进行分组的问题模式。无论如何，这里甚至不可能发生相同的问题，因为分组集部分相当于

    SELECT Surname,
       NULL AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(FirstName,1)

    这不会传递FirstName上游的整个列（甚至没有保证可以传递的唯一 列FirstName），因此不可能在此基础上计算表达式。LEFT(FirstName,1)

    出于同样的原因，您看不到(RN10), (SUBSTRING(RN,3,99)).

    @i-one在评论中的理由是很可能

    规范化（代数化）中的错误。它的逻辑可以SELECT在GROUP BY. 相同的逻辑似乎允许我们编写例如

    SELECT Surname, LEFT(Surname, 1), COUNT(*)
FROM   Queen
GROUP BY Surname

    无需显式添加计算表达式，如下所示

    GROUP BY Surname, LEFT(Surname, 1)

    或者另一个例子是

    SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       LEFT(Surname,2) AS SurnamePrefix,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )

    在这种情况下，这LEFT(Surname,2)是允许的，并且计算它的唯一方法是以对该LEFT(Surname,1)案例有问题的方式进行。

    • 12

相关问题