UPDATE statement processing records that should be eliminated by WHERE clause - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

UPDATE statement processing records that should be eliminated by WHERE clause

sql-server sql-server-2008 add tag

James (imported from SE)

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?

Top Answer

aaron bertrand (imported from SE)

You should not make any assumptions about how SQL Server ***will*** process your query, except this: you should always assume that SQL Server ***can*** process your query in a way that is different from how it is explicitly written on the screen. And also this behavior can change based on any of the factors that can influence whether a new plan will be used for the next execution of even the same query, so if you apply a hint or change the query in any way or add or remove an index and the error goes away, don't assume the error won't come back tomorrow.

In this case, SQL Server is processing a calculation before it is eliminating rows from the `WHERE` clause. The way you avoid this is, like you said, ensuring that those rows are also filtered out inside the `CASE` expression (not statement).

A more common but similar approach is this kind of thing:

    SELECT DATEPART(MONTH, varchar_column)
    FROM dbo.some_table
    WHERE ISDATE(varchar_column) = 1;

In many scenarios you will get an error message because SQL Server tried to apply the date functions against some values in the column that didn't turn out to be dates (and this attempt occurred prior to filtering). The workaround is tedious - use the `CASE` expression - but necessary unless you have some other way to verify the worthiness of the column (e.g. a computed column or fixing the data type in the first place). Just keep in mind that even this [can fail to "short circuit" in some scenarios](https://dba.stackexchange.com/questions/12941/does-sql-server-read-all-of-a-coalesce-function-even-if-the-first-argument-is-no/12945#12945).

    SELECT CASE WHEN ISDATE(varchar_column) = 1 
      THEN DATEPART(MONTH, varchar_column) END
    FROM dbo.some_table
    WHERE ISDATE(varchar_column) = 1;

This is explained more thoroughly in the following feedback item by Erland Sommarskog:

- [SQL Server should not raise illogical errors](https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors)

1 Answer