MySQL Forums
Forum List  »  Partitioning

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
Posted by: chillyroll r
Date: April 15, 2009 03:04AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
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
5766
April 15, 2009 03:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.