MySQL Bugs: #16002: PARTITION BY RANGE(bigint unsigned) is broken: values >2G treated as negative

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

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.

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)

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.

We'll need to test that partition pruning works for unsigned fields and unsigned partitioning functions. That will be done in BUG#20257

With this patch partition functions that are unsigned are supported
Will be available in 5.1.12

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.