Repository navigation
Compiled statements are not compiled again after a schema change in another database, in alasql.compile() and in views #2613
Description
Compiled SQL is kept in three places, and only one of them is ever compiled again after a schema change. After DROP TABLE and CREATE TABLE with other columns, the other two keep serving code compiled for the old table. The result is wrong and no error is raised.
Reproduced on develop with #2609 (which fixes the single-database case of #2603):
alasql('CREATE DATABASE a; CREATE DATABASE b; USE a');| Where the compiled code is kept | Steps | Result | Expected |
|---|---|---|---|
| Statement cache, table in another database | CREATE TABLE b.t (x INT), SELECT * FROM b.t, DROP TABLE b.t, CREATE TABLE b.t (x INT, y INT), insert (1,2), SELECT * FROM b.t |
[{x:1}] |
[{x:1, y:2}] |
A statement from alasql.compile() |
st = alasql.compile('SELECT * FROM c'), then the same drop and create on c, then st() |
[{x:1}] |
[{x:1, y:2}] |
A view, with alasql.options.cache = false |
CREATE VIEW v AS SELECT * FROM t, SELECT * FROM v, then the same drop and create on t, then SELECT * FROM v |
[{x:1}] |
[{x:1, y:2}] |
Cause
There is no record of what a compiled statement depends on. The only check is statement.dbversion === db.dbversion in alasql.dexec, and it covers one of the three places:
- Statement cache:
dbis the database the statement runs in, not the database of each table it reads. A change in databasebdoes not changea.dbversion. alasql.compile(): the returned function never checks a version.- Views: the view's SELECT is compiled once, on first use, and kept on the table object (
table.select,src/420from.js). Nothing compiles it again.
Direction
One global schema version, for example alasql.schemaVersion. Every schema change in any database increases it: CREATE / DROP / ALTER TABLE, views, indexes, ATTACH / DETACH, transaction commit and rollback. Every piece of compiled code stores the version it was compiled for and compiles again when it differs: cached statements, functions from alasql.compile(), and the compiled SELECT of a view.
A schema change then throws away all compiled code, not only the code that depends on the changed table. Schema changes are rare compared with queries, so this is the simple and safe choice. Tracking dependencies per table can come later if a benchmark shows the need.
This is a fix for a result that depends on what ran before, so it can go into 4.x.
Tests: the three cases above, each with the cache on and off. Expected results are the same as for a statement compiled fresh.
Activity
Compile cached statements and views again after a schema change to close