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, которые соответствуют, кажутся JOIN'd. Если я добавлю подсказку присоединения, они тогда сделают. сказать;

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

Что я заметил в плане запроса, так это то, что добавление подсказки о соединении добавляет данные AwareDate своего рода перед соединением, чего нет в противном случае. Кроме того, планировщик запросов переворачивает соединение в ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, когда нет подсказки, и, конечно, сохраняет левое соединение там, где есть подсказка.

Я сделал следующее без ошибок;

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), я получу правильные результаты. Также, если добавить подсказку о соединении в UTF AwareDates или даже добавить COLLATE Latin1_General_BIN в отношении LEFT JOIN между инициалами и сбросами.

---

Количество строк в плане запроса - без подсказки соединения (не работает)

• Случаи {Фактические: 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), но это подозрительно похоже на ошибку, которая была исправлена в накопительном обновлении 6 для SQL 2008 R2 (очевидно, это также относится и к SQL 2008).

KB 2433265
ИСПРАВЛЕНИЕ: вы можете получить неверный результат при запуске запроса, который использует функцию ROW_NUMBER вместе с левым внешним объединением в SQL Server 2008

В примере в статье указано DISTINCT. Статья, однако, сформулирована неоднозначно - неясно, НУЖЕН ли вам отчетливый или DISTINCT является одним из триггеров.

В вашем примере нет ничего похожего на статью, но он кажется измененным ради того, чтобы задать вопрос (то есть пропущено 42 столбца). Есть ли отличное? Также в AwareDates UDF к тому времени, когда я приступаю к Initials CTE вы делаете GROUP BY, который может иметь тот же эффект, что и DISTINCT.

---

ОБНОВИТЬ

@ Денис из вашего комментария Я до сих пор не могу сказать, используете ли вы SQL 20080 или 2008 R2.

Если вы работаете в 2008 году, в статье базы знаний говорится: "Исправление для этой проблемы было впервые выпущено в Накопительном обновлении 11 для SQL Server 2008 с пакетом обновления 1". Итак, пост SP1.

С другой стороны, если вы используете SQL 2008 R2, вы правы, что это было исправлено в CU 6, который был частью SP1. Но эта ошибка, похоже, всплыла. Посмотрите на накопительный пакет обновления 4 для SQL Server 2008 R2 с пакетом обновления 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 

В соответствующей статье базы знаний 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 - формулировка предполагает, что отличительные - это только одно из многих условий, которые могут вызвать поведение. Кажется, что в этот раз виновным является план параллельного выполнения.