Bug #72274 Allow DECIMAL as partitioning key and/or improve pruning for FLOOR(DECIMAL)
Submitted: 8 Apr 2014 11:45 Modified: 8 Apr 2014 14:12
Reporter: - - Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S5 (Performance)
Version:5.6.17 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[8 Apr 2014 11:45] - -
Description:
DECIMAL is currently not allowed as partitioning key and one has to use FLOOR(DECIMAL) as partitioning key. Partition pruning is very limited in this case, for example queries with equality filter WHERE decimal_partitioning_key=:constant_value: get pruned while queries with range filter WHERE decimal_partitioning_key BETWEEN :constant_value_1: AND :constant_value_2: not. 

How to repeat:
mysql> CREATE TABLE test(test DECIMAL(31,20) PRIMARY KEY)PARTITION BY RANGE(FLOOR(test))(PARTITION p0 VALUES LESS THAN(1),PARTITION p1 VALUES LESS THAN(2)) SELECT 0 test UNION ALL SELECT 1;
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE test=0;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | p0         | const | PRIMARY       | PRIMARY | 14      | const |    1 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.19 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE test BETWEEN -1 AND 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | p0,p1      | index | PRIMARY       | PRIMARY | 14      | NULL |    2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.15 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE test>=0 AND test<1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | p0,p1      | index | PRIMARY       | PRIMARY | 14      | NULL |    2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.13 sec)

Suggested fix:
mysql> CREATE TABLE test(test DECIMAL(31,20) PRIMARY KEY)PARTITION BY RANGE(FLOOR(test))(PARTITION p0 VALUES LESS THAN(1),PARTITION p1 VALUES LESS THAN(2)) SELECT 0 test UNION ALL SELECT 1;
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE test=0;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | p0         | const | PRIMARY       | PRIMARY | 14      | const |    1 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.19 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE test BETWEEN -1 AND 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | p0      | index | PRIMARY       | PRIMARY | 14      | NULL |    2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.15 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE test>=0 AND test<1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | p0      | index | PRIMARY       | PRIMARY | 14      | NULL |    2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.13 sec)
[8 Apr 2014 14:12] Miguel Solorzano
Thank you for the bug report.