On 11/2/2005 at 16:13 Eric Bohlman wrote:
>Henry Miller wrote:
>> As for 5/2, my grade school teachers taught me that if I round it at
>> all, the answer is 3, NEVER 2. It is only latter in life that I
>> learned about bankers rounding which sometimes is 2, sometimes 3.
I
>> have never seen a justification for rounding to 2, except for the
bogus
>> answer that it is easy for the computer to do. Thus I conclude
>> whatever the final answer is, sqlite would be wrong if 5/2 is always
2.
>
>The problem with the grade-school rule is that, assuming the last
digit
>is uniformly distributed, you'll be rounding up 5 times out of 9 and
>rounding down 4 times out of 9. That means that if you add up a large
>number of rounded numbers, the result will always be larger than the
>what you'd get if you added up the unrounded numbers and then rounded
>the sum. That introduces a systematic bias in financial and
statistical
>calculations.
Hence bankers rounding, which I mentioned latter. This has nothing
to do with the subject, which is not rounding per se, but division in
general.
That has nothing to do with my point though: 5/2 can never be ALWAYS
rounded down to 2 correctly. You would be correct to always round up
to 3 - by my grade school math. You would be correct to use one of the
bankers rounding schemes which would round up or down.
Maybe I should change the problem to 5/3. SQLITE will currently round
this down to 1. This is not rounding, this is a floor function.
Floor functions have their use, but they are not rounding, and should
not be the default when doing division and turning the result into an
integer.
The question is since sqlite is clearly doing the wrong thing when
dividing two integers, would be we ok to just turn the result into a
real, or must we implement real rounding. (with some way to select
from the different bankers rounding, or simple rounding) If a floor
function is useful, sqlite could provide that too, but that is a
different discussion (which I would likely argue against unless someone
has a good argument for).
