René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"sqlite: statements with JOINs are re-prepared on every run since SQLite 3.54 (sqlite3_clear_bindings)","articleBody":"### Version\n\nv26.12.0\n\n### Platform\n\nLinux 6.17 aarch64 (also expected on other platforms; the cause is not platform specific)\n\n### Subsystem\n\nsqlite\n\n### What steps will reproduce the bug?\n\n```js\nimport { Database } from 'node:sqlite';\nconst db = new Database(':memory:');\ndb.exec('create table t (id integer primary key, h text); create table p (id integer primary key, a integer)');\nconst insT = db.prepare('insert into t values (?, ?)');\nconst insP = db.prepare('insert into p values (?, ?)');\nfor (let i = 1; i \u003c= 1000; i++) { insT.run(i, 'x'); insP.run(i, i); }\nfor (const sql of ['select h from t where id = ?', 'select t.h from p join t on t.id = p.a where p.id = ?']) {\n  const st = db.prepare(sql);\n  const t0 = performance.now();\n  for (let i = 0; i \u003c 100_000; i++) st.get(1 + (i % 1000));\n  console.log(sql, ((performance.now() - t0) * 1000 / 100_000).toFixed(2), 'us/op, reprepare =', st.stat('reprepare'));\n}\n```\n\n### How often does it reproduce? Is there a required condition?\n\nAlways, for statements where SQLite 3.54's planner marks a bound parameter for\nre-optimization (e.g. a JOIN filtered by `rowid = ?`).\n\n### What is the expected behavior? Why is that the expected behavior?\n\nThe statement is prepared once and reused, as on v24.21.0 (SQLite 3.53.4).\n\n### What do you see instead?\n\n```\nv24.21.0 (SQLite 3.53.4)  join: 2.14 us/op   (same script with DatabaseSync; no stat() on v24)\nv26.12.0 (SQLite 3.54.0)  join: 8.44 us/op, reprepare = 100000\n                          single table: 1.53 us/op, reprepare = 0 (unchanged)\n```\n\n### Additional information\n\n`Statement::BindParams()` and `SQLTagStore::ResetAndBindStatement()` call `sqlite3_clear_bindings()`\nbefore every execution. In SQLite 3.54, `sqlite3_clear_bindings()` expires any\nstatement whose `expmask` is non-zero, without honouring the new `smimask`\n\"only re-prepare on change to/from 0 or 1\" rule that `sqlite3_bind_*()` honours.\n3.54 sets `expmask` for ordinary JOIN statements, so they are re-prepared on every\n`sqlite3_step()`. A plain C repro (no Node involved) shows the same thing:\n3.54.1 with `clear_bindings` re-prepares 200000/200000 runs; without it, 8.\n\nThis is also being reported on the SQLite forum; I'll link the thread in a comment.\n\nIndependently of an upstream fix, node:sqlite could avoid the unconditional\n`sqlite3_clear_bindings()` call, e.g. only clear when fewer parameters are bound\nthan `sqlite3_bind_parameter_count()`, or bind NULL explicitly to the remaining\nones. This also matters for LTS lines once SQLite 3.54 is backported (deps update\nwas #66663).\n","author":{"url":"https://github.com/Glasden","@type":"Person","name":"Glasden"},"datePublished":"2026-10-16T19:01:02Z","url":"https://github.com/nodejs/node/issues/66800"}