Bug #16002 PARTITION BY RANGE(bigint unsigned) is broken: values >2G treated as negative
Submitted: 27 Dec 2005 5:46 Modified: 15 Jun 2006 11:09
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1-bk OS:
Assigned to: Mikael Ronström CPU Architecture:Any

[27 Dec 2005 5:46] Sergey Petrunya
Description:
PARTITION BY RANGE(bigint unsigned) is broken: values >2G treated as negative.

How to repeat:
create table w1 (a bigint unsigned) 
partition by range(a) (
  partition p0 values less than (1), 
  partition p1 values less than (2)
);
# From user POV, it is logical to expect that the only possible
# values of `a` in the table are 0 and 1.

mysql> insert into w1 values (0xffffffffffffffff);
Query OK, 1 row affected (0.02 sec)

mysql> select * from w1;
+----------------------+
| a                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.01 sec)

Suggested fix:
Process BIGINTs correctly.
[27 Dec 2005 13:07] Aleksey Kishkin
mysql> create table w1 (a bigint unsigned)  partition by range(a) (   partition p0 values less than (1),    partition p1 values less than (2) );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into w1 values (0xffffffffffffffff);
Query OK, 1 row affected (0.00 sec)

mysql> insert into w1 values (8);
ERROR 1500 (HY000): Table has no partition for value 8
mysql> select * from w1;
+----------------------+
| a                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)
[15 Apr 2006 14:54] Mikael Ronström
An interesting bug. It had some implications on how things were checked at table creation and
table altering.
In principal one has to keep track of whether values are unsigned or signed or not. It's a first
step towards supporting partitioning functions of more general type than just returning integers.
In principal all places where one does comparisons and show commands one has to check for
whether signed or unsigned partition function.

When a partition function is unsigned it is also necessary that we don't have VALUES IN or
VALUES LESS THAN constants that are negative. In this case we should report an error. This
is a new feature when this patch is completed.

Another important thing to document is that only RANGE and LIST partitions will check for
sign. Hash partitions will always treat functions is signed integers and then apply the hash
function on this value.
[4 Jun 2006 10:34] Sergey Petrunya
We'll need to test that partition pruning works for unsigned fields and unsigned partitioning functions. That will be done in BUG#20257
[14 Jun 2006 21:05] Mikael Ronström
With this patch partition functions that are unsigned are supported
Will be available in 5.1.12
[15 Jun 2006 11:09] 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.12 changelog; closed.