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

[24 Apr 2006 22:52] Andy
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.
[23 May 2006 22:57] Hartmut Holzgraefe
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. ...
[23 May 2006 23:35] Peter Gulutzan
MySQL will be more strict about allowing any expressions for partitions. See bug#18198.
[30 May 2006 21:56] Valeriy Kravchuk
So, this almost surely will not be implemented.