In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function.
I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic).
To limit it to the minimum, I noticed that, in Oracle, 1/3*3 is not 1 !
SQL> select * from dual where 1/3*3=1
no rows selected.
Let me tell you, this was unexpected. Ok, on my first pocket calculator 30 years ago, I noticed than 1/3=.3333333 and .3333333*3=.9999999. But since then, I used to expect 1.
perl :
$ perl -e 'print 1 if 1/3*3==1'
1
OK, I tried in java
public class X {
public static void main(String[] args){
if (1/3*3<1) {
System.out.println("<\n");
}
}
}
$ javac X.java
$ java X
<
In java it seems different too.
So what is 1/3*3 in Oracle?
SQL> set numw 50
SQL> select 1/3*3 from dual;
1/3*3
--------------------------------------------------
.9999999999999999999999999999999999999999
Surprised?
surprised? no, not really. given the precision of numbers and operator order i would be surprised if Oracle returned 1.
Fortunately, 10=10, and with the inflation nowadays, that’s not bad 🙂
SQL> select 10/30*30,1/3*3 from dual;
10/30*30
————————————————–
1/3*3
————————————————–
10
.9999999999999999999999999999999999999999
What about my situation ?
Connected to:
Oracle Database 10g Release 10.2.0.3.0 – Production
SQL> select 10/30*30,1/3*3 from dual;
10/30*30 1/3*3
———- ———-
10 1
SQL> select * from dual where 1/3*3=1
2 ;
no rows selected
SQL> select 1/3*3 from dual
2 ;
1/3*3
———-
1
results are same on windows and unix for 10.2.0.3 and 10.2.0.2
Any explanation ?
it is set numw 50 setting
SQL> set numw 50
SQL> select 10/30*30,1/3*3 from dual;
10/30*30 1/3*3
————————————————– ————————————————–
10 .9999999999999999999999999999999999999999
Thank god 🙂
tony@WQDEV> set numformat 999,999,999.9999999999
tony@WQDEV> set numwidth 50
tony@WQDEV> select 1/3*3 from dual;
1/3*3
———————–
1.0000000000
1 row selected.
Elapsed: 00:00:00.00
tony@WQDEV> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
PL/SQL Release 11.1.0.7.0 – Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production
SQL> select * from dual where 1/3*3=1;
no rows selected
SQL> select * from dual where 1/3*3*2=2;
D
–
X
SQL> set pages 100
SQL> select level n, case when 1/level*level=1 then ‘X’ end ok from dual connect by level<=20;
N O
———- –
1 X
2 X
3
4 X
5 X
6 X
7 X
8 X
9
10 X
11
12
13
14
15 X
16 X
17 X
18 X
19
20 X
20 rows selected.
Very strange…. looks like a big bug, no ?
1/3*3
———————
0,9999999999999999999999999999999999999999
1 rows selected
3*1/3
—–
1
1 rows selected
3/3*1
—–
1
Great to see so many math fanatics among my reader!
Leo, no it is not a bug, 3*1=3, 3/3=1, 1/3=.333…
It is about rounding. But it really puzzled me 😈
Fun isn’t it.
Had a similar issue when a PL/SQL program and a Java program were coming out with different results for the same calculation. In that case it seemed to be the divide by 12 (months in a year).
Try explaining to a business person that both different results were ‘right’.
Using BINARY_FLOAT / BINARY_DOUBLE as the PL/SQL datatype, instead of NUMBER, got them to agree.
3*(1/3)
———————
0,9999999999999999999999999999999999999999
1 rows selected
3*1/3
—–
1
?!
you can use binary_float or binary_double in which case you get “better” results:
SQL> select ‘ok’ from dual where 3*cast(1/3 as binary_float)=1;
‘O
—
ok
SQL> select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level select count(*), count(ok) from
2 (
3 select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level select count(*), count(ok) from
2 (
3 select level n, case when cast(1/level as binary_double)*level=1 then ‘X’ end ok from dual connect by level<=1000
4 );
COUNT(*) COUNT(OK)
———- ———-
1000 918
SQL> select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level select count(*), count(ok) from
2 (
3 select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level<=1000
4 );
COUNT(*) COUNT(OK)
———- ———-
1000 865
Use round when doing division to avoid leaving this operation to chance.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
SQL> select * from dual where 1/3*3=1;
no rows selected
SQL> select * from dual where 1/3*3*2=2;
D
–
X
SQL> select 1/3*3 from dual;
1/3*3
———-
1
SQL> select 1/3*3*2 from dual;
1/3*3*2
———-
2
SQL> select round(1/3*3) from dual;
ROUND(1/3*3)
————
1
SQL> select * from dual where round(1/3*3)=1;
D
–
X
Pingback: Little known but useful facts about Oracle « Klein Oracle denkraam
Hi Laurent,
I think there is only one way in order to right this test correctly (in any programming language)
Select * from dual where abs(1/3*3 – 1) < 1.E06
This quite funny you write this because it is exactly the kind of code that made me write the article.
what ever you chose 1e6, 1e-6, or whatever, it will not solve the case 1/n*n=1 because n could be huge or tiny.
I had exactly that piece of code where the difference was check at the 6 decimal point, kind of round(m/n,6)…
Laurent,
Sorry but I don’t get your point. If you want to compare two floating point values there is only one way to do it. The problem is not in the errors about rounding but in the inherently error of the representation of the floating point values. The choice of the value 1.E-06 or whatever (1.E06 was my mistake, I was in a hurry) depends on the nature of the application. And I think that in fact what you try to compare is not 1/n*n = 1 but 1/n*n = 1.0.
The choice of the value 1.E-06 or whatever (1.E06 was my mistake, I was in a hurry) depends on the nature of the application
that is quite true, and to give an example I am working on foreign exchange. So when comparing currency1 to currency2 using rate, the code may look like :
if round(currency1*rate,6)=round(currency2,6) then do something
And for most currencies like dollar, euros, chf, 6 positions after the points are more than enough… But please note that trading one million dollar in ZWD will be in trillions !
Therefore I had a hot discussion here to review the current comparison mechanism…
My two cents !
On a related topic – this is why you should avoid using number as a column type, but choose the explicit number(p,s) form; otherwise you could (a) run into lots of reporting errors and (b) waste space when you store the value 1 (two bytes if stored in number(6,0) say) by generating 1/3 * 3 (21 bytes as Oracle if you store it in number).
Design design…
The issue I am currently facing is that I have 2 currencies, CCY1 and CCY2, and a rate RATE. I need to figure out if CCY1=CCY2*RATE or CCY2=CCY1*RATE because the external sources of our systems provide messy datas…
The current code who check if round(CCY1,6)=round(CCY2*RATE,6) is innacurate. But due to the unreliable data source, there are mistakes if 1CHF~=1USD or when buying for 2 cents of gold …
Pingback: Laurent Schneider » How many decimals do you need?
SELECT TO_NUMBER(1/3*3) FROM dual
–this gives 1, so it might be easier to do explicit casting (not even rounding)
This works by accident. Actually it shouldn’t work. And it has implicit casting TO_NUMBER(TO_CHAR(1/3*3)) and buggy rounding.
According to the doc,If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. .
SQL> select to_char(.9999999999999999999999999999999999999999,'FM9.99999999999999999999999999999999999999999999999') from dual;
TO_CHAR(.9999999999999999999999999999999999999999,
--------------------------------------------------
.9999999999999999999999999999999999999999
SQL> select to_char(.9999999999999999999999999999999999999999) from dual;
T
-
1
SQL> select TO_NUMBER(to_char(1/3*3,'FM9.99999999999999999999999999999999999999999999999')) from dual;
TO_NUMBER(TO_CHAR(1/3*3,'FM9.99999999999999999999999999999999999999999999999'))
-------------------------------------------------------------------------------
.9999999999999999999999999999999999999999