partition by range on decimal type

[Greg K]

hi experts,

I need your help in getting my partitioning working.
I have a problem with paritioning on DECIMAL column (in fact this should be DATE but I have no chance to change it).
So I use floor() to return integer. Column 'starttime' used in partitioning is used in primary key, so the requirement is met.

this is my code:
===============

mysql> desc bts_hour;
+-----------------------------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------------------+---------------+------+-----+---------+-------+
| bts_gid | decimal(20,0) | NO | PRI | NULL | |
| starttime | decimal(14,0) | NO | PRI | NULL | | <-------- this is it
| bsc_gid | decimal(20,0) | NO | | NULL | |
| BSC_NAME | varchar(80) | YES | | NULL | |
| BTS_NAME | varchar(80) | YES | MUL | NULL | |
...

#added index
alter table test.bts_hour
add PRIMARY KEY (`bts_gid`,`starttime`),
add KEY `starttime` (`starttime`),
add KEY `bts_gid` (`bts_gid`),
add KEY `bts_name` (`BTS_NAME`);

select count(*) from test.bts_hour;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+

select min(starttime), max(starttime) from test.bts_hour;
+----------------+----------------+
| min(starttime) | max(starttime) |
+----------------+----------------+
| 2011033000 | 2011063018 |
+----------------+----------------+

# add partitiong
alter table test.bts_hour
partition by RANGE (floor(starttime)) (
partition p00 values less than (2011040100),
partition p01 values less than (2011050100),
partition p02 values less than (MAXVALUE)
);

#check ditribution of data among partitions
select partition_name part, partition_expression expr, partition_description val, partition_method meth, table_rows
from information_schema.partitions
where table_name = 'bts_hour'
and TABLE_SCHEMA = 'test';

+------+------------------+------------+-------+------------+
| part | expr | val | meth | table_rows |
+------+------------------+------------+-------+------------+
| p00 | floor(starttime) | 2011040100 | RANGE | 20352 |
| p01 | floor(starttime) | 2011050100 | RANGE | 297084 |
| p02 | floor(starttime) | MAXVALUE | RANGE | 682564 |
+------+------------------+------------+-------+------------+

===================

now, when I run 'explain', all the partitions are used instead of p01 that has the set of values I want!

explain partitions
SELECT bts_name, str_to_date(convert(STARTTIME, CHAR(10)), '%Y%m%d%H') as Date
FROM test.bts_hour
WHERE STARTTIME between 2011040200 and 2011040523
ORDER BY bts_name, starttime
\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bts_hour
partitions: p00,p01,p02 <--------- here it is, instead of p01 only
type: range
possible_keys: starttime,starttime_bts_gid,starttime_bts_name
key: starttime_bts_name
key_len: 7
ref: NULL
rows: 26021
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)


What's wrong with this approach?