| Bug #1723 | Assigning negative value to bigint unsigned records (maxval_bigint - value) | ||
|---|---|---|---|
| Submitted: | 31 Oct 2003 4:14 | Modified: | 12 Nov 2003 1:06 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.13-max-log | OS: | Linux (Linux 2.4.18) |
| Assigned to: | CPU Architecture: | Any | |
[31 Oct 2003 5:06]
[ name withheld ]
This behaviour also works the other way. Trying to find out the minimum of two values with the LEAST()-function results in the previous inserted negative value. An inserted positive value shows an equal result: INSERT INTO bigint_test VALUES(18446744073709550000); SELECT LEAST(1, temp) AS least, temp FROM bigint_test;
[12 Nov 2003 1:06]
Sergei Golubchik
I don't know what we can do here. It's a price for having max possible integer range. If usigned bigint would be in the range [0..9223372036854775807], then we could be able to detect a negative value there and convert it to zero.
[4 Jun 2004 21:13]
Kevin Dooley
The underlying problem seems to be that even though a BIGINT is defined as UNSIGNED, the bit-wise or operator (|) is not treating it as such. Below is an example (using binary numbers to make what's happening a little clearer) that shows what happens when you set the sign (left-most) bit on an INT (32 bits) and a BIGINT (64 bits).
I get the same result running MySQL 3.23.56 on RedHat 9, and 4.0.18-nt on Win2K.
CREATE TABLE t
(
t32 INT UNSIGNED NOT NULL DEFAULT 0,
t64 BIGINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO t SET t32=0, t64=0;
SELECT LPAD(CONV(t32, 10, 2), 32, "0") AS t32, LPAD(CONV(t64, 10, 2), 64, "0") as t64 FROM t;
+----------------------------------+------------------------------------------------------------------+
| t32 | t64 |
+----------------------------------+------------------------------------------------------------------+
| 00000000000000000000000000000000 | 0000000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------+------------------------------------------------------------------+
UPDATE t SET t32 = t32 | CONV('00100000000000000000000000000000', 2, 10),
t64 = t64 | CONV('0010000000000000000000000000000000000000000000000000000000000000', 2, 10);
SELECT LPAD(CONV(t32, 10, 2), 32, "0") AS t32, LPAD(CONV(t64, 10, 2), 64, "0") as t64 FROM t;
+----------------------------------+------------------------------------------------------------------+
| t32 | t64 |
+----------------------------------+------------------------------------------------------------------+
| 00100000000000000000000000000000 | 0010000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------+------------------------------------------------------------------+
UPDATE t SET t32 = t32 | CONV('01000000000000000000000000000000', 2, 10),
t64 = t64 | CONV('0100000000000000000000000000000000000000000000000000000000000000', 2, 10);
SELECT LPAD(CONV(t32, 10, 2), 32, "0") AS t32, LPAD(CONV(t64, 10, 2), 64, "0") as t64 FROM t
+----------------------------------+------------------------------------------------------------------+
| t32 | t64 |
+----------------------------------+------------------------------------------------------------------+
| 01100000000000000000000000000000 | 0110000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------+------------------------------------------------------------------+
# Watch out! Here it comes!
UPDATE t SET t32 = t32 | CONV('10000000000000000000000000000000', 2, 10),
t64 = t64 | CONV('1000000000000000000000000000000000000000000000000000000000000000', 2, 10);
SELECT LPAD(CONV(t32, 10, 2), 32, "0") AS t32, LPAD(CONV(t64, 10, 2), 64, "0") as t64 FROM t;
+----------------------------------+------------------------------------------------------------------+
| t32 | t64 |
+----------------------------------+------------------------------------------------------------------+
| 11100000000000000000000000000000 | 0111111111111111111111111111111111111111111111111111111111111111 |
+----------------------------------+------------------------------------------------------------------+
# This also happens when working with user-defined variables
SET @t32 = 0, @t64= 0;
SET @t32 = @t32 | CONV('10000000000000000000000000000000', 2, 10),
@t64 = @t64 | CONV('1000000000000000000000000000000000000000000000000000000000000000', 2, 10);
SELECT LPAD(CONV(@t32, 10, 2), 32, "0") AS '@t32', LPAD(CONV(@t64, 10, 2), 64, "0") as '@t64';
+----------------------------------+------------------------------------------------------------------+
| @t32 | @t64 |
+----------------------------------+------------------------------------------------------------------+
| 10000000000000000000000000000000 | 0111111111111111111111111111111111111111111111111111111111111111 |
+----------------------------------+------------------------------------------------------------------+
# Interestingly, you can initialize a BIGINT with just the left most bit set, you just can't 'or' it and preserve the other bits...
DELETE FROM t;
INSERT INTO t SET t32 = CONV('10000000000000000000000000000000', 2, 10),
t64 = CONV('1000000000000000000000000000000000000000000000000000000000000000', 2, 10)
SELECT LPAD(CONV(t32, 10, 2), 32, "0") AS '@t32', LPAD(CONV(t64, 10, 2), 64, "0") as '@t64' FROM t
+----------------------------------+------------------------------------------------------------------+
| @t32 | @t64 |
+----------------------------------+------------------------------------------------------------------+
| 10000000000000000000000000000000 | 1000000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------+------------------------------------------------------------------+
Kevin
B=)x
Description: When I try to assign a negative value to a BIGINT UNSIGNED field, the value recorded in the database is (maxvalue_bigint - value). This behaviour doesn't occur when I use other INT-fieldtypes. Inserting a negative value of -9223372036854775809 or lower records a 0, like the rest of the INT-types do with all negative values I tried to set in an unsigned field. I know I shouldn't insert a negative value into a unsigned field, but while hunting bugs in my own script I found out this behaviour. How to repeat: CREATE TABLE bigint_test (temp BIGINT UNSIGNED NOT NULL); INSERT INTO bigint_test VALUES(-1); INSERT INTO bigint_test VALUES('-1'); INSERT INTO bigint_test VALUES(-9223372036854775809); INSERT INTO bigint_test VALUES(-9223372036854775808); Output: mysql> select * from bigint_test; +----------------------+ | temp | +----------------------+ | 18446744073709551615 | | 0 | | 0 | | 9223372036854775808 | +----------------------+ 4 rows in set (0.00 sec) Suggested fix: Trying to insert any negative value records a zero in the database.