Bug #49742 Partition Pruning not working correctly for RANGE
Submitted: 16 Dec 2009 18:37 Modified: 15 Mar 2010 5:24
Reporter: Leandro Morgado Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.41, 5.1.37, 5.1.43,5.5.0 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any

[16 Dec 2009 18:37] Leandro Morgado 
Description:
Partitions are not being correctly pruned. For some values, it looks up more partitions than it should. I tested this with id TINYINT and id INT.

How to repeat:
Example:

CREATE TABLE partition_int_test (id TINYINT PRIMARY KEY)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION max VALUES LESS THAN MAXVALUE);

INSERT INTO partition_int_test VALUES (0),(1),(2),(3),(4),(5),(6);

explain partitions select * from partition_int_test where id < 1;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0 | range | PRIMARY | PRIMARY | 1 | NULL | 1 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test where id < 2;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0,p1,p2 | range | PRIMARY | PRIMARY | 1 | NULL | 3 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test where id < 3;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0,p1,p2 | range | PRIMARY | PRIMARY | 1 | NULL | 3 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test where id < 4;
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0,p1,p2,p3,p4 | range | PRIMARY | PRIMARY | 1 | NULL | 5 | Using where; Using index |
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test where id < 5;
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0,p1,p2,p3,p4 | range | PRIMARY | PRIMARY | 1 | NULL | 5 | Using where; Using index |
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

Notice that when you compare < 2 it doesn't prune partition p2. When you compare < 4 it doesn't prune partition p4..

Suggested fix:
In the first example, we should get:

explain partitions select * from partition_int_test where id < 2;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0,p1 | range | PRIMARY | PRIMARY | 1 | NULL | 3 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test where id < 4;
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test | p0,p1,p2,p3 | range | PRIMARY | PRIMARY | 1 | NULL | 5 | Using where; Using index |
+----+-------------+--------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
[16 Dec 2009 18:37] Leandro Morgado 
Another example:
CREATE TABLE partition_int_test2 (id TINYINT PRIMARY KEY)
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION p6 VALUES LESS THAN (7),
PARTITION max VALUES LESS THAN MAXVALUE);
INSERT INTO partition_int_test2 VALUES (1),(2),(3),(4),(5),(6),(7);

explain partitions select * from partition_int_test2 where id < 2;
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test2 | p1 | range | PRIMARY | PRIMARY | 1 | NULL | 1 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test2 where id < 3;
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test2 | p1,p2,p3 | range | PRIMARY | PRIMARY | 1 | NULL | 3 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test2 where id < 4;
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test2 | p1,p2,p3 | range | PRIMARY | PRIMARY | 1 | NULL | 3 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test2 where id < 5;
+----+-------------+---------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test2 | p1,p2,p3,p4,p5 | range | PRIMARY | PRIMARY | 1 | NULL | 5 | Using where; Using index |
+----+-------------+---------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

explain partitions select * from partition_int_test2 where id < 6;
+----+-------------+---------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_int_test2 | p1,p2,p3,p4,p5 | range | PRIMARY | PRIMARY | 1 | NULL | 5 | Using where; Using index |
+----+-------------+---------------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

Notice that this time when you compare < 3 it doesn't prune partition p3. When you compare < 5 it doesn't prune partition p5
[16 Dec 2009 18:44] Valeriy Kravchuk 
This is what we have in current 5.1.43 from bzr:

77-52-7-73:5.1 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.1.43-debug Source distribution

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

mysql> drop table partition_int_test;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE partition_int_test (id TINYINT PRIMARY KEY)
    -> PARTITION BY RANGE (id) (
    -> PARTITION p0 VALUES LESS THAN (1),
    -> PARTITION p1 VALUES LESS THAN (2),
    -> PARTITION p2 VALUES LESS THAN (3),
    -> PARTITION p3 VALUES LESS THAN (4),
    -> PARTITION p4 VALUES LESS THAN (5),
    -> PARTITION p5 VALUES LESS THAN (6),
    -> PARTITION max VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> INSERT INTO partition_int_test VALUES (0),(1),(2),(3),(4),(5),(6);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> 
mysql> explain partitions select * from partition_int_test where id < 1;
+----+-------------+--------------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table              | partitions | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------------------+------------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | partition_int_test | p0         | system | PRIMARY       | NULL | NULL    | NULL |    1 |       |
+----+-------------+--------------------+------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select * from partition_int_test where id < 2;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table              | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | partition_int_test | p0,p1,p2   | index | PRIMARY       | PRIMARY | 1       | NULL |    3 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from partition_int_test where id < 3;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table              | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | partition_int_test | p0,p1,p2   | index | PRIMARY       | PRIMARY | 1       | NULL |    3 | Using where; Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
...
[22 Dec 2009 16:08] Mattias Jonsson 
Found the error (in get_partition_id_range_for_endpoint). Will commit patch soon (after running some more tests).
[22 Dec 2009 18:00] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95398

3293 Mattias Jonsson	2009-12-22
      Bug#49742: Partition Pruning not working correctly for RANGE
      
      Problem was when calculating the range of partitions for
      pruning.
      
      Solution was to get the calculation correct. I also simplified
      it a bit for easier understanding.
     @ mysql-test/r/partition_pruning.result
        Bug#49742: Partition Pruning not working correctly for RANGE
        
        Added results.
     @ mysql-test/t/partition_pruning.test
        Bug#49742: Partition Pruning not working correctly for RANGE
        
        Added tests to prevent regressions.
     @ sql/sql_partition.cc
        Bug#49742: Partition Pruning not working correctly for RANGE
        
        Simplified calculation for partition id for ranges.
        Easier to get right and understand.
        
        Added comments.
[4 Jan 2010 8:53] Giuseppe Maxia 
This bug also affects MySQL 5.5.0
[17 Jan 2010 21:13] Mattias Jonsson 
Pushed to mysql-5.1-bugteam and mysql-pe
[4 Feb 2010 10:19] Bugs System 
Pushed into 5.1.44 (revid:joro@sun.com-20100204101444-2j32mhqroo0iiio6) (version source revid:mattias.jonsson@sun.com-20100117210037-l3nr4w36t7u7znhv) (merge vers: 5.1.43) (pib:16)
[5 Feb 2010 11:48] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100127195551-hzccsc9m2o2ir1j0) (pib:16)
[5 Feb 2010 11:54] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 12:00] Bugs System 
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20100123210923-lx4o1ettww9fdkqk) (merge vers: 5.5.2-m2) (pib:16)
[5 Feb 2010 14:13] Jon Stephens 
Documented bugfix in the 5.1.44, 5.5.2, 5.6.0, and 6.0.14 changelogs, as follows:

      In some cases, partition pruning failed to remove all unnecessary
      partitions before checking them.

Closed.
[12 Mar 2010 14:07] Bugs System 
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:23] Bugs System 
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:37] Bugs System 
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 5:24] Jon Stephens 
No additional changelog entries required. Returning to Closed state.