Repository navigation
column "nan" does not exist #310
Description
Using PostGres, when a value is set which cannot be interpreted as a Number, incorrect SQL is generated which tries to use NaN as a value:
Example:
var orm = require('orm');
orm.connect('postgres://user:pass@host/db?debug=true', function (err, db) {
if (err) throw err;
var fun = db.define('fun', {'count': Number});
fun.sync(function (err) {
if (err) throw err;
fun.create([{'count': 'bugz'}], function (err, items) {
if (err) throw err;
console.log(item);
});
});
});Causes:
(orm/postgres) CREATE TABLE "fun" ("id" SERIAL, "count" REAL, PRIMARY KEY ("id"))
(orm/postgres) INSERT INTO "fun" ("count") VALUES (NaN) RETURNING *
test.js:9
if (err) throw err;
^
error: column "nan" does not exist
at Connection.parseE (pg/lib/connection.js:526:11)
at Connection.parseMessage (pg/lib/connection.js:371:17)
at Socket.<anonymous> (pg/lib/connection.js:86:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:736:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
Yes, "bugz" can't be converted to a number, but the natural PostGres error is much more useful:
# INSERT INTO "fun" ("count") VALUES ('bugz');
ERROR: invalid input syntax for type real: "bugz"
Activity
dxg commentedon Aug 22, 2013
I've added some code to deal with it here.
Please try:
and see if that fixes it.
ghazel commentedon Aug 22, 2013
Why pass 'NaN' instead of the string which was passed in? Maybe postgres knows how to convert whatever it is.
dxg commentedon Aug 22, 2013
The code should only affect numbers.
If a number is invalid [NaN, Infinity], it gets converted to a string and you get a nice database error.
ghazel commentedon Aug 23, 2013
What I mean is, the query orm generates now is:
Instead of:
Which actually suppresses the error entirely, storing the postgres NaN value instead of throwing the error like it should.
Conceivably I could be passing a string which postgres interprets properly but which orm cannot, exactly like NaN; Infinity and -Infinity are also supported. Also postgres knows what to do with '0xff' while orm doesn't.
dxg commentedon Aug 23, 2013
Interestingly, this throws an error as expected:
Something else is casting the value but only in the
createcall, not when assigning directly.I can't really look into into this now but will do so on the weekend if noone else does so by then.
Edit: You've inadvertantly stumbled onto another bug. I'm looking at it now.
dxg commentedon Aug 25, 2013
I've got a fix, just gotta fix some tests.
Edit: Tests fixed; writing more new tests to prevent regressions..
Infinity & NaN support.