Bug #14161 Stored procedure cannot retrieve bigint unsigned
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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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