Description:
Hi Team ,
I want to create a range based partition based on user defined function .
Details :
I have a table , design of table is below
CREATE TABLE Parttest (
id bigint(20) NOT NULL AUTO_INCREMENT,
datetime1 BIGINT ,
PRIMARY KEY (id,datetime1)
) ENGINE=InnoDB
In datetime1 column contain unix timestamp value .
now i want to create a range based partition on this table , when i am using FROM_UNIXTIME with month function below is the details :
PARTITION BY RANGE(MONTH(DATE_FORMAT(FROM_UNIXTIME(datetime1), '%Y-%m-%d ') ))
(
PARTITION p_JAN VALUES LESS THAN(2)
);
it's throws error
So I create a function below is the function body
DROP FUNCTION IF EXISTS func1;
DELIMITER $$
CREATE FUNCTION func1(dt1 bigint)
RETURNS INT READS SQL DATA
BEGIN
DECLARE mn2 INT ;
DECLARE dd DATE ;
-- SET dd =
select DATE_FORMAT(FROM_UNIXTIME(dt1), '%Y-%m-%d') into dd ;
-- select dd from DUAL;
set mn2 = MONTH(dd) ;
RETURN (mn2) ;
END;
$$
DELIMITER ;
Now I want to call this function in range partition , below is the query .
PARTITION BY RANGE( func1(datetime1 ) )
(
PARTITION p_JAN VALUES LESS THAN(2)
);
it throws error :
[Err] 1064 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')
(
I just want to know whether i can call a user defined function in partition or not ?
How to repeat:
Hi Team ,
I want to create a range based partition based on user defined function .
Details :
I have a table , design of table is below
CREATE TABLE Parttest (
id bigint(20) NOT NULL AUTO_INCREMENT,
datetime1 BIGINT ,
PRIMARY KEY (id,datetime1)
) ENGINE=InnoDB
In datetime1 column contain unix timestamp value .
now i want to create a range based partition on this table , when i am using FROM_UNIXTIME with month function below is the details :
PARTITION BY RANGE(MONTH(DATE_FORMAT(FROM_UNIXTIME(datetime1), '%Y-%m-%d ') ))
(
PARTITION p_JAN VALUES LESS THAN(2)
);
it's throws error
So I create a function below is the function body
DROP FUNCTION IF EXISTS func1;
DELIMITER $$
CREATE FUNCTION func1(dt1 bigint)
RETURNS INT READS SQL DATA
BEGIN
DECLARE mn2 INT ;
DECLARE dd DATE ;
-- SET dd =
select DATE_FORMAT(FROM_UNIXTIME(dt1), '%Y-%m-%d') into dd ;
-- select dd from DUAL;
set mn2 = MONTH(dd) ;
RETURN (mn2) ;
END;
$$
DELIMITER ;
Now I want to call this function in range partition , below is the query .
PARTITION BY RANGE( func1(datetime1 ) )
(
PARTITION p_JAN VALUES LESS THAN(2)
);
it throws error :
[Err] 1064 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')
(
I just want to know whether i can call a user defined function in partition or not ?
