It’s interesting how an OR short circuits.
24June 17, 2019 by Kenneth Fisher
I recently did a post on the case expression short circuting and received a very very interesting comment from Chad Estes. He posted, among other things, this query:
SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 4 OR col1 = 7/0
If you look, you'll see the condition col1 = 7/0 which should return a divide by zero error. But it doesn’t!
Now to make it even more interesting, if we make a very small change:
SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 3 OR col1 = 7/0
The first condition is now col1 < 3 and we do get an error! Looks like it short circuits but I’m honestly surprised that the parser doesn’t find the error before it gets that far.
Just to play around let’s try a few other things:
- Put the divide by zero condition first. (Same results)
-- No error SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 = 7/0 OR col1 < 4 -- Error SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 = 7/0 OR col1 < 3
- Add another value (Both error)
-- Error SELECT col1 FROM (VALUES (1),(2),(3),(4)) myTable (col1) WHERE col1 < 4 OR col1 = 7/0 -- Error SELECT col1 FROM (VALUES (1),(2),(3),(4)) myTable (col1) WHERE col1 < 3 OR col1 = 7/0
- Move the divide by zero into a subquery (Same as the original two)
-- No error SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 4 OR col1 IN (SELECT 7/0) -- Error SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 3 OR col1 IN (SELECT 7/0)
- Add a top 1 (Same)
-- No error SELECT TOP 1 col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 4 OR col1 = 7/0 -- Error SELECT TOP 1 col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 3 OR col1 = 7/0
- Top 0 (No errors)
-- No error SELECT TOP 0 col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 4 OR col1 = 7/0 -- No Error SELECT TOP 0 col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 < 3 OR col1 = 7/0
- Gaurenteed condition vs impossible condition (No error and Error)
-- No error SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE 1=1 OR col1 = 7/0 -- Error SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE 1=2 OR col1 = 7/0
By test here’s what I’ve learned:
- OR appears to short circut.
- (Change the order) It doesn’t appear to matter what order the OR is in. If one condition is true then no error is returned.
- (Add a row) Just confirming that if a row fails the condition that doesn’t return an error we get an error.
- (Move the error to a subquery) We can’t hide the error in something like a subquery.
- (Top 1) It doesn’t matter how many of the rows we are going to return. If any of them would have thrown the error we get the error.
- (Top 0) On the other hand, if we aren’t going to return any rows then only the struture matters.
- (Guareenteed vs impossible conditions) Really just another confirmation that if there is a true in there somewhere then we are good.
So yes, OR short circuts and even seems to be fairly intelligent about how it handles errors while it’s at it.
Edit: Based on the comments below (always read the comments in my blog) I’m adding a few more tests and another thought or two.
Edit: Edit: I’m an idiot sometimes and managed to get 0/7 in some places (not an error) and 7/0 in others (does get an error). So I’m re-doing the update.
-- Error SELECT * FROM dbo.spt_values WHERE number < 100000 OR number = 7/0; -- Error -- Server has max database_id of 32 SELECT database_id FROM sys.databases WHERE database_id < 1000 OR database_id < 7/0; -- Also Error -- Server has max database_id of 32 select name, database_id from sys.databases where database_id=database_id or database_id = 7/0;
Ok, so for system databases it doesn't work. Let's continue on with a user database.
-- Setup
-- Quicky table with 300 rows.
CREATE TABLE a (id int not null identity(1,1), col1 char(1));
GO
INSERT INTO a VALUES ('a');
GO 300
-- Error SELECT id FROM a WHERE id < 1000 or id = 7/0;
-- Maybe if we add a clustered index? CREATE CLUSTERED INDEX a_id ON a(id); -- Still an error SELECT id FROM a WHERE id < 1000 or id = 7/0;
-- All right how about a constraint? ALTER TABLE a ADD CONSTRAINT b CHECK (id < 1000); -- No error!!!! SELECT id FROM a WHERE id < 1000 or id = 7/0;
-- Add in the other column. -- Still no error!! SELECT id, col1 FROM a WHERE id < 1000 or id = 7/0;
So, in the end, yes, OR will short circuit .. sometimes. In the end it’s looking like if there is a constraint so that it can be certain that the results will 100% be true then it can skip the other test. I’m not sure with the VALUES worked but I guess it has something similar built in.
FYI: These tests were run on both SQL 2016 and 2012 with no difference.
SQL Studies 
Yikes. I’ve gotten in the habit of really checking subqueries because I found more than once where I had a completely erroneous subquery that produced no results and I was saying ‘where not in’ the subquery. But if I highlight and run the subquery by itself, it’s an error. It’s all very nerve-racking.
Nice Post @Kenneth Fisher 🙂
Of course it can be a backside that it does run and execute the “short circuited OR”.
But the upside makes up for this:
Speed wise, it is so important that it short circuits, imagine a 5000 SQL line stored procedure which always goes through everything, that would be horrible :).
The same applies to the AND as which is equally as important speed wise 🙂
Oh I agree that it’s important speed wise to short circuit as much as possible. What I found interesting was how it managed the short circuit in this case. i.e. the lack of errors.
As far as the AND goes that does bring up an interesting thought and probably a second post 😀
What version of SQL did you use to run these tests? I don’t think older versions really do the short circuit or the open circuit on AND. It might be interesting to try it will older versions dating back to SQL 2000 if you have it available anywhere.
I was testing these using 2016 & 2017. I could probably test it on 2015 (although I would expect the same results) but unfortunately I don’t have a 2000 or older to test on.
I received similar results in 2008 R2
Hi, I don’t believe this is a true statement.
(Change the order) It doesn’t appear to matter what order the OR is in. If one condition is true then no error is returned.
The first portion of the where clause in this statement is true, it returns 2 rows.
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 < 3 OR col1 = 7/0 — it errors
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 < 3 — returns two rows
Based on this, I think your statement should read
(Change the order) It doesn’t appear to matter what order the OR is in. If one condition is true "for all rows" then no error is returned.
The error happens when the 3 row is evaluated, it is not true and so it then tests the other side of the "or" predicate and finds the divide by zero.
Right. But how does SQL know which side of the OR to evaluate first? If I change it to this:
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 = 7/0 OR col1 < 3 — it errors
I get the same result even though the error part is first. So it must be evaluating both, at least at some level, and if either is true it ignores the error.
This is interesting, but it seems this behavior doesn’t occur when you query an actual table (not the VALUES construct).
I have a table A (ID int, Name varchar(10), value (float)) all nullables.
The table contains some random values where ID is between 3 and 100 (not unique).
If I run:
select id from a
where id < 1000 or id = 7/0
It gives the Divide by zero error, all the time. I even changed the order of the where conditions, and some other variations to see if I can reproduce the behavior mentioned in this post, but no success.
I'm running SQL 2014, but tried the DB Compatibility level to 100, 110 and 120.
Interesting. I tried this on a 2016 SP2:
select *
from dbo.spt_values
where number < 100000 or number = 0/7
And it worked fine.
I'll see if I can find some older versions to test on.
It worked fine as in you got the error? or you didn’t?
As in no error. Sorry, I should have been more explicit.
I just realized you have the condition wrong: number = 0/7, instead of number = 7/0
Seriously? *sigh* Sometimes I’m an idiot. Ok. I re-did my tests briefly and it looks like it’s just a matter of the constraint. Updating my post again.
Don’t be too harsh on yourself 🙂 that happens to me all the time…. it’s confusing.
Now regarding the constraint. When there’s a constraint on a table the query optimizer/planner will always look at it first to evaluate any where or join conditions (if present) . So it will “know” ahead of time that the constraint conditions are true no matter what. In this case it will know that id<1000, always. So it will never get to evaluate the condition in the WHERE clause, because it's exactly the same (id<1000). On the other hand, if you change the WHERE clause to, say, id <900 (or to anything other than id < 1000), then you will get the Divide by zero error again.
Right. That’s why I tried that. The same reason a 1=1 would also work. Which then begs the question of why the VALUES worked.
Interesting. I’m even more confused now. I’m adding another section to the post.
I think the main thing to keep in mind is that short-circuiting it’s not guaranteed. The query optimizer will choose which order to evaluate which conditions and what short circuits or appear to short-circuit today, may not do so tomorrow. At least four where conditions and join predicates. Case expressions do indeed Short circuit.
hello Kenneth Fischer
this works too:
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 > 3 and col1 = 7/0
Your Question:
Right. That’s why I tried that. The same reason a 1=1 would also work. Which then begs the question of why the VALUES worked.
Answer: Set theory and SQL-Statistics.
If in an OR-clause one condition is true for all values ( 1=1 ; 1 != 2; in your example col1 3)
then the other conditions are not evaluated, because you can’t get less then “nothing”. AND is cutting.
Constants are easy to evaluate.
SQL-Statistics knows the min, avg and max value in a column. (max = 3 in our examples)
SQL Server doesn’t like to work!! They are lazy.
I guess so. Statistics are just that, statistics not actual values. Constants on the other hand SQL can know exactly what’s going on all the time. Still pretty interesting stuff 🙂
[…] other day I did a post on how an OR short circuits. It was a somewhat unusual type of post for me in that I wasn’t really sure what was going on […]
Kenneth, I wonder why SQL Server allows you using the 7/0 expression without throwing a compilation error. As far as I know, the optimizer should do the so called constant folding. Actually, the 7/0 expression should have been evaluated even before compiling the script. Does the optimizer just silently swallow the exception and go on?
Thanks,
Dmitriy.
There are actually a fair number of places where the 7/0 would work. But in each case it’s because the parser doesn’t need to check or doesn’t check that part of the query.
Great read thank yyou