Did the way SQL Server 2008 evaluates a CASE WHEN condition when used in a filter?
-
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.
-
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 ?
-
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.
-
There is no problem doing what you really want.
Try this:
drop table test1create 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
-
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 🙂
-
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
-
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 🙂
-
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
-
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 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 ENDThat 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.tablesWHERE 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
-
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
-
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:
-
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 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 datetimeset @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 ?
-
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.
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply