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;