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.

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.