Should 'undefined' map to DEFAULT?

[ravi]

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).

[boenrobot]

I know this issue is old, but I too am currently in a case where it would be very much convenient to have some way to specify "DEFAULT", with "undefined" being the perfect candidate. I mean, sure, I could hack around it with a custom formatter, or alter my DB schema slightly to support NULL, and convert via trigger, but both of these seem... well... not ideal.

In my case, the default value is CURRENT_TIMESTAMP, so I could just use new Date(), which is what I'm doing currently (and is better), but it would be nicer to make sure the time is relative always to the DB server's time (which might be different from the app's time). Not currently a problem for me, because the MySQL server and Node are on the same device, but I'd rather have the app work regardless of that wherever possible.

[ravi]

BTW, I achieve this today using a custom query format.

[condinoaljoseph]

@ravi HOW??