Monday, June 12, 2006 10:08 AM
I think I might have found an (obscure) bug in the SQL Server expression evaluator.
I'm trying to create some dummy data for testing purposes.
I need to make an int column contain either 1, 2 or NULL. Here's my SQL:
SELECTnullif(floor(rand(checksum(newid())) * 3), 0)
The rand(checksum(newid()) * 3 creates a random number between 0 and 2. I then use NULLIF to change zeros to nulls.
The problem is that the result data contains 1, 2, NULL *and* 0 !
It looks like the NULLIF function evaluates its first argument twice - the first time round to compare against its second argument, the second to return the result.
As the first argument is non-deterministic (because of the newid()), we get a different value on the second evaluation, hence the zeros appear in the resultset.
I can work around this problem, but is it a known bug?
Monday, June 12, 2006 9:48 PM
NULLIF is just a short-hand of writing:
case <expr> when 0 then NULL else <expr> end
As you observed, this will not produce the correct results if the <expr> is non-deterministic in nature and it gets evaluated twice in case of non-zero values. The best way to deal with this result is to dump the results of the expression into a temporary table first and then perform the NULLIF check like:
SELECT floor(rand(checksum(newid())) * 3) as rnd
fromanytable SELECT NULLIF(rnd, 0) AS rnd
I am however not sure if this is considered a bug since the use of NULLIF is just a short-hand for the CASE expression I showed above. This example probably shows the reason why non-deterministic functions should not be used in a set-based statement. There are many cases where this will fail. I would encourage you to file a bug just the same using http://connect.microsoft.com.
Tuesday, June 13, 2006 11:51 AMThanks.
To quote the documentation:
http://msdn2.microsoft.com/en-us/library/ms177562.aspxNULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
At the very least, the documentation should be altered to warn that the first expression is re-evaluated and may change for non-deterministic functions.
Tuesday, June 13, 2006 8:28 PMI agree that the documentation is confusing. Please use the "Send Feedback" button in the Books Online topic to send your comments. This will get sent to the topic author directly and a bug will be filed automatically. I will also send email internally about this issue. Thanks for reporting the problem.