MySQL Bugs: #42944: partition not pruned correctly

Bug #42944	partition not pruned correctly
Submitted:	17 Feb 2009 19:50	Modified:	6 May 2009 14:21
Reporter:	Nishant Deshpande	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1..31, 5.1, 6.0 bzr	OS:	Linux (2.6.18-92.el5)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	Partition pruning

Description:
extra partition is being used in the query explain.

btw, is there a way to see which partition a row is in? or related to this, a way to explicitly query a particular partition? sorry i feel like this is somewhere in the documentation but i can't find it.

How to repeat:
db1102:root test1> create table tp1 (x int not null primary key, y int) partition by range (x) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.01 sec)

db1102:root test1> insert into tp1 values (10,10), (100,100), (200,200), (300,300), (400,400);                                                                                                   
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

db1102:root test1> explain partitions select * from tp1 where x>=300;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tp1   | p3         | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where | 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

db1102:root test1> explain partitions select * from tp1 where x>=200;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tp1   | p1,p2,p3   | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where | 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

sorry i should add that the first explain shows the correct partition selected, but the second one selects p1, which i would not expect. i.e. i would expect the row (200,200) to be in p2.

Thank you for the report.

Verified as described.

Also with = comparision:

explain partitions select * from tp1 where x=199;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  tp1     p1      const   PRIMARY PRIMARY 4       const   1
explain partitions select * from tp1 where x=200;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  tp1     p1,p2   const   PRIMARY PRIMARY 4       const   1
explain partitions select * from tp1 where x=201;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  tp1     p2      const   PRIMARY PRIMARY 4       const   1

Related to bug#29258.

The fix is:
=== modified file 'sql/sql_partition.cc'
--- sql/sql_partition.cc	2009-01-09 13:18:08 +0000
+++ sql/sql_partition.cc	2009-02-21 20:22:08 +0000
@@ -2937,7 +2937,9 @@
       the maximum value is in the current partition.
     */
     if (part_func_value > bound ||
-        (part_func_value == bound && !part_info->defined_max_value))
+        (part_func_value == bound &&
+         (!part_info->defined_max_value ||
+          loc_part_id < max_partition)))
       loc_part_id++;
   }
   else 

The bug is that it does not prune the LESS THAN partition if a MAXVALUE is given and the given value is equal to a LESS THAN value.

So a minor bug and an easy fix.

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/70995

2841 Ramil Kalimullin	2009-04-01
      Fix for bug#42944: partition not pruned correctly
      
      Problem: we don't prune a LESS THAN partition if MAXVALUE is given and
      given value is equal to a LESS THAN value.
      
      Fix: prune partitions in such cases.
     @ mysql-test/r/partition.result
        Fix for bug#42944: partition not pruned correctly
          - test result.
     @ mysql-test/t/partition.test
        Fix for bug#42944: partition not pruned correctly
          - test case.
     @ sql/sql_partition.cc
        Fix for bug#42944: partition not pruned correctly
          - prune partition if given value is equal to a LESS THAN value
            and it's not a "PARTITION ... LESS THAN MAXVALUE" one.

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Documented bugfix in the 5.1.35 changelog as follows:

        When a value was equal to a PARTITION ... VALUES LESS THAN (...)
        value other than MAXVALUE, the corresponding partition was not
        pruned.

Set status to NDI: waiting on merge to 6.0.

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:ramil@mysql.com-20090401091550-ot5r5xj69b5ymoxz) (merge vers: 6.0.11-alpha) (pib:6)

Also documented in 6.0.12 changelog. Closed.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)