Short circuiting an AND

The other day I did a post on how an OR short circuts. 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.

Rate

Share

Share

Rate