Bug #51830 Incorrect partition pruning on range partition (regression)

• Submitted: 8 Mar 2010 13:57
• Modified: 20 Jun 2010 22:57
• Reporter: Kristian Nielsen
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1.44, 5.1.45-bzr
• OS: Linux (amd64)
• Assigned to: Mattias Jonsson
• CPU Architecture: Any
• Tags: regression

[8 Mar 2010 13:57] Kristian Nielsen

Description:
There is a regression between MySQL 5.1.43 and MySQL 5.1.44, my guess is it is
caused by the patch for Bug#49742.

The regression is seen with the following extract from partition_pruning.test
using innodb instead of myisam as storage engine. Here are the results from
MySQL 5.1.44:

create table t7 (a int not null) ENGINE=innodb partition by RANGE(a) (
  partition p10 values less than (10),
  partition p30 values less than (30),
  partition p50 values less than (50),
  partition p70 values less than (70),
  partition p90 values less than (90)
);
insert into t7 values (10),(30),(50);
explain partitions select * from t7 where a > 90;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t7    | p90        | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

In this case there is no need to check partition p90. So it is a bug to include p90 in the list of partitions to check.

In MySQL 5.1.43 the pruning is done correctly:

create table t7 (a int not null) ENGINE=innodb partition by RANGE(a) (
  partition p10 values less than (10),
  partition p30 values less than (30),
  partition p50 values less than (50),
  partition p70 values less than (70),
  partition p90 values less than (90)
);
insert into t7 values (10),(30),(50);
explain partitions select * from t7 where a > 90;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t7    |            | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

An interesting point is that relaxing the WHERE condition actually strengtens
the pruning in MySQL 5.1.44:

explain partitions select * from t7 where a > 89;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t7    |            | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

(Note that this bug would have been caught by a test in partition_pruning.test
if it used innodb instead of myisam. With myisam, we get "Impossible WHERE
noticed after reading const tables" in the explain, which isn't very useful
for testing partition pruning).

How to repeat:
See above

Suggested fix:
Restore the correct partition pruning behaviour of MySQL 5.1.43

[8 Mar 2010 14:43] Kristian Nielsen

This revert of part of the fix for Bug#49742 fixes this particular issue for me.

=== modified file 'sql/sql_partition.cc'
--- sql/sql_partition.cc	2010-03-04 08:03:07 +0000
+++ sql/sql_partition.cc	2010-03-08 14:26:39 +0000
@@ -3019,8 +3019,9 @@ uint32 get_partition_id_range_for_endpoi
       In case of PARTITION p VALUES LESS THAN MAXVALUE
       the maximum value is in the current partition.
     */
-    if (part_func_value == part_end_val &&
-        (loc_part_id < max_partition || !part_info->defined_max_value))
+    if (part_func_value > part_end_val ||
+        (part_func_value == part_end_val &&
+         (loc_part_id < max_partition || !part_info->defined_max_value)))
       loc_part_id++;
   }
   else

[8 Mar 2010 16:11] Valeriy Kravchuk

Thank you for the bug report. Verified just as described with recent 5.1.45 from bzr on Mac OS X:

Bye
77-52-24-143: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 3
Server version: 5.1.45-debug Source distribution

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

mysql> create table t7 (a int not null) ENGINE=innodb partition by RANGE(a) (
    ->   partition p10 values less than (10),
    ->   partition p30 values less than (30),
    ->   partition p50 values less than (50),
    ->   partition p70 values less than (70),
    ->   partition p90 values less than (90)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t7 values (10),(30),(50);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from t7 where a > 90;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t7    | p90        | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t7 where a > 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t7    | p90        | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t7 where a > 89;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t7    |            | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

[10 Mar 2010 11:56] 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/102869

3375 Mattias Jonsson	2010-03-10
      Bug#51830: Incorrect partition pruning on range partition
      (regression)
      
      Problem was that partition pruning did not exclude the
      last partition if the range was beyond it
      (i.e. not using MAXVALUE)
      
      Fix was to not include the last partition if the
      partitioning function value was not within the partition
      range.
     @ mysql-test/r/partition_innodb.result
        Bug#51830: Incorrect partition pruning on range partition
        (regression)
        
        Updated result
     @ mysql-test/r/partition_pruning.result
        Bug#51830: Incorrect partition pruning on range partition
        (regression)
        
        Updated result
     @ mysql-test/t/partition_innodb.test
        Bug#51830: Incorrect partition pruning on range partition
        (regression)
        
        Added test for pruning in InnoDB, since it does not show
        for MyISAM due to 'Impossible WHERE noticed after reading
        const tables'.
     @ mysql-test/t/partition_pruning.test
        Bug#51830: Incorrect partition pruning on range partition
        (regression)
        
        Added test
     @ sql/sql_partition.cc
        Bug#51830: Incorrect partition pruning on range partition
        (regression)
        
        Also increase the partition id if not inside the last partition
        (and no MAXVALUE is defined).
        
        Added comments and DBUG_ASSERT.

[16 Mar 2010 16:08] Mattias Jonsson

pushed into mysql-5.1-bugteam and mysql-pe

[26 Mar 2010 8:23] Bugs System

Pushed into 5.5.4-m3 (revid:[e-mail]-20100326080914-2pz8ns984e0spu03) (version source revid:[e-mail]-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)

[26 Mar 2010 8:27] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100326081116-m3v4l34yhr43mtsv) (version source revid:[e-mail]-20100325072612-4sds00ix8ajo1e84) (pib:16)

[26 Mar 2010 8:31] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100326081944-qja07qklw1p2w7jb) (version source revid:[e-mail]-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

[29 Mar 2010 9:53] Jon Stephens

Documented bugfix in the 5.5.4 and 6.0.14 changelogs as follows:

        Partition pruning on RANGE partitioned tables did not always
        work correctly; the last partition partition was not excluded
        if the range was beyond it (when not using MAXVALUE).Now the
        last partition is not included if the partitioning function
        value is not within the range.

NM - waiting for 5.1 merge.

[31 Mar 2010 16:00] Paul DuBois

5.5.4 changelog entry was moved to 5.5.5.

[6 Apr 2010 8:00] Bugs System

Pushed into 5.1.46 (revid:[e-mail]-20100405111026-7kz1p8qlzglqgfmu) (version source revid:[e-mail]-20100316162138-u9724fhm54cj3or0) (merge vers: 5.1.46) (pib:16)

[6 Apr 2010 10:31] Jon Stephens

Also documented in the 5.1.46 changelog.

Closed.

[17 Jun 2010 12:18] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:05] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:46] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)