Asked  7 Months ago    Answers:  5   Viewed   49 times

Are boolean expressions in SQL WHERE clauses short-circuit evaluated ?

For example:

SELECT * 
FROM Table t 
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key) 

If @key IS NULL evaluates to true, is @key IS NOT NULL AND @key = t.Key evaluated?

If No, why not?

If Yes, is it guaranteed? Is it part of ANSI SQL or is it database specific?

If database specific, SqlServer? Oracle? MySQL?

 Answers

51

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

Tuesday, June 1, 2021
 
Lorav
answered 7 Months ago
90

Yes, this is guaranteed.

C# Language Specification - 7.11 Conditional logical operators:

The && and || operators are called the conditional logical operators. They are also called the "short-circuiting" logical operators.

Therefore they will support logical short-circuiting by definition - you can rely on this behavior.

Now it is important to make a distinction between a conditional operator and a logical operator:

  • Only conditional operators support short-circuiting, logical operators do not.
  • C#'s logical operators look just like their conditional counterparts but with one less character so a logical OR is | and a logical AND is &.
  • Logical operators can be overloaded but conditional operators cannot (this is a bit of an technicality as conditional operator evaluation does involve overload resolution and this overload resolution can resolve to a custom overload of the type's logical operator, so you can work around this limitation to a certain extent).
Friday, July 30, 2021
 
akosch
answered 5 Months ago
84

Is that an actual or estimated plan? Sql Server builds plans based on what it expects to do based on collected statistics, and that doesn't always jibe with what specific conditions you send it for one instance of a query run.

Friday, July 30, 2021
 
Xatoo
answered 5 Months ago
18

Basically, I was using sprintf() to see what kind of bounding coordinates where being computed, and since I couldn't run the query on any place other than PHP (because of the UDF) I was generating another query with prepared statements. The problem was, I wasn't generating the last bound parameter (the kilometers in the distance <= ? clause) and I was fooled by my sprintf() version.

Guess I shouldn't try to code when I'm sleepy. I'm truly sorry for your wasted time, and thank you all!


Just for the sake of completeness, the following returns (correctly!) 873 records, in ~ 0.04 seconds:

SELECT "code",
    geo(38.73311, -9.138707, "geo_latitude", "geo_longitude") AS "distance"
    FROM "pt_postal" WHERE 1 = 1
        AND "geo_latitude" BETWEEN 38.7241268076 AND 38.7420931924
        AND "geo_longitude" BETWEEN -9.15022289523 AND -9.12719110477
        AND "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;
Sunday, August 29, 2021
 
Angolao
answered 4 Months ago
98

Based on answers and comments by Daniel and Søren Debois, I've come up with the following:

let tryMap f xs =
    let rec loop ys = function
        | []    -> maybe { return List.rev ys }
        | x::xs -> maybe { let! y = f x in return! loop (y::ys) xs }
    loop [] xs

I'm not quite sure if it's tail-recursive or not though, since I'm not a total expert in the inner workings of computation- (or in this case specifically: maybe-) expressions.

What do you guys think of this? :-)

Saturday, October 16, 2021
 
grantespo
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share