April 21, 2010 at 8:41 am
Paul White NZ (4/21/2010)
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
Now we are getting somewhere!
You have a newer version than I. My version is:
10.0.1600.22 (X64)
So, apparently Microsoft agrees with me. This was a bug in the RTM version that has apparently been fixed in build 2757.
So, Paul, have you tried running the code posted by the OP ?
What does it return on your system ?
Maybe he can simply upgrade to the latest version to fix his problem.
April 21, 2010 at 9:22 am
Paul White NZ (4/21/2010)
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.
But select * from dbo.SafeDateAdd('99991231',1) produces an error for me.
I claim that this is a bug.
You say that "No, this is not a bug because there is no error when I run it."
Your own SafeDateAdd also relies on the so called "undocumented behavior" of CASE.
I think your argument makes no sense. You will probably see my point after a night's sleep 😉
/SG
April 21, 2010 at 10:07 am
I have to agree with Paul. Yes, if I run his function the way you are calling it, I get an error. However, look at the following code and I think you may agree it has more to do with HOW it is getting called.
DECLARE @Example
TABLE (
date_time DATETIME NOT NULL,
days_to_add INTEGER NOT NULL
);
INSERT @Example
(date_time, days_to_add)
select '9999-12-31T00:00:00.000', 1 union all
select '1753-01-01T00:00:00.000', -300000000 union all
select '1900-01-01T00:00:00.000', -1 union all
select '1900-01-02T00:00:00.000', -5 union all
select '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
declare @MyDate as datetime = '99991231';
select @MyDate, * from dbo.SafeDateAdd(@MyDate,1) -- works
GO
declare @MyDate as varchar(8) = '99991231';
select @MyDate, * from dbo.SafeDateAdd(@MyDate,1) -- works
GO
select @MyDate, * from dbo.SafeDateAdd('99991231',1) -- fails
GO
April 21, 2010 at 12:38 pm
Paul White NZ (4/21/2010)
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
I have now actually updated my SQL 2008 installation to version 10.0.2757
select @@version
Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64) Jan 8 2010 19:55:08 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
The behavior is still exactly the same.
select * from dbo.SafeDateAdd('99991231',1)
still generates an error.
Paul, are you really 100% sure that you can run this on your 2008 system without error ?
About the question if this is a bug or not, I repeat that I still think this is a minor bug, but as I said my opinion does not really matter since I dont work for Microsoft.
Paul, and Lynn: I understand that you dont think this is a bug. I still find it a bit surprising that you have that view, but you are of couse free to think what you like.
Let me try to explain why I think this is a bug:
Microsoft usually goes to extreme lengths to ensure backwards compatibility when they release new versions of their software. All existing correct SQL code should continue to run on the newest version unless there is a very good reason for a change in behavior. Most of the time deliberate breaking changes are only introduced to solve security problems or to introduce major new functionality.
In this case I personally believe that this change in behavior from SQL 2005 to SQL 2008 was unintended.
A breaking change is either by design, or unitended. If it is unintended it is a bug. 😎
/SG
April 21, 2010 at 12:50 pm
Stefan_G (4/21/2010)
Paul White NZ (4/21/2010)
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
I have now actually updated my SQL 2008 installation to version 10.0.2757
The behavior is still exactly the same.
select * from dbo.SafeDateAdd('99991231',1)still generates an error.
Paul, are you really 100% sure that you can run this on your 2008 system without error ?
About the question if this is a bug or not, I repeat that I still think this is a minor bug, but as I said my opinion does not really matter since I dont work for Microsoft.
Paul, and Lynn: I understand that you dont think this is a bug. I still find it a bit surprising that you have that view, but you are of couse free to think what you like.
Let me try to explain why I think this is a bug:
Microsoft usually goes to extreme lengths to ensure backwards compatibility when they release new versions of their software. All existing correct SQL code should continue to run on the newest version unless there is a very good reason for a change in behavior. Most of the time deliberate breaking changes are only introduced to solve security problems or to introduce major new functionality.
In this case I personally believe that this change in behavior from SQL 2005 to SQL 2008 was unintended.
A breaking change is either by design, or unitended. If it is unintended it is a bug. 😎
/SG
I think you are missing the point. If it is a bug, it isn't a bug in how CASE works, it's a bug in how the iTVF is handling the implicit conversion of the constant string to datetime during the invocation of the iTVF. If you replace the '99991231' with a varchar variable with the SAME value the iTVF works.
April 21, 2010 at 1:08 pm
Lynn Pettis (4/21/2010)
I think you are missing the point. If it is a bug, it isn't a bug in how CASE works, it's a bug in how the iTVF is handling the implicit conversion of the constant string to datetime during the invocation of the iTVF. If you replace the '99991231' with a varchar variable with the SAME value the iTVF works.
Sorry, but I think you are missing the point. You came in late to this thread so maybe you did not read the entire conversation.
The only thing I am saying is that because the same code behaves differently in SQL server 2005 and 2008 there is a minor bug in 2008.
I have never said anything about exactly where the bug is.
There are multiple ways to demonstrate the bug. an iTVF is not necessary.
The bug is all about how a CASE statment with only constants is evaluated.
For example:
SELECT CASE WHEN 0=0 THEN getdate() ELSE dateadd(day, 1, '99991231') END
This code also generates an error on 2008 but not on 2005. It is clear that there is no real reason why an error should be generated - the return value is simply the current time.
I know the code is silly, but that is not the point. The point is that this is a breaking change between 2005 and 2008 and I dont think it was intentional.
As I said, a minor bug.
If I report it on Connect I expect Microsoft to resolve it as "Wont Fix" - not "By Design" 😎
/SG
April 21, 2010 at 1:14 pm
Stefan_G (4/21/2010)
Lynn Pettis (4/21/2010)
I think you are missing the point. If it is a bug, it isn't a bug in how CASE works, it's a bug in how the iTVF is handling the implicit conversion of the constant string to datetime during the invocation of the iTVF. If you replace the '99991231' with a varchar variable with the SAME value the iTVF works.Sorry, but I think you are missing the point. You came in late to this thread so maybe you did not read the entire conversation.
The only thing I am saying is that because the same code behaves differently in SQL server 2005 and 2008 there is a minor bug in 2008.
I have never said anything about exactly where the bug is.
There are multiple ways to demonstrate the bug. an iTVF is not necessary.
The bug is all about how a CASE statment with only constants is evaluated.
For example:
SELECT CASE WHEN 0=0 THEN getdate() ELSE dateadd(day, 1, '99991231') ENDThis code also generates an error on 2008 but not on 2005. It is clear that there is no real reason why an error should be generated - the return value is simply the current time.
I know the code is silly, but that is not the point. The point is that this is a breaking change between 2005 and 2008 and I dont think it was intentional.
As I said, a minor bug.
If I report it on Connect I expect Microsoft to resolve it as "Wont Fix" - not "By Design" 😎
/SG
NOT A BUG, the code isn't even being executed. It is failing during the compile phase. So, as Paul indicated, it is constant folding.
Check this out:
begin try
SELECT CASE WHEN 0=0 THEN getdate() ELSE dateadd(day, 1, '99991231') END
end try
begin catch
select 'Error occured'
end catch
go
declare @MyDate as varchar(8) = '99991231';
begin try
SELECT CASE WHEN 0=0 THEN getdate() ELSE dateadd(day, 1, @MyDate) END
end try
begin catch
select 'Error occured'
end catch
go
declare @MyDate as varchar(8) = '99991231';
begin try
SELECT CASE WHEN 0=1 THEN getdate() ELSE dateadd(day, 1, @MyDate) END
end try
begin catch
select 'Error occured'
end catch
go
April 21, 2010 at 1:24 pm
And by the way, the following code in SQL Server 2005 behaves the same way the code above works in SQL Server 2008.
begin try
SELECT CASE WHEN 0=0 THEN getdate() ELSE dateadd(day, 1, '99991231') END
end try
begin catch
select 'Error occured'
end catch
go
declare @MyDate as varchar(8);
set @MyDate = '99991231';
begin try
SELECT CASE WHEN 0=0 THEN getdate() ELSE dateadd(day, 1, @MyDate) END
end try
begin catch
select 'Error occured'
end catch
go
declare @MyDate as varchar(8);
set @MyDate = '99991231';
begin try
SELECT CASE WHEN 0=1 THEN getdate() ELSE dateadd(day, 1, @MyDate) END
end try
begin catch
select 'Error occured'
end catch
go
April 21, 2010 at 1:25 pm
Lynn Pettis (4/21/2010)
NOT A BUG, the code isn't even being executed. It is failing during the compile phase. So, as Paul indicated, it is constant folding.
I know that the problem is constant folding. It does not matter. The fact is still that code that worked in 2005 no longer workds in 2008.
It does not matter if the problem is during compilation or during execution.
Another thing to think about is this:
SELECT CASE WHEN 0=0 THEN getdate() ELSE CAST('20100100' as datetime) END
SELECT CASE WHEN 1=0 THEN getdate() ELSE CAST('20100100' as datetime) END
The first statement executes without error, and the second throws an error.
This is exactly what I expect, but I would expect dateadd to behave the same as CAST. Both CAST and dateadd are documented as foldable expressions in BOL.
/SG
April 21, 2010 at 1:30 pm
Stefan_G (4/21/2010)
Lynn Pettis (4/21/2010)
NOT A BUG, the code isn't even being executed. It is failing during the compile phase. So, as Paul indicated, it is constant folding.I know that the problem is constant folding. It does not matter. The fact is still that code that worked in 2005 no longer workds in 2008.
It does not matter if the problem is during compilation or during execution.
Another thing to think about is this:
SELECT CASE WHEN 0=0 THEN getdate() ELSE CAST('20100100' as datetime) END
SELECT CASE WHEN 1=0 THEN getdate() ELSE CAST('20100100' as datetime) END
The first statement executes without error, and the second throws an error.
This is exactly what I expect, but I would expect dateadd to behave the same as CAST. Both CAST and dateadd are documented as foldable expressions in BOL.
/SG
But it doesn't work in SQL Server 2005. I tried it on one of my servers and it behaved the same way.
Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
April 21, 2010 at 1:32 pm
In the last code snippet you posted, the first statement works in both SQL Server 2005 and SQL Server 2008 on my servers. The second fails just as it should.
April 21, 2010 at 1:38 pm
Lynn Pettis (4/21/2010)
But it doesn't work in SQL Server 2005. I tried it on one of my servers and it behaved the same way.Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
OK, in that case I think I should stop this argument until tomorrow when I have both an SQL 2005 and an SQL 2008 available. :unsure:
As you understand my whole point is that 2005 and 2008 allegedly behaves differently.
Have you tried the code posted by the OP on both 2005 and 2008 ?
My examples are really just intended to be simplifications of his code.
/SG
April 21, 2010 at 2:55 pm
OK, I have now made some tests on both 2005 and 2008.
Exact versions:
Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64) Jan 8 2010 19:55:08 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
The following script works on 2005, but fails on 2008:
NOTE! To get the desired effect I had to make a few changes to Paul's function. I have added a call to isnull().
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, isnull(@DaysToAdd,0), @BaseDate)
END;
GO
select * from dbo.SafeDateAdd('99991231',1)
The following script also works on 2005 but fails on 2008:
SELECT * FROM sys.tables WHERE
CASE WHEN CAST(CAST('99991231' AS DATETIME) as int) + isnull(1,0) >= 2958464 THEN '99991231'
ELSE DATEADD(dd,ISNULL(1, 0),'99991231')
END
IS NOT NULL
The following script works in both 2005 and 2008:
SELECT
CASE WHEN CAST(CAST('99991231' AS DATETIME) as int) + isnull(1,0) >= 2958464 THEN '99991231'
ELSE DATEADD(dd,ISNULL(1, 0),'99991231')
END
Once again, in my opinion just the fact that the behavior is different in 2005 and 2008 indicates that this is a minor bug in 2008.
/SG
April 21, 2010 at 3:16 pm
Stefan_G (4/21/2010)
OK, I have now made some tests on both 2005 and 2008.Exact versions:
Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64) Jan 8 2010 19:55:08 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
The following script works on 2005, but fails on 2008:
NOTE! To get the desired effect I had to make a few changes to Paul's function. I have added a call to isnull().
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, isnull(@DaysToAdd,0), @BaseDate)
END;
GO
select * from dbo.SafeDateAdd('99991231',1)
The following script also works on 2005 but fails on 2008:
SELECT * FROM sys.tables WHERECASE WHEN CAST(CAST('99991231' AS DATETIME) as int) + isnull(1,0) >= 2958464 THEN '99991231'
ELSE DATEADD(dd,ISNULL(1, 0),'99991231')
END
IS NOT NULL
The following script works in both 2005 and 2008:
SELECTCASE WHEN CAST(CAST('99991231' AS DATETIME) as int) + isnull(1,0) >= 2958464 THEN '99991231'
ELSE DATEADD(dd,ISNULL(1, 0),'99991231')
END
Once again, in my opinion just the fact that the behavior is different in 2005 and 2008 indicates that this is a minor bug in 2008.
/SG
Or it could be by design, changes in how the database engine works.
April 21, 2010 at 4:52 pm
Well this took off while I was asleep!
Stefan, I strongly disagree that changes in undocumented, implementation-specific behaviour amounts to a bug. Optimiser code can, and does, vary between versions, service packs, cumulative updates, QFEs...and so on.
Please do raise this as a Connect Item. Dollars to doughnuts it gets closed as By Design.
My position remains that it is unwise and unsafe to depend on inferred rules based on observed behaviour. This is exactly the sort of thinking that leads to people expect results in some predictable order without an ORDER BY clause, for example.
I can recommend the following book:
http://www.simple-talk.com/books/sql-books/defensive-database-programming/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply