Join Bug In SQL Server? (can't Be...)

If this is a SQL-Server bug, what do I do from here? If it's my problem, I sure can't see it.


The second SELECT in the UNION below is virtually identical to the first. However, the second SELECT joins on a different fields (blue & red ink) and is limited (green ink) in the WHERE clause to show only partially null rows for brevity, but fails to show both partially null rows that do show up in the first. (The Join_Field values are different for each SELECT, so the missing field should not be due to a
coalescing of similar rows)


As can be seen, all qualifying source rows are fully shown in the first SELECT because this is a FULL OUTER JOIN, so there is no EmpID = 1 for TeamID = 2 in table X and there is also no EvaluatorID = 1 for TeamID = 2 in table X, but a record for EmpID =1 and TeamID =2 does exist in table Y. Nonetheless, EvaluatorID does not produce the respective, partially null records in the second SELECT.


This appears to me to be a bug in SQL Server, but must be due to something not obvious.
I've cloned the data and SQL to a test database, where everything works correctly, so unless there is something else to consider in the schema, there seems to be a bug based on a database with more than just the test tables and fields.



Ancillary information: all fields shown for both tables are members of a compound primary key for their respective tables. There are additional, unused fields in both tables (table X has dozens of defined fields with lots of potential comments and scores). The database currently has no indexes (under development, not yet production, but the deadline is looming large). The database is runing remotely on a test internet site.


SQL Statement:


SELECT     'Y.EmpID=X.EmpID' AS Join_Field, X.CoID, X.EvalID, X.TeamID, X.EvaluatorID, X.EmpID, Y.CoID, Y.TeamID, Y.EmpID AS Y_EmpID
FROM         EvalAnswers X FULL OUTER JOIN
                      MergeEmps Y ON X.CoID = Y.CoID AND X.TeamID = Y.TeamID AND Y.EmpID = X.EmpID
WHERE     (ISNULL(X.CoID, Y.CoID) = 113) AND (ISNULL(X.EvalID, 1) = 1)
UNION
SELECT     'Y.EmpID=X.EvaluatorID' AS Join_Field, X.CoID, X.EvalID, X.TeamID, X.EvaluatorID, X.EmpID, Y.CoID, Y.TeamID, Y.EmpID AS Y_EmpID
FROM         EvalAnswers X FULL OUTER JOIN
                      MergeEmps Y ON X.CoID = Y.CoID AND X.TeamID = Y.TeamID AND Y.EmpID = X.EvaluatorID
WHERE     (ISNULL(X.CoID, Y.CoID) = 113) AND (ISNULL(X.EvalID, 1) = 1) AND (X.CoID IS NULL OR
                      Y.CoID IS NULL)
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9


Results:

            Join_Field                                     X.CoID   X.EvalID  X.TeamID  X.Ev...ID   X.EmpID      Y.CoID   Y.TeamID   Y.EmpID
Y.EmpID=X.EmpID
                null
       null
       null
       null
       null
       113
      2
          1
Y.EmpID=X.EmpID 
               113
      1 
         1
          1
          1
          113   
   1   
       1
Y.EmpID=X.EmpID 
               113
      1 
         1   
       1
          2
          113 
     1 
         2
Y.EmpID=X.EmpID 
               113
      1 
         1 
         2 
         1 
         113 
     1 
         1
Y.EmpID=X.EmpID 
               113 
     1 
         1 
         2 
         2 
         113 
     1 
         2
Y.EmpID=X.EmpID 
               113 
     1 
         1 
         3 
         3 
         null
       null
       null
Y.EmpID=X.EmpID 
               113 
     1 
         2 
         2 
         2 
         113 
     2 
         2
Y.EmpID=X.EmpID 
               113 
     1 
         2 
         2 
         3 
         113 
     2 
         3
Y.EmpID=X.EmpID 
               113 
     1 
         2 
         3 
         2 
         113 
     2 
         2
Y.EmpID=X.EmpID 
               113 
     1 
         2 
         3 
         3 
         113 
     2 
         3
Y.EmpID=X.EvaluatorID
         113 
     1 
         1 
         3 
         3
          null
       null
       null
 The results for the second SELECT in the UNION should be two partially null rows, not just one.