Bug #213 BIGINT UNSIGNED columns does not cut negative values properly

• Submitted: 31 Mar 2003 11:43
• Modified: 17 Apr 2003 16:22
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.0
• OS: Any (All)
• CPU Architecture: Any

[31 Mar 2003 11:43] Peter Zaitsev

Description:
BIGINT UNSIGNED column does not work similar way to INT UNSIGNED and other UNSIGNED column types. This has an explanation of using unsigned arithmetics for such case but still it is a bug from user point of view.

Especially bad it can be if you extend some column from INT UNSIGNED  to BIGINT UNSIGNED with application growth.

It is also bad what "-1" and -1 has different behavior in such context:

How to repeat:
mysql> create table testb(i bigint unsigned);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testb values(-1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from testb;
+----------------------+
| i                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.26 sec)

---------------

mysql> create table b(i bigint unsigned not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into b values("-1");
Query OK, 1 row affected (0.00 sec)

mysql> select * from b;
+----------------------+
| i                    |
+----------------------+
|                    0 |
+----------------------+
1 rows in set (0.00 sec)

[17 Apr 2003 16:22] Michael Widenius

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The different behaviour between usigned int and unsigned bigint is NOT fixable as MySQL can't with bigint know if the value has 'wrapped around' due to an arithmetic operation or if the value comes from a client that doesn't handle unsigned bigint's properly.

The later is not uncommon in ODBC applications as BIGINT is a relatively new thing in ODBC.

In MySQL 4.1.x or 5.0.x we will enable a new string -> longlong code that will make -1 and "-1" work identical for bigints