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 | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.0.67, 5.0.74, 5.1.30, 6.0.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | FLOAT, optimisation, PROCEDURE ANALYSE |
[16 Jan 2009 11:17]
Mathias Walter
[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)