sql-server-2008 - SQL Server LEFT JOIN 无法匹配没有 JOIN 提示的行

Question

我有什么似乎是损坏的索引？

这是正在发生的事情。我有两个表函数，第一个是一组案例，第二个是一组已知的日期。这两个集合具有 1(案例)到 0 或 1(知道日期)的关系。通常我会像这样查询它们；

SELECT c.CaseID, a.AwareDate  
FROM Cases(@date) AS c  
LEFT JOIN AwareDates(@date) AS a ON c.CaseID = a.CaseID;

问题在于并非 AwareDates 中匹配的所有行似乎都已加入。如果我添加加入提示，他们就会这样做。说;

SELECT c.CaseID, a.AwareDate  
FROM Cases(@date) AS c  
LEFT MERGE JOIN AwareDates(@date) AS a ON c.CaseID = a.CaseID;

我从查询计划中注意到的是，添加连接提示会在连接之前添加一种 AwareDate 数据，否则不存在。此外，当没有提示时，查询计划器将连接翻转为 RIGHT OUTER JOIN，并且当然保留存在提示的 LEFT JOIN。

我已经完成了以下操作，没有检测到错误；

DBCC UPDATEUSAGE (0) WITH INFO_MESSAGES, COUNT_ROWS;  
EXECUTE sp_updatestats 'resample';  
DBCC CHECKDB (0) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS;  

我很难过......有什么想法吗？

这是UDF定义

ALTER FUNCTION dbo.Cases( @day date ) RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
SELECT 
    CaseID -- other 42 columns ommitted
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY UpdateDate DESC, UpdateNumber DESC) AS RecordAge,
        CaseID, 
        Action
    FROM
        dbo.CaseAudit
    WHERE
        convert(date,UpdateDate) <= @day
    ) AS History
WHERE
    RecordAge = 1                    -- only the most current record version
    AND isnull(Action,'') != N'DEL'  -- only include cases that have not been deleted
)

ALTER FUNCTION dbo.AwareDates( @day date ) RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
WITH 
    History AS (
        SELECT row_number() OVER (PARTITION BY CaseID, ContactID ORDER BY UpdateDate DESC, UpdateNumber DESC) AS RecordAge,
            CaseID, InfoReceived, ReceiveDate, ResetClock, Action
        FROM dbo.ContactLogAudit WITH (NOLOCK)
        WHERE convert(date,UpdateDate) <= @day
        ),
    Notes AS (
        SELECT 
            CaseID,
            convert(date,ReceiveDate,112) AS ReceiveDate,
            ResetClock
        FROM History 
        WHERE RecordAge = 1                -- only the most current record version
        AND isnull(Action,'') != N'DEL'    -- only include notes that have not been deleted
        AND InfoReceived = N'Y'            -- only include notes that have Info Rec'd checked 
        AND len(ReceiveDate) = 8 AND isnumeric(ReceiveDate) = 1 AND isdate(ReceiveDate) = 1 -- only include those with a valid aware date
        ),
    Initials AS (
        SELECT CaseID, min(ReceiveDate) AS ReceiveDate
        FROM Notes 
        GROUP BY CaseID
        ),
    Resets AS (
        SELECT CaseID, max(ReceiveDate) AS ReceiveDate
        FROM Notes 
        WHERE ResetClock = N'Y'
        GROUP BY CaseID
        )
SELECT 
    i.CaseID                              AS CaseID,
    i.ReceiveDate                         AS InitialAwareDate, -- the oldest valid aware date value (must have AE Info Reveived checked and a received date)
    coalesce(r.ReceiveDate,i.ReceiveDate) AS AwareDate  -- either the newest valid aware date value with the Reset Clock checked, otherwise the initial aware date value
FROM Initials AS i
LEFT JOIN Resets AS r 
    ON i.CaseID = r.CaseID
);

我进一步发现，如果我删除“WITH (NOLOCK)”表提示，我会得到正确的结果。此外，如果向 AwareDates UTF 添加连接提示，甚至在 Initials 和 Resets 之间的 LEFT JOIN 关系上添加 COLLATE Latin1_General_BIN。

查询计划行数——没有连接提示(损坏)

案例 { 实际:25,891，估计:19,071.9 }

AwareDates { 实际:24,693，估计:1,463.09 }

缩写 { 实际:24,693，估计:1,463.09 }

休息 { 实际:985，估计:33.2671 }

AwareDates 匹配连接结果集中的 8,108 个 Cases 行

查询计划行数——带连接提示(工作)

案例 { 实际:25,891，估计:19,071.9 }

AwareDates { 实际:24,673，估计:1,837.67 }

缩写 { 实际:24,673，估计:1,837.67 }

休息 { 实际:982，估计:42.6238 }

AwareDates 匹配连接结果集中的 24,673 个 Cases 行

我进一步缩小了问题的范围。我可以;

SELECT * FROM AwareDate(@date);  

和

SELECT * FROM AwareDate(@date) ORDER BY CaseID;  

具有不同的行数。

Answer 1

您没有指定 SQL 的特定版本 (@@version)，但这似乎很像 bug that was fixed在 SQL 2008 R2 的累积更新 6 中(显然它也适用于 SQL 2008)。

KB 2433265
FIX: You may receive an incorrect result when you run a query that uses the ROW_NUMBER function together with a left outer join in SQL Server 2008

文章中的示例指定了 DISTINCT。然而，这篇文章的措辞含糊不清——不清楚您是否需要一个独特的或 DISTINCT 是否是触发器之一。

您的示例与文章没有明显区别，但为了提出问题，它似乎已修改(即缺少 42 列)。有区别吗？也在 AwareDates udf 到我开始的时候 Initials CTE 你做一个 GROUP BY 可能与 DISTINCT 具有相同的效果。

更新

@Dennis 从您的评论中我仍然无法判断您使用的是 SQL 20080 还是 2008 R2。

如果您运行的是 2008，知识库文章会指出“此问题的修复程序首次在 SQL Server 2008 Service Pack 1 的累积更新 11 中发布”。所以，发布SP1。

另一方面，如果您使用的是 SQL 2008 R2，那么您在 CU 6(SP1 的一部分)中已修复此问题是正确的。但是这个错误似乎又出现了。看 Cumulative update package 4 for SQL Server 2008 R2 Service Pack 1 - 发布后 SP1。

970198      FIX: You receive an incorrect result when you run a 
            query that uses the row_number function in SQL Server 2008 
            or in SQL Server 2008 R2 

在 associated KB article MS 删除了对不同的引用:

Consider the following scenario. You run a query against a table that has a 
clustered index in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008
R2. In the query, you use the row_number function. In this scenario, you 
receive an incorrect result when a parallel execution plan is used for the 
query. If you run the query many times, you may receive different results.

这似乎证实了我之前对 KB 2433265 的阅读——措辞表明不同的是 众多中的一个 可能导致行为的条件。看来这次并行执行计划是罪魁祸首。