Question 348528 in Mysql Question posted in
The official MySQL documentation can be found here.

Porting JS/TS function to MySQL — Large Integers

TL/DR: What logic can I add to SELECT (-1816598198 << 8); to ensure that it returns -1192670720 like in JS/TS?

Full Question:

I’m having trouble porting a JS/TS function to MySQL. The function I have is:

const BuildIDLookupTable = [0x00000000, 0x04c11db7, 0x09823b6e, /* ... */ 0xb8757bda, 0xb5365d03, 0xb1f740b4];

function getBuildId(gPsyonixBuildId: string): number {
  let result = -1;

  for (const character of gPsyonixBuildId) {
    const charCode = character.charCodeAt(0);

    result = BuildIDLookupTable[(result >>> 24) ^ (charCode & 0xff)] ^ (result << 8);
    result = BuildIDLookupTable[(result >>> 24) ^ (charCode >>> 8)] ^ (result << 8);
  }

  return ~result;
}

I created a MySQL table, build_id_lookup, and inserted the proper values. I’ve got the MySQL function:

CREATE DEFINER=`stev`@`%` FUNCTION `getBuildId`(gPsyonixBuildId VARCHAR(256)) RETURNS bigint
    DETERMINISTIC
BEGIN
    DECLARE characterCode INT;
    DECLARE characterIndex INT DEFAULT 0;
    DECLARE gPsyonixBuildIdLength INT DEFAULT CHAR_LENGTH(gPsyonixBuildId);
    DECLARE result BIGINT DEFAULT -1;

    WHILE characterIndex < gPsyonixBuildIdLength DO
        SET characterCode = ASCII(SUBSTRING(gPsyonixBuildId, characterIndex, 1));
        
        SELECT CAST(`value` ^ (result << 8) AS SIGNED) INTO result FROM `build_id_lookup` WHERE `index` = result >> 24 & 0xff ^ (characterCode & 0xff);
        
        -- The (result << 8) yields unexpected results in comparison to JS/TS.
        SELECT CAST(`value` ^ (result << 8) AS SIGNED) INTO result FROM `build_id_lookup` WHERE `index` = result >> 24 & 0xff ^ (characterCode >> 8 & 0xff);
        
        SET characterIndex = characterIndex + 1;
    END WHILE;

    RETURN ~result;
END

I’m finding that MySQL’s (result << 0x8) is giving different results than JS/TS.

For example, SELECT (-1816598198 << 8); yields 18446743608660412928 in MySQL, but yields -1192670720 in JS/TS.

Thanks!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up creating a basic MySQL function to simulate overflow:

    CREATE DEFINER=`stev`@`%` FUNCTION `simulateOverflow`(number DECIMAL(65, 0)) RETURNS int
    DETERMINISTIC
BEGIN
  DECLARE largeNumber DECIMAL(65, 0) DEFAULT 4294967296;
  DECLARE maxInt DECIMAL(65, 0) DEFAULT 2147483647;
  DECLARE minInt DECIMAL(65, 0) DEFAULT -2147483648;
  DECLARE result INT;

  IF number > maxInt THEN
    SET result = number - FLOOR((number + maxInt + 2) / largeNumber) * largeNumber;
  ELSEIF number < minInt THEN
    SET result = number + FLOOR((number - minInt) / largeNumber) * -largeNumber;
  END IF;

  RETURN result;
END

    Which was just a port of my JS/TS function for doing the same:

    export function overflow(number: bigint): bigint;
export function overflow(number: number): number;
export function overflow(number: bigint | number): bigint | number {
  if (number > 2_147_483_647) {
    if (typeof number === 'bigint') {
      return number - ~~((number + 2_147_483_649n) / 4_294_967_296n) * 4_294_967_296n;
    }

    return number - ~~((number + 2_147_483_649) / 4_294_967_296) * 4_294_967_296;
  } else if (number < -2_147_483_648) {
    if (typeof number === 'bigint') {
      return number + ~~((number - 2_147_483_648n) / 4_294_967_296n) * -4_294_967_296n;
    }

    return number + ~~((number - 2_147_483_648) / 4_294_967_296) * -4_294_967_296;
  }

  return number;
}

  2. 0 
    -- convert to binary string
SELECT BIN(-1816598198) step1; 
-- shift then convert to binary string
SELECT BIN(-1816598198 << 8) step2;
-- cut off excess bits
SELECT RIGHT(BIN(-1816598198 << 8), 32) step3;
-- convert to decimal value
SELECT CONV(RIGHT(BIN(-1816598198 << 8), 32), 2, 10) step4;
-- adjust due to 1st bit is 1 (negative output)
SELECT CONV(RIGHT(BIN(-1816598198 << 8), 32), 2, 10) - 4294967296 step5;
    step1
    1111111111111111111111111111111110010011101110001110100101001010
    step2
    1111111111111111111111111001001110111000111010010100101000000000
    step3
    10111000111010010100101000000000
    step4
    3102296576
    step5
    -1192670720

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search