DUMP it
Duke Ganote, September 21, 2005 - 2:04 pm UTC
Pretty clearly seen using DUMP... (can't always believe your eyes...)
1 select rownum R, rownum/9*9 R_versed
2 , case when rownum/9*9 <> rownum
3 then '!'
4 else '=' end Equality
5 , dumP(rownum,17) D, dump(rownum/9*9,17) D_versed
6* from dual connect by level <= 3
XPLT9173:GANOTEDP\dganote> /
R R_VERSED E
---------- ---------- -
D
-----------------------
D_VERSED
1 1 !
Typ=2 Len=2: c1,^B
Typ=2 Len=21: c0,d,d,d,d,d,d,d,d,d,d,d,d,d,d,d,d,d,d,d,d
2 2 =
Typ=2 Len=2: c1,^C
Typ=2 Len=2: c1,^C
3 3 =
Typ=2 Len=2: c1,^D
Typ=2 Len=2: c1,^D
Greg Cantlon, September 21, 2005 - 6:31 pm UTC
You can workaround it easily enough by changing the order of the operations. So instead of 1 / 9 * 9 you would use 1 * 9 / 9 to get the result.
But your explanation of only having 38 digits of precision doesn't quite work.
Using the 1 / 9 * 9 example, I would expect that 1 / 9 would be evaluated first and then multiplied by 9, which would give you:
1 / 9 = 0.11111...
0.11111... * 9 = 0.99999....
So it would be correct to say that (1 / 9 * 9) != 1
However you would expect the same to be true when the numerator is 2:
2 / 9 = 0.22222...
0.22222... * 9 = 1.99999... 8
So then why does (2 / 9 * 9) = 2 but (1 / 9 * 9) != 1? Is this expected behaviour or is it a bug?
September 21, 2005 - 8:15 pm UTC
with imprecise computer representations of repeating numbers like this, i would say "not a bug" - it is like working with floats and doubles and anything else - the numbers are inheritly "imprecise"
some explanation
Nopparat V., September 21, 2005 - 11:52 pm UTC
[quote]Using the 1 / 9 * 9 example, I would expect that 1 / 9 would be evaluated first
and then multiplied by 9, which would give you:
1 / 9 = 0.11111...
0.11111... * 9 = 0.99999....
So it would be correct to say that (1 / 9 * 9) != 1
However you would expect the same to be true when the numerator is 2:
2 / 9 = 0.22222...
0.22222... * 9 = 1.99999... 8
So then why does (2 / 9 * 9) = 2 but (1 / 9 * 9) != 1? Is this expected
behaviour or is it a bug? [/quote]
I assume some explanations for this behaviour.
For the first case 1/9 *9, the result is 0.9999...9 with 38 digits of 9. But when you calculate 2/9 * 2, you said the result should be 1.99999... 8. Think about it, now we have 1 digit of 1 and 37 digits of 9 and the last one digit of 8. It takes 39 digits now and this is not allowed. So the last 8 is rounded up. the result is now 2.0000..... with 37 digits of 0
This calculate and round-up is done before function trunc work. So you get that result.
floating point in binary
Colin 't Hart, September 22, 2005 - 5:46 am UTC
Search Google for 'floating point binary' to get more information:
</code>
http://www.google.com/search?q=floating+point+binary <code>
You'll see that certain floating point values can easily be expressed in binary, while others (most) are an approximation.
Cheers,
Colin
September 22, 2005 - 1:55 pm UTC
right, but we don't use floats, we use a BCD like beast for numbers - they have 38 digits of precision (better than a floats 6 or doubles 13 digits) and generally don't suffer from the issue as "big" as floats do.
eg: safe to use for pennies (financial apps) whereas floats would not be.
What Every Computer Scientist Should Know About Floating-Point Arithmetic
michal, September 22, 2005 - 7:46 am UTC
google for "What Every Computer Scientist Should Know About Floating-Point Arithmetic" and you get a lot of materials explaining problem ...
floor
Geo, September 22, 2005 - 10:45 am UTC
I perform first the multiplications and the division I leave her as last step
select floor(1*9/9) from dual;
FLOOR(1*9/9)
------------
1
The Math Side
Andrew, September 22, 2005 - 3:46 pm UTC
Setting aside machine arithmetic for just a moment. . .
I think, to a mathematician, 0.99999.... effectively EQUALS 1. This, in the sense that the limit of ( 1 - power( 10, -n ) ) approaches 1 as n steps toward infinity. Different notations for the same number, basically.
Just in case there's any confusion about the calculation beyond how the machine handles it.
September 22, 2005 - 6:09 pm UTC
right, but we have computers :) they just compare bits.
many scientific applications will not:
if ( a == b )
{
....
but rather
if ( abs(a-b) <= acceptable_error )
{
......
for this reason
Floating point
Anders, September 22, 2005 - 4:28 pm UTC
September 22, 2005 - 6:16 pm UTC
but we are talking about Oracle numbers here -- not floats. Use floats and get an entirely different result:
ops$tkyte@ORA10GR1> select *
2 from (
3 select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
4 then 'x' end what
5 from (select cast( level as binary_float ) l from dual connect by level <= &1 )
6 )
7 where what = 'x';
old 3: select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
new 3: select l, case when l/cast(9 as binary_float)*cast(9 as binary_float) <> l
old 5: from (select cast( level as binary_float ) l from dual connect by level <= &1 )
new 5: from (select cast( level as binary_float ) l from dual connect by level <= 9 )
no rows selected
ops$tkyte@ORA10GR1> @test 10
ops$tkyte@ORA10GR1> select *
2 from (
3 select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
4 then 'x' end what
5 from (select cast( level as binary_float ) l from dual connect by level <= &1 )
6 )
7 where what = 'x';
old 3: select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
new 3: select l, case when l/cast(10 as binary_float)*cast(10 as binary_float) <> l
old 5: from (select cast( level as binary_float ) l from dual connect by level <= &1 )
new 5: from (select cast( level as binary_float ) l from dual connect by level <= 10 )
no rows selected
ops$tkyte@ORA10GR1> @test 1000
ops$tkyte@ORA10GR1> select *
2 from (
3 select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
4 then 'x' end what
5 from (select cast( level as binary_float ) l from dual connect by level <= &1 )
6 )
7 where what = 'x';
old 3: select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
new 3: select l, case when l/cast(1000 as binary_float)*cast(1000 as binary_float) <> l
old 5: from (select cast( level as binary_float ) l from dual connect by level <= &1 )
new 5: from (select cast( level as binary_float ) l from dual connect by level <= 1000 )
L WHAT
---------- ------------------------------
1.27E+002 x
2.51E+002 x
2.53E+002 x
2.54E+002 x
5.02E+002 x
5.03E+002 x
5.06E+002 x
5.07E+002 x
5.08E+002 x
5.11E+002 x
10 rows selected.
ops$tkyte@ORA10GR1> select *
2 from (
3 select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
4 then 'x' end what
5 from (select cast( level as binary_float ) l from dual connect by level <= &1 )
6 )
7 where what = 'x';
old 3: select l, case when l/cast(&1 as binary_float)*cast(&1 as binary_float) <> l
new 3: select l, case when l/cast(123 as binary_float)*cast(123 as binary_float) <> l
old 5: from (select cast( level as binary_float ) l from dual connect by level <= &1 )
new 5: from (select cast( level as binary_float ) l from dual connect by level <= 123 )
L WHAT
---------- ------------------------------
1.0E+000 x
2.0E+000 x
4.0E+000 x
8.0E+000 x
1.6E+001 x
3.2E+001 x
6.4E+001 x
7 rows selected.
Use doubles and get yet DIFFERENT results:
ops$tkyte@ORA10GR1> select *
2 from (
3 select l, case when l/cast(&1 as binary_double)*cast(&1 as binary_double) <> l
4 then 'x' end what
5 from (select cast( level as binary_double ) l from dual connect by level <= &1 )
6 )
7 where what = 'x';
old 3: select l, case when l/cast(&1 as binary_double)*cast(&1 as binary_double) <> l
new 3: select l, case when l/cast(1000 as binary_double)*cast(1000 as binary_double) <> lold 5: from (select cast( level as binary_double ) l from dual connect by level <= &1 )new 5: from (select cast( level as binary_double ) l from dual connect by level <= 1000 )
no rows selected
ops$tkyte@ORA10GR1> @test 123
ops$tkyte@ORA10GR1> select *
2 from (
3 select l, case when l/cast(&1 as binary_double)*cast(&1 as binary_double) <> l
4 then 'x' end what
5 from (select cast( level as binary_double ) l from dual connect by level <= &1 )
6 )
7 where what = 'x';
old 3: select l, case when l/cast(&1 as binary_double)*cast(&1 as binary_double) <> l
new 3: select l, case when l/cast(123 as binary_double)*cast(123 as binary_double) <> l
old 5: from (select cast( level as binary_double ) l from dual connect by level <= &1 )new 5: from (select cast( level as binary_double ) l from dual connect by level <= 123 )
L WHAT
---------- ------------------------------
3.1E+001 x
6.2E+001 x
Equality
Andrew, September 22, 2005 - 5:37 pm UTC
If 0.99999... does not equal 1, then what is
1 - 0.99999. . .
?
There must be some non-zero difference for the numbers to be different, right? Chalk it up to one of the tricks infinity can play.
Whoa...
Anders, September 22, 2005 - 6:34 pm UTC
> but we are talking about Oracle numbers here -- not floats
Exactly. I said "floating point numbers", you where the one saying "floats" (which I just quoted).
September 22, 2005 - 9:52 pm UTC
I don't get it? floats - are 4 and 8 byte IEEE floats - a standard, hardware capable.
We use a thing that is very similar to BCD (which supports a floating point)...
It is a very interesting discussion.
Helena Marková, September 23, 2005 - 1:39 am UTC
1-0.99999...
Vladimir Andreev, September 23, 2005 - 4:21 am UTC
@Andrew:
I expect Tom - having majored in Algebra - can express this in better terms, but:
The (rational) number 0.(9) is different, and smaller, than 1. Mathematicians have no problem dealing with that fact, and the difference between 1 and 0.(9) is an infinitely small, yet positive, (rational) number.
In fact, there are many such interesting things in Mathematics. For example:
If we take the set of the so-called Real numbers and the operations defined in it (addition and multiplication) - or in other words, the Real number field, we will notice that it is dense. "Dense" means that if you have two different members of the set, you can be sure that there is an infinite number of members of the set between them, i.e.:
for any a<b there exists an infinite number of x such that a<x<b, where a, b, and x are all real numbers.
So, even if we take the infinitely small number from above, between it and the zero you can find an infinite number of real numbers. In other words, 1 and 0.99999... are no more or less equal than 2 and 3 are (see Anders' comment above) - both are "infinitely different" (a nonsense in Mathspeak, actually - things are either different or equal there)
Cheers,
Flado
P.S.: I learned my Maths in Bulgarian, not in English, so the terms and notation I used might seem "wrong" in English, since I'm translating them literally. Sorry. Corrections are welcome.
0.(9) is the way we denote the infinitely periodical fraction 0.99999999...
don't get it...
Mark Watton, September 23, 2005 - 6:27 am UTC
Wasn't the original question why does floor(1/9*9) = 0 but floor(2/9*9) = 2?
September 23, 2005 - 9:24 am UTC
yes, and the answer is - rounding, these are imprecise numbers, computers do not accurately represent numbers with infinite precision.
Another strange strange behavior
Kamran, September 23, 2005 - 6:52 am UTC
For original question, i learn:
Do multiplication first than divide. e.g. floor(9*1)/9.
A now my question :- What happens when we type doc or any string or sentense starting with 'doc' on the sql-plus console.
September 23, 2005 - 9:33 am UTC
doc(ument) is the old way to do a comment.
ops$tkyte@ORA10GR1> doc
DOC>hello
DOC>there
DOC>#
ops$tkyte@ORA10GR1>
# ends the document command
The last math post from me
Andrew, September 23, 2005 - 11:32 am UTC
How about proof by contradiction. . .
Assume that 1 > 0.(9).
Then there must be some number j > 0, such that
0.(9) + j = 1
Since 0 < j < 1, j must look something like
0.000...000n...
That is, a number of zeros (call it 'm') followed by a
non-zero integer (call it 'n'), then followed by other
less-significant digits.
To simplify matters, consider the number k, where
-(m+1)
k = 1 x 10 (k = 0.000...01)
k <= j, so 0 < 0.(9) + k <= 0.(9) + j = 1.
Now, let's add 0.(9) and k:
0.999...99999...
0.000...001
----------------
1.000...00099...
A number that is > 1. But this contradicts the assertion
that 0.(9) + k <= 1. Therefore, our original assumption
was false, and
1 <= 0.(9)
We can probably rule out 1 < 0.(9) without a rigorous
argument (at least in this forum), so the only possible
conclusion is
1 = 0.(9)
***
This makes me happy, because I wouldn't want to think,
in a world not bound by digits of precision, that
1 / 9 * 9
is somehow different than
1 * 9 / 9
***
Apologies for the sledgehammer approach, and I'll let
things return to all topics Oracle.
September 23, 2005 - 8:20 pm UTC
...This makes me happy, because I wouldn't want to think,
in a world not bound by digits of precision, that
1 / 9 * 9
is somehow different than
1 * 9 / 9.....
but that is the problem. In a world bound by digits of precision - they can be.
The link a couple above, or the one I've included in my new book:
</code>
http://docs.sun.com/source/806-3568/ncg_goldberg.html <code>
is a good read.
Is there really a problem?
Andrew Max, September 23, 2005 - 1:56 pm UTC
Hi Andrew
> How about proof by contradiction...
> Assume that 1 > 0.(9).
> ...
Nice proof. But I can propose the shorter one:
1. x = 0.9999...
2. 10*x = 9.9999...
3. 10*x - x = 9.9999... - 0.9999...
4. 9*x = 9
5. x = 1
Therefore, 0.9999... = 1 ;)
And now to be serious -- I don't see what the problem with FLOOR is. And I have no idea why it's claimed that there is some FLOOR bug.
Consider another example:
SQL> select log(2, 4) lg24, floor(log(2, 4)) floor_lg24 from dual;
LG24 FLOOR_LG24
---------- ----------
2 1
SQL> select log(3, 9) lg39, floor(log(3, 9)) floor_lg39 from dual;
LG39 FLOOR_LG39
---------- ----------
2 2
So -- where the bug is? In LOG? Or in FLOOR? Or may be in both of them? ;)
I think the correct answer is "nowhere". This is not a bug, rather it is a normal behavior of imprecise NUMBER arithmetic.
No one should expect 100% accuracy here. That's my opinion.
Best regards --
Andrew.
September 23, 2005 - 8:39 pm UTC
concurr