CODEWITHSUNDEEP
sqloracle-databaseoracle10goperator-precedence
machidyo
machidyo

Reputation: 33

Inconsistent rounding in Oracle

I calculated an amount of money in Oracle DB 10g (10.2.0.4.0 64bit), but I can't understand how some of the numbers are calculated

Set one

10000 * (1 / 30) = 333.333*
15600 * (8 / 15) = 8319.999*

Set two

7800 * (1 / 30) = 260*
23400 * (5 / 6) = 19500*

Based on set one I expected the set two calculations to result in 259.9 and 19499.9, unlike the actual results.

If 260 and 19500 are the correct results for set two I would have thought that that 15600 * (8 / 15) should result in 8320.

I was doing this in SQL, for instance:

select 15600 * (8 / 15) from dual

Upvotes: 2

Views: 250

Answers (1)

Ben
Ben

Reputation: 52863

If you're seeing this then it would appear to be a bug in 10g:

It's fine in 12c

SQL> select banner from v$version where banner like 'Oracle%';

BANNER
--------------------------------------------------------------
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

SQL> select 15600 * (8 / 15)  from dual;

15600*(8/15)
------------
        8320

and in 11.2

SQL> select banner from v$version where banner like 'Oracle%';

BANNER
--------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select 15600 * (8 / 15)  from dual;

15600*(8/15)
------------
        8320

SQL>

Oracle's operator order of precendence places addition and subtraction ahead of multiplication and division. So, I would expect these to resolve as follows:

10000 x (1 / 30) = 10000 x 0.03333* = 333.333*
15600 x (8 / 15) = 15600 x 0.5333* = 8320
7800 x (1 / 30) = 7800 x 0.03333* = 260
23400 x (5 / 6) = 23400 x 0.833333* = 19500

I disagree with OldProgrammers' comment as the implication of the results from 11.2 and 12 is that Oracle 10 is not "rounding" up correctly, if the client was at fault I would be expecting less rounding not more.

Upvotes: 1

Related Questions