| Bug #42944 | partition not pruned correctly | ||
|---|---|---|---|
| Submitted: | 17 Feb 20:50 | Modified: | 6 May 16:21 |
| Reporter: | Nishant Deshpande | ||
| Status: | Closed | ||
| Category: | Server: Partition | Severity: | S3 (Non-critical) |
| Version: | 5.1..31, 5.1, 6.0 bzr | OS: | Linux (2.6.18-92.el5) |
| Assigned to: | Ramil Kalimullin | Target Version: | 5.1+ |
| Tags: | Partition pruning | ||
| Triage: | Triaged: D3 (Medium) / R1 (None/Negligible) / E1 (None/Negligible) | ||
[17 Feb 20:52]
Nishant Deshpande
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.
[17 Feb 21:36]
Sveta Smirnova
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
[21 Feb 21:29]
Mattias Jonsson
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.
[1 Apr 7:35]
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/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.
[5 May 21:40]
Bugs System
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)
[6 May 10:43]
Jon Stephens
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.
[6 May 16:07]
Bugs System
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)
[6 May 16:21]
Jon Stephens
Also documented in 6.0.12 changelog. Closed.
[15 Jun 10:27]
Bugs System
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)
[15 Jun 11:06]
Bugs System
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)
[15 Jun 11:47]
Bugs System
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)
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)