[MDEV-8271] Min_value from PROCEDURE ANALYSE() is wrong - JIRA

Details

Type: Bug
Status: Confirmed
Priority: Minor
Resolution: Unresolved
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 10.1, 10.0, 5.5
Fix Version/s: 10.0
Component/s: Admin statements
Labels:
- upstream
Environment:
CentOS release 6.5 (X86_64)

Description

The Min_value returned from PROCEDURE ANALYSE() is false.
This problem is also in MySQL but is not reported anywhere yet.

For column `T_buy_1y` which has values 0 and 1,
Optimal_fieldtype is correctly calculated ENUM('0','1') NOT NULL while the Min_value is 1.

I created a test case with CSV data to import.

[root@kc0022 win]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.0.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> DROP TABLE IF EXISTS table256;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE table256 (
    ->   N_O_data_id int(10) unsigned NOT NULL default '0',
    ->   E_OA_recency int(11) default NULL,
    ->   T_buy_1y int(11) default NULL,
    ->   T_buy_6m int(11) default NULL,
    ->   T_buy_2m int(11) default NULL,
    ->   PRIMARY KEY  (N_O_data_id)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=cp932;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> LOAD DATA INFILE '/win/table256.csv' INTO TABLE table256 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 300000 rows affected (2.10 sec)
Records: 300000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> SELECT T_buy_1y, COUNT(*) FROM table256 GROUP BY T_buy_1y;
+----------+----------+
| T_buy_1y | COUNT(*) |
+----------+----------+
|        0 |   220579 |
|        1 |    79421 |
+----------+----------+
2 rows in set (0.14 sec)

MariaDB [test]> SELECT T_buy_1y FROM table256 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.table256.T_buy_1y | 1         | 1         |          1 |          1 |           220579 |     0 | 0.2647                  | 0.4412 | ENUM('0','1') NOT NULL |
+------------------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+------------------------+
1 row in set (0.06 sec)

In addition:

It seems to happen when the first data is not 0.
Please look at the below test.
Row `T_buy_2m` returns the correct Min_value because the first value read is a 0.

Altering the table to change the first value of row `T_buy_2m` effects the results of PROCEDURE ANAYSE().

[root@kc0022 ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.0.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> DROP TABLE IF EXISTS table257;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE table257 (
    ->   N_O_data_id int(10) unsigned NOT NULL default '0',
    ->   E_OA_recency int(11) default NULL,
    ->   T_buy_1y int(11) default NULL,
    ->   T_buy_6m int(11) default NULL,
    ->   T_buy_2m int(11) default NULL,
    ->   PRIMARY KEY  (N_O_data_id)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=cp932;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> LOAD DATA INFILE '/win/table257.csv' INTO TABLE table257 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 300000 rows affected (1.46 sec)
Records: 300000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> SELECT T_buy_1y, COUNT(*) FROM table257 GROUP BY T_buy_1y;
+----------+----------+
| T_buy_1y | COUNT(*) |
+----------+----------+
|        0 |   220579 |
|        1 |    79421 |
+----------+----------+
2 rows in set (0.14 sec)

MariaDB [test]> SELECT T_buy_6m, COUNT(*) FROM table257 GROUP BY T_buy_6m;
+----------+----------+
| T_buy_6m | COUNT(*) |
+----------+----------+
|        0 |   236346 |
|        1 |    63654 |
+----------+----------+
2 rows in set (0.13 sec)

MariaDB [test]> SELECT T_buy_2m, COUNT(*) FROM table257 GROUP BY T_buy_2m;
+----------+----------+
| T_buy_2m | COUNT(*) |
+----------+----------+
|        0 |   275832 |
|        1 |    24168 |
+----------+----------+
2 rows in set (0.13 sec)

MariaDB [test]> SELECT * FROM table257 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.table257.N_O_data_id  | 1         | 300000    |          1 |          6 |                0 |     0 | 150000.5000             | 173337.8797 | MEDIUMINT(6) UNSIGNED NOT NULL |
| test.table257.E_OA_recency | 1         | 1839      |          1 |          4 |              807 |     0 | 936.2592                | 639.5488    | SMALLINT(4) UNSIGNED NOT NULL  |
| test.table257.T_buy_1y     | 1         | 1         |          1 |          1 |           220579 |     0 | 0.2647                  | 0.4412      | ENUM('0','1') NOT NULL         |
| test.table257.T_buy_6m     | 1         | 1         |          1 |          1 |           236346 |     0 | 0.2122                  | 0.4089      | ENUM('0','1') NOT NULL         |
| test.table257.T_buy_2m     | 0         | 1         |          1 |          1 |           275832 |     0 | 0.0806                  | 0.2722      | ENUM('0','1') NOT NULL         |
+----------------------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+
5 rows in set (0.15 sec)

MariaDB [test]> ALTER TABLE table257 ORDER BY T_buy_2m DESC;
Query OK, 300000 rows affected (0.90 sec)
Records: 300000  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM table257 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.table257.N_O_data_id  | 1         | 300000    |          1 |          6 |                0 |     0 | 150000.5000             | 173337.8797 | MEDIUMINT(6) UNSIGNED NOT NULL |
| test.table257.E_OA_recency | 1         | 1839      |          1 |          4 |              807 |     0 | 936.2592                | 639.5488    | SMALLINT(4) UNSIGNED NOT NULL  |
| test.table257.T_buy_1y     | 1         | 1         |          1 |          1 |           220579 |     0 | 0.2647                  | 0.4412      | ENUM('0','1') NOT NULL         |
| test.table257.T_buy_6m     | 1         | 1         |          1 |          1 |           236346 |     0 | 0.2122                  | 0.4089      | ENUM('0','1') NOT NULL         |
| test.table257.T_buy_2m     | 1         | 1         |          1 |          1 |           275832 |     0 | 0.0806                  | 0.2722      | ENUM('0','1') NOT NULL         |
+----------------------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+
5 rows in set (0.16 sec)

MariaDB [test]> ALTER TABLE table257 ORDER BY T_buy_2m ASC;
Query OK, 300000 rows affected (0.88 sec)
Records: 300000  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM table257 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.table257.N_O_data_id  | 1         | 300000    |          1 |          6 |                0 |     0 | 150000.5000             | 173337.8797 | MEDIUMINT(6) UNSIGNED NOT NULL |
| test.table257.E_OA_recency | 1         | 1839      |          1 |          4 |              807 |     0 | 936.2592                | 639.5488    | SMALLINT(4) UNSIGNED NOT NULL  |
| test.table257.T_buy_1y     | 1         | 1         |          1 |          1 |           220579 |     0 | 0.2647                  | 0.4412      | ENUM('0','1') NOT NULL         |
| test.table257.T_buy_6m     | 1         | 1         |          1 |          1 |           236346 |     0 | 0.2122                  | 0.4089      | ENUM('0','1') NOT NULL         |
| test.table257.T_buy_2m     | 0         | 1         |          1 |          1 |           275832 |     0 | 0.0806                  | 0.2722      | ENUM('0','1') NOT NULL         |
+----------------------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+
5 rows in set (0.15 sec)

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

table256.csv
7.72 MB
2015-06-05 08:17
table257.csv
7.72 MB
2015-06-09 07:30
test256.sql
0.5 kB
2015-06-05 08:19
test257.sql
0.8 kB
2015-06-09 13:37

Issue Links

links to

Bug #77299 - Inconsistent behavior of PROCEDURE ANALYSE for 0 in Min_value

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-06-05 15:27

I think it might be intentional, because zeros are counted separately in the Empties_or_zeros column; but I haven't found anything in either MySQL manual or MariaDB KB to confirm that, so I will go for an expert opinion.

Sergei Golubchik,
Can you confirm (or deny) that it's intentional to ignore 0 as Min_value or Max_value in PROCEDURE ANALYSE output?

Show

Elena Stepanova added a comment - 2015-06-05 15:27 I think it might be intentional, because zeros are counted separately in the Empties_or_zeros column; but I haven't found anything in either MySQL manual or MariaDB KB to confirm that, so I will go for an expert opinion. Sergei Golubchik , Can you confirm (or deny) that it's intentional to ignore 0 as Min_value or Max_value in PROCEDURE ANALYSE output?

5 older comments

Hide

Permalink

Elena Stepanova added a comment - 2015-06-09 19:37

I agree, I don't think there is a good excuse for this.
Here is a simpler test case which demonstrates the same problem:

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;

Actual output

MariaDB [test]> create table t1 (i int);
Query OK, 0 rows affected (0.87 sec)

MariaDB [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  | NULL      | NULL      |          0 |          0 |                0 |     0 | 0.0                     | 0.0  | CHAR(0) NOT NULL  |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+-------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t1 values (1);
Query OK, 1 row affected (0.10 sec)

MariaDB [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 |                0 |     0 | 1.0000                  | 0.0000 | ENUM('1') NOT NULL |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t1 values (0);
Query OK, 1 row affected (0.02 sec)

MariaDB [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.01 sec)

MariaDB [test]> create table t1 (i int);
Query OK, 0 rows affected (0.78 sec)

MariaDB [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  | NULL      | NULL      |          0 |          0 |                0 |     0 | 0.0                     | 0.0  | CHAR(0) NOT NULL  |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+-------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t1 values (0);
Query OK, 1 row affected (0.04 sec)

MariaDB [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         | 0         |          1 |          1 |                1 |     0 | 0.0000                  | 0.0000 | ENUM('0') NOT NULL |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t1 values (1);
Query OK, 1 row affected (0.04 sec)

MariaDB [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)

While not taking into account 0 always looks like a very questionable design decision, here we see that zeros are ignored in some cases but not another.

Takuya Aoki,
As you said before, it's the same with MySQL (as far as I can see, all of 5.1-5.7 at least). Are you planning to report it at bugs.mysql.com?

For a note, another curious inconsistency:

MariaDB [test]> drop table t1;
Query OK, 0 rows affected (0.26 sec)

MariaDB [test]> create table t1 (i int) engine=InnoDB;
Query OK, 0 rows affected (0.71 sec)

MariaDB [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  | NULL      | NULL      |          0 |          0 |                0 |     0 | 0.0                     | 0.0  | CHAR(0) NOT NULL  |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+-------------------+
1 row in set (0.00 sec)

MariaDB [test]> drop table t1;
Query OK, 0 rows affected (0.41 sec)

MariaDB [test]> create table t1 (i int) engine=MyISAM;
Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> select * from t1 procedure analyse();
Empty set (0.00 sec)

That is, for an empty InnoDB table it returns rows, while for MyISAM it's an empty set.

Show

Elena Stepanova added a comment - 2015-06-09 19:37 I agree, I don't think there is a good excuse for this. Here is a simpler test case which demonstrates the same problem: 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; Actual output MariaDB [test]> create table t1 (i int); Query OK, 0 rows affected (0.87 sec) MariaDB [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 | NULL | NULL | 0 | 0 | 0 | 0 | 0.0 | 0.0 | CHAR(0) NOT NULL | +------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+-------------------+ 1 row in set (0.00 sec) MariaDB [test]> insert into t1 values (1); Query OK, 1 row affected (0.10 sec) MariaDB [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 | 0 | 0 | 1.0000 | 0.0000 | ENUM('1') NOT NULL | +------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+--------------------+ 1 row in set (0.00 sec) MariaDB [test]> insert into t1 values (0); Query OK, 1 row affected (0.02 sec) MariaDB [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.01 sec) MariaDB [test]> create table t1 (i int); Query OK, 0 rows affected (0.78 sec) MariaDB [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 | NULL | NULL | 0 | 0 | 0 | 0 | 0.0 | 0.0 | CHAR(0) NOT NULL | +------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+-------------------+ 1 row in set (0.00 sec) MariaDB [test]> insert into t1 values (0); Query OK, 1 row affected (0.04 sec) MariaDB [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 | 0 | 1 | 1 | 1 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL | +------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+--------+--------------------+ 1 row in set (0.00 sec) MariaDB [test]> insert into t1 values (1); Query OK, 1 row affected (0.04 sec) MariaDB [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) While not taking into account 0 always looks like a very questionable design decision, here we see that zeros are ignored in some cases but not another. Takuya Aoki , As you said before, it's the same with MySQL (as far as I can see, all of 5.1-5.7 at least). Are you planning to report it at bugs.mysql.com? For a note, another curious inconsistency: MariaDB [test]> drop table t1; Query OK, 0 rows affected (0.26 sec) MariaDB [test]> create table t1 (i int) engine=InnoDB; Query OK, 0 rows affected (0.71 sec) MariaDB [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 | NULL | NULL | 0 | 0 | 0 | 0 | 0.0 | 0.0 | CHAR(0) NOT NULL | +------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+-------------------+ 1 row in set (0.00 sec) MariaDB [test]> drop table t1; Query OK, 0 rows affected (0.41 sec) MariaDB [test]> create table t1 (i int) engine=MyISAM; Query OK, 0 rows affected (0.15 sec) MariaDB [test]> select * from t1 procedure analyse(); Empty set (0.00 sec) That is, for an empty InnoDB table it returns rows, while for MyISAM it's an empty set.

Hide

Permalink

Takuya Aoki added a comment - 2015-06-10 06:17

I didn't know about the second case where the table is empty.
I don't plan to report it to bugs.mysql.com since I use MariaDB now.

Show

Takuya Aoki added a comment - 2015-06-10 06:17 I didn't know about the second case where the table is empty. I don't plan to report it to bugs.mysql.com since I use MariaDB now.

Hide

Permalink

Elena Stepanova added a comment - 2015-06-10 17:08

Okay, I filed one on your behalf (http://bugs.mysql.com/bug.php?id=77299).

Show

Elena Stepanova added a comment - 2015-06-10 17:08 Okay, I filed one on your behalf ( http://bugs.mysql.com/bug.php?id=77299 ).

Hide

Permalink

Takuya Aoki added a comment - 2015-06-11 06:01

Thank you, will it be fixed in MySQL beforehand?

Show

Takuya Aoki added a comment - 2015-06-11 06:01 Thank you, will it be fixed in MySQL beforehand?

Hide

Permalink

Elena Stepanova added a comment - 2015-06-11 13:31

As the normal routine goes, we will wait for a while to see if it gets fixed in the upstream version. If it does, we will merge or backport the fix. If it does not, we might consider fixing it directly in MariaDB.

Show

Elena Stepanova added a comment - 2015-06-11 13:31 As the normal routine goes, we will wait for a while to see if it gets fixed in the upstream version. If it does, we will merge or backport the fix. If it does not, we might consider fixing it directly in MariaDB.

Min_value from PROCEDURE ANALYSE() is wrong

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Agile