title: What are the rules MySQL uses for displaying float values?
body: <p>I'm storing values in a column defined as float. When I read back these values from the database they sometimes differ much more from the original values than I would expect (I know that float doesn't store exact values, please take a look at the example to see what I mean).</p>
  
  <p>Here's my test case:</p>
  
  <pre><code>drop table if exists  float_test;
  create table float_test (value1 float(8, 3), value2 float);
  insert into float_test values (11743.85, 11743.85);
  select mt.*, round(mt.value1, 6), round(mt.value2, 6) from float_test mt;
  </code></pre>
  
  <p>The results of the select are:</p>
  
  <pre><code>mysql&gt; select mt.*, round(mt.value1, 6), round(mt.value2, 6) from float_test mt;
  +-----------+---------+---------------------+---------------------+
  | value1    | value2  | round(mt.value1, 6) | round(mt.value2, 6) |
  +-----------+---------+---------------------+---------------------+
  | 11743.850 | 11743.8 |        11743.849609 |        11743.849609 |
  +-----------+---------+---------------------+---------------------+
  1 row in set (0.01 sec)
  </code></pre>
  
  <p>As you can see, selecting value2 results in 11743.8 whereas selecting round(value2, 6) results in a value that is much closer to the one I originally put in. Also if you</p>
  
  <pre><code>mysql&gt; select * from float_test mt where value1 = value2;
  +-----------+---------+
  | value1    | value2  |
  +-----------+---------+
  | 11743.850 | 11743.8 |
  +-----------+---------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>you can see that the values stored in value1 and value2 are actually equal. Thus I think it's just a matter of how the results are displayed. Looking through the MySQL documentation I could not find any rules that say how float values are being rounded automatically for display.<br>
  So now I have tow questions:<br>
  1. What are the rules mysql uses for displaying float values?<br>
  2. Is there a way (e.g. some configuration options) I can retrieve the value that's not  rounded to the first decimal place without modifying my table definition and without changing my select statement?</p>
  
  <p>I've tested this on mysql 4.0, 5.0 and 5.1. </p>
  
  <p>Thank you,<br>
  Stefan</p>