Compatibility issue - Possible corruption of values with min and max values for a double.

[markddrake]

Given the following example

import mysql from 'mysql2/promise'
import fs from 'fs'

class Test { 

    vendorProperties = {
      "multipleStatements":true
     ,"typeCast":true
     ,"supportBigNumbers":true
     ,"bigNumberStrings":true
     ,"dateStrings":true
     ,"trace":true
     ,"user":"root"
     ,"password": "oracle"
     ,"host":"yadamu-db1"
     ,"database":"sys"
     ,"port":3306
     , infileStreamFactory : (path) => {return fs.createReadStream(path)}
     }  

  async createConnectionPool() {

    let stack, operation

    try {
      stack = new Error().stack;
      operation = 'mysql.createPool()'  
      this.pool = mysql.createPool(this.vendorProperties)
      console.log('Pool Created')
    } catch (e) {
      throw e
    }

  }

  async getConnectionFromPool() {

    let stack

    try {    
      stack = new Error().stack;
      const connection = await this.pool.getConnection()
      console.log('Connection obtained')
      return connection
    } catch (err) {
      throw err 
    }
  }

  async closeConnection(options) {

    if ((this.connection !== undefined) && (typeof this.connection.release === 'function')) {
      let stack;
      try {
        stack = new Error().stack
        await this.connection.release()
        this.connection = undefined;
      } catch (e) {
        this.connection = undefined;
        throw e
      }
    }
  };

  async closePool(options) {

    if ((this.pool !== undefined) && (typeof this.pool.end === 'function')) {
      let stack;
      try {
        stack = new Error().stack
        await this.pool.end()
        this.pool = undefined;
      } catch (e) {
        this.pool = undefined;
        throw e
      }
    }
  }

  async executeSQL(sqlStatement,args) {

    let stack
    let results

      try {
        stack = new Error().stack;
        const [results,fields] = await this.connection.query(sqlStatement,args)
        return results;
      } catch (e) {
        throw e
      }
  }

  async test() {
      const data = [[1 , -1.7976931348623157e308],[2,1.7976931348623157e308]]
      let results
      try {
        await this.createConnectionPool()
        this.connection = await this.getConnectionFromPool()
        results = await this.executeSQL(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON'`);
        results = await this.executeSQL(`CREATE TEMPORARY table if not exists "t1"("key" smallint  ,"double_col" double); `);
        results = await this.executeSQL(`insert into "t1"("key","double_col")  values  ?`,[data]);
        results = await this.executeSQL(`select "key", "double_col", cast("double_col" as char(64)) as "cast_result" from "t1"`);
        console.log(results)
        await this.closeConnection();
        await this.closePool();
      } catch (e) {
        await this.closeConnection();
        await this.closePool();
        console.log(e)
      }
  }

}

const test = new Test();
test.test().then(() => console.log('Success')).catch((e) => console.log(e))

I get

C:\Development\YADAMU>node src\scratch\mysql\dbIssue.js
Pool Created
Connection obtained
[
  {
    key: 1,
    double_col: -1.7976931348623155e+308,
    cast_result: '-1.7976931348623157e308'
  },
  {
    key: 2,
    double_col: 1.7976931348623155e+308,
    cast_result: '1.7976931348623157e308'
  }
]
Success

As you can see the values in question appear to have been stored correctly in the database, given the cast() returns the expected values, but the values retrieved appear to have been corrupted at the least significant digits. This is working with the lastest MYSQL Docker VM (9.x).

I als see the issue when I run MYSQL2 against the latest MYSQL 8.0 docker container.

[markddrake]

MySQL version of test case

import mysql from 'mysql'
import fs from 'fs'

class Test { 

    vendorProperties = {
      "multipleStatements":true
     ,"typeCast":true
     ,"supportBigNumbers":true
     ,"bigNumberStrings":true
     ,"dateStrings":true
     ,"trace":true
     ,"user":"root"
     ,"password": "oracle"
     ,"host":"yadamu-db1"
     ,"database":"sys"
     ,"port":33061
     , infileStreamFactory : (path) => {return fs.createReadStream(path)}
     }  

  async createConnectionPool() {

    let stack, operation

    try {
      stack = new Error().stack;
      operation = 'mysql.createPool()'  
      this.pool = mysql.createPool(this.vendorProperties)
      console.log('Pool Created')
    } catch (e) {
      throw e
    }

  }

  async getConnectionFromPool() {

    const connection = await new Promise((resolve,reject) => {
      this.pool.getConnection((err,connection) => {
        if (err) {
          reject(this.getDatabaseException(this.DRIVER_ID,err,stack,'mysql.Pool.getConnection()'))
        }
        resolve(connection)
      })
    })

    return connection
  }

  async closeConnection(options) {

    if ((this.connection !== undefined) && (typeof this.connection.release === 'function')) {
      let stack;
      try {
        stack = new Error().stack
        await this.connection.release()
        this.connection = undefined;
      } catch (e) {
        this.connection = undefined;
        throw e
      }
    }
  };

  async closePool(options) {

    if ((this.pool !== undefined) && (typeof this.pool.end === 'function')) {
      let stack;
      try {
        stack = new Error().stack
        await this.pool.end()
        this.pool = undefined;
      } catch (e) {
        this.pool = undefined;
        throw e
      }
    }
  }

  executeSQL(sqlStatement,args) {

    return new Promise((resolve,reject) => {

      const stack = new Error().stack;
      const sqlStartTime = performance.now() 
      this.connection.query(sqlStatement,args,async (err,results,fields) => {
        console.log(results)
        resolve(results)
      })
    })
  }  

  async test() {
      const data = [[1 , -1.7976931348623157e308],[2,1.7976931348623157e308]]
      let results
      try {
        await this.createConnectionPool()
        this.connection = await this.getConnectionFromPool()
        results = await this.executeSQL(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON'`);
        results = await this.executeSQL(`CREATE TEMPORARY table if not exists "t1"("key" smallint  ,"double_col" double); `);
        results = await this.executeSQL(`insert into "t1"("key","double_col")  values  ?`,[data]);
        results = await this.executeSQL(`select "key", "double_col", cast("double_col" as char(64)) as "cast_result" from "t1"`);
        console.log(results)
        await this.closeConnection();
        await this.closePool();
      } catch (e) {
        await this.closeConnection();
        await this.closePool();
        console.log(e)
      }
  }

}

const test = new Test();
test.test().then(() => console.log('Success')).catch((e) => console.log(e))

results in

C:\Development\YADAMU>node src\scratch\mysql\dbIssue-MYSQL.js
Pool Created
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 0,
  warningCount: 1,
  message: '',
  protocol41: true,
  changedRows: 0
}
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 1,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}
OkPacket {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 1,
  warningCount: 0,
  message: '&Records: 2  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0
}
[
  RowDataPacket {
    key: 1,
    double_col: -1.7976931348623157e+308,
    cast_result: '-1.7976931348623157e308'
  },
  RowDataPacket {
    key: 2,
    double_col: 1.7976931348623157e+308,
    cast_result: '1.7976931348623157e308'
  }
]
[
  RowDataPacket {
    key: 1,
    double_col: -1.7976931348623157e+308,
    cast_result: '-1.7976931348623157e308'
  },
  RowDataPacket {
    key: 2,
    double_col: 1.7976931348623157e+308,
    cast_result: '1.7976931348623157e308'
  }
]
Success

[markddrake]

Simplified Test Case (No Table Required)..

import mysql from 'mysql2/promise'
import fs from 'fs'

class Test { 

    vendorProperties = {
      "multipleStatements":true
     ,"typeCast":true
     ,"supportBigNumbers":true
     ,"bigNumberStrings":true
     ,"dateStrings":true
     ,"trace":true
     ,"user":"root"
     ,"password": "oracle"
     ,"host":"yadamu-db1"
     ,"database":"sys"
     ,"port":3306
     , infileStreamFactory : (path) => {return fs.createReadStream(path)}
     }  

  async createConnectionPool() {

    let stack, operation

    try {
      stack = new Error().stack;
      operation = 'mysql.createPool()'  
      this.pool = mysql.createPool(this.vendorProperties)
      console.log('Pool Created')
    } catch (e) {
      throw e
    }

  }

  async getConnectionFromPool() {

    let stack

    try {    
      stack = new Error().stack;
      const connection = await this.pool.getConnection()
      console.log('Connection obtained')
      return connection
    } catch (err) {
      throw err 
    }
  }

  async closeConnection(options) {

    if ((this.connection !== undefined) && (typeof this.connection.release === 'function')) {
      let stack;
      try {
        stack = new Error().stack
        await this.connection.release()
        this.connection = undefined;
      } catch (e) {
        this.connection = undefined;
        throw e
      }
    }
  };

  async closePool(options) {

    if ((this.pool !== undefined) && (typeof this.pool.end === 'function')) {
      let stack;
      try {
        stack = new Error().stack
        await this.pool.end()
        this.pool = undefined;
      } catch (e) {
        this.pool = undefined;
        throw e
      }
    }
  }

  async executeSQL(sqlStatement,args) {

    let stack
    let results

      try {
        stack = new Error().stack;
        const [results,fields] = await this.connection.query(sqlStatement,args)
        return results;
      } catch (e) {
        throw e
      }
  }

  async test() {
      const minVal = -1.7976931348623157e308
      const maxVal = 1.7976931348623157e308
      let results
      try {
        await this.createConnectionPool()
        this.connection = await this.getConnectionFromPool()
        results = await this.executeSQL(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON'`);
        results = await this.executeSQL(`select ? as "C1",cast(? as char(64)) as "C2",? as "C3",cast(? as char(64)) as "C4",${minVal} as "C5",cast(${minVal} as char(64)) as "C6",${maxVal} as "C7",cast(${maxVal} as char(64)) as "C8"`,[minVal,minVal,maxVal,maxVal]);
        console.log(results)
        const compare = {
          C1: results[0].C1 === minVal
        , C2: parseFloat(results[0].C2) === minVal
        , C3: results[0].C3 === maxVal
        , C4: parseFloat(results[0].C4) === maxVal
        , C5: results[0].C5 === minVal
        , C6: parseFloat(results[0].C6) === minVal
        , C7: results[0].C7 === maxVal
        , C8: parseFloat(results[0].C8) === maxVal
        }
        console.log(compare)
        await this.closeConnection();
        await this.closePool();
      } catch (e) {
        await this.closeConnection();
        await this.closePool();
        console.log(e)
      }
  }

}

const test = new Test();
test.test().then(() => console.log('Success')).catch((e) => console.log(e))

Results in

C:\Development\YADAMU>node src\scratch\mysql\dbleIssue2.js
Pool Created
Connection obtained
[
  {
    C1: -1.7976931348623155e+308,
    C2: '-1.797693134862316e308',
    C3: 1.7976931348623155e+308,
    C4: '1.7976931348623157e308',
    C5: -1.7976931348623155e+308,
    C6: '-1.797693134862316e308',
    C7: 1.7976931348623155e+308,
    C8: '1.7976931348623157e308'
  }
]
{
  C1: false,
  C2: false,
  C3: false,
  C4: true,
  C5: false,
  C6: false,
  C7: false,
  C8: true
}
Success

C:\Development\YADAMU>

Expected results are 'true' for C1 thru C8. I can use this test to 'duck-type' the error and code a workaround for moment.