MySQL Bugs: #19319: Deterministic Stored Function should be a legal partition function

Bug #19319	Deterministic Stored Function should be a legal partition function
Submitted:	24 Apr 2006 22:52	Modified:	30 May 2006 21:55
Reporter:	Andy	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.1.10-beta	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
If a UDF is defined as being deterministic, it should be allowed as a partition function.   Builtin functions, such as YEAR could be called a  deterministic function and it is allowed, why shouldn't a deterministic UDF be allowed.

How to repeat:
create table t1 (
        a char(1) default NULL,
        b int default NULL
);

insert into t1(a,b) values ('a',1);
insert into t1(a,b) values ('b',2);

drop function test_det_func;
delimiter //
CREATE FUNCTION test_det_func(s char(32)) RETURNS int(3) deterministic
reads sql data
begin
declare ret_num int;
select SQL_CACHE num into ret_num from t1 where a=s;
return ret_num;
end
//

delimiter ;

create table t2 (
        a char(1) default NULL,
        b int,
        c varchar(10) default NULL
)
PARTITION BY RANGE(test_det_func(a)) (
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION other VALUES LESS THAN MAXVALUE
);

Suggested fix:
If a user creates a UDF and defines it as being deterministic, using that function for partitions should be allow.  If the function is defined as deterministic, but really isn't, that is the users problem.

Changed "UDF" to "Stored Function" in the bug title as UDFs are actually something different.

Not sure about the actual request though as Stored Function definitions may change whereas builtin functions usually don't change even between server releases. 

So on an ALTER FUNCTION all tables using the function for partitioning would need to be altered, too.
Dependency graphs between Stored Functions and Procedures and the tables using them for partitioning (both direct and indirec calls) would have to be maintained etc. ...

MySQL will be more strict about allowing any expressions for partitions. See bug#18198.

So, this almost surely will not be implemented.