Subject | Re: [ib-support] Re: 3 * 1/3 = 0 ??? |
---|---|

Author | Raymond Kennington |

Post date | 2002-09-01T20:20:52Z |

Hi Roger.

Re:

floating-point calculations are not precise.

It so happenned that one of my staff in 1995 wrote 3 SPs that calculated pays, tax, etc.

in 3 different ways.

For weeks the trial balance was ok. Then it was off by 3 cents. My clients spent weeks of

effort looking for the error(s) that caused it.

It turned out to be the use of floating-point numbers.

x/12.0 is more accurate that (1./12.) * x, so you had better explain to your users this so

that they will always get consistent results and know which one is more accurate.

From a different point of view, anyone who creates a report should check that it produces

correct results with a small sample.

Raymond Kennington

rogervellacott wrote:

...

remaining 4 can be kept in the box for the next allocation.

Therefore, the correct answer to the question 100/12 is 8 each, not 8.5.

Your use of the word "correct" is to define "correct" to suit your usage, which is a

priori self-referential (i.e. circular) and has no value in a logical argument.

Cheers,

Raymond Kennington

Re:

> SELECT ANNUAL_OVERHEAD / 12 AS MONTH_OVERHEAD FROM OVERHEADS...These are different even *with* the interpretation of 1/12 as 1.0 / 12.0 because

> is not the same as

> SELECT (1/12) * ANNUAL_OVERHEAD AS MONTH_OVERHEAD FROM OVERHEADS...

floating-point calculations are not precise.

It so happenned that one of my staff in 1995 wrote 3 SPs that calculated pays, tax, etc.

in 3 different ways.

For weeks the trial balance was ok. Then it was off by 3 cents. My clients spent weeks of

effort looking for the error(s) that caused it.

It turned out to be the use of floating-point numbers.

x/12.0 is more accurate that (1./12.) * x, so you had better explain to your users this so

that they will always get consistent results and know which one is more accurate.

From a different point of view, anyone who creates a report should check that it produces

correct results with a small sample.

Raymond Kennington

rogervellacott wrote:

>I don't think these are different: 1./12 and 1./12.

> Several billion electronic calculators have been produced which state

> that 1/3 = 0.33333... and not 0, and this is what most people

> understand. SQL is not the private province of developers - my own

> users are invited to write their own queries for their reports - so

> user-friendliness is important.

>

> Now, I know we are all very intelligent people, and perhaps we find

> it strange that anyone should understand 1/3 to mean "one third". But

> we cannot change the fact that this is exactly what most people do

> understand. So if 1/3 is to mean zero, then SQL calculations become

> highly accident-prone, and from most people's point of view the

> answers given by SQL can be WRONG.

>

> We are in a situation in which we have to explain to our users that

>

> SELECT ANNUAL_OVERHEAD / 12 AS MONTH_OVERHEAD FROM OVERHEADS...

> is not the same as

> SELECT (1/12) * ANNUAL_OVERHEAD AS MONTH_OVERHEAD FROM OVERHEADS...

>

> The second expression returns zero, but anyone with any common sense

> would see that "1/12" means "one twelfth", and should give the RIGHT

> answer, and not zero.

>

> And then, if we explain that

>

> SELECT (1.00/12) * ANNUAL_OVERHEAD

> is different to

> SELECT (1.00/12.00) * ANNUAL_OVERHEAD

> then our users will look at us very strangely, and make a mental note

> to check carefully any calculations we present them with in future!

...

> It looks to me as though Oracle have got it right. I am told that inIf one allocates 100 pens equally amongst 12 people then each one gets 8 pens; the

> Oracle, all the above expressions will return the correct answer.

remaining 4 can be kept in the box for the next allocation.

Therefore, the correct answer to the question 100/12 is 8 each, not 8.5.

Your use of the word "correct" is to define "correct" to suit your usage, which is a

priori self-referential (i.e. circular) and has no value in a logical argument.

Cheers,

Raymond Kennington