Re: Odd math

From: <oratune_at_aol.com>
Date: 2000/07/12
Message-ID: <8kijmc$2uf$1@nnrp1.deja.com>#1/1

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. To calculate this, however, approximations are made and the error in approximation is magnified with each successive approximation. The calculation actually becomes:

log 4 == log 4

   2        10
         ------
         log  2
            10

We now have not only logarithmic calculations but division as well. Oracle believes:

SQL> select log(10,4)
  2 from dual
  3 /

 LOG(10,4)

---

.602059991

and

SQL> select log(10,2)
  2 from dual
  3 /

 LOG(10,2)

---

.301029996

Performing the division of these values we obtain:

SQL> l
  1 select to_char(.602059991/.301029996)   2* from dual
SQL> / TO_CHAR(.602059991/.301029996)

---

1.99999999667807190882067446860013245989

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.

Received on Wed Jul 12 2000 - 00:00:00 CDT