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: - -
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S5 (Performance)
• Version: 5.6.17
• OS: Linux
• CPU Architecture: Any

[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] MySQL Verification Team

Thank you for the bug report.