Bug #93381 Error while insert/update TINYINT column with falsy values (0 or false)
Submitted: 28 Nov 2018 9:40 Modified: 7 Dec 2018 22:12
Reporter: Chirag Shah (OCA) Email Updates:
Status: Duplicate 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

[28 Nov 2018 9:40] Chirag Shah
Description:
While inserting or updating TINYINT column with 0 or false it gives error.

Error while Insert
---
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)

Error while Update
---
{ Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (`id` = 4)' at line 1
    at SqlResultHandler.BaseHandler.(anonymous function) (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:113:19)
    at Array.entry (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:90:29)
    at WorkQueue.process (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\WorkQueue.js:75:19)
    at Client.handleServerMessage (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:222:21)
    at Client.handleNetworkFragment (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:266:14)
    at TLSSocket.stream.on.data (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:72:36)
    at emitOne (events.js:116:13)
    at TLSSocket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
  info:
   { severity: 0,
     code: 1064,
     sqlState: '42000',
     msg: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'WHERE (`id` = 4)\' at line 1' } }

How to repeat:
Insert
---
table.insert(['isActive']).values([false]).execute(); // not working
table.insert(['isActive']).values([0]).execute(); // not working
table.insert(['isActive']).values(['0']).execute(); // working perfectly

Update
---
table.update().where('id = 19').set('isActive', false).execute(); // not working
table.update().where('id = 19').set('isActive', 0).execute(); //not working
table.update().where('id = 19').set('isActive', '0').execute(); //working perfectly

Suggested fix:
It should work with 0 or false.
[7 Dec 2018 22:12] Rui Quelhas
Hi Chirag,

thanks for the bug report and for your interest in MySQL and Connector/Node.js.

As we discussed before, I'll be closing this one as a duplicate of the following reports:

- BUG#93314
- BUG#93315

Since the fix for both would encompass any valid JavaScript falsy value.