Partitioning throwing #1064 - Constant/Random expression in (sub)partitioning function is not allowed near ') ( PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2) PARTIT' at line 2 error

[chillyroll r]

I am trying to create some partitions to my user table which has around 8.4 million records. I want to create partitions on the state column basis which is a CHAR 2 type column.

So since partitioning needs integer values, I created following Function in my database which returns unique Integer for different state codes. I have shortened the function code to delete remaining state codes:

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`stateCode`$$

CREATE DEFINER=``@`localhost` FUNCTION `stateCode`(state CHAR) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE ret INT;
IF state LIKE 'NY' THEN SET ret = 1;
ELSEIF state LIKE 'CA' THEN SET ret = 2;
ELSE SET ret = 3;
END IF;

RETURN ret;
END$$

DELIMITER ;


Now I am trying to alter the table schema to use this function for partitioning. Below is the query I am trying:

ALTER TABLE USER
PARTITION BY LIST(stateCode(`state_code`)) (
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2)
PARTITION p3 VALUES IN (3)
PARTITION pNULL VALUES IN (NULL)
)


But it says:

#1064 - Constant/Random expression in (sub)partitioning function is not allowed near ') ( PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2) PARTIT' at line 2


Any idea on this??? Am I doing anything wrong here???

Thanks!