Bug #47310 Feature request: allow partitioning for any function which returns an integer.
Submitted: 14 Sep 2009 12:53 Modified: 14 Sep 2009 14:22
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1.37 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[14 Sep 2009 12:53] Roel Van de Paar
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
[14 Sep 2009 12:54] Roel Van de Paar
See http://forums.mysql.com/read.php?10,281020,281020#msg-281020
[14 Sep 2009 12:55] Roel Van de Paar
Verifying as D5 (Feature Request)
[14 Sep 2009 13:26] Giuseppe Maxia
Workaround: 
use a BEFORE INSERT trigger to convert any column to an integer, using the forbidden functions.
[14 Sep 2009 14:22] Mattias Jonsson
This is a duplicate of bug#33679, and was discussed in bug#18198.