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)
• Status: Closed
• 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.