Did the way SQL Server 2008 evaluates a CASE WHEN condition when used in a filter?

Question

Did the way SQL Server 2008 evaluates a CASE WHEN condition when used in a filter?

Ionut Hrubaru

SSC Veteran

Points: 256
More actions
April 21, 2010 at 1:28 am

#219497

I am looking at a weird issue since morning. Below query works fine in SQL Server 2005 but throws a datetime overflow exception in 2008:
select 1 from sys.tables where
((
CASE WHEN
(CASE WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) as NUMERIC(38,10)) + ISNULL(CAST(1 AS NUMERIC(38, 28)),0) >= 2958464
THEN '9999/12/31'
WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) AS NUMERIC(38, 10)) + ISNULL(CAST(1 AS NUMERIC(38,28)),0) <= 0
THEN '1900/01/01'
ELSE DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST('9999-12-31 00:00:00' AS DATETIME))
END) IS NULL
THEN 1
ELSE 0
END =1 ))
The weirdness comes from the fact that if I extract the case when statement and execute it, it works just fine:
SELECT
CASE WHEN
(CASE WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) as NUMERIC(38,10)) + ISNULL(CAST(1 AS NUMERIC(38, 28)),0) >= 2958464
THEN '9999/12/31'
WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) AS NUMERIC(38, 10)) + ISNULL(CAST(1 AS NUMERIC(38,28)),0) <= 0
THEN '1900/01/01'
ELSE DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST('9999-12-31 00:00:00' AS DATETIME))
END) IS NULL
THEN 1
ELSE 0
END
My version of the SQL Server 2008 is:
SQL Server 2008 R2 (CTP) - 10.50.1352.12 (X64)
I am really stuck with this, so any help would be appreciated.

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

I suspect that what is happening in the original select is that sql server is trying to compile the case expression, and during compilation it encounters the overflow while trying to calculate the expression.

When you just select the singel case expression it will be evaluated immediately without compilation and then the offending part of the expression does not have to be evaluated.

Why is this a problem ?

Ionut Hrubaru SSC Veteran Points: 256 More actions · Answer 2

Stefan,

Thanks for the answer.

It is a problem, because in Sql Server 2005 it works just fine.

Let's say that I have a function which is used to add a number of days to a date parameter. I want this function to:

-return 9999/12/31 in case we try to add a number of days which will lead to a date value greater than what sql server can hold

-return 1900/01/01 in case we try to add a number of days which will lead to a date value smaller than what 1900/01/01

-add using DATEADD that number of days if first two cases are not encountered.

Since I do not want to use a UDF (performance reason), I am building the query from java using the CASE WHEN statement (since it needs to be a SELECT statement, IFs cannot be used).

At some point this function can be used in a filter records operation. It all worked fine until we upgraded to SQL 2008. So this is why I asked about the change in behavior compared with 2005.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

There is no problem doing what you really want.

Try this:

drop table test1

create table test1(d datetime, n int)

insert into test1 values ('99991230 23:00', 2), ('17530101', -1), ('20100101',1)

select

CASE

WHEN CAST(d as NUMERIC(38,10)) + n >= 2958464 THEN '9999/12/31'

WHEN CAST(d AS NUMERIC(38,10)) + n <= 0 THEN '1900/01/01'

ELSE DATEADD(dd, n, d)

END

from test1

The problem you are seeing is only related to constant expressions, but in real life both your date and number of days are probably never constants - if they are, your java app can calculate the correct value itself.

/SG

Ionut Hrubaru SSC Veteran Points: 256 More actions · Answer 4

I agree with you that the problem occurs only when constants are used. It doesn't occur when variables or column names are used.

Changing the way constants are used by the java app (to calculate the result directly in java) implies changes in the my java expression builder. I wish I wouldn't do that right now

Do you have any clue if this change is documented anywhere? (sql 2005 vs sql 2008). I just wish it was a bug 🙂

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

Sorry, I dont know if this is documented anywhere, but I doubt it.

This seems to be a very special edge case to me.

They probably have not changed the behaviour on purpose - it is probably a side effect of a performance optimization or something like that.

/SG

Ionut Hrubaru SSC Veteran Points: 256 More actions · Answer 6

And also since the issue occurs only in case on constants we can trick the engine not to throw the error, like this:

select 1 from sys.tables where

((

CASE WHEN

(CASE WHEN CAST(CAST((select '9999-12-31 00:00:00' AS MY_COL) AS DATETIME) as NUMERIC(38,10)) + ISNULL(CAST(1 AS NUMERIC(38, 28)),0) >= 2958464

THEN '9999/12/31'

WHEN CAST(CAST((select '9999-12-31 00:00:00' AS MY_COL) AS DATETIME) AS NUMERIC(38, 10)) + ISNULL(CAST(1 AS NUMERIC(38,28)),0) <= 0

THEN '1900/01/01'

ELSE DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST((select '9999-12-31 00:00:00' AS MY_COL) AS DATETIME))

END) IS NULL

THEN 1

ELSE 0

END =1 ))

Issue closed I guess 🙂

Paul White SSC Guru Points: 150457 More actions · Answer 7

Ionut Hrubaru (4/21/2010)
I just wish it was a bug 🙂

It is a bug, just not in SQL Server 😛

The optimiser in SQL Server can use a number of transformations to improve performance or find better plans. For example, it is free to move expressions around and pre-compute constant expressions ('constant folding'). In this case, it is the constant folding which exposes the bug in your code.

It has never been safe to rely on a particular order or timing of scalar evaluations, and just because it happened to work in one particular version does not imply any sort of guarantee. What has happened here is that you have relied on an implementation detail in SQL Server 2005, and come unstuck when it changed in SQL Server 2008.

Rather than code an 'expression builder' in a procedural language and submit the resulting ad-hoc SQL, consider wrapping this sort of routine in an in-line table-valued function. These behave exactly as a parameterized view would - they have no overhead (unlike scalar functions, which are horribly expensive) - and are expanded into the overall query plan and fully optimized just like a view.

Paul

Some other references which might help you appreciate how all this works:

http://msdn.microsoft.com/en-us/library/ms175933.aspx

http://blogs.msdn.com/craigfr/archive/2008/04/28/conversion-and-arithmetic-errors.aspx

http://blogs.msdn.com/craigfr/archive/2006/08/23/715306.aspx

http://blogs.msdn.com/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx

http://blogs.msdn.com/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx

http://en.wikipedia.org/wiki/Constant_folding

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 8

In-line function example:

Creation:

USE tempdb;

GO

CREATE FUNCTION dbo.SafeDateAdd

(

@BaseDate DATETIME,

@DaysToAdd INTEGER

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT

datetime_result =

CASE

WHEN DATEDIFF(DAY, @BaseDate, CONVERT(DATETIME, '9999-12-31T00:00:00.000', 126)) < @DaysToAdd

THEN CONVERT(DATETIME, '9999-12-31T00:00:00.000', 126)

WHEN DATEDIFF(DAY, @BaseDate, CONVERT(DATETIME, '1900-01-01T00:00:00.000', 126)) > @DaysToAdd

THEN CONVERT(DATETIME, '1900-01-01T00:00:00.000', 126)

ELSE DATEADD(DAY, @DaysToAdd, @BaseDate)

END;

GO

Test:

DECLARE @Example

TABLE (

date_time DATETIME NOT NULL,

days_to_add INTEGER NOT NULL

);

INSERT @Example

(date_time, days_to_add)

VALUES ('9999-12-30T00:00:00.000', 2),

('1753-01-01T00:00:00.000', -300000000),

('1900-01-01T00:00:00.000', -1),

('1900-01-02T00:00:00.000', -5),

('2010-01-01T00:00:00.000', 113);

SELECT E.date_time, E.days_to_add, iTVF.datetime_result

FROM @Example E

CROSS

APPLY dbo.SafeDateAdd(E.date_time, E.days_to_add) iTVF;

GO

Function properties:

SELECT deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SafeDateAdd'), 'IsDeterministic'),

inline = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SafeDateAdd'), 'IsInlineFunction'),

precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SafeDateAdd'), 'IsPrecise'),

verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SafeDateAdd'), 'IsSystemVerified'),

schemabound= OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SafeDateAdd'), 'IsSchemaBound');

GO

DROP FUNCTION dbo.SafeDateAdd;

Query plan:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Stefan_G SSCertifiable Points: 6609 More actions · Answer 9

Paul White NZ (4/21/2010)
It is a bug, just not in SQL Server 😛
The optimiser in SQL Server can use a number of transformations to improve performance or find better plans. For example, it is free to move expressions around and pre-compute constant expressions ('constant folding'). In this case, it is the constant folding which exposes the bug in your code.
It has never been safe to rely on a particular order or timing of scalar evaluations, and just because it happened to work in one particular version does not imply any sort of guarantee. What has happened here is that you have relied on an implementation detail in SQL Server 2005, and come unstuck when it changed in SQL Server 2008.

Hmm. As a matter of fact, in this case, there is really no reason why the constant folding should produce an error. The value of the constant expression is well-defined.

It has always been ok to rely on CASE to avoid evaluating a potentially dangerous expression, for example:

SELECT CASE WHEN @d=0 THEN NULL ELSE 1/d END

That expression will return NULL instead of throwing a divide-by-zero error when @d happens to be 0

The above expression actually works fine even with constants:

SELECT * FROM sys.tables

WHERE CASE WHEN 0=0 THEN NULL ELSE 1/0 END IS NULL

That constant expression is very similar in spirit to what the OP was doing and it does NOT throw an error.

So, I actually consider this behavior a minor bug in SQL 2008. It is a minor low-priority bug just because it only shows itself under very special circumstances, but I definitely consider it a bug.

On the other hand, I dont work for Microsoft so who cares what I think. 😛

/SG

Stefan_G SSCertifiable Points: 6609 More actions · Answer 10

Paul White NZ (4/21/2010)
In-line function example:

Nice function, the only problem is that this throws an error:

select * from dbo.SafeDateAdd('99991231',1)

Msg 517, Level 16, State 1, Line 1

Adding a value to a 'datetime' column caused an overflow.

:w00t:

/SG

Paul White SSC Guru Points: 150457 More actions · Answer 11

Stefan_G (4/21/2010)
Hmm. As a matter of fact, in this case, there is really no reason why the constant folding should produce an error. The value of the constant expression is well-defined.

:unsure: So, what is the constant-folded value of the ELSE part:

DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST('9999-12-31 00:00:00' AS DATETIME))

It has always been ok to rely on CASE to avoid evaluating a potentially dangerous expression, for example: SELECT CASE WHEN @d=0 THEN NULL ELSE 1/d END

It has. So far. You have a reference that documents this as being ok? 😉

That constant expression is very similar in spirit to what the OP was doing and it does NOT throw an error.

1/0 is not foldable since the result depends on SET settings.

There's a list of things the optimizer currently evaluates (or folds) at compile time in Books Online.

So, I actually consider this behavior a minor bug in SQL 2008. It is a minor low-priority bug just because it only shows itself under very special circumstances, but I definitely consider it a bug.

I disagree, energetically :hehe:

More details:

Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 12

Stefan_G (4/21/2010)
Nice function, the only problem is that this throws an error:
select * from dbo.SafeDateAdd('99991231',1)

Works fine here. :w00t: :w00t: :w00t:

Have you tried passing it a date time rather than a string?

Maybe the implicit conversion is to blame?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Stefan_G SSCertifiable Points: 6609 More actions · Answer 13

Paul White NZ (4/21/2010)
Stefan_G (4/21/2010)
Nice function, the only problem is that this throws an error:
select * from dbo.SafeDateAdd('99991231',1)
Works fine here. :w00t: :w00t: :w00t:
Have you tried passing it a date time rather than a string?
Maybe the implicit conversion is to blame?

select * from dbo.SafeDateAdd(cast('99991231' as datetime),1)

Msg 517, Level 16, State 1, Line 1

Adding a value to a 'datetime' column caused an overflow.

:w00t:

declare @d datetime

set @d=cast('99991231' as datetime)

select * from dbo.SafeDateAdd(@d,1)

9999-12-31 00:00:00.000

Do you still think this is not a bug ?

Paul White SSC Guru Points: 150457 More actions · Answer 14

Stefan_G (4/21/2010)
select * from dbo.SafeDateAdd(cast('99991231' as datetime),1)
declare @d datetime
set @d=cast('99991231' as datetime)
select * from dbo.SafeDateAdd(@d,1)

Both run without error for me on both 2005 and 2008.

Versions tested:

SQL Server 2005: 9.0.4285

SQL Server 2008: 10.0.2757

Do you still think this is not a bug?

I do, and I hope you can see why - none of your code samples produce an error for me.

Stefan, I have done my best to explain this to you. I have provided references to support my views, and also given what I think is a better solution (the in-line view). If you seriously feel you have found a bug, file it on Connect.

The wider point is that these behaviours are not guaranteed and could change at any time. No-one should infer a guarantee from undocumented apparently-reproducible behaviour. It's just common sense.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi