I have a question about how MS SQL evaluates functions inside a CTE. A few searches did not lead to any results related to this problem, but I apologize if this is well known, and I just lag behind the curve. This will not be the first time :-)
This request is a simplified (and obviously less dynamic) version of what I'm actually doing, but it has a problem that I am facing. It looks like this:
CREATE TABLE #EmployeePool(EmployeeID int, EmployeeRank int); INSERT INTO #EmployeePool(EmployeeID, EmployeeRank) SELECT 42, 1 UNION ALL SELECT 43, 2; DECLARE @NumEmployees int; SELECT @NumEmployees = COUNT(*) FROM #EmployeePool; WITH RandomizedCustomers AS ( SELECT CAST(c.Criteria AS int) AS CustomerID, dbo.fnUtil_Random(@NumEmployees) AS RandomRank FROM dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') c) SELECT rc.CustomerID, ep.EmployeeID FROM RandomizedCustomers rc JOIN #EmployeePool ep ON ep.EmployeeRank = rc.RandomRank; DROP TABLE #EmployeePool;
For all accomplishments of the above, the following can be assumed:
The result of dbo.fnUtil_Random() always an int value greater than zero and less than or equal to the argument passed. Since it is called above with @NumEmployees , which has a value of 2, this function always evaluates to 1 or 2.
The result of dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') creates a single-column single-row table containing sql_variant with the base type 'int', which has a value of 219935.
Given the above assumptions, it makes sense (for me, anyway) that the result of the expression above should always contain a table with two columns containing one record - CustomerID and EmployeeID. CustomerID must always be an int value of 219935, and EmployeeID must be either 42 or 43.
However, this is not always the case. Sometimes I get the expected single record. In other cases, I get two records (one for each EmployeeID), and the third - I have no records. However, if I replace the RandomizedCustomers CTE with a true temporary table, the problem will completely disappear.
Every time I think I have an explanation for this behavior, it turns out to be meaningless or impossible, so I can’t explain why this will happen. Since the problem does not occur when I replace the CTE with a temporary table, I can only assume that it has something to do with the functions inside the CTE that are evaluated when joining this CTE. Do you have any theories?