Bug #70769 PROCEDURE ANALYSE() gives invalid datatype for expressions
Submitted: 30 Oct 2013 8:40 Modified: 30 Oct 2013 9:19
Reporter: Justin Swanhart Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.6.10, 5.5.34, 5.6.14 OS:Any
Assigned to: CPU Architecture:Any

[30 Oct 2013 8:40] Justin Swanhart
Description:
Notice OPTIMAL_FIELDTYPE is incorrect for expressions, but works for colrefs:
mysql> select max_updates+(2<<23)-1, max_updates, max_updates+1 from mysql.user procedure analyse(1,1)\G
*************************** 1. row ***************************
             Field_name: max_updates+(2<<23)-1
              Min_value: 16777215
              Max_value: 16777215
             Min_length: 8
             Max_length: 8
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 16777215.0000
                    Std: 0.1890
      Optimal_fieldtype:  NOT NULL
*************************** 2. row ***************************
             Field_name: mysql.user.max_updates
              Min_value: 0
              Max_value: 0
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 7
                  Nulls: 0
Avg_value_or_avg_length: 0.0000
                    Std: 0.0000
      Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 3. row ***************************
             Field_name: max_updates+1
              Min_value: 1
              Max_value: 1
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.0000
                    Std: 0.0000
      Optimal_fieldtype:  NOT NULL
3 rows in set (0.00 sec

How to repeat:
run query
[30 Oct 2013 9:19] Umesh Shastry
Hello Justin,

Thank you for the report.
Verified as described.

Thanks,
Umesh