Re: Odd math

From: <oratune_at_aol.com>
Date: 2000/07/27
Message-ID: <8lqes2$b48$1@nnrp1.deja.com>

In article <8lq7l2$5o5$1_at_nnrp1.deja.com>, dejaisbogus_at_my-deja.com wrote:
> 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.

>

This could be argued ad infinitum/ad nauseaum but it won't change the behavior of Oracle.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Jul 27 2000 - 00:00:00 CDT