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

SQL Teaser: Where Clause Gone Wild

Try to guess what this WHERE clause is supposed to do.

 

WHERE

r.ApptId IS NULL

AND r.DATE >= ISNULL(NULL , '1/1/1900')

AND r.DATE < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000'))

AND

--Filter on resource

( ( NULL IS NOT NULL

AND r.DoctorResourceID IN ( NULL ) )

OR ( NULL IS NULL ) )

AND --Filter on facility

( ( NULL IS NOT NULL

AND r.FacilityID IN ( NULL ) )

OR ( NULL IS NULL ) )

AND --Filter on Inactive

ISNULL(r.inactive , 0) = 0

ORDER BY

ISNULL(g.LAST , '') + ISNULL(g.FIRST , '')

 

If you think I made that up, then you are in for a surprise. This is actually part of a query, I found it here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3662895&SiteID=1

This NULL IS NULL and NULL IS NOT NULL stuff is just killing me. I wonder why this person did not get an answer yet.....Enjoy your weekend  :-)

Published Friday, July 25, 2008 3:24 PM 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

 

Fabiano Neves Amorim said:

Wow, NULL IS NOT NULL is just killing us :-(.

July 25, 2008 3:32 PM
 

jchang said:

I have seen code like this before, it is called "job insurance" style

July 25, 2008 5:07 PM
 

jchang said:

and people thought terry childs was paranoid

July 25, 2008 5:13 PM
 

arb said:

This looks like it was dynamically generated. The "IN ( NULL )" sections in particular look to be dynamically generated - I would guess that the parent code has a string of IDs and it is supposed to generate something like "r.DoctorResourceID IN ( 1243, 1244, 236 )" but somehow the strings are empty/NULL.

July 25, 2008 7:28 PM
 

Rachel Appel said:

Oh my, is someone posting that at thedailywtf.com or what? The null is not null part is a riot!

July 25, 2008 7:55 PM
 

Itaborai, D. L. said:

ok, this looks kinda scary but its obvious what it is doing. It probably came from a stored procedure with a lot of optional parameters.

For example, let´s assume we are filtering on a resource, then we would have.

( ( 0xDEADBEEF IS NOT NULL

AND r.DoctorResourceID IN ( 0xDEADBEEF ) )

OR ( 0xDEADBEEF IS NULL ) ),

wich would retrieve all rows from the recall table that referenced the doctor resource association table wich had a primary key of 3735928559.

I´m not saying that it is pretty or even that I would use such an abomination ... well ... maybe when I´m given a crappy deadline.

I just think that he could at least leave the parameters unchanged instead of replacing them with NULLs all over the place. This is typical enterprise suckiness at its best ... At least it´s not 200 lines long and references half the tables in the application´s model.

July 25, 2008 9:19 PM
 

Itaborai, D. L. said:

as I said before, this probably came from a sproc with optional parameters.

It turns out, you actually need the NULL IS NOT NULL part. It works as a safeguard so the "AND r.DoctorResourceID IN ( NULL )" does not get evaluated thanks to boolean short-circuiting.

Given a non-null value, it would evaluate both parts and return true.

Given a null value, it evaluates the parenthesized AND expression as false, but the expression after the OR would evaluate as true.

The poor fella seems to be using string interpolation. I´m not a developer anymore, but I always remember seeing the puzzled eyes of junior developers staring at a query like this. They still had souls back then.

July 25, 2008 9:35 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