Bug #39584 Treating unsigned BIGINT as signed BIGINT in INSERT ON DUPLICATE KEY UPDATE

• Submitted: 22 Sep 2008 11:10
• Modified: 22 Sep 2008 19:52
• Reporter: Thomas Jensen
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Windows
• CPU Architecture: Any

[22 Sep 2008 11:10] Thomas Jensen

Description:
Treating unsigned BIGINT as signed BIGINT in INSERT ON DUPLICATE KEY UPDATE when assigning updated value to an user variable.

This example shows a solution of how to implement a simple Serial Number Generator. A table to store the last given serial number for a given name and 2 stored procedures to increment the serial number.
The stored procedure named IncrementSerialNo is just incrementing the serial number without returning the "given serial number".
The stored procedure named GetSerialNo does the same but also returns the given Serial Number - this is interesting since the incrementing and setting variable is done atomic. (so we don't need to lock table)
The only problem is that when the serial number is bigger than 9223372036854775807 the GetSerialNo function begins to fail. 9223372036854775807 is the biggest value for a signed BIGINT, but the SerialNo column in table sernogenerator is unsigned BIGINT.

How to repeat:
CREATE DATABASE sernogenerator;

CREATE TABLE  `sernogenerator`.`sernogenerator` (
  `Name` varchar(45) NOT NULL,
  `SerialNo` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

USE sernogenerator;

DELIMITER $$
CREATE PROCEDURE  `sernogenerator`.`IncrementSerialNo`(pGeneratorName TEXT)
BEGIN
  INSERT INTO sernogenerator (Name, SerialNo) VALUES (pGeneratorName, 0) ON DUPLICATE KEY UPDATE SerialNo=SerialNo+1;
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE  `sernogenerator`.`GetSerialNo`(pGeneratorName TEXT,
                                                 OUT pGivenSerialNo BIGINT UNSIGNED)
BEGIN
  SET @tmpSerNo=0;
  INSERT INTO sernogenerator (Name, SerialNo) VALUES (pGeneratorName, 0) ON DUPLICATE KEY UPDATE SerialNo=IF((@tmpSerNo:=SerialNo+1)<>NULL IS NULL,SerialNo+1, NULL);
  SET pGivenSerialNo=@tmpSerNo;
END $$
DELIMITER ;

CALL IncrementSerialNo('MySerNo');

CALL GetSerialNo('MySerNo', @SerialNo);
SELECT @SerialNo;

UPDATE sernogenerator SET SerialNo=9223372036854775806 WHERE Name='MySerNo';
CALL IncrementSerialNo('MySerNo');
CALL IncrementSerialNo('MySerNo');
CALL IncrementSerialNo('MySerNo');
SELECT * FROM sernogenerator;

UPDATE sernogenerator SET SerialNo=9223372036854775806 WHERE Name='MySerNo';
CALL GetSerialNo('MySerNo', @SerialNo);
SELECT @SerialNo;
CALL GetSerialNo('MySerNo', @SerialNo);  ----> this will cause error: ERROR 1264 (22003): Out of range value adjusted for column 'SerialNo' at row 1

[22 Sep 2008 19:52] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html about casting issues with using UNSIGNED BIGINT in expressions.

Workaround: use explicit cast:

  INSERT INTO sernogenerator (Name, SerialNo) VALUES (pGeneratorName, 0) ON DUPLICATE KEY
UPDATE SerialNo=cast(IF((@tmpSerNo:=cast((SerialNo+1) as unsigned))<>NULL IS NULL,SerialNo+1, NULL) as unsigned);