Bug #20257 Partition pruning may be broken for BIGINT UNSIGNED fields.
Submitted: 4 Jun 2006 10:32 Modified: 3 Aug 2006 2:17
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1-bk with BUG#16002 fix OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[4 Jun 2006 10:32] Sergey Petrunya
Description:
Partition pruning may be broken for partitioning over BIGINT UNSIGNED fields/unsigned expressions. Currently there is no test coverage at all (that was intentional, see WL#2985 "Partition pruning", LLD: "We also ignore the problems with BIGINT UNSIGNED fields, as they are not handled correctly by partitioning code itself (see BUG#16002)").

Since now BUG#16002 is patch pending and will be soon pushed, we'll need to verify partition pruning works for BIGINT UNSIGNED fields/unsigned expressions.

How to repeat:
n/a

Suggested fix:
Add the tests. Fix the problems that will show up.
[21 Jul 2006 14:36] Sergey Petrunya
Some of the tests that need to be added are added in patch for BUG#20733.
[22 Jul 2006 17:58] Sergey Petrunya
As suspected, one can get wrong query results/hangs in edge cases.

A wrong query result case:
CREATE TABLE t1 ( a bigint(20) unsigned NOT NULL) PARTITION BY RANGE (a+0) (
  PARTITION p1 VALUES LESS THAN (10), 
  PARTITION p2 VALUES LESS THAN (20), 
  PARTITION p3 VALUES LESS THAN (2305561538531885056),
  PARTITION p4 VALUES LESS THAN (2305561538531950591)
);

mysql> explain partitions select * from t1 where a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p1         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (23.98 sec)
[22 Jul 2006 19:11] 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/9453
[24 Jul 2006 14:30] Sergey Petrunya
Notes for the changelog:
Partition pruning could cause wrong query results (missing rows) when partitioning function used BIGINT UNSIGNED fields and "edge case" intervals were inferred from the query WHERE clause.
[2 Aug 2006 19:10] Evgeny Potemkin
Fixed in 5.1.12
[3 Aug 2006 2:17] Jon Stephens
Documented in 5.1.12 changelog.