What is the Output?

  • Miles Neale

    SSChampion

    Points: 13147

    SQLRNNR (6/24/2015)


    EZ PZ lemon squeezy. That said, it is a rather good lesson as Hugo laid out for us.

    +1

    Not all gray hairs are Dinosaurs!

  • Arno Kwetters

    Hall of Fame

    Points: 3381

    Thanks for the question and the explanation of Nakul and Hugo.

    I have learned again something.

  • Nakul Vachhrajani

    SSChampion

    Points: 10217

    Hugo Kornelis (6/24/2015)


    We cannot find a reference, but apparently the divide by zero doesn't cause an issue in the EXISTS() clause.

    The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.

    There is no very explicit reference, but I think that this link is valuable: https://technet.microsoft.com/en-us/library/ms188336.aspx. It mentions that EXISTS returns true if the subquery contains any rows. From this, one can conclude that the content of those rows is irrelevant. This is somewhat reinforced by example A.

    Nakul Vachhrajani (6/24/2015)


    However, here's a twist to the tale. The following will *not* return an error:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)

    select 1

    else

    select 2

    This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.

    Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.

    It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.

    Thank-you very much, Hugo for the additional explanation.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I've run into this before so was not surprised.

  • amit_adarsh

    Ten Centuries

    Points: 1273

    Cool findings ....

  • radek.celuch

    Default port

    Points: 1430

    g.britton (6/24/2015)


    Nakul Vachhrajani (6/24/2015)


    I guess the explanation is quite simple:

    Divide by 0 is not causing a problem in the SELECT clause because of the presence of EXISTS. You can have pretty much anything in the SELECT and the result will be the same because all EXISTS does is evaluate for the existance of a record in the result set. Whether the record exists or not (the FROM and WHERE clauses) does not have any bearing on how it is represented (which is the job of the SELECT).

    The divide by 0 will cause issues if it is in the WHERE clause, because that needs to be evaluated fully. Here's the snippet:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND (1/0)=1)

    select 1

    else

    select 2

    However, here's a twist to the tale. The following will *not* return an error:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)

    select 1

    else

    select 2

    This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.

    Pretty sure you can *not* rely on that short-circuiting. It may work 9 times out of 10 then fail.

    see here:

    This is quite interesting issue. I was curious if short-circuiting is also the case in UNION (ALL)/EXCEPT/INTERSECT operators.

    Below is my testing code. I am pretty surprised by queries 7, 20 and 10. It seems that optimizer evaluates SELECT clause first. Even before WHERE clause. Query 21 seems to act "normally" though - well, I think it's due to the fact that EXCEPT is an ordered operator.

    Another fancy things goes with queries 13 and 15 - 19. Although SELECT clause throws error, "IF EXISTS(SELECT..)" works and returns logical value of TRUE!

    set nocount on

    print '

    SIMPLE SELECT

    '

    print '1 throws 0 division error (no results)'

    select 1 where 1/0 = 1 and 1 = 1

    print @@rowcount

    print '2 works (no results)'

    select 1 where 1/0 = 1 and 1 = 0 and 1 = null

    print @@rowcount

    print '3 works (no results)'

    select 1 where 1/0 = 1 and 1 = null

    print @@rowcount

    print '4 works'

    select 1 where 1/0 = 1 or 1 = 1

    print @@rowcount

    print '

    INTERSECT

    '

    print '5 works (no results)'

    select 1

    intersect

    select 1 where 1/0 = 1 and 1 = null

    print @@rowcount

    print '6 throws 0 division error (no results)'

    select 1

    intersect

    select 1 where 1/0 = 1

    print @@rowcount

    print '7 works (no results) - quite interesting'

    select 1

    intersect

    select 0 where 1/0 = 1

    print @@rowcount

    print '

    EXCEPT

    '

    print '8 works'

    select 1

    except

    select 1 where 1/0 = 1 and 1 = null

    print @@rowcount

    print '9 throws 0 division error (no results)'

    select 1

    except

    select 1 where 1/0 = 1

    print @@rowcount

    print '10 works - quite interesting'

    select 1

    except

    select 0 where 1/0 = 1

    print @@rowcount

    print '

    UNION

    '

    print '11 works'

    select 1

    union

    select 1 where 1/0 = 1 and 1 = null

    print @@rowcount

    print '12 throws 0 division error, "if exists" works and returns TRUE'

    select 1

    union

    select 1 where 1/0 = 1

    --if exists (

    --select 1

    --union

    --select 1 where 1/0 = 1)

    -- select 1 else select 2

    print @@rowcount

    print '13 throws 0 division error, there is a result set but @@rowcount = 0, "if exists" works and returns TRUE'

    select 1

    union

    select 0 where 1/0 = 1

    --if exists (

    --select 1

    --union

    --select 0 where 1/0 = 1)

    -- select 1 else select 2

    print @@rowcount

    print '

    UNION ALL

    '

    print '14 works'

    select 1

    union all

    select 1 where 1/0 = 1 and 1 = null

    print @@rowcount

    print '15 throws 0 division error, there is a result set but @@rowcount = 0, "if exists" works and retunrs TRUE'

    select 1

    union all

    select 1 where 1/0 = 1

    --if exists (

    --select 1

    --union all

    --select 1 where 1/0 = 1

    --)select 1 else select 2

    print @@rowcount

    print '16 throws 0 division error, there is a result set but @@rowcount = 0, "if exists" works and retunrs TRUE'

    select 1

    union all

    select 0 where 1/0 = 1

    --if exists (

    --select 1

    --union all

    --select 0 where 1/0 = 1

    --)select 1 else select 2

    print @@rowcount

    print '

    REVERSED UNION

    '

    print '17 as reversed 13 - now there is no result set, @@rowcount = 0, "if exists" works and returns TRUE'

    select 1 where 1/0 = 1

    union

    select 0

    --if exists (select 1 where 1/0 = 1

    --union

    --select 0)

    -- select 1 else select 2

    print @@rowcount

    print '18 as reversed 15 - now there is no result set, @@rowcount = 0, if exists works and returns TRUE'

    select 1 where 1/0 = 1

    union all

    select 1

    --if exists (

    --select 1 where 1/0 = 1

    --union all

    --select 1)

    -- select 1 else select 2

    print @@rowcount

    print '19 as reversed 16 - now there is no result set, @@rowcount = 0, if exists works and returns TRUE'

    select 1 where 1/0 = 1

    union all

    select 0

    --if exists (

    --select 1 where 1/0 = 1

    --union all

    --select 0)

    -- select 1 else select 2

    print @@rowcount

    print '

    ADDITION to 7 and 10 queries

    '

    print '20 works (no results) - quite interesting'

    select 1 where 1/0 = 1

    intersect

    select 0

    print @@rowcount

    print '21 throws 0 division error (no results)'

    select 1 where 1/0 = 1

    except

    select 0

    print @@rowcount

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    radek.celuch (6/27/2015)


    This is quite interesting issue. I was curious if short-circuiting is also the case in UNION (ALL)/EXCEPT/INTERSECT operators.

    Below is my testing code.

    As a "let's see what this does" experiment, it is interesting. But please do not make the mistake of drawing any conclusions from this. The fact that you observe some behaviour when running some code under some circumstances does not guarantee anything for other code - no matter how similar the code and the circumstances may be. The optimizer is a complex piece of logic, and even though I am pretty sure that there is no randomization in there, the number of factors that are taken into account and are hard or impossible to control can sometimes make it seem as if it's throwing dice. In other words, consider the behaviour of the optimizer as always within the bounds of documentation, but otherwise "sort of" unpredictable.

    For order of evaluation, the only guarantee we have is that in a CASE expression, a THEN clause will only be evaluated if it's WHEN clause is true, and the ELSE is only evaluated when no WHEN is true. And even this rule has one known exception that is not considered a bug: for an expression like "CASE WHEN SUM(a-b) > 10 THEN AVG (y/z) END", first aggregating to get the SUM of a+b, then going back to the original rows to do the y/z division and get the average aggregation would be very slow, so here the optimizer will in fact decide to do both the a+b and the y/z computation before aggregation.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • radek.celuch

    Default port

    Points: 1430

    @hugo

    Thanks for the valuable comment. However, I didn't meant nor said that the behavior I observed is standard or repeatable. My goal was to just show how awkward and unexpected results you may witness when division-by-zero occurs in UNION/EXCEPT/INTERSECT. Sometimes optimizer seems to know that you try to add/subtract empty set and skips an operation.

    I totally agree that we should trust the documentation. However - as you showed in CASE case - not with blindly.

Viewing 8 posts - 16 through 23 (of 23 total)

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