title: Oracle SQL rounding misbehavior
body: <p>I encountered a strange behavior of the <code>binary_double</code> rounding with Oracle SQL. <code>binary_double</code> values should be rounded <code>half even</code> according to the <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm" rel="nofollow">documentation</a>, but when testing this with the following queries, it seems that there are some inconsistencies. <strong>All the queries below should give the same last digit</strong>, 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). <strong>Problem is that they do not</strong>. 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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The bottom line, is this:
  Why in the following query, there is a difference between the two values:</p>
  
  <pre><code>SELECT (round( CAST (0.0000065 AS BINARY_DOUBLE ) ,6)), (round( cast (0.1000065 as binary_double ) ,6)) FROM dual;
  </code></pre>
  
  <p>Following @zerkms advice, I <a href="http://www.binaryconvert.com/convert_double.html?decimal=048046049048048048048054053" rel="nofollow">convert</a> the numbers into binary format, and I get:</p>
  
  <pre><code>0.0000065 -&gt; 6.49999999999999959998360846147E-6
  0.1000065 -&gt; 1.00006499999999998173905169097E-1
  </code></pre>
  
  <p>The query rounds this up to 6 digits. Surprisingly, for me, I see that the rounding results in:</p>
  
  <pre><code>0.0000065 -&gt; 0.000006 (execute the query above to see this)
  0.1000065 -&gt; 0.100007 (execute the query above to see this)
  </code></pre>
  
  <p>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?</p>