Regression: Number printed as floating-point when bound to VARCHAR column

[assen-totin]

After upgrading a demo app from your connector 1.1 to the latest 2.3 series, I stumbled onto the following regression: we use prepared statements to insert textual data into a VARCHAR column. Sometimes, however, the textual data will consist only of digits (and will therefore be interpreted internally by NodeJS as an integer; the source of the data is an JSON file that is loaded from a text file).

With the current version of the connector, this implicit number will passed to the database, rendered as a floating-point number. This way, 8000020190424152000 gets sent to the database as 8.000020190424152e18 - which is completely wrong and unacceptable.

The older version that we had, 1.1, works properly and the database receives 8000020190424152000.

The connector is supposed to check the datatype of the target column when binding the value and apply explicit conversion to text if the column is textual.

[sidorares]

hi @assen-totin , can you reduce your case to a small self contained example? This sounds like a bug, a PS string input parameter "8000020190424152000" should not auto convert to a float

[assen-totin]

Hello, @sidorares,

Sure, here is the example I used for a quick PoC when tracing the issue:

CREATE TABLE t (id SERIAL PRIMARY KEY, c VARCHAR(255));

var mysql2 = require('mysql2');
var config = {host: 'some_host', user: 'some_user', password: 'some_password', database: 'some_database'};
var db = mysql2.createConnection(config);
var query = "INSERT INTO t (c) VALUES (?) RETURNING id";
var values = [8000020190424152000];

db.connect(function(error){
	if (error)
		return console.log(error);

	db.prepare(query, function(error, result){
		if (error)
			return console.log(error);

		result.execute(values, function(error, result, meta) {
			console.log(error);
			console.log(result);

			db.end();
		});
	});
});

The databases tested were MariaDB 10.5 and 10.6, but this should not matter. NodeJS is version 14.

Run the script once with connector 1.1.x and once with 2.3.x, then select the records from the table. The one is taken with connector 1.1 and the data is sent to the DB as text. The second is taken with connector 2.3 and the data lands on the DB as floating-point number. Note that the target column is VARCHAR, so no matter what we write there, it should be passed to the DB as a string literal.

MariaDB [test]> select * from t;
+----+----------------------+
| id | c                    |
+----+----------------------+
|  1 | 8000020190424152000  |
|  2 | 8.000020190424152e18 |
+----+----------------------+
2 rows in set (0.003 sec)

A workaround is, of course, to forcefully change the internal data type to string, but this is a bit ugly and should not be necessary.

var values = ['' + 8000020190424152000];

WWell,

Assen

[sidorares]

main problem is that your input is way over Number.MAX_SAFE_INTEGER. .execute() serialises it as 64 bit float, and .query() just interpolates input as a string and then sends the string as an sql query.

Even with .query() you'd get unexpected results - try to use 8000020190424152001 as an input for example and you'll see that actual inserted value is 8000020190424152000

I'll try to think if I can add some sort of warning about potential precision loss on the driver side

To me the fix should be on your side by just using strings instead numbers that can potentially be outside of range that JS can handle

[assen-totin]

I don't think a warning is worth it.

It worked OK with 1.1 series connectors, so this is a regression.

Also, as I mentioned, the connector should know the datatype of the column and send the data accordingly. While MariaDB may be forgiving, no big database will allow you to pass a number to a text field - you will need to pass it text. And if you pass text, then make sure to cast the data to text and do not rely on the JS datatype whatsoever.