MySQL Bugs: #23265: Partitioning by key puts records in wrong partition

Bug #23265	Partitioning by key puts records in wrong partition
Submitted:	13 Oct 2006 15:39	Modified:	24 Oct 2006 15:57
Reporter:	Andy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.12-beta	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
When partitioning by key records will go into the wrong partition.  This also causes counts wrong.

How to repeat:
CREATE TABLE `key_test1` (
  `t` varchar(10) DEFAULT NULL
) PARTITION BY KEY (t) (PARTITION p1, 
PARTITION p2,
PARTITION p3,
PARTITION p4);

INSERT INTO `key_test1` VALUES ('try'),('try2'),('test1'),('test1'),('test1'),('test1'),('test1'),('test1'),('try3'),('try3'),('try3'),('try3'),('try4');

This demonstrates the count problem. 

insert into key_test1(t) values ('try3');
select count(*) from key_test1 where t = 'try3';
select * from key_test1;
select count(*) from key_test1 where t = 'try3';

If you look at the files for key_test1 try3 will be in two different partitions.

Suggested fix:
Since Key is a type of hash, it should be returning the same value each time, and all records for a given value (t in this case) should go into the same partition.

Suggested fix should red

Since Key is a type of hash, it should be returning the same value each time for a given input value (of t in this case), thus having the same module value and go into the same partition as the previous ones did for that value of t.

No matter what, count should always accurately return the number of rows in a table for a given criteria value.

obviously, red should be read.

Thank you for the report.

I can not repeat the issue using current BK sources:

mysql> insert into key_test1(t) values ('try3');
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from key_test1 where t = 'try3';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.02 sec)

mysql> select * from key_test1;
+-------+
| t     |
+-------+
| try2  |
| test1 |
| test1 |
| test1 |
| test1 |
| test1 |
| test1 |
| try   |
| try4  |
| try3  |
| try3  |
| try3  |
| try3  |
| try3  |
+-------+
14 rows in set (0.00 sec)

mysql> select count(*) from key_test1 where t = 'try3';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

This does seem to be fixed in the latest bk mysql versions.