THE SQL Server Blog Spot on the Web

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

Denis Gobo

Do you want to laugh or cry?

I don't know if I should laugh or cry after looking at this code. This could not have been written by a human right?

Anyway here it is, consider it a teaser. If you want to torture yourself and look at the whole query then go here:


HAVING (((1 = 1)
AND (PP.Created >= ISNULL(NULL,'1/1/1900')
AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
OR((1 = 2)
AND ((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900')
AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))


 AND pv.DoctorId IN (NULL))


WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName

Published Friday, October 26, 2007 11:21 AM by Denis Gobo
Filed under: ,



Philhege said:

ZOMG, Denis, that is BRUTAL.

Really, now, is NULL NULL?  

I didn't know you could engage SQL Server in epistemological debate.

October 26, 2007 11:51 AM

Denis Gobo said:

I can engage SQL Server in any debate.  ;-)

I believe that that code has to be generated by some tool

I remember seeing a where clause starting with 1 =1 so that the tool didn't have to worry about writing AND or WHERE, it would always be AND

October 26, 2007 11:57 AM

Jared Ko said:

The sad part is somebody somewhere in Microsoft's SQL Query Optimization team has to write code to parse things like "1 = 1" out of the conditions to derive what SQL actually needs to do with the query.

The cool thing is that SQL Server is smart enough to parse some of that stuff out becuase of people writing automated code generators.

It looks like ISNULL on the hard-coded NULL is optimized out as well. Check out the execution plans for these statements in Adventureworks:

DECLARE @StateProvinceID INT

SELECT * FROM Person.Address WHERE StateProvinceID = ISNULL(@StateProvinceID, 1)

SELECT * FROM Person.Address WHERE StateProvinceID = ISNULL(NULL, 1)

October 26, 2007 4:15 PM

Adron said:

I think it was so bad that they took it off the site.

October 26, 2007 6:02 PM

Denis Gobo said:

Yep, it got red flagged

October 26, 2007 6:21 PM

Denis Gobo said:

I do have a complete copy of that brilliant code here:

Just in case you want to torture yourself

October 26, 2007 6:38 PM

James Luetkehoelter said:

I've literally seen code like this at least once a month in actual purchased systems, whether the platform is SQL or some other dbms I work with. I know it can be easy to write bad code if you aren't careful (I routinely look at things I did 5 years ago and think "what the &&*( was I thinking"), but this kind of thing is often the result of a product (or project) being driven by a deadline rather than actually being functional...

October 27, 2007 6:00 PM

Domagoj Orec said:

Thnx for the code D. , made me laugh.

Somehow human brain never stops to amaze me.

November 4, 2007 4:53 AM

Charles Kincaid said:

You said, "I believe that that code has to be generated by some tool".  I've worked with more than ione developer that I would have considered in that category.

November 5, 2007 8:40 AM
New Comments to this post are disabled

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


Privacy Statement