Post date: Apr 21, 2014 11:57:42 PM
While doing these problems with integer tables and divisors, I discovered a very strange SQL Server bug. It requires a very specific situation to generate. You need to have a CTE or subquery containing at least one join, where it is possible for a zero to appear in the results. The query must be written so that zero never appears in the results, but could if the query were slightly different. You must be dividing (or modding) by the nonzero value in the WHERE clause of query referencing the subquery. If you do all this, you will see a divide by zero error even when the query never divides by zero.
Consider the following query snippet:
WITH Bin(i) AS
(
SELECT 0 UNION SELECT 1
)
SELECT t.i + o.i
FROM Bin o, Bin t
WHERE (t.i + o.i) > 0
This returns three rows, 1, 1, and 2. That is all
Now run the following innocent query:
WITH Bin(i) AS
(
SELECT 0 UNION SELECT 1
),
Test(i) AS
(
SELECT t.i + o.i
FROM Bin o, Bin t
WHERE (t.i + o.i) > 0
)
SELECT i
FROM Test
WHERE 10 / i > 1
Your result:
Divide by zero error encountered.
Where is the zero being divided by? It's certainly not in my query, which only divides by 1, 1, and 2. My best guess is that it is in the query optimizer. For performance reasons, SQL Server must be running the statement even on rows that never appear and blowing up on the ghost records.
The following query, which returns identical results, does not blow up:
WITH Bin(i) AS
(
SELECT 0 UNION SELECT 1
),
Test(i) AS
(
SELECT NULLIF(t.i + o.i,0)
FROM Bin o, Bin t
WHERE (t.i + o.i) > 0
)
SELECT i
FROM Test
WHERE 10 / i > 1
I wasn't able to find any other references to the bug on Google, so you read it here first. Maybe. I hope I made your DBA world a little bit more weird.