Laurent Schneider

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

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

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

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.

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).