The result of this query is strange. If the * 2.0 part is removed one can see 9 rows (which is the correct result). With the multiplication the result are 81 rows (which is wrong). The reason for this is that the nested loops operators uses Expr1003 = Expr1003 as the join predicated.
WITH MyEmps AS (
SELECT e.EmployeeID * 2.0 AS EmployeeId,
e.FirstName + ' ' + e.LastName AS FullName
FROM Employees e
)
SELECT D1.EmployeeID AS [D1.EmployeeID],
D1.FullName AS [D1.FullName],
D2.EmployeeID AS [D2.EmployeeID],
D2.FullName [D2.FullName]
FROM MyEmps D1
INNER JOIN MyEmps D2 ON D1.EmployeeID = D2.EmployeeID
Ported from CodePlex
The result of this query is strange. If the
* 2.0
part is removed one can see 9 rows (which is the correct result). With the multiplication the result are 81 rows (which is wrong). The reason for this is that the nested loops operators usesExpr1003 = Expr1003
as the join predicated.