Bug in expression evaluator using NULLIF and RAND()

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:

SELECT nullif(floor(rand(checksum(newid())) * 3), 0)

from anytable

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?

Alan.

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

INTO #t

from anytable

SELECT NULLIF(rnd, 0) AS rnd

FROM #t

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.

|||Thanks.

To quote the documentation:

http://msdn2.microsoft.com/en-us/library/ms177562.aspx

NULLIF 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.

|||I 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.