Bug #18558 Partition pruning results are incorrect for certain class of WHERE clauses
Submitted: 27 Mar 2006 22:01 Modified: 7 Apr 2006 11:14
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.2-bk, 5.1.9 OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[27 Mar 2006 22:01] Sergey Petrunya
Description:
Partition pruning results are incorrect (and so the query results will be incorrect too) for certain class of WHERE clauses.

How to repeat:
-- Create/fill the test table:
create table t1 (a int not null, b int not null)
partition by list(a) 
  subpartition by hash(b) subpartitions 4 
(
  partition p0 values in (1),
  partition p1 values in (2),
  partition p2 values in (3)
);
insert into t1 values (1,1),(1,2),(1,3),(1,4),
  (2,1),(2,2),(2,3),(2,4);

-- Then run the queries:

mysql> explain partitions select * from t1 where a=1 AND (b=1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0_p0sp1
...
-- This is ok

mysql> explain partitions select * from t1 where a=1 AND (b=2)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0_p0sp2
...
-- This is ok too

mysql> explain partitions select * from t1 where a=1 AND (b=1 OR b=2)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0_p0sp1
...
-- From two previous queries it follows that the set of partition set for this 
-- query must be "p0_p0sp1, p0_p0sp2", while it is not.

Suggested fix:
in find_used_partitions(), when we find a condition for partitioning field, we 
1. create a partition set iterator (*) for it and proceed to find condition(s) for subpartitioning fields. When we find a condition for subpartitioning , we 
2. determine used subpartitions, traverse the iterator (*) and mark partitionX_subpartitionY as used.

This bug is caused by the following: for a step (1) there can be several steps (2), between which the iterator is not reset.
[31 Mar 2006 10:25] 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/4361
[3 Apr 2006 19:40] Sergey Petrunya
The following doesn't currently work:
 create table t10 ( a int)  partition by list (MOD(a, 10))
( partition p0  values in (0),
   partition p1 values in (1),
   partition p2 values in (2),
   partition p3 values in (3),
   partition p4 values in (4),
   partition p5 values in (5),
   partition p6 values in (6),
   partition pn values in (NULL)
);

insert into t10 values (NULL), (0),(1),(2),(3),(4),(5),(6);
explain partitions select * from t10 where a is null or a < 2;

It will be fixed within the scope of this bug (as it will be easier to do code-wise)
[4 Apr 2006 22:48] 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/4466
[6 Apr 2006 17:17] 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/4572
[6 Apr 2006 18:05] Sergey Petrunya
The fix has been pushed into 5.1.9 tree.
Notes for the changelog:
The bug was that for certain kinds of partitioning/subpartitioning and WHERE clauses partition pruning didn't work properly: [sub]partitions that ought to be marked as used weren't. The error showed up as incorrect content in EXPLAIN PARTITIONS output, and missing rows in the query.
(The precise definition of affected set of WHERE clauses is rather complicated and hence I don't provide it here)
[7 Apr 2006 11:14] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.9 changelog. Closed.