October 24, 2012 at 5:45 am
Thanks for the question and discussion.
October 24, 2012 at 6:26 am
thanks for the question,
KR
Iulian
October 24, 2012 at 6:33 am
I must ask, because this section of code gives the proper answer of 105. Why does casting to anything besides an INT (TinyInt, Int, BigInt, etc.) return 105?
For example:
DECLARE @Value AS INT
SET @Value = 100
PRINT (CAST(@Value AS REAL) * 1.05)
PRINT CAST(CAST(@Value AS REAL) * 1.05 AS VARCHAR(MAX))
PRINT CAST(CAST(@Value AS REAL) * 1.05 AS DECIMAL)
PRINT CAST(CAST(@Value AS REAL) * 1.05 AS INT)
October 24, 2012 at 7:09 am
Koen Verbeeck (10/24/2012)
Interesting question, thanks.I do wonder how people are supposed to get this right without running the script.
I got it right when I realized my answer wasn't on the list.
Had to use calc to figure out that indeed 1.05 * 20 = 21, and not 20.1 because I don't have enough caffeine in me to even be able to handle basic math right now.
Then it took a lot of memory of the weird things I used to have to deal with in chemical manufacturing back when we were dealing with ml but somebody had decided the UOM for a product was in L.
October 24, 2012 at 8:10 am
Interesting question.
Thank you for the detailed explaination Hugo.
October 24, 2012 at 8:45 am
Thanks for the responses. I found the issue when testing real code to calculate a quota of free jobs based on 5% of jobs (rounded down) and posed it as a slightly provocative question. Anyone who got the right answer should have two points deducted for either cheating (running the script or a similar one) or guessing!
For the record, a rewording of the required algorithm is 'give one free for every 'whole lot' of 20 they have'.
We all think we know about the perils of inexact values, but rarely does it show itself so weirdly, especially that 20 * 1.05 doesn't round down but 100 * 1.05 does.
Some have commented that @Quota should be not an integer, but this doesn't make sense when my answer is to be an integer.
Some suggest using Round, but the algorithm is required to always round down, so @Value=99 should give 4 free and a total of 103 (whereas e.g. Hugo's and Tom's versions using Round gives 104). And even with 'normal' rounding to the nearest integer, one could imagine a calculation generating say 2.4999999 and rounding down rather than up as expected.
When I was researching the answer I did browse some articles on IEEE floating point definitions, and got somewhere down page two before my eyes glazed over. Perhaps it would be fixed by an improved floating point specification. I'd have to say the results I got are counter-intuitive and in an ideal world, it shouldn't happen.
In the meanwhile, I have re-coded it to to follow the reworded algorithm, i.e. set @Quota3 = (@value / 20) + @Value. Where such a solution is not possible then I'll stick with the ugly solution of adding a tiny fraction, and I'll keep a much more careful eye out in future when rounding.
October 24, 2012 at 8:50 am
mhtanner (10/24/2012)
Anyone who got the right answer should have two points deducted for either cheating (running the script or a similar one) or guessing!
Excuse me!
October 24, 2012 at 8:54 am
Thomas Abraham (10/24/2012)
Any chance we could get Hugo to rewrite BOL, so they are correct and useful? Thanks for the, as usual, great explanation.
+1,000
October 24, 2012 at 9:13 am
palotaiarpad (10/24/2012)
My problem is, that the first expression evaluates to 105, and if i cast the 105 to int it should remain 105 and not less.
No, it doesn't evaluate to 105, it evaluates to 104.99999237060547; it's displayed as 105 because when SMSS displays a real number result it rounds it to 7 digits (in this case getting 105.0000) because the precision of the real (ie float(24)) is only 24 bits, so about 7¼ decimal digits, so everything after the 8th digit is pure junk (caused by the inability to represent 1.05 accurately enough) and even the 8th digit is not terribly accurate (when it is a 9 all we really know is that it's higher than 6) and then drops any trailing zeroes after the decimal point, so here we see 105. However, when the value is either implicitly converted or explicitly converted to int, it is truncated not rounded (pure stupidity, this, IMHO, but then a lot of things are remarkably stupid) so we get 104.
Incidentally, going via a display format (using str) to get to int is another way of getting rounding instead of truncation, perhaps easier to understand than using the round function. So something like
int @x = str(cast(100 as real)*1.05,9)
will put the int value 105 into @x, not 104, because there is no truncation other than of trailing zeroes, there's rounding instead.
But the best solution is to use decent floating point, which unfortunately SQL doesn't have.
edit: fix quote tags.
Tom
October 24, 2012 at 9:16 am
Koen Verbeeck (10/24/2012)
Interesting question, thanks.I do wonder how people are supposed to get this right without running the script.
In my case was because some years ago I worked on a trading application using many decimals for calculations, and due to my limited knowledge of SQL at that time, I assumed that 'real' datatype has the most accurate precision. But when the user validated the results, I realized than 'real' is not so real, and I ended up changing hundreds of fields and variables from 'real' to 'float'.
October 24, 2012 at 9:17 am
mhtanner (10/24/2012)
Some have commented that @Quota should be not an integer, but this doesn't make sense when my answer is to be an integer.
I think you may get an argument about this.
October 24, 2012 at 10:06 am
mhtanner (10/24/2012)
For the record, a rewording of the required algorithm is 'give one free for every 'whole lot' of 20 they have'.In the meanwhile, I have re-coded it to to follow the reworded algorithm, i.e. set @Quota3 = (@value / 20) + @Value. Where such a solution is not possible then I'll stick with the ugly solution of adding a tiny fraction, and I'll keep a much more careful eye out in future when rounding.
Yes, that algorithm works as long as @value isn't large enough that the addition causes int overflow.
There's no straightforward way using float(24) and multiplication (instead of division) that will work for that range of values. It is pretty easy to write something that will work for @value up to 12582916 (4+224-222) and probably a bit further, for example this
set @Quota3 = floor(round(cast(@value as real)*1.05,10))
But your algorithm has the greater range and is simpler, so clearly it's much better. 😎
Tom
October 24, 2012 at 11:24 am
venoym (10/24/2012)
I must ask, because this section of code gives the proper answer of 105. Why does casting to anything besides an INT (TinyInt, Int, BigInt, etc.) return 105?
I don't have time to verify, but I think that this is because conversion to integer uses truncation, and the other conversions use rounding. For conversion to decimal, it is rounding to a whole number (since you didn't specify precision); for conversion to string, it rounds to a number of digits that SQL Server considers sensible for float data (slightly less than the actual internal precision), then removes trailing zeroes.
mhtanner (10/24/2012)
Some have commented that @Quota should be not an integer, but this doesn't make sense when my answer is to be an integer.Some suggest using Round, but the algorithm is required to always round down, so @Value=99 should give 4 free and a total of 103 (whereas e.g. Hugo's and Tom's versions using Round gives 104). And even with 'normal' rounding to the nearest integer, one could imagine a calculation generating say 2.4999999 and rounding down rather than up as expected.
In that case, why are you using approximate numerics? Without the explicit casting to float, fixed point aritmetics are used, and you should not get these issues.
mhtanner (10/24/2012)
Anyone who got the right answer should have two points deducted for either cheating (running the script or a similar one) or guessing!
No, it is possible to work out the correct answer without cheating or guessing,
@Quota1 uses no floating points, so you can't get a rounding error there. For @Quota2, you can get a rounding error in the first part of the calculation (20 instead of 21), and for @Quota3 at the end. So possible answers are 105,100,104 / 105,100,105 / 105,105,104 / 105,105,105. Only two of those were listed. And it's easy to choose between those two by using a bit of psychology - would anyone submit a question where these rounding/truncaction errors do not cause any weird results at all? I think not, so I felt fairly safe checking the one answer I then had left.
October 24, 2012 at 3:37 pm
Nice
October 25, 2012 at 1:00 am
Select (cast(100 as real) * 1.05)*1000000000.0000000000000,(cast(100 as real)*1.049999237060547)*1000000000.0000000,cast(cast(100 as real) * 1.05 as int);
--result: 1,05E+11 , 1,049999E+11, 104
Sorry, but maybe i'm too dumb to understand the logic how SQL Server works. 😀
Yes, i'm! There are 5 nines and not 4.
Select (cast(100 as real) * 1.05)*1000000000.0000000000000,(cast(100 as real)*1.0499999237060547)*1000000000.0000000,cast(cast(100 as real) * 1.05 as int);
--result: 1,05E+11 , 1,05E+11, 104 :Whistling:
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply