Summer SQL Teaser #10 NULLIF

My colleague and I were playing with this and noticed that the result set not only produced  1's and NULLS but it also stores blanks which should not occur since the blanks should be converted to NULL.  What's interesting is that when you parameterize the  Rand()  function then the results are accurate 1's and Nulls only.  

drop table #j  

CREATE TABLE #j (n varchar(15), val varchar(15), orig varchar(15) )

DECLARE @a int , @val float

SET @a = 1  

WHILE @a <= 1000 BEGIN  

SET @val = RAND()

INSERT #j  

SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ') , @val, NULLIF(REPLICATE('1', RAND()*2 ) , ' ')

SET @a = @a + 1  

END  

select * from #j

That's fine but RAND just producing one and blanks.  The deterministic function NULLIF should interpret all the blanks to NULL, but it doesn't.  How is it possible to have blanks in the result set?

I second Kim's question: how is it possible for NULLIF() to ever evaluate to ' ', when that is the value of the second expression passed to it?  *And* why does NULLIF() evaluate to ' ' when RAND() is not parameterized, but it doesn't when RAND() *is* parameterized?  I guess I'm not clear on what part determinism would play in this inconsistency, but perhaps I am missing something... :)

(BTW - in the above example Kim posted, it's not expected for column #j.n to ever match column #j.orig on a row-by-row basis, but when looking at each column as a whole you can clearly see that one column has ' ' values and the other does not.)

Thanks in advance.  :)

Ohhhh, I think the lightbulb just went off in my head!  :)

> SELECT CASE  WHEN REPLICATE('1', RAND()*2)   =''

> THEN NULL ELSE REPLICATE('1', RAND()*2) END

So the REPLICATE() statement is evaluated twice - once in the test, and once when the value is returned.  That is why a blank is able to squeak by.

Now I see where determinism comes into play!

Hvala mnogo!  ;)   I will now be able to sleep tonight!  :)

Hey Denis,

>>(I might be wrong, so someone can correct me if I am)<<

You're completely right, as can be verified by running this:

CREATE TABLE #j (n varchar(15))

go

SET SHOWPLAN_TEXT ON;

go

INSERT #j

SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ');

go

SET SHOWPLAN_TEXT OFF;

go

DROP TABLE #j

Or by reading the ANSI standard documents, of course. I hoped that Books Online would explain this as well, but alas!

Sorry about the double spacing, I didn't add them.

Oh, and I forgot to add that this is a really great teaser - one of your best so far!!