Description:
When selecting a column that has a value outside the range of BIGINT SIGNED, it gets
transformed to a negative number. See here:
mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS cp_test;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test(col1 BIGINT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO test VALUE (18446744073709551614);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> delimiter //
mysql> CREATE PROCEDURE cp_test(IN i_col1 BIGINT UNSIGNED)
-> BEGIN
-> SELECT i_col1;
->
-> SELECT * FROM test WHERE col1 = i_col1;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> CALL cp_test(18446744073709551614);
+--------+
| i_col1 |
+--------+
| -2 |
+--------+
1 row in set (0.00 sec)
Empty set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> select version();
+-------------------------------+
| version() |
+-------------------------------+
| 5.0.16-nightly-20051017-debug |
+-------------------------------+
How to repeat:
USE test;
DROP TABLE IF EXISTS test;
DROP PROCEDURE IF EXISTS cp_test;
CREATE TABLE test(col1 BIGINT UNSIGNED);
INSERT INTO test VALUE (18446744073709551614);
delimiter //
CREATE PROCEDURE cp_test(IN i_col1 BIGINT UNSIGNED)
BEGIN
SELECT i_col1;
SELECT * FROM test WHERE col1 = i_col1;
END
//
delimiter ;
CALL cp_test(18446744073709551614);
Suggested fix:
Make sure UNSIGNED values are returned correctly
Description: When selecting a column that has a value outside the range of BIGINT SIGNED, it gets transformed to a negative number. See here: mysql> USE test; Database changed mysql> DROP TABLE IF EXISTS test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP PROCEDURE IF EXISTS cp_test; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE test(col1 BIGINT UNSIGNED); Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO test VALUE (18446744073709551614); Query OK, 1 row affected (0.00 sec) mysql> mysql> delimiter // mysql> CREATE PROCEDURE cp_test(IN i_col1 BIGINT UNSIGNED) -> BEGIN -> SELECT i_col1; -> -> SELECT * FROM test WHERE col1 = i_col1; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> CALL cp_test(18446744073709551614); +--------+ | i_col1 | +--------+ | -2 | +--------+ 1 row in set (0.00 sec) Empty set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> select version(); +-------------------------------+ | version() | +-------------------------------+ | 5.0.16-nightly-20051017-debug | +-------------------------------+ How to repeat: USE test; DROP TABLE IF EXISTS test; DROP PROCEDURE IF EXISTS cp_test; CREATE TABLE test(col1 BIGINT UNSIGNED); INSERT INTO test VALUE (18446744073709551614); delimiter // CREATE PROCEDURE cp_test(IN i_col1 BIGINT UNSIGNED) BEGIN SELECT i_col1; SELECT * FROM test WHERE col1 = i_col1; END // delimiter ; CALL cp_test(18446744073709551614); Suggested fix: Make sure UNSIGNED values are returned correctly