Bug #42154 PROCEDURE ANALYSE() recommends FLOAT for DOUBLE fields with very small values

• Submitted: 16 Jan 2009 11:17
• Modified: 16 Jan 2009 11:50
• Reporter: Mathias Walter
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.67, 5.0.74, 5.1.30, 6.0.8
• OS: Any
• CPU Architecture: Any
• Tags: FLOAT, optimisation, PROCEDURE ANALYSE

[16 Jan 2009 11:17] Mathias Walter

Description:
PROCEDURE ANALYSE() recommends FLOAT for DOUBLE fields with very small values, i. e. 5.7e-301.

If the field is than changed to FLOAT, the values become truncated and will be 0.

How to repeat:
CREATE TABLE test(score DOUBLE);
INSERT INTO test VALUES(5.7e-301);

SELECT * FROM test PROCEDURE ANALYSE(3)\G
*************************** 1. row ***************************
             Field_name: test.score
              Min_value: 5.7e-301
              Max_value: 5.7e-301
             Min_length: 8
             Max_length: 8
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 5.7e-301
                    Std: 0
      Optimal_fieldtype: FLOAT NOT NULL

Suggested fix:
Do not recommend field types which would result in a data loss.

[16 Jan 2009 11:50] Valeriy Kravchuk

Thank you for a bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test(score DOUBLE);
Query OK, 0 rows affected (0.47 sec)

mysql> INSERT INTO test VALUES(5.7e-301);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test PROCEDURE ANALYSE(3)\G
*************************** 1. row ***************************
             Field_name: test.test.score
              Min_value: 5.7e-301
              Max_value: 5.7e-301
             Min_length: 8
             Max_length: 8
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 5.7e-301
                    Std: 0
      Optimal_fieldtype: FLOAT NOT NULL
1 row in set (0.03 sec)

mysql> select * from test;
+----------+
| score    |
+----------+
| 5.7e-301 |
+----------+
1 row in set (0.02 sec)

mysql> alter table test modify score float not null;
Query OK, 1 row affected (0.92 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-------+
| score |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO test VALUES(5.7e-301);
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+-------+
| score |
+-------+
|     0 |
|     0 |
+-------+
2 rows in set (0.00 sec)