George's Division Teaser

This teaser was posted by my friend George on Tek Tips and I am posting it here after I asked for his permission. the main reason I am posting it here is because I want to get some opinions from the experts.

Take a look at these two queries

If

1/0 = 10 And 1/1 = 0

Select'True'As Query1

Else

Select'False'As Query1

If 1/0 = 10 Andsqrt(1)=0

Select'True'As Query2

Else

Select'False'As Query2

The first one runs, the second one does not. It looks like the engine doesn't even look at the 1/0 because the 1/1 = 0 is false anyway. The second query is not using a constant but a function and the optimizer actually has to run the statement and it bombs out. So what do you think, am I right?

Looks like pretty simple circuiting to me? Remember, the query engine can evaluate predicates in whatever order it sees fit (including those in an IF condition) and either short circuit or not. This is quite different from C-style languages, where short circuiting always happens left-to-right (or otherwise based on order of operations).

In response to your question are other languages like this, then I can say quite possibly but usually it is defined somewhere. Take C# for example

say you make a simple Command line with the code below

|| the double pipe is an or, and what happens is method 1 fires and method 2 does not.

static void Main(string[] args)

{

if(Method1() || method2())

{

Console.WriteLine("true as far as if is concerned. Did both fire?");

}

}

static bool Method1()

{

Console.WriteLine("Method 1");

return true;

}

static bool method2()

{

Console.WriteLine("Method 2");

return true;

}

Now if you change the If to a single pipe | like below

if(Method1() | method2())

This is still an or statement but it does force both sides to be evaluated.

So I guess this does this answer your question? In other languages yes things like this do happen, however they are specifically there for a reason and they are well documented. This is in the C# spec that is you use a double pip and the first side of the or statement meets the criteria then do not bother with the other. However this commonly is a gotcha for a lot of inexperienced programmers in C# when they write the second method and are depending on it to actually do something.

Yep, I didn't have your reply in my reader before I posted, otherwise I would have just agreed with you. However does transact SQL have this defined anywhere that this is what is really going on or is this accidental, I have never seen anything on it in SQL before. Never really thought about doing it in Transact SQL before, then again maybe I have and just glanced over it but I honestly can not remember ever in Transact SQL relying on this behavior.

IIRC, I did some research on this a few years ago and did find it documented somewhere that the query optimizer can -choose- to short circuit if it sees a good reason to do so. But the key thing to remember is that unlike with C-style languages, this is a choice the optimizer can make, and it may or may not decide to actually do it. So I would not rely on this behavior. If you want something you can rely on, a CASE expression does the trick.

CASE

WHEN <whatever>

THEN

CASE

WHEN <whatever else> THEN 'TRUE'

ELSE 'FALSE'

END

ELSE 'FALSE'

END = 'TRUE'

January 18, 2008 12:20 PM

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