MySQL Bugs: #20257: Partition pruning may be broken for BIGINT UNSIGNED fields.

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

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.

Some of the tests that need to be added are added in patch for BUG#20733.

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)

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

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.

Fixed in 5.1.12

Documented in 5.1.12 changelog.