It’s interesting how an OR short circuits.

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.