Understanding T-SQL Expression Short-Circuiting

  • spaghettidba

    SSC Guru

    Points: 105661

    CirquedeSQLeil (12/31/2010)


    Well done Gianluca

    Thank you, Jason.

  • spaghettidba

    SSC Guru

    Points: 105661

    magarity kerns (12/30/2010)


    Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?

    This is the output of my tests on other RDBMSs. I tested Oracle 11gR2, DB2/400 V5R4M0, Firebird 1.5 and PostgreSQL 8.3. I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.

    I executed the following queries to discover the behaviour of the DB engine:

    -- Does the engine short-circuit?

    SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;

    -- Does the engine detect contraddictions?

    SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;

    I didn't have the time to test other particular things, but this is a good starting point.

    Here's the results:

    DB2 Oracle Firebird PostgreSQL

    ------------------------ ---- ------ -------- ----------

    Short-circuit no yes yes yes

    Contraddiction detection no yes yes no

    Just a few words on Oracle's contraddiction detection: under some circumstances, the contraddiction is not detected at all. This doesn't surprise me, as Oracle's optimizer is full loaded of bugs (more features, more things that could go wrong). In particular, when the statement text is VERY big and contains a whole lot of literals, the optimizer goes quirks and tries to produce a plan (tries for several minutes...) even if the statement contains a contraddiction. This could mean that the contraddiction detection is applied as one of the last rules, that could make sense since it also checks for constraints contraddiction.

  • tpbury

    Grasshopper

    Points: 20

    Thanks for that - I liked the way you cohesively demonstrated the point at hand. It requires engaging one's brain, but that is why I subscribed to the group!

    Thanks again

    TAP

  • Solomon Rutzky

    SSCoach

    Points: 16234

    Gianluca Sartori (12/31/2010)


    I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.

    I executed the following queries to discover the behaviour of the DB engine:

    -- Does the engine short-circuit?

    SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;

    -- Does the engine detect contraddictions?

    SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;

    Hello Gianluca. First, thanks for your answer about how to update the state of the DB via a CLR Function.

    Now, I do have a MySQL instance to test with so I tried this on version 5.0.91 running on Linux and neither statement failed. The first produced rows and the second produced no rows as expected.

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    Excellent article Gian.. very precise and elegant...

  • spaghettidba

    SSC Guru

    Points: 105661

    Solomon Rutzky (12/31/2010)


    Hello Gianluca. First, thanks for your answer about how to update the state of the DB via a CLR Function.

    Now, I do have a MySQL instance to test with so I tried this on version 5.0.91 running on Linux and neither statement failed. The first produced rows and the second produced no rows as expected.

    Excellent! Thanks.

  • spaghettidba

    SSC Guru

    Points: 105661

    ColdCoffee (1/1/2011)


    Excellent article Gian.. very precise and elegant...

    Thank you, Mr. Coffee.

  • gabriele.acconcia

    Grasshopper

    Points: 18

    Interesting article.

    What I'll put in my bag is "never rely on short-circuiting in t-sql"!

    I would only sign out that I got an error when I tried to execute the following sample from the article:

    DECLARE @a int = 1

    DECLARE @B int = 0

    IF 1/0 = 1 AND @a = @B

    SELECT 'True'AS result

    ELSE

    SELECT 'False'AS result

    The error was "Cannot assign a default value to a local variable".

    I used sqlexpress 2005, and I changed the code this way:

    DECLARE @a int

    DECLARE @B int

    SET @a = 1

    SET @B = 0

    IF 1/0 = 1 AND @a = @B

    SELECT 'True'AS result

    ELSE

    SELECT 'False'AS result

    Best regards

  • Solomon Rutzky

    SSCoach

    Points: 16234

    gabriele.acconcia (1/4/2011)


    I would only sign out that I got an error when I tried to execute the following sample from the article:

    DECLARE @a int = 1

    DECLARE @B int = 0

    Hello. That syntax actually started in SQL Server 2008. So it makes sense that it would error in SQL Server 2005.

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Bill Kline-270970

    SSC Eights!

    Points: 915

    Great article. It should be read by anyone who codes T-SQL.

  • Mark Cowne

    One Orange Chip

    Points: 26743

    Excellent, really interesting article!

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • peter-757102

    SSCertifiable

    Points: 6877

    To make matters worse:

    Sometimes an expression in a select clause will be evaluated before filtering expressions in the where clause.

    This means that if we have an expression in the select clause that performs a division, where the right side value is queried from a table and a where clause exists to filter any zero's out, it can still go wrong due to a divide by 0!

    In such a case you need to harden your select expression by using a case construct to filter out the 0 values before doing the division. It does not matter what the result of the expression is in such a case, as the where clause will filter out the result afterwards anyway.

    I doubt many SQL statements in existence that involve such sensitive expressions (and there are quite a few) are in fact hardened. I say this foremost as nearly all of the time code works just fine without, but then it can suddenly break after years of fine operation as some unseen threshold is reached. The second reason is that it generates complicated hard to maintain code, not to mention that it also works slower.

    I also think few wil be aware of how far the freedom of execution order in SQL stretches. Personally I think it goes too far as it results in unreliable code or complicated code and thus is counter productive to the things we all want. At a minimum where clause filtering should always happen before select expressions to provide a simple model that is just as good 99.9% of the time anyway.

  • Bart Duncan

    Valued Member

    Points: 58

    This post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/[/url].

  • spaghettidba

    SSC Guru

    Points: 105661

    Bart Duncan (3/3/2011)


    This post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/[/url].

    Nice catch, Bart.

    It looks like that behaviour is determined by the fact that the code executes inside a TVF.

    Try this:

    -- Autonomous T-SQL batch: everything runs just fine

    DECLARE @input int

    SELECT @input = 0

    SELECT calculated_value =

    CASE

    WHEN @input <= 0 THEN 0

    ELSE LOG10 (@input)

    END

    -- Scalar function: runs fine

    CREATE FUNCTION dbo.test_case_short_circuit2 (@input INT)

    RETURNS int

    AS BEGIN

    RETURN (

    SELECT calculated_value =

    CASE

    WHEN @input <= 0 THEN 0

    ELSE LOG10 (@input)

    END

    )

    END

    GO

    SELECT dbo.test_case_short_circuit2 (-1);

    GO

    I think it should be reported on connect as a bug. BOL is quite clear on that point.

    Thanks for sharing

  • Paul White

    SSC Guru

    Points: 150442

    Gianluca Sartori (3/3/2011)


    It looks like that behaviour is determined by the fact that the code executes inside a TVF.

    It is constant-folding at work. If you replace the literal constant zero with a variable, the problem no longer occurs. SQL Server expands the in-line TVF at optimization time and fully evaluates the CASE with the constant values available.

    I think it should be reported on connect as a bug. BOL is quite clear on that point.

    I agree. Constant-folding should never cause an error condition (such as an overflow) at compilation time - there have been other bugs in this area fixed for the same reason. Bart, if you put this on Connect, please leave a link here so I can vote for it.

Viewing 15 posts - 31 through 45 (of 61 total)

You must be logged in to reply to this topic. Login to reply