Bug #77299 Inconsistent behavior of PROCEDURE ANALYSE for 0 in Min_value
Submitted: 10 Jun 2015 14:08 Modified: 26 Nov 2019 21:12
Reporter: Elena Stepanova Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6, 5.7 OS:Any
Assigned to: CPU Architecture:Any

[10 Jun 2015 14:08] Elena Stepanova
Description:
Initially reported by our user as https://mariadb.atlassian.net/browse/MDEV-8271

Test case provided in the 'How to repeat' section shows that Min_value in PROCEDURE ANALYSE depends on the order of rows in the table. 

Inserted 1, then 0:

MySQL [test]> select * from t1 procedure analyse();
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std    | Optimal_fieldtype      |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
| test.t1.i  | 1         | 1         |          1 |          1 |                1 |     0 | 0.5000                  | 0.5000 | ENUM('0','1') NOT NULL |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
1 row in set (0.00 sec)

Inserted 0, then 1:

MySQL [test]> select * from t1 procedure analyse();
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std    | Optimal_fieldtype      |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
| test.t1.i  | 0         | 1         |          1 |          1 |                1 |     0 | 0.5000                  | 0.5000 | ENUM('0','1') NOT NULL |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists t1;

create table t1 (i int);
select * from t1 procedure analyse();
insert into t1 values (1);
select * from t1 procedure analyse();
insert into t1 values (0);
select * from t1 procedure analyse();
drop table t1;

create table t1 (i int);
select * from t1 procedure analyse();
insert into t1 values (0);
select * from t1 procedure analyse();
insert into t1 values (1);
select * from t1 procedure analyse();
drop table t1;
[10 Jun 2015 15:20] MySQL Verification Team
Thank you for the bug report. Verified as described.
[26 Nov 2019 21:12] Roy Lyseng
Posted by developer:
 
PROCEDURE ANALYSE has been removed, so not reproducible.