Bug #93314 Error while Inserting NULL values
Submitted: 23 Nov 2018 11:11 Modified: 4 Dec 2018 0:36
Reporter: Chirag Shah (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector for Node.js Severity:S2 (Serious)
Version:8.0.13 OS:Windows
Assigned to: Rui Quelhas CPU Architecture:Any
Tags: MySQL, NodeJS, XDevAPI

[23 Nov 2018 11:11] Chirag Shah
Description:
I am unable to insert NULL values using Connector for Node.js

This is my code block
-----
let InsertData = async (tableName, columns, values) => {
  try {
    let data = await GetTable(tableName); //Here it will get return me table object.
    let { table, session } = data;
    console.log({ values });

    let insert = await table
      .insert(columns) //columns is Array
      .values(values) // values is also Array
      .execute();
    session.close();
    return {
      result: GetResult(insert) // Here I am getting final result object
    };
  } catch (ex) {
    console.log(ex);
    return null;
  }
};
-----

Error: invalid input expression
    at module.exports (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\DevAPI\Util\parseFlexibleParamList.js:43:15)
    at Object.values (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\DevAPI\TableInsert.js:106:28)
    at Object.InsertData (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\src\server\database.js:115:8)
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:188:7)

How to repeat:
table.insert(['name','isActive']).values([null,1]).execute();

Here I tried null and undefined both values but it didn't work.
I don't know how to insert NULL value into database using this connector.

Suggested fix:
null/undefined from JS can automatically convert to NULL into MySQL.
[4 Dec 2018 0:36] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Node.js 8.0.14 release, and here's the changelog entry:

Attempting to use false-like values such as 0, false, null, and undefined
would emit errors when inserting a new table row. Additionally, now
boolean values become numeric values (true=1, false=0) while null and
undefined are converted to MySQL's NULL type.

Thank you for the bug report.