Description:
Manual lists:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html
'Data type of partitioning key[:] A partitioning key must be either an integer column or an expression that resolves to an integer.'
But:
---------
mysql> CREATE TABLE `users` (`user_id` int(10) NOT NULL,`username` varchar(25) DEFAULT NULL,PRIMARY KEY (`user_id`),UNIQUE KEY `username`(`username`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `users` VALUES (1,'aaaa'),(2,'zzzz');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT ASCII('a');
+------------+
| ASCII('a') |
+------------+
| 97 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT ASCII('z');
+------------+
| ASCII('z') |
+------------+
| 122 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT ASCII('m');
+------------+
| ASCII('m') |
+------------+
| 109 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT `user_id`, ASCII(LOWER(LEFT(`username`,1))) FROM `users`;
+---------+----------------------------------+
| user_id | ASCII(LOWER(LEFT(`username`,1))) |
+---------+----------------------------------+
| 1 | 97 |
| 2 | 122 |
+---------+----------------------------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE `users` PARTITION BY RANGE (ASCII(LOWER(LEFT(`username`,1)))) (
-> PARTITION p0 VALUES LESS THAN (109), #being m
-> PARTITION p1 VALUES LESS THAN (122) #being z
-> );
ERROR 1564 (HY000): This partition function is not allowed
---------
Because:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html
Hence, this feature request (Extend/allow partitioning for any function which returns an integer).
There's no real workaround if you would like to partition based on username while keeping the primary key on user_id.
How to repeat:
As per the above
Suggested fix:
As per the above
