Missing Rows when running SELECT in SQL Server

Question

I have a simple select statement. It's basically 2 CTE's, one includes a ROW_NUMBER() OVER (PARTITION BY, then a join from these into 4 other tables. No functions or anything unusual.

WITH Safety_Check_CTE AS 
(
    SELECT  
        Fact_Unit_Safety_Checks_Wkey,
        ROW_NUMBER() OVER (PARTITION BY [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] 
                           ORDER BY [Dim_Safety_Check_Date_Wkey] DESC) AS Check_No
    FROM 
        [Pitches].[Fact_Unit_Safety_Checks]
), Last_Safety_Check_CTE AS 
(
    SELECT 
        Fact_Unit_Safety_Checks_Wkey 
    FROM 
        Safety_Check_CTE 
    WHERE 
        Check_No = 1
)
SELECT
    COUNT(*)
FROM
    Last_Safety_Check_CTE lc
JOIN 
    Pitches.Fact_Unit_Safety_Checks f ON lc.Fact_Unit_Safety_Checks_Wkey = f.Fact_Unit_Safety_Checks_Wkey
JOIN 
    DIM.Dim_Unit u ON f.Dim_Unit_Wkey = u.Dim_Unit_Wkey
JOIN 
    DIM.Dim_Safety_Check_Type t ON f.Dim_Safety_Check_Type_Wkey = t.Dim_Safety_Check_Type_Wkey
JOIN 
    DIM.Dim_Date d ON f.Dim_Safety_Check_Date_Wkey = d.Dim_Date_Wkey
WHERE 
    f.Safety_Check_Certificate_No IN ('GP/KB11007') --option (maxdop 1)

Sometimes it returns 0, 1 or 2 rows. The result should obviously be consistent.

I have ran a profile trace whilst replicating the issue and my session was the only one in the database.

I have compared the Actual execution plans and they are both the same, except the final hash match returns the differing number of rows.

I cannot replicate if I use MAXDOP 0.

Answer 1

In case you use my comment as the answer.

My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.

Answer 2

In the CTE's you are finding the [Fact_Unit_Safety_Checks_Wkey] that's associated with the most resent row for any given [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] combination... With no regard for weather or not [Safety_Check_Certificate_No] is equal to 'GP/KB11007'.

Then, in the outer query, you are filtering results based on [Safety_Check_Certificate_No] = 'GP/KB11007'.

So, unless the most recent [Fact_Unit_Safety_Checks_Wkey] happens to have [Safety_Check_Certificate_No] = 'GP/KB11007', the data is going to be filtered out.