April 21, 2010 at 5:00 pm
Paul White NZ (4/21/2010)
Well this took off while I was asleep!
You actually sleep??? When did this change occur? :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 21, 2010 at 5:03 pm
Stefan_G (4/21/2010)
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.
That does not follow at all, even from a purely logical perspective. Internal implementation changes take place all the time. Those changes can break code that makes unsound assumptions, and that is all that has happened here. If you seek to prove that it is a 'fixed bug', please provide the KB reference that covers it.
So, Paul, have you tried running the code posted by the OP ?
What does it return on your system ?
It produces an overflow error on 2008.
Maybe he can simply upgrade to the latest version to fix his problem.
No, he needs to fix the code.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 5:25 pm
Stefan_G (4/21/2010)
Your own SafeDateAdd also relies on the so called "undocumented behavior" of CASE.
The evaluation order of CASE is documented here: CASE (Transact-SQL).
The expression in the ELSE part is not subject to constant-folding if a local variable is used as documented here:
Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation - Nonfoldable Expressions section, "Expressions whose results depend on a local variable or parameter".
The ELSE part can still produce an overflow if the parameter to the function is a literal and forced parameterization is not on for the database.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 5:33 pm
Stefan_G (4/21/2010)
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?
Yes, but the database I used happens to have forced parameterization on, which prevents the constant folding.
If I use a literal, with simple parameterization, it produces the overflow 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.
If all that concerns you is whether you are entitled to regard this as a bug or not, go ahead - it really doesn't matter, as you say. We simply disagree on that point.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 5:50 pm
Stefan_G (4/21/2010)
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.
It is absolutely clear why that statement causes an overflow. The expressions are foldable and the DATEADD results in an overflow at compilation time. You cannot expect the error to be circumvented by the fact that 0=0 always evaluates to true.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 6:00 pm
Stefan_G (4/21/2010)
Another thing to think about is thisSELECT 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.
Both compile fine, the second produces a run-time error.
CAST as DATETIME is not foldable since it depends on SET settings (no folding, so no compile-time error).
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.
Constant-folding occurs at compile time, so of course it matters - it is at the heart of the issue here.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 6:05 pm
WayneS (4/21/2010)
You actually sleep??? When did this change occur? :w00t:
:laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 7:09 pm
Stefan_G (4/21/2010)
NOTE! To get the desired effect I had to make a few changes to Paul's function. I have added a call to isnull().
Why add the ISNULL? It isn't clear from your post, and I don't have time right now to analyse it in detail.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 7:26 pm
Lynn Pettis (4/21/2010)
Or it could be by design, changes in how the database engine works.
Exactly. SQL Server is being improved all the time - and enhancing its ability to constant-fold in different circumstances is just one of those ongoing things. Producing better plans with better cardinality estimates is much more important than preserving undocumented and unintended behaviours.
Imagine if Microsoft had to preserve the behaviour of all code ever written based on any observed behaviour that appeared to be reproducible at the time! :hehe:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 22, 2010 at 10:40 am
This thread actually highlights two different but related issues.
Issue 1:
Fact: The behavior of some very specific constant-only expressions in filters has changed between 2005 and 2008.
My opinion: I consider this to be a minor bug in 2008. You might disagree, Microsoft might disagree, this does not change my opinion.
Script that demonstrates issue 1:
-- This code works on SQL 2005, but fails on SQL 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
Issue 2:
Fact: The constant folding in both 2005 and 2008 introduces compile-time errors for some perfectly valid SQL code.
My opinion: Constant folding is a compiler optimization feature. Compiler optimizations are supposed to improve performance but maintain the semantics of the code. The only effect of optimizations should be improved performance. In this case perfectly valid SQL code causes a compile-time error. Imagine if a C++ compiler did the same: when compiling a valid C++ program the optimizer generates a compiler error that casues compilation to stop. I can assure you that this would most definitely be regarded as a bug by the C++ compiler team.
So, my opinion is that the fact that constant folding can cause a compiler failure is a bug.
More discussion about Issue 2:
In this case the behavior is the same in both 2005 and 2008. This only means that this bug was introduced in 2005 (or earlier).
To reproduce the compiler error you can try the following code:
create procedure test1
as
declare @result datetime
if getdate()>'20100401'
set @result = dateadd(d,1,'99991231') -- datetime overflow
else
set @result = 'Too early'
select @result
To see that errors during constant folding does not always cause compiler errors you can try this code:
create procedure test2
as
declare @result datetime
if getdate()>'20100401'
set @result = dateadd(d,1,'99991200') -- invalid date
else
set @result = '19000101'
select @result
There is no logical reason why test1 should be impossible to compile while test2 just causes a runtime error if the offending line happens to be executed.
Paul has tried to explain this using the rules for constant folding, saying that test2 cannot be constant folded because CAST to datetime depends on SET options. My response to that is that in that case the implicit cast from a string literal in test1 should also make test1 non-foldable. Also, as a matter of fact cast('99991231' as datetime) does not depend on any SET options. A non-separated date literal is always interpreted as yyyymmdd regardless of language settings.
A much better explanation for this difference is that there is special code in the constant folding routines that detects conversions from illegal datetime string literals and avoids a compile-time error. For dateadd overflow they have simply forgotten to perform the same check.
If you believe this behavior to be by design, I would really like to see some documentation that tells us that dateadd(d,1,'99991231') should cause a compiler error while dateadd(d,1,'99991200') will only cause a runtime error.
So, in summary:
There are two separate issues here. Both are in my opinion bugs in SQL Server. The first was introduced with 2008, and the second was introduced in 2005 or earlier. I fully understand that the bugs must be considered minor because they are only triggered under very special circumstances.
I will report both on Connect just to see what happens.
/SG
April 22, 2010 at 12:36 pm
It took me some time to go through all the posts. I didn't imagine the discussion will take such a turn 🙂
Anyway, my question was only about the change in behavior between 2005 and 2008.
What I have is a java app that used to work perfectly using sql server 2005 and while trying to certify it for sql 2008 also, some of our regression tests started to fail.
I understand Paul's point of view, that I might have made a mistake by design, relying on some behavior that is not documented. Paul, I believe you have always encouraged defensive programming 🙂
However, I agree with Stefan, that such changes should be documented by Microsoft since they make a change in behavior, otherwise they might be considered bugs. For example, we have also had issues with REPLACE function between these 2 versions, but that is documented on msdn -
Behavior Changes to Database Engine Features in SQL Server 2008
Also RIGHT function has differences between versions (couldn't find any official reference for it), but that's another discussion.
Bottom line, I need to change my code.
Thanks
April 22, 2010 at 6:47 pm
Stefan,
I'm not going over old ground again - I have made my position clear, as best I can.
However, you might find it of general interest to read the SQL Server Development Team's feedback to the following Connect item:
https://connect.microsoft.com/SQLServer/feedback/details/350485/bug-with-newid-and-table-expressions
That might give you more of an appreciation of the design-goal differences between T-SQL and C++ and why some things are the way they are.
I will continue to promote defensive programming practices, and to encourage people not to rely on seemingly dependable observed behaviours of a particular version of the product, where a sound alternative exists.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 22, 2010 at 9:17 pm
Thanks for the comments Ionut, I agree with what you say - even if it may not be practical to ask Microsoft to document every internal change that might affect existing code. I think they do a pretty good job of that in general.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 23, 2010 at 2:04 pm
Paul White NZ (4/22/2010)
I will continue to promote defensive programming practices, and to encourage people not to rely on seemingly dependable observed behaviours of a particular version of the product, where a sound alternative exists.
First of all, I totally agree that defensive programming practices should be used, and that relying on undocumented implementation details is bad. We have no disagreement there.
The problem is that in this case nobody is relying on undocumented behavior. I even find it slightly offending that you are accusing me of that.
What we have here is perfectly valid T-SQL code that can not even be compiled. I dont understand how you can say that the code in my test1 procedure above relies on undocumented behavior.
To me there is no doubt that test1 only contains valid syntactically correct T-SQL. The code might cause a runtime error when executed, but I can see no valid reason why it should not be possible to even compile the code.
At least I am not aware of any documentation that tells us that some constant expressions that cause errors when evaluated can cause the compiler to fail.
Do you know of any such documentation ?
April 23, 2010 at 8:59 pm
Stefan_G (4/23/2010)
First of all, I totally agree that defensive programming practices should be used, and that relying on undocumented implementation details is bad. We have no disagreement there.
Sweet.
The problem is that in this case nobody is relying on undocumented behavior. I even find it slightly offending that you are accusing me of that. What we have here is perfectly valid T-SQL code that can not even be compiled. I dont understand how you can say that the code in my test1 procedure above relies on undocumented behavior. To me there is no doubt that test1 only contains valid syntactically correct T-SQL. The code might cause a runtime error when executed, but I can see no valid reason why it should not be possible to even compile the code.
There's certainly no need to feel offended - but I don't recall making any such comment specifically about 'script1'?
What I do disagree with you about is your statement that because no error was observed in 2005, it is a bug in 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.
SQL Server does not in fact guarantee to produce a compiled plan for any syntactically valid statement, as far as I know.
To be blunt about this, I think this is just something you're going to have to accept. Though perhaps we should wait for the official feedback on your Connect item to come through. My dollars-to-doughnuts remark still applies by the way - this has By Design written all over it!
At least I am not aware of any documentation that tells us that some constant expressions that cause errors when evaluated can cause the compiler to fail. Do you know of any such documentation?
I could just as well ask you to show me documentation to say that SQL Server guarantees to produce a compiled query plan for any syntactically-valid T-SQL statement...
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply