Re: Odd math

From: <dejaisbogus_at_my-deja.com>
Date: 2000/07/27
Message-ID: <8lq7l2$5o5$1@nnrp1.deja.com>#1/1

In article <8kijmc$2uf$1_at_nnrp1.deja.com>,   oratune_at_aol.com wrote:
> In article <396befcf.127433039_at_news.eagles.bbs.net.au>,
> steve.adams_at_ixora.com.au (Steve Adams) wrote:
> > Hi All,
> >
> > Oracle 8.1.6 appears to think that ... trunc(log(2, 4)) = 1.
> >
> > SQL> select trunc(log(2,4)) from dual;
> >
> > TRUNC(LOG(2,4))
> > ---------------
> > 1
> >
> > Does it reproduce on earlier versions?
> > Any comments?
> >
> > Regards,
> > Steve Adams
> > http://www.ixora.com.au/
> > http://www.oreilly.com/catalog/orinternals/
> > http://www.christianity.net.au/
> >
> >
>
> Let's look at how this calculation is actually being performed. The
> desired result is the log 4, which would be 2, fairly obvious to
 anyone
> 2
> proficient in mathematics.
> ....
> And, even if we let Oracle use it's almost unlimited decimal places we
> STILL get a value less than 2.00 due to truncation of the intermediate
> results:
>
> SQL> select to_char(log(2,4))
> 2 from dual
> 3 /
>
> TO_CHAR(LOG(2,4))
> ----------------------------------------
> 1.99999999999999999999999999999999999998
>
> Since the 'calculated' value is still less than 2 the truncated value
 is
> going to be 1, not 2 as expected. TRUNC operates on the actual value,
> not the displayed value; the above value if displayed as a number
 would
> be 2 due to implicit rounding but this rounding is for display
 purposes
> only. The actual value is not changed until TRUNC or ROUND act upon
 it.
> This is not a bug, but inherent inaccuracies due to inexact
 intermediate
> results as a consequence of hardware and software limitations.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

It's a given that hardware has limited precision but the display is adjusted to show the correct answer (2 in this case). Shouldn't trunc (and to_char, for that matter) operate on the displayed value, not the internal limited-precision value?

After all, on base-two limited-precision arithmetic log(10,100) doesn't come out even, yet Oracle returns

SQL> select to_char(log(10,100)) from dual;

T
-
2

Why should to_char(log(10,100)) get special treatment? See:

SQL> select to_char(log(3,9)) from dual;

TO_CHAR(LOG(3,9))

---

1.99999999999999999999999999999999999997

SQL> select to_char(log(4,16)) from dual;

TO_CHAR(LOG(4,16))

---

2.00000000000000000000000000000000000001

SQL> select to_char(log(5, 25)) from dual;

TO_CHAR(LOG(5,25))

---

2.00000000000000000000000000000000000001

SQL> select to_char(log(6,36)) from dual;

TO_CHAR(LOG(6,36))

---

1.99999999999999999999999999999999999999

SQL> select to_char(log(7,49)) from dual;

TO_CHAR(LOG(7,49))

---

1.99999999999999999999999999999999999999


----------------------------------------
1.99999999999999999999999999999999999999

SQL> select to_char(log(8,64)) from dual;

TO_CHAR(LOG(8,64))

---

1.99999999999999999999999999999999999999

SQL> select to_char(log(9,81)) from dual;

TO_CHAR(LOG(9,81))

---

2.00000000000000000000000000000000000001

SQL> select to_char(log(10,100)) from dual; -- why treated special?

T
-
2

Alternately, you can define everything in natural logs, so

log (4) = log(2, 4) = ln(4)/ln(2)

   2

select ln(4)/ln(2) from dual;

LN(4)/LN(2)

---

          2

I have one of the early electric calculators that returns 1.9999999999999 instead of 2.0, but that doesn't mean that is preferable to the correct answer.

Even Microsoft Access can do this! viz from Access help:

"If you want coercion to occur... you can pass the argument in its own set of parentheses...

Dim MyVar
MyVar = 3.1415
Call SomeSub((MyVar))

Sub SomeSub (MyNum As Integer)

        MyNum = MyNum + MyNum
End Sub

Placing the argument in its own set of parentheses forces evaluation of it as an expression. During this evaluation, the fractional portion of the number is ROUNDED (not truncated) to make it conform to the expected argument type."

What is desired is something like

select trunc(log(10,4)/log(10,2) + .0000000000001) from dual;

TRUNC(LOG(10,4)/LOG(10,2)+.0000000000001)

---

                                        2

or (less desirable):

select to_char(log(10,4)/log(10,2) +

.0000000000000000000000000000000000001

) from dual;

TO_CHAR(LOG(10,4)/LOG(10,2)+.00000000000

---

2.00000000000000000000000000000000000008

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 27 2000 - 00:00:00 CDT