Description
Apologies if this should not have been filed as an Issue (unfortunately I was not able to provoke a discussion on the mailing list -- perhaps I should take that as a hint!).
Right now SqlString.escape() maps both JavaScript null and undefined to SQL NULL. I am wondering if instead JavaScript null should be SQL NULL but JavaScript undefined should be mapped to DEFAULT.
My reasoning is driven by two things: it seems to be sensible to let undefined values default to whatever default is specified in the DB. Second, because MySQL treats each NULL as a unique value multiple records with the same non-NULL unique keys can be created if partial arrays are sent to node-mysql.
Example of the second case: consider a table with three fields (a, b, c) which together form a unique key value. The file 'a' is set NOT NULL, and b and c have default values 0. Assuming that for the sake of multi-row INSERTs, I use the VALUES() syntax for INSERT (rather than SET), then I would do:
function db_insert(fld_vals)
{
conn.query(
'INSERT INTO tbl1 (a, b, c) VALUES ?',
[ fld_vals ],
function(err, result) { … });
}
db_insert([5]);
db_insert([5]);
The first and second call to db_insert() would now translate to SQL:
INSERT INTO tbl1 (a, b, c) VALUES (5, NULL, NULL);
INSERT INTO tbl1 (a, b, c) VALUES (5, NULL, NULL);
Both of which would succeed (because of the way MySQL treats NULLs) and insert two rows into the DB. But is that really true to what the caller intended and what the interpretation of undefined elements in JS arrays should be? If the caller really wanted the values to be NULL if not supplied, s/he has the option of setting the default value in the DB schema to NULL for each such field. OTOH, the caller of conn.query() has no way to say let this field default to its default value (other than using SET syntax, which is usable only for single row INSERTs).