CODEWITHSUNDEEP
sqloracle-databasecastingrounding
Simon Righley
Simon Righley

Reputation: 4969

Oracle SQL rounding misbehavior

I encountered a strange behavior of the binary_double rounding with Oracle SQL. binary_double values should be rounded half even according to the documentation, but when testing this with the following queries, it seems that there are some inconsistencies. All the queries below should give the same last digit, respectively, i.e., 0.x00008 and 0.x00006 (rounded to 6 digits) or 0.x0008 and 0.x0006 (rounded to 5 digits) with x in (0,1,2,3,4,5,6,7,8,9). Problem is that they do not. Any help on understanding why the rounding results depend on the first digit after the seperator point and/or the number of digits in the original number is kindly appreciated.

select 1,(round( cast (0.0000075 as binary_double ) ,6)), (round( cast (0.0000065 as binary_double ) ,6)) from dual
  union
  select 2,(round( cast (0.1000075 as binary_double ) ,6)), (round( cast (0.1000065 as binary_double ) ,6)) from dual
  union
  select 3,(round( cast (0.2000075 as binary_double ) ,6)), (round( cast (0.2000065 as binary_double ) ,6)) from dual
  union
  select 4,(round( cast (0.3000075 as binary_double ) ,6)), (round( cast (0.3000065 as binary_double ) ,6)) from dual
  union
  select 5,(round( cast (0.4000075 as binary_double ) ,6)), (round( cast (0.4000065 as binary_double ) ,6)) from dual
  union
  select 6,(round( cast (0.5000075 as binary_double ) ,6)), (round( cast (0.5000065 as binary_double ) ,6)) from dual
  union
  select 7,(round( cast (0.6000075 as binary_double ) ,6)), (round( cast (0.6000065 as binary_double ) ,6)) from dual
  union
  select 8,(round( cast (0.7000075 as binary_double ) ,6)), (round( cast (0.7000065 as binary_double ) ,6)) from dual
  union
  select 9,(round( cast (0.8000075 as binary_double ) ,6)), (round( cast (0.8000065 as binary_double ) ,6)) from dual
  union
  select 10,(round( cast (0.9000075 as binary_double ) ,6)), (round( cast (0.9000065 as binary_double ) ,6)) from dual
  union
  select 11,(round( cast (0.000075 as binary_double ) ,5)), (round( cast (0.000065 as binary_double ) ,5)) from dual
  union
  select 12,(round( cast (0.100075 as binary_double ) ,5)), (round( cast (0.100065 as binary_double ) ,5)) from dual
  union
  select 13,(round( cast (0.200075 as binary_double ) ,5)), (round( cast (0.200065 as binary_double ) ,5)) from dual
  union
  select 14,(round( cast (0.300075 as binary_double ) ,5)), (round( cast (0.300065 as binary_double ) ,5)) from dual
  union
  select 15,(round( cast (0.400075 as binary_double ) ,5)), (round( cast (0.400065 as binary_double ) ,5)) from dual
  union
  select 16,(round( cast (0.500075 as binary_double ) ,5)), (round( cast (0.500065 as binary_double ) ,5)) from dual
  union
  select 17,(round( cast (0.600075 as binary_double ) ,5)), (round( cast (0.600065 as binary_double ) ,5)) from dual
  union
  select 18,(round( cast (0.700075 as binary_double ) ,5)), (round( cast (0.700065 as binary_double ) ,5)) from dual
  union
  select 19,(round( cast (0.800075 as binary_double ) ,5)), (round( cast (0.800065 as binary_double ) ,5)) from dual
  union
  select 20,(round( cast (0.900075 as binary_double ) ,5)), (round( cast (0.900065 as binary_double ) ,5)) from dual;

The bottom line, is this: Why in the following query, there is a difference between the two values:

SELECT (round( CAST (0.0000065 AS BINARY_DOUBLE ) ,6)), (round( cast (0.1000065 as binary_double ) ,6)) FROM dual;

Following @zerkms advice, I convert the numbers into binary format, and I get:

0.0000065 -> 6.49999999999999959998360846147E-6
0.1000065 -> 1.00006499999999998173905169097E-1

The query rounds this up to 6 digits. Surprisingly, for me, I see that the rounding results in:

0.0000065 -> 0.000006 (execute the query above to see this)
0.1000065 -> 0.100007 (execute the query above to see this)

Why is that? I could understand, if I'd try round to >12 digits, where the series of digits in the binary representation start to differ, but how come the difference becomes visible on such early stage?

Upvotes: 5

Views: 4141

Answers (3)

Cedric Simon
Cedric Simon

Reputation: 4659

You should better use the DECIMAL data type to avoid rounding issues.

More info here : http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj15260.html

Try this one:

select 1,round(cast (0.0000075 as decimal(15,7)),6), round(cast (0.0000065 as decimal(15,7)),6) from dual;

Since I have no Oracle database installed, I could not test it but it should work.

One important remark: if decimal has a scale smaller than the actual number, exceeding decimals will be truncated. So you might want to cast to decimal (17,8) for more security.

Upvotes: 1

zerkms
zerkms

Reputation: 254916

Let's have a look at the first example, since others are pretty much the similar:

The 0.0000075 in double precision IEEE 754 is presented as 7.50000000000000019000643072808E-6

The 0.0000065 is presented as 6.49999999999999959998360846147E-6

When you round both by 6 - the former becomes 8e-6, the latter 6e-6

There is no "consistent" behaviour, because different numbers are factorized to divisors of 2 differently.

So, even though when you do SELECT 0.0000065 FROM DUAL and see 0.0000065 as a result - it's not how it's represented internally in a binary form, it's already "broken" and less than that number by a tiny fraction. Then it's rounded for you during output formatting.

The double IEEE 754 provides 15-16 significant digits. So for output purposes they become: 7.500000000000000e-6 and 6.499999999999999e-6 which is rounded to 6.5e-6

UPD:

6.49999999999999959998360846147E-6 == 0.00000649999999999999959998360846147. If you round it by 6 - it equals to 0.000006, because it's followed by 4 which is less than 5

1.00006499999999998173905169097E-1 == 0.100006499999999998173905169097 is rounded by 6 to 0.100006, because the next digit is 4, that is less than 5. And I see the difference with actual result. And honestly I don't have a good explanation here. I suspect it's an oracle issue, since:

UPD 2:

after even more research with fellows from a skype chat I've been given a good example that the result depends on the rounding mode chosen:

flock.core> (import '[org.apache.commons.math3.util Precision])

flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_CEILING)
0.100007
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_DOWN)
0.100006
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_UP)
0.100007
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_HALF_DOWN)
0.100006
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_HALF_EVEN)
0.100006
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_HALF_UP)
0.100007
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_FLOOR)
0.100006

Conclusion:

there is no "correct" or "incorrect" result in this case, they are all correct and strongly depend on the implementation (+ options you use when you perform arithmetic operations).

References:

Upvotes: 5

Patrick Hofman
Patrick Hofman

Reputation: 156978

That is because binary_double is a floating point data type, and isn't always (or always isn't) accurate.

See this related article from Oracle on floating point data types.

Upvotes: 0

Related Questions