Bug #36303 It is not possible to use PARTITION BY RANGE over FLOAT column
Submitted: 23 Apr 2008 18:13 Modified: 22 Dec 2009 9:50
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: D5 (Feature request)

[23 Apr 2008 18:13] Sergey Petrunya
Description:
It is now not possible to use PARTITION BY RANGE over a FLOAT/DOUBLE column - the limitations over return type of partitioning function and the list of partitioning functions are such that one cannot construct any valid

 PARTITION BY RANGE( anything (double_type_column)) 

statement. (original complaint was voiced here: http://s.petrunia.net/blog/?p=17)

How to repeat:
Try things like

CREATE TABLE min5(
sCode varchar( 7 ) NOT NULL ,
sTime double NOT NULL ,
sVolume float DEFAULT NULL
)
partition BY range(floor( sTime ))
(
PARTITION p0 VALUES LESS THAN( 1995 ) , 
PARTITION p1 VALUES LESS THAN( 1996 ) , 
PARTITION p2 VALUES LESS THAN MAXVALUE
);

CREATE TABLE min5(
  sCode varchar( 7 ) NOT NULL ,
  sTime double NOT NULL ,
  sVolume float DEFAULT NULL
)
partition BY range( convert(floor(sTime), signed))
(
  PARTITION p0 VALUES LESS THAN( 1995 ) ,
  PARTITION p1 VALUES LESS THAN( 1996 ) ,
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

and see that none of them is accepted, although one can expect such functions to work.

Suggested fix:
allow functions or some other way to partition by range.
[24 Apr 2008 4:28] Valeriy Kravchuk
As current limitations are clearly documented at http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html, I'd call this a high priority feature request.
[15 Dec 2008 7:19] Mattias Jonsson
Changing back to verified.

This is implemented by WL#3352. There is a preview of the implementation at launchpad:
lp:~mikael-ronstrom/mysql-server/mysql-5.1-wl3352
[2 Dec 2009 13:51] Mattias Jonsson
It may be possible to add support for this in a future version now that WL#3352 is included in mysql-trunk.
[22 Dec 2009 9:50] Mattias Jonsson
I'm closing this, since it is now possible to do 'PARTITION BY RANGE COLUMNS(float_col)' in 5.5 (mysql-trunk).