Bug #20305 PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D)
Submitted: 6 Jun 2006 22:23 Modified: 6 Oct 2006 2:58
Reporter: Jeremy Cole (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0, 5.1 OS:
Assigned to: Iggy Galarza CPU Architecture:Any

[6 Jun 2006 22:23] Jeremy Cole
Description:
The Optimal_fieldtype field from PROCEDURE ANALYSE() returns wrong values for M for field types FLOAT(M, D) and DOUBLE(M, D).

mysql> select * from t procedure analyse(1, 1) \G                                                                                                  
*************************** 1. row ***************************
             Field_name: test.t.f
              Min_value: 5.99900
              Max_value: 9.55500
             Min_length: 7
             Max_length: 7
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 7.77700
                    Std: 1.77800
      Optimal_fieldtype: FLOAT(1,3) NOT NULL

Note that the M argument must, logically, be larger than D..

How to repeat:
drop table if exists t;
create table t (f double(10,5));
insert into t (f) values (5.999), (9.555);
select * from t procedure analyse(1, 1) \G

Suggested fix:
The code in question is from sql/sql_analyse.cc:

>>>>>
      if (num_info.dval > -FLT_MAX && num_info.dval < FLT_MAX)
        sprintf(buff, "FLOAT(%d,%d)", num_info.integers, num_info.decimals);
      else
        sprintf(buff, "DOUBLE(%d,%d)", num_info.integers, num_info.decimals);
<<<<<

It should likely use num_info.integers+num_info.decimals+2 for the value of M.
[26 Sep 2006 17:58] Timothy Smith
OK to push, Iggy.  Thanks.
[28 Sep 2006 18:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12758

ChangeSet@1.2543, 2006-09-28 14:30:20-04:00, iggy@rolltop.ignatz42.dyndns.org +3 -0
  Bug#20305: PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D)
[3 Oct 2006 20:02] Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:09] Chad MILLER
Available in 5.1.12-beta.
[4 Oct 2006 13:57] Chad MILLER
Available in 4.1.22.
[6 Oct 2006 2:58] Paul Dubois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

Changed "decimal digits" to "digits" per Iggy's comment.