Description:
As described in the document(see https://dev.mysql.com/doc/refman/8.0/en/partitioning-handling-nulls.html), NULL is handled as 0 in the tables partitioned by key and hash. However, according to the actual execution result, NULL is not processed as 0 in the KEY partition table.
1.We create a table partitioned by key and insert values including 0 and NULL.
mysql> CREATE TABLE tk (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY KEY(c1)
-> PARTITIONS 2;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='tk';
+------------+----------------+------------+----------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH |
+------------+----------------+------------+----------------+
| tk | p0 | 0 | 0 |
| tk | p1 | 0 | 0 |
+------------+----------------+------------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tk VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
2.Then,we check the Information Schema and specified partition to verify if NULL and 0 are in the same partition,finding that NULL is not treated as 0.
mysql> analyze table tk;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.tk | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='tk';
+------------+----------------+------------+----------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH |
+------------+----------------+------------+----------------+
| tk | p0 | 1 | 16384 |
| tk | p1 | 1 | 16384 |
+------------+----------------+------------+----------------+
2 rows in set (0.00 sec)
mysql> select * from tk partition (p0);
+------+--------+
| c1 | c2 |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> select * from tk partition (p1);
+------+-------+
| c1 | c2 |
+------+-------+
| 0 | gigan |
+------+-------+
1 row in set (0.00 sec)
How to repeat:
USE test;
CREATE TABLE tk ( c1 INT, c2 VARCHAR(20) ) PARTITION BY KEY (c1) PARTITIONS 2;
SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='tk';
INSERT INTO tk VALUES (NULL, 'mothra'), (0, 'gigan');
analyze table tk;
SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='tk';
Suggested fix:
NULL is suggested in the same partition as 0 when the table is partitioned by key.
