Bug #16108 Incorrect result of assigning CONV to a variable in a function/procedure

• Submitted: 30 Dec 2005 22:55
• Modified: 3 Jan 2006 17:55
• Reporter: Dmitry Apresian
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.16
• OS: Linux (Linux)
• CPU Architecture: Any

[30 Dec 2005 22:55] Dmitry Apresian

Description:
When a hex string representing a number greater that 7FFFFFFFFFFFFFFF is passed to CONV and the result is assigned to a variable, the variable contains 7FFFFFFFFFFFFFFF.

How to repeat:
delimiter $

drop function if exists `ConvBug`$

create function `ConvBug`(chunk varchar(16)) returns varchar(16)
begin
	declare val bigint unsigned;

	set val = conv(chunk, 16, 10);
	return val;
end
$

delimiter ;

select ConvBug('8000000000000000');

Suggested fix:
This may be related to bug #7751, perhaps a fix is similar?

[3 Jan 2006 17:13] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with 5.0.19-BK (ChangeSet@1.2003.3.1, 2005-12-28 20:30:57+01:00) on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter $
mysql> create function `ConvBug`(chunk varchar(16)) returns varchar(16)
    -> begin
    -> declare val bigint unsigned;
    ->
    -> set val = conv(chunk, 16, 10);
    -> return val;
    -> end
    -> $
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select ConvBug('8000000000000000');
+-----------------------------+
| ConvBug('8000000000000000') |
+-----------------------------+
| 9223372036854775            |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------
----+
| Level   | Code | Message
    |
+---------+------+--------------------------------------------------------------
----+
| Warning | 1265 | Data truncated for column 'ConvBug('8000000000000000')' at row 1 |
+---------+------+--------------------------------------------------------------
----+
1 row in set (0.00 sec)

mysql> select conv('8000000000000000', 16, 10);
+----------------------------------+
| conv('8000000000000000', 16, 10) |
+----------------------------------+
| 9223372036854775808              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> drop function if exists `ConvBug`;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $
mysql> create function `ConvBug`(chunk varchar(16)) returns varchar(32) begin declare val bigint unsigned; set val = conv(chunk, 16, 10); return val; end$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select ConvBug('8000000000000000');
+-----------------------------+
| ConvBug('8000000000000000') |
+-----------------------------+
| 9223372036854775808         |
+-----------------------------+
1 row in set (0.00 sec)

Looks like the problem, if any, is fixed already.

[3 Jan 2006 17:55] Dmitry Apresian

Thanks for the heads up. I've installed 5.0.18 and the problem appears to have gone away.