I am getting a very odd error. Consider the table below:
CREATE TABLE #MyTable (
Key1 INT , Key2 INT ,
x SMALLINT , y INT , z INT ,
a FLOAT , b FLOAT , c SMALLINT , s FLOAT
)
-- insert many records
CREATE UNIQUE CLUSTERED INDEX CI ON #MyTable ( Key1 , Key2 )
For some reason, the following update statement tries to divide by zero. This can only happen if c=0
or c=1
. The WHERE
clause clearly specifies c>1
.
-- this fails with divide-by-zero error
UPDATE #MyTable
SET s = CASE
WHEN a - SQUARE ( b ) / c <= 0 THEN 0
ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
END
WHERE x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )
The problem is completely eliminated if I redundantly check for c<=1
in my CASE
expression:
-- this completes without an error
UPDATE #MyTable
SET s = CASE
WHEN ( c <= 1 ) OR ( a - SQUARE ( b ) / c <= 0 ) THEN 0
ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
END
WHERE x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )
Has anyone encountered this before? Why would SQL Server touch the records with c>1
?
The problem is also avoided if there is no index on the table (the index is useful in steps later in the procedure). Why would the existence of an index cause a condition in the WHERE
clause to be ignored?