The bigint column is treated as a string in the query (in mysql log)

[imjuni]

Problem

Table

CREATE TABLE IF NOT EXISTS `super_hero` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `property` bigint(20) NOT NULL,
  `kind` char(10) NOT NULL,
  `name` varchar(200) NOT NULL,
  primary key (`id`),
  key `idx_property` (`property`),
  key `idx_kind_property` (`kind`, `property`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Database Connection

const conn = await mysql.createConnection({
    host: "localhost",
    port: 15000,
    user: "root",
    password: process.env.PW,
    database: "biginttest",
    supportBigNumbers: true,
    bigNumberStrings: true,
  });

Insert

const insertData = `INSERT INTO super_hero (\`property\`, \`name\`) VALUES (?, ?)`;
const r1 = await conn.query(insertData, ["36028797018963970", "marvel", "ironman"]); // 2^55
const r2 = await conn.query(insertData, ["288230376151711740", "marvel", "black panther", ]); // 2^58

Select

const selectData = `SELECT * FROM super_hero WHERE kind = ? and property > ?`;
const [r1, _fields] = await conn.execute(selectData, [
    "marvel",
    "36028797018963970",
  ]);

Ok, I got [ { id: '2', property: '288230376151711740', kind: 'marvel', name: 'black panther' } ]. This is reasonable. Because JavaScript only manipulate 2^53 range. But I found my query in mysql query log.

Bigint column pass string.

Environment

• node 14.20.1
• mysql2 3.2.0
• mysql 5.7.41

Expect

I think, Node.js manipulate bigint column by string, final query populate numeric value.

-- Current query
SELECT * FROM super_hero WHERE property = '36028797018963970'

-- Expect query
SELECT * FROM super_hero WHERE property = 36028797018963970

The current query may cause 'table-scan'. 'bigint' column compare that using by string value.

Reproducable Repo.

• https://github.com/imjuni/mysql-bigint

npm install
node src/create.js
node src/insert.js
node src/select.js

show variables like 'general%';
set global general_log = 'ON'

And You can connect docker terminal. tail -f command execute log file.

[sidorares]

Hi @imjuni ,

First of all - thanks for your effort putting all the context for the issue, I wish all reports were of this quality!

Can we start by researching if this an issue at all? How do you know that "current query may cause 'table-scan'. 'bigint' column compare that using by string value.". I had an impression that if you have an index for property its used regardless of the type of the parameter passed

[imjuni]

Hi @sidorares, Thank you for your flash feedback.

I completly agree with you. The problem I mentioned that was feedback from DBA in my company.

She said that bigint and string compare operation cause type conversion so cannot exactly measure on table scan range.

I don't completly agree this feedback. But I want to remove every possibilities of table-scan. So I found some of option, cannot found.

I update my reproducable repo. Add random scripts and change table schema.

I completly agree with you. The problem I mentioned that was feedback from DBA in my company.

She said that bigint and string compare operation cause type conversion so cannot exactly measure on table scan range.

I don't completly agree this feedback. Because MySQL does Type Conversion on SQL Query so string convert to BigInt number. But If SQL queries execute without Type Conversion, there are several benefits:

1. save Type Conversion cost
2. increase readability
3. prevent weird result

For example of weird result,

INSERT INTO super_hero (`property`,  `kind`, `name`) VALUES (0, 'marvel', 'spiderman');
SELECT * FROM super_hero WHERE property = 'it cannot convert to number';

-- MySQL type conversion fail, so that replace to 0 (number zero), so MySQL return 'spiderman' row
-- SELECT CONVERT('it cannot convert to number', UNSIGNED) > MySQL Type Conversion Query

I update my reproducable repo. Add random scripts and change table schema.

Also I understand that this issue is outside the scope of the node-mysql2.

[sidorares]

with .query() you can force a string to be inserted without quotation by using raw() function:

const { raw } = require('mysql2');

const selectData = `SELECT * FROM super_hero WHERE kind = ? and property > ?`;
const [r1, _fields] = await conn.query(selectData, [
    "marvel",
    raw("36028797018963970")
  ]);

For .execute() we need to make a decision somehow (prepare type hint, javascript type passed) -> reported type, converted value matching reported type. See some discussion in #1407

[sidorares]

SELECT * FROM super_hero WHERE property = 'it cannot convert to number'

I'd like to add some degree of runtime type safety here for the case of .execute() / prepared statements, but again it's complicated. Quite often the reported type for a prepared statement is not known or not accurate and will be a generic VARCHAR ( example: select ? as result ). Again, see #1407

For .query() its simply not possible, we don't try to understand sql semantics and just do a very basic string interpolation + santitization

[imjuni]

Thank you for your feedback! I solve my problem by your answer.

const { raw } = require('mysql2');

const selectData = `SELECT * FROM super_hero WHERE kind = ? and property > ?`;
const [r1, _fields] = await conn.query(selectData, [
    "marvel",
    raw("36028797018963970")
  ]);

This query logged by MySQL query engine like that,

MySQL query logger logged number instead of numeric string. This function exactly I want it. I close this issue.

Thank you! 👍