Bug #54537 bigint bug

• Submitted: 16 Jun 2010 0:45
• Modified: 16 Jun 2010 22:39
• Reporter: John NIU
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.1.46, 5.1.49-bzr
• OS: Linux (CENTOS5.4)
• CPU Architecture: Any

[16 Jun 2010 0:45] John NIU

Description:

the following procedure , in "DEBUG1" and "DEBUG2", the biUniqueId_Out values is different.

--
-- iccdb_organisation_getsysuniqueid_test
--
DROP PROCEDURE IF EXISTS `iccdb_organisation_getsysuniqueid_test`;

CREATE PROCEDURE iccdb_organisation_getsysuniqueid_test(OUT biUniqueId_Out BIGINT UNSIGNED, iOrgId_In INT UNSIGNED, iNumber INT)
BEGIN
        DECLARE biUniqueId      BIGINT UNSIGNED;

        SET biUniqueId_Out = (iOrgId_In * 1000000 + SUBSTRING(NOW()+0,3,6)) * 1000000 + iNumber;
        SELECT "DEBUG 1",biUniqueId_Out;

        SET biUniqueId_Out = iOrgId_In * 1000000 + SUBSTRING(NOW()+0,3,6);
        SET biUniqueId_Out = biUniqueId_Out * 1000000 + iNumber;
        SELECT "DEBUG 2",biUniqueId_Out;
END //

when you call the procedure:
call iccdb_organisation_getsysuniqueid_test(@abc,9008, 1001), then you got the following result:

+---------+------------------+
| DEBUG 1 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 1 | 9008100616001000 |
+---------+------------------+
+---------+------------------+
| DEBUG 2 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 2 | 9008100616001001 |

How to repeat:
when you call the procedure:
call iccdb_organisation_getsysuniqueid_test(@abc,9008, 1001), then you got the following result:

+---------+------------------+
| DEBUG 1 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 1 | 9008100616001000 |
+---------+------------------+
+---------+------------------+
| DEBUG 2 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 2 | 9008100616001001 |

[16 Jun 2010 6:08] Valeriy Kravchuk

Verified just as described:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter //
mysql> CREATE PROCEDURE iccdb_organisation_getsysuniqueid_test(OUT biUniqueId_Out BIGINT
    -> UNSIGNED, iOrgId_In INT UNSIGNED, iNumber INT)
    -> BEGIN
    ->         DECLARE biUniqueId      BIGINT UNSIGNED;
    -> 
    ->         SET biUniqueId_Out = (iOrgId_In * 1000000 + SUBSTRING(NOW()+0,3,6)) * 1000000 +
    -> iNumber;
    ->         SELECT "DEBUG 1",biUniqueId_Out;
    -> 
    ->         SET biUniqueId_Out = iOrgId_In * 1000000 + SUBSTRING(NOW()+0,3,6);
    ->         SET biUniqueId_Out = biUniqueId_Out * 1000000 + iNumber;
    ->         SELECT "DEBUG 2",biUniqueId_Out;
    -> END //
Query OK, 0 rows affected (0.06 sec)

mysql> call iccdb_organisation_getsysuniqueid_test(@abc,9008, 1001);
    -> //
+---------+------------------+
| DEBUG 1 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 1 | 9008100616001000 |
+---------+------------------+
1 row in set (0.40 sec)

+---------+------------------+
| DEBUG 2 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 2 | 9008100616001001 |
+---------+------------------+
1 row in set (0.40 sec)

Query OK, 0 rows affected (0.40 sec)

I feel this has something to do with mixing INT (iNumber), INT UNSIGNED and BIGINT UNSIGNED in one expression.

[16 Jun 2010 6:11] Valeriy Kravchuk

Sorry, I was wrong. With BIGINT UNSIGNED everywhere result is the same:

mysql> CREATE PROCEDURE iccdb_organisation_getsysuniqueid_test(OUT biUniqueId_Out BIGINT
    -> UNSIGNED, iOrgId_In BIGINT UNSIGNED, iNumber BIGINT UNSIGNED)
    -> BEGIN
    ->         DECLARE biUniqueId      BIGINT UNSIGNED;
    -> 
    ->         SET biUniqueId_Out = (iOrgId_In * 1000000 + SUBSTRING(NOW()+0,3,6)) * 1000000 +
    -> iNumber;
    ->         SELECT "DEBUG 1",biUniqueId_Out;
    -> 
    ->         SET biUniqueId_Out = iOrgId_In * 1000000 + SUBSTRING(NOW()+0,3,6);
    ->         SET biUniqueId_Out = biUniqueId_Out * 1000000 + iNumber;
    ->         SELECT "DEBUG 2",biUniqueId_Out;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> call iccdb_organisation_getsysuniqueid_test(@abc,9008, 1001)//
+---------+------------------+
| DEBUG 1 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 1 | 9008100616001000 |
+---------+------------------+
1 row in set (0.00 sec)

+---------+------------------+
| DEBUG 2 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 2 | 9008100616001001 |
+---------+------------------+
1 row in set (0.00 sec)

[16 Jun 2010 6:19] Sveta Smirnova

Problem is type of SUBSTRING(NOW()+0,3,6): cast(SUBSTRING(NOW()+0,3,6) AS UNSIGNED) in first expression solves the problem.

[16 Jun 2010 22:39] John NIU

Thanks all your reply,

Thanks Sveta Smirnova, I think you give us a good solution, but not means there's no problem with bigint calculation.

  call iccdb_organisation_getsysuniqueid_test(@abc,9008, 1001) 
if change input parameter 9008 to any number less than 9008, it all work well.
for example call iccdb_organisation_getsysuniqueid_test(@abc,9007, 1001)
+---------+------------------+
| DEBUG 1 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 1 | 9007100617001001 |
+---------+------------------+
+---------+------------------+
| DEBUG 2 | biUniqueId_Out   |
+---------+------------------+
| DEBUG 2 | 9007100617001001 |

anyway, thanks a lot for all your reply.