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

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 And sqrt(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?

Now change the first query to this

If 1/0 = 10 or 1/1 = 0

Select 'True' As Query1

Else

Select 'False' As Query1

And?  Does this also happen in other languages like this?

 

There is some more....:-) Switch the if statement around.

If 1/0 = 10 And sqrt(1)=0

Select 'True'

Else

Select 'False'

-- Same query with the order changed.

If sqrt(1)=0 And 1/0 = 10

Select 'True'

Else

Select 'False'

Now let's make the AND an OR

If sqrt(1)=1 or 1/0 = 10

Select 'True'

Else

Select 'False'

 

If 1/0 = 10 or sqrt(1)=1

Select 'True'

Else

Select 'False'

 

Very interesting indeed. 

 

Published Thursday, January 17, 2008 4:45 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

 

Peter W. DeBetta said:

The second one (If 1/0 = 10 And sqrt(1)=0) actually works for me.

It seems that for a simple AND condition, if either expression is FALSE, the result is FALSE, regardless of whether or not one of but not both of the expressions raises an exception.

And for a simple OR condition, if either expression is TRUE, the result is TRUE, regardless of whether or not one of but not both of the expressions raises an exception.

If this exception is considered the same as UNKNOWN, then this is correct (true to truth logic, at least). For example, an AND truth table with TRUE, FALSE, and UNKNOWN.

^  T  F  U

T  T  F  U

F  F  F  F

U  U  F  U

January 17, 2008 5:03 PM
 

Denis Gobo said:

Peter,

If 1/0 = 10 And sqrt(1)=0

Select 'True' As Query2

Else

Select 'False' As Query2

this works on SQL server 2005 but bombs out on 2000

this one will work on both 2000 and 2005

If sqrt(1)=0 and 1/0 = 10

Select 'True' As Query2

Else

Select 'False' As Query2

January 17, 2008 5:07 PM
 

Peter W. DeBetta said:

Ah, I tested only on 2005.

January 17, 2008 5:44 PM
 

Adam Machanic said:

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

January 17, 2008 7:46 PM
 

flengi pig roast yum yum said:

dobar dan

is this really representative of the stuff you have to solve?

come on, how about something more 'real world'?

dovijenya

January 18, 2008 8:40 AM
 

Jeff said:

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.

January 18, 2008 8:59 AM
 

Adam Machanic said:

Jeff: Exactly; that's called "short circuiting".

Reference: http://en.wikipedia.org/wiki/Short-circuit_evaluation

January 18, 2008 9:06 AM
 

Jeff said:

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.

January 18, 2008 12:06 PM
 

Denis Gobo said:

The thing that I found interesting is that this blows up on SQL Server 2000

If 1/0 = 10 And sqrt(1)=0

Select 'True' As Query2

Else

Select 'False' As Query2

but this does not

If 1=0 and 1/0 = 10

Select 'True' As Query2

Else

Select 'False' As Query2

Both of these run without a problem on 2005 and return false

>>but I honestly can not remember ever in Transact SQL relying on this behavior.

Yes nobody should ever use this since tomorrow we might be able to divide by zero. remember this?  http://www.bbc.co.uk/berkshire/content/articles/2006/12/06/divide_zero_feature.shtml

January 18, 2008 12:14 PM
 

Adam Machanic said:

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

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