strange error handling with incorrect float definition

Hi Peter

[email protected] writes:
> Hi all,
> 
> my environment: WindowsNT4 with SP6, mysql4.0.15-max-nt
> I tried the following only for fun:
> 
> mysql> CREATE TABLE t2 (a int, b float(24,99));      -->this should give me
> an error but it doesn't!
> Query OK, 0 rows affected (0.02 sec)

MySQL does not give error, but changes the values to match the
closest possible one that was requested.

> mysql> show tables;
> +---------------+
> | Tables_in_pb1 |
> +---------------+
> | t1            |
> | t2            |
> +---------------+
> 2 rows in set (0.01 sec)
> 
> mysql> describe t2;
> +-------+--------------+------+-----+---------+-------+
> | Field | Type         | Null | Key | Default | Extra |
> +-------+--------------+------+-----+---------+-------+
> | a     | int(11)      | YES  |     | NULL    |       |
> | b     | float(31,30) | YES  |     | NULL    |       |
> -->this conversion is also very strange
> +-------+--------------+------+-----+---------+-------+

Float is defined as FLOAT(M,D). D cannot be more than M-1, M-2 is the
largest one recommended. The largest possible value for D is 30. So in
this case MySQL gave value 30 to D (requested 99) and raised the value
of M to 31, which is the minimum requirement, if D is 30.

> 2 rows in set (0.01 sec)
> 
> mysql> insert into t2 values (1, 1.234567890);
> Query OK, 1 row affected (0.00 sec)
> 
> mysql> select * from t2;
> +------+----------------------------------+
> | a    | b                                |
> +------+----------------------------------+
> |    1 | 1.234567880630493200000000000000 |
> +------+----------------------------------+
> 1 row in set (0.00 sec)
> 
> Where does this number come from? I haven't inserted it and further
> calculations with this would lead to incorrekt results!

It won't leed to incorrect results, when you use floating point
numbers correctly. For example when comparing, they must be compared
with certain accuracy, but not absolutely. Any floating point numbers
must NEVER be compared as F1=F2, the results are always unexpectable
and hardware dependent.

Floating point numbers are inaccurate. The above is the best possible
presentation for the number that you inserted when using FLOAT. If you
need better accurancy, use DOUBLE instead, but remember, that it is an
inaccurate number just as FLOAT is, only it has a better accuracy.
FLOAT is 4 bytes and DOUBLE is 8 bytes.

The reason it is trying to display so many decimals, which are clearly
out of bounds what a FLOAT can handle, is because you asked it to
display the numbers in format (31,30).

For further details about floating point accurancy issues, please
see the following MySQL manual chapter:

A.5.7 Problems with Floating-Point Comparison

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Jani Tolonen <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
       <___/   www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]