Short circuiting an AND

2

July 15, 2019 by Kenneth Fisher

The 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 and was hoping for comments and reactions to help me clarify things. And in the end the post got quite a bit of traction (thanks Brent (b/t) ) and I got some great responses that did in fact teach me quite a bit. I’m not going to say I understand things perfectly but certainly better than I did before. In the comments someone pointed out that AND short circuits as well, so I thought for this post I’d use what I learned and apply it to AND.

I’m going to use the same method as last time. The second condition is 7/0. That way if SQL doesn’t short circuit there will be an error. If it does, there won’t. Now, the main thing I learned is that in order for SQL to short circuit the OR it has to know for sure what the values are going to be. So either constants or a constraint.

Initial tests with VALUES

-- No error
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 > 3 AND col1 = 7/0;
-- Error
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 = 3 AND col1 = 7/0;

That was exactly what I expected. Because VALUES, in this case, is a constant it was able to short circuit. Now, out of curiosity, I’m going to replace the hard-coded values with variables, then parameters.

DECLARE @var1 int = 1
DECLARE @var2 int = 2
DECLARE @var3 int = 3
-- Error
SELECT col1
FROM (VALUES (@var1),(@var2),(@var3)) myTable (col1)
WHERE col1 > 3 AND col1 = 7/0;
GO
CREATE PROCEDURE test (@var1 int, @var2 int, @var3 int)
AS
SELECT col1
FROM (VALUES (@var1),(@var2),(@var3)) myTable (col1)
WHERE col1 > 3 AND col1 = 7/0;
GO
-- Error
EXEC test 1,2,3;

I have to admit I expected the variables to work given that they are, sort of, hard coded. But no, it has to be actually hard coded.

Tests with a real table

I’m going to use the StackOverflow database for my tests. Specifically the Users table. There are no constraints other than the primary key. The Id column is an identity column with a seed of 1 so presumably there won’t be negative values. (I checked, there is a -1 value in there so I’ll check for less than -2.)

-- Error
SELECT * FROM Users 
WHERE Id < -2 AND Id = 7/0;

Exactly as expected. There is no constraint to help out, so we got the error. Now I’ll add the constraint and try again.

ALTER TABLE Users ADD CONSTRAINT Users_NoLowIds CHECK (Id > -2);
GO
-- Error
SELECT * FROM Users 
WHERE Id < -2 AND Id = 7/0;

Interesting. I fully expected this to work but it didn’t. Maybe the constraint needs to be phrased the same way as the condition?

ALTER TABLE Users DROP CONSTRAINT Users_NoLowIds;
ALTER TABLE Users ADD CONSTRAINT Users_NoLowIds CHECK (NOT (Id < -1));
GO
-- Error
SELECT * FROM Users 
WHERE Id < -3 AND Id = 7/0;

Still an error!

Ok, so the constraint helped with an OR (I double checked) but not the AND. Very strange. Anyone feel like helping me understand this one? (I really need to go buy an Itzik Ben-Gan (b/t) book.)


So just based on the tests I did it looks like AND is a lot pickier. It will still short circuit with constants but not with a constraint. I should point out that since Id is the primary key there is already a constraint that it not allow NULLs in case you were worried that that was part of the issue.

2 thoughts on “Short circuiting an AND

  1. Steve Hall says:

    Your link that is supposed to take me to your previous article is actually pointing to this article. I did find it by going through our archives though – and both are interesting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,528 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: