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
Triage: Triaged: D2 (Serious)

[17 Dec 2010 18:15] Peter Gulutzan
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
[17 Dec 2010 18:34] Valeriy Kravchuk
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