Bug #105186 a null value is not treated as zero in hash partition table

• Submitted: 11 Oct 2021 9:40
• Modified: 11 Oct 2021 10:31
• Reporter: peng chen
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S2 (Serious)
• Version: 8.0.26, 5.7.35
• OS: Any
• CPU Architecture: Any

[11 Oct 2021 9:40] peng chen

Description:
got:
mysql> select * from th partition(p0);
+------+-------+
| c1   | c2    |
+------+-------+
|    0 | gigan |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from th partition(p3);
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

expect:

mysql> select * from th partition(p0);
+------+--------+
| c1   | c2     |
+------+--------+
|    0 | gigan  |
| NULL | mothra |
+------+--------+
2 rows in set (0.01 sec)

mysql> select * from th partition(p3);
Empty set (0.00 sec)

How to repeat:
create table th(c1 int, c2 varchar(20)) partition by hash(c1) partitions 5;
INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
select * from th partition(p0);
select * from th partition(p3);

[11 Oct 2021 10:31] MySQL Verification Team

Hello peng chen,

Thank you for the report and test case.
Verified as described.

regards,
Umesh