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

Ah, I tested only on 2005.

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

dobar dan

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

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

dovijenya

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.

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

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

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'