Bug #14161 Stored procedure cannot retrieve bigint unsigned
Submitted: 19 Oct 2005 23:40 Modified: 7 Dec 2005 20:44
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.13-rc, 5.0.16-nightly-20051017-debug OS:Linux (Linux, Windows)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[19 Oct 2005 23:40] Shane Bester
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
[7 Dec 2005 20:44] Paul Dubois
Noted in 5.0.18 changelog.