List:General Discussion« Previous MessageNext Message »
From:Jeff Mathis Date:August 5 2004 9:45pm
Subject:Re: problem with 4.1.3
View as plain text  
yes indeed. I will have to change my column definitions.

However, this behavior was not the case with 4.0.4. What seemed to be 
going on with that branch was I could have an 11 digit number, with a 
maximum of 9 digits behind the decimal. so numbers like 100493.43 were 
fine. I'm assuming that this was a bug that was then fixed, and I just 
happened to get nipped by it.

jeff
Dan Nelson wrote:
> In the last episode (Aug 05), Jeff Mathis said:
> 
>>after lokking at this, it appears that our float(11,9) columns cannot 
>>store an number larger than 100 or smaller than -100. the database is 
>>rounding the number! If we insert numbers -100 < x < 100, then its fine.
>>
>>is there a configuration setting somewhere, or is this a known bug?
> 
> 
> It's doing exactly what you asked for.
> 
>   `FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]'
>   `M' is the display width and `D' is the number of decimals.
> 
> You asked for a field 11 digits wide, with 9 of them being to the right
> of the decimal point.  11-9 = 2, so you really should only be able to
> store -99 through 99, since 100 is 3 digits and puts you over the edge. 
> So you did find a mysql bug, but not the one you thought you had :)
> 
> mysql> create table test ( f1 float(11,9) );
> Query OK, 0 rows affected (0.07 sec)
> 
> mysql> insert into test values
> (1.1234567890123),(100.1234567890123),(200.123456789);
> Query OK, 3 rows affected, 2 warnings (0.09 sec)
> Records: 3  Duplicates: 0  Warnings: 2
> 
> mysql> show warnings;
> +---------+------+-------------------------------------------------------+
> | Level   | Code | Message                                               |
> +---------+------+-------------------------------------------------------+
> | Warning | 1264 | Data truncated; out of range for column 'f1' at row 2 |
> | Warning | 1264 | Data truncated; out of range for column 'f1' at row 3 |
> +---------+------+-------------------------------------------------------+
> 2 rows in set (0.00 sec)
> 
> mysql> select * from test;
> +---------------+
> | f1            |
> +---------------+
> |   1.123456836 |
> | 100.000000000 |
> | 100.000000000 |
> +---------------+
> 3 rows in set (0.00 sec)
> 
> This odd rounding is due to the low precision of a 'float' type
> (usually around 6 decimal digits).
> 


-- 
Jeff Mathis, Ph.D.			505-955-1434
Prediction Company			jmathis@stripped
525 Camino de los Marquez, Ste 6	http://www.predict.com
Santa Fe, NM 87505

Thread
problem with 4.1.3Jeff Mathis5 Aug
  • Re: problem with 4.1.3Jeff Mathis5 Aug
    • Re: problem with 4.1.3Dan Nelson5 Aug
      • Re: problem with 4.1.3Jeff Mathis5 Aug
Re: problem with 4.1.3Jeff Mathis5 Aug