June 24, 2015 at 10:22 am
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!
June 24, 2015 at 11:55 pm
Thanks for the question and the explanation of Nakul and Hugo.
I have learned again something.
June 25, 2015 at 12:58 am
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
Follow me on
Twitter: @sqltwins
June 25, 2015 at 7:08 am
I've run into this before so was not surprised.
June 26, 2015 at 4:47 pm
Cool findings ....
June 27, 2015 at 4:33 pm
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.
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
June 28, 2015 at 1:04 am
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.
June 28, 2015 at 12:21 pm
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 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply