MySQL Bugs: #91203: For partitions table, deal with NULL with is mismatch with reference guide.

Bug #91203	For partitions table, deal with NULL with is mismatch with reference guide.
Submitted:	10 Jun 2018 16:02	Modified:	11 Jun 2018 7:58
Reporter:	li xichao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.6.38, 5.6.40, 5.7.22, 8.0.11	OS:	CentOS (6,5)
Assigned to:		CPU Architecture:	x86

Description:
In my database, For partitions table, deal with NULL value with is mismatch with reference guide:
when i drop left partitions, the NULL value is droped else.

and after execute ddl, the information_schema.partitions data is mismatch with actual table information.

thanks.

How to repeat:
---------------------------------------------------test case 1---------------------------------------------------
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (
    -> c1 INT,
    -> c2 VARCHAR(20))
    -> PARTITION BY RANGE(c1) (
    -> PARTITION p0 VALUES LESS THAN (-5),
    -> PARTITION p1 VALUES LESS THAN (0),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> 
mysql> select *  from t2;
Empty set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'scott' AND TABLE_NAME LIKE 't2';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t2         | p0             |          0 |              0 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO t2 VALUES (NULL, 'row1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (-8, 'row2');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2 VALUES (100, 'row3');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
| NULL | row1 |
|   -8 | row2 |
|  100 | row3 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'scott' AND TABLE_NAME LIKE 't2';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t2         | p0             |          2 |           8192 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          1 |          16384 |       16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

mysql> alter table t2 drop partition p0;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'scott' AND TABLE_NAME LIKE 't2';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
3 rows in set (0.00 sec)

mysql> 
mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
|  100 | row3 |
+------+------+
1 row in set (0.00 sec)

----------------------------------test case 2---------------------------------------------------
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (
    -> c1 INT,
    -> c2 VARCHAR(20))
    -> PARTITION BY RANGE(c1) (
    -> PARTITION p0 VALUES LESS THAN (-5),
    -> PARTITION p1 VALUES LESS THAN (0),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> select *  from t2;
Empty set (0.00 sec)

mysql> 
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'scott' AND TABLE_NAME LIKE 't2';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t2         | p0             |          0 |              0 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO t2 VALUES (-8, 'row2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (100, 'row3');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
|   -8 | row2 |
|  100 | row3 |
+------+------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'scott' AND TABLE_NAME LIKE 't2';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t2         | p0             |          1 |          16384 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          1 |          16384 |       16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

mysql> alter table t2 drop partition p0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'scott' AND TABLE_NAME LIKE 't2';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
3 rows in set (0.00 sec)

mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (c1)
(PARTITION p1 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
|  100 | row3 |
+------+------+
1 row in set (0.00 sec)

mysql>

Hello li xichao,

Thank you for the report!

Thanks,
Umesh