THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #10 NULLIF

Here is a fun teaser. NULLIF will return a null value if the two specified expressions are equivalent.
So to give an example

DECLARE @v varchar
SET @v = ' '
SELECT NULLIF(@v,' ')

That returned NULL because @v and ' ' are the same

Now run this first

CREATE TABLE #j (n varchar(15))

DECLARE @a int
SET
@a = 1
WHILE @a <= 1000 BEGIN
INSERT
#j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END


Then without running try to guess if the following query will return any rows

SELECT * FROM #j WHERE n = ' '
Published Friday, August 03, 2007 10:22 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kim Ulvang said:

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

August 3, 2007 12:43 PM
 

Denis Gobo said:

when you parameterize RAND it is not non-deterministic anymore  ;-)

August 3, 2007 2:25 PM
 

Kim Ulvang said:

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?

August 3, 2007 2:55 PM
 

Valerie (Kim's colleague) said:

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.  :)

August 3, 2007 3:03 PM
 

Denis Gobo said:

Valerie and Kim,

Here is what i think happens

The NULLIF does this (I might be wrong, so someone can correct me if I am)

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

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

it checks the first expression and if that is not '' then it does the second, however that can also be a blank

just run this 10 times or so and you will see that it will become a blank eventually

August 3, 2007 3:17 PM
 

Valerie said:

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!  :)

August 3, 2007 3:29 PM
 

Denis Gobo said:

No problem (or should that be nema na cemu), I don't want to cause anyone sleepless nights  ;-)

August 3, 2007 3:33 PM
 

Hugo Kornelis said:

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!

August 3, 2007 4:27 PM
 

Hugo Kornelis said:

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!!

August 3, 2007 4:28 PM
 

Denis Gobo said:

Thanks Hugo

August 3, 2007 9:40 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement