April 24, 2010 at 2:51 am
Paul White NZ (4/23/2010)
What I do disagree with you about is your statement that because no error was observed in 2005, it is a bug in 2008.
This is Issue 1. I see no point discussing that issue anymore. We simply have different opinions and it is not very important in any case.
The rest of my discussion is about Issue 2 which is the same in both 2005 and 2008.
As it happens, I don't think script1 demonstrates anything useful - after all, what would be the point of compiling a plan that is guaranteed to throw an error? The benefits of better constant folding (better plans, cardinality estimation) are massively more important than the change in behaviour you are objecting to.
OK, script1 doesn't demonstrate anything useful. It was just written to show that this problem really had nothing to do with the evaluation order of case, which is where this discussion started.
How about this for a more useful script:
create function dbo.SafeDateIncrement(@d datetime) returns table
as
return select case when @d='99991231' then '99991231' else dateadd(d, 1, @d) end as result
go
select * from dbo.safedateincrement('20101231') -- works as expected
go
select * from dbo.safedateincrement('99991231') -- fails during compilation
go
What this demonstrates is that this bug makes it impossible to write an ITVF that implements the desired semantics. We have alread have this discussion with your SafeDateAdd. My new function is just simplified to highlight the problem even more clearly.
This is caused by exactly the same problem as script1, but in this case the compiled execution plan is definitely not 'guaranteed to throw an error' - quite the opposite: the code is designed to avoid any possible overflow errors.
SQL Server does not in fact guarantee to produce a compiled plan for any syntactically valid statement, as far as I know.
This is just silly. Of course there is an implied guarantee that the compiler should be able to handle any valid code unless there are documented compiler limitations like the nesting levels of case for example.
April 24, 2010 at 4:03 am
Stefan_G (4/24/2010)
What this demonstrates is that this bug makes it impossible to write an ITVF that implements the desired semantics. We have alread have this discussion with your SafeDateAdd. My new function is just simplified to highlight the problem even more clearly.
All that shows is that DATEADD(DAY, 1, '99991231') produces an overflow error 😀
Is it impossible to write an iTVF that implements the requirement?
ALTER FUNCTION dbo.SafeDateAdd
(
@BaseDate DATETIME,
@DaysToAdd INTEGER
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH Derived
AS (
SELECT base = @BaseDate,
low_delta = DATEDIFF(DAY, @BaseDate, CONVERT(DATETIME, '1900-01-01T00:00:00.000', 126)),
high_delta = DATEDIFF(DAY, @BaseDate, CONVERT(DATETIME, '9999-12-31T00:00:00.000', 126))
)
SELECT result =
DATEADD
(
DAY,
CASE
WHEN @DaysToAdd BETWEEN low_delta AND high_delta THEN @DaysToAdd
WHEN low_delta >= 0 THEN low_delta
ELSE high_delta
END,
base
)
FROM Derived;
Just added some defensive coding there. 😎
SQL Server does not in fact guarantee to produce a compiled plan for any syntactically valid statement, as far as I know.
This is just silly. Of course there is an implied guarantee that the compiler should be able to handle any valid code unless there are documented compiler limitations like the nesting levels of case for example.
I don't think it is at all silly. The 'implied guarantee' you refer to has no basis I can think of. I can see why you would expect it to be true (perhaps by analogy to a compiled language like C#?) so I would not call your assertion silly - but it is faulty 😉
Paul
April 24, 2010 at 8:12 am
Paul White NZ (4/24/2010)
Is it impossible to write an iTVF that implements the requirement?Just added some defensive coding there. 😎
OK, I should have known better than to challenge you like that. Well done. 🙂
I now officially give up. I realize that I will not be able to make you see the light. 😉
/SG
April 24, 2010 at 7:33 pm
Stefan_G (4/24/2010)
OK, I should have known better than to challenge you like that. Well done. 🙂 I now officially give up. I realize that I will not be able to make you see the light. 😉
I do understand the points you have been making - we just differ in our assessment of how important they are. Thanks for another very interesting discussion.
June 16, 2010 at 2:56 am
I just saw that Microsoft actually decided that this problem was not only regarded as a bug but that they even considered it worth fixing.
So it seems that my understanding of this problem was not so bad after all... 😎
/SG
June 16, 2010 at 3:56 am
Stefan_G (6/16/2010)
I just saw that Microsoft actually decided that this problem was not only regarded as a bug but that they even considered it worth fixing.So it seems that my understanding of this problem was not so bad after all... 😎
/SG
Yes - I've been following the progress of that Connect item with interest.
It surprises me, to be honest, but there you go: they've decided it is a bug and fixed it for SQL11.
Good news for anyone that finds the behaviour surprising.
Well done.
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply