When a SQL Engine Records Column Types but Never Reads Them

Code: Megapixel99/sql-nodejs

sql-nodejs is an in-memory SQL database I wrote to understand how a parser turns a statement into stored rows. It takes SQL strings, creates tables, stores rows, and answers SELECT. Its CREATE TABLE accepts column types, and it records them.

It never reads them again.

Here is the whole thing, against the published package:

const SqlParser = require('sql-nodejs');       // 0.0.6
const db = new SqlParser();

db.Parse('CREATE DATABASE mydb;');
db.Parse('CREATE TABLE users (id INT, name VARCHAR, age INT);');
db.Parse('INSERT INTO users (id, name, age) VALUES (1, alice, 30);');
db.Parse('INSERT INTO users (id, name, age) VALUES (2, bob, 25);');

db.Parse('SELECT * FROM users;');
// [ [ '1', 'alice', '30' ], [ '2', 'bob', '25' ] ]

id was declared INT and comes back as '1'. Every value there is a string, including the two columns whose declared type says otherwise. The type survives parsing and reaches storage; nothing downstream consults it.

The query that finds nothing

Storing numbers as strings is a limitation the README already admits. On its own it is not very interesting. What makes it worth writing down is that WHERE appears to work anyway:

db.Parse('SELECT * FROM users WHERE age=25;');
// [ [ '2', 'bob', '25' ] ]

That is the right row, and it is exactly why the problem is easy to miss, because the obvious test passes and nothing suggests looking further. Now ask for the same row a different way:

db.Parse('SELECT * FROM users WHERE age=25.0;');
// []

25.0 and 25 are the same integer, bob is still 25 years old, and the result is empty.

Why the first query works

The comparison is a strict equality between two strings, in table.js:

_where[i].split("=")[1] === this.rows[j].data[
  this.getColmunNames().indexOf(_where[i].split("=")[0])
]

The left side is a fragment of the query text: everything after the = in age=25, which is the string '25'. The right side is what INSERT put in the row, which is also the string '25', because that is how the value arrived and nothing converted it. So '25' === '25' is true and the row matches.

Nothing in that expression knows the column is an INT. It matches because the two pieces of text are spelled identically, and 25.0 is spelled differently. The equality operator is doing string comparison and getting the right answer for integers by coincidence. That coincidence held for every example I wrote while building it, because I wrote the same integer on both sides every time.

The part that has not bitten yet

WHERE currently supports one column=value equality and nothing else, and that limit is what has been hiding the rest of this, because equality is the one operator where comparing strings usually agrees with comparing numbers. Ordering does not have that property:

'9' > '10'   // true

Adding > by reusing the existing comparison would produce a database in which nine is greater than ten, and it would pass any test whose numbers happen to have the same digit count. The bug would not be in the new operator, but in the much earlier decision to keep the parsed type and never look at it, and the new operator would only be the first thing to ask.

What I would fix, and what that costs

The fix is coercion at INSERT, using the type CREATE TABLE already captured: store 30 rather than '30', and parse the right-hand side of a WHERE before comparing it rather than slicing it out of the query string. That is where the recorded type starts doing work.

It is also a behaviour change for anyone reading results today, since SELECT would begin returning numbers where it now returns strings. That belongs in a version bump rather than a patch.

What generalises

Parsing a type and honouring a type are separate pieces of work, and it is possible to ship the first while believing you shipped both. CREATE TABLE users (id INT, ...) is accepted, stored, and displayed back, so every surface agrees the type is real. The only way to find out it is decorative is to ask a question where the string and the number disagree. age=25 is not that question; age=25.0 is, and it costs one line to ask.