When I execute the following query I would expect the results to be two of the same number. But it is two different numbers.
with t as (SELECT RAND())
SELECT * FROM t
UNION ALL
SELECT * FROM t;
I was using a similar process to try to randomize treatment and control and I thought the CTE was storing results, but it seems like it is storing a reference to a function and the function re-rolls the rand() every time it is queried later in the CTE chain. Why are the numbers not the same?
I executed a Google search on the phrase "when is sql cte evaluated" and I got back a good number of responses. In each response, the message was the same:
Common Table Expressions (CTEs) in SQL are evaluated every time they are referenced.
This feel pretty clear ... if you reference a CTE twice in a query, then it will be free to be evaluated twice.
If what you want is a single random number that is consistent across your query, consider declaring a variable that has a value for that randomly genereated value and referencing the variable in the remainder of your logic.