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 6:46	Modified:	15 Jun 2006 13:09
Reporter:	Sergey Petrunya
Status:	Closed
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:
Assigned to:	Mikael Ronstrom	Target Version:

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.