SQL Kiwi (1/5/2012)
L' Eomot Inversé (1/5/2012)
SQL Kiwi (1/4/2012)
As far as I recall, the issue with multiplying or dividing by one was a bug in type inference, which has since been fixed.
Well, I'm on an XP platform so I can't test if it is fixed in Denali, but it certainly isn't fixed in SQL 2008 R2 with latest updates.
Maybe someone could try this in Denali:
Hm, I was confusing what I thought you were describing with this bug: https://connect.microsoft.com/SQLServer/feedback/details/557523/automatic-type-assignment-causes-data-corruption, which is 'fixed' (for some value of 'fixed'). Your code example still throws the same error in the latest build of Denali I have, with TF 4199 on just in case it was an optimizer change that requires that flag. I need to look at it a bit more closely to understand fully what the script is telling me - do you know if there is an existing bug report on Connect for this?
As far as I know there is no Connect bug report, and I don't think there's much point in raising one as it's clearly "by design" - the two numbers being multiplied (or divided, or remaindered) are type decimal (38,5) and decimal(2,1) so the result has to be decimal (38,6) into which the decimal(38,5) number will not fit. Perhaps I should have given a simpler example of teh problem, so here goes:
declare @t table (A decimal(38,5) primary key)
insert @t(A) values((1234567890.0*10000000000.0*10000000000*1000)+0.12345)
select A from @t
update @t set A=A*1.0
There are all sorts of things in T-SQL that ought to be redone completely, perhaps even throwing T-SQL away completely, but that's a whole different discussion, and something that doesn't look any more likely to happen than SQL Server adding some new decimal type with better semantics and more logical behaviours in general.
I'm tempted to raise a connect item asking for support for the 2008 revision of the IEE floating point standard, including exponents to base 10 and 128 bit forms. That seems the obvious way to go in the long term. I looked a while back to see if there was a connect item for that and didn't spot one. An improved decimal type might be more likely to see a positive response in the short to medium term, but isn't as good a solution.