MySQL Bugs: #59004: partition with floor(big_decimal

Bug #59004	partition with floor(big_decimal_column) fails
Submitted:	17 Dec 2010 18:15	Modified:	17 Dec 2010 18:34
Reporter:	Peter Gulutzan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.9	OS:	Linux (SUSE 64-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Now floor() and ceiling() are allowable
partition functions with decimals,
see comments on Bug#30577.

They don't work when there's no easy
conversion to integer, so decimal(19)
fails during CREATE and decimal(18)
fails during INSERT, with a misleading
message.

How to repeat:
create table t1 (s1 decimal(19)) partition by list (floor(s1))
  (partition p1 values in (null));
create table t2 (s1 decimal(18)) partition by list (floor(s1))
 (partition p1 values in (1234567890123456789));
insert into t2 values (1234567890123456789);

Sample run:

mysql> create table t1 (s1 decimal(19)) partition by list (floor(s1))
    ->   (partition p1 values in (null));
ERROR 1491 (HY000): The PARTITION function returns the wrong type

mysql> create table t2 (s1 decimal(18)) partition by list (floor(s1))
    ->  (partition p1 values in (1234567890123456789));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t2 values (1234567890123456789);
ERROR 1526 (HY000): Table has no partition for value 999999999999999999

Verified with current mysql-5.5-security tree:

macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (s1 decimal(19)) partition by list (floor(s1))
    ->   (partition p1 values in (null));
ERROR 1491 (HY000): The PARTITION function returns the wrong type
mysql> create table t2 (s1 decimal(18)) partition by list (floor(s1))
    ->  (partition p1 values in (1234567890123456789));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t2 values (1234567890123456789);
ERROR 1526 (HY000): Table has no partition for value 999999999999999999