Repository navigation
Bundled extension-functions.c replaces SQLite's built-in sign() #646
Description
sql.js registers the functions from extension-functions.c, which includes its own sign(). That replaces SQLite's built-in sign() (core function since 3.35.0). The replacement returns different values and is not registered as deterministic, so schemas that use sign() in an index or generated column fail.
const initSqlJs = require('sql.js');
(async () => {
const SQL = await initSqlJs();
const db = new SQL.Database();
console.log(JSON.stringify(db.exec("SELECT sign('abc'), sign(x'01'), sign(''), typeof(sign(2.5))")[0].values));
console.log(JSON.stringify(db.exec("SELECT name, builtin, flags FROM pragma_function_list WHERE name = 'sign'")[0].values));
db.run('CREATE TABLE t(a)');
try { db.run('CREATE INDEX ti ON t(sign(a))'); console.log('index ok'); } catch (e) { console.log(String(e)); }
try { db.run('CREATE TABLE g(a, b GENERATED ALWAYS AS (sign(a)))'); console.log('generated ok'); } catch (e) { console.log(String(e)); }
})();Actual (node repro.js, sql.js 1.15.2, SQLite 3.54.0):
[[0,0,0,"real"]]
[["sign",1,2099200],["sign",0,0]]
Error: non-deterministic functions prohibited in index expressions
Error: non-deterministic functions prohibited in generated columns
Expected (what SQLite itself does, e.g. @sqlite.org/sqlite-wasm 3.54.1):
[[null,null,null,"integer"]]
index ok
generated ok
Per the SQLite docs, sign() returns NULL for strings and blobs that are not numbers and returns an integer. A database created elsewhere with an index on sign(...) also cannot be used normally in sql.js, since the schema no longer parses with the replaced function.
Leaving sign out of the extension-functions.c function table (or not registering a function when a built-in with that name exists) would fix it. #619 would also fix it by dropping extension-functions.c.
Versions: sql.js 1.15.2 (same as master at 4f1c22d), Node.js 26.13.0.
Credit: https://ucsbplse.github.io/
Activity
[-]Bundled extension-functions.c replaces SQLite's built-in sign() (wrong results, breaks indexes on sign())[/-][+]Bundled extension-functions.c replaces SQLite's built-in sign()[/+]lovasoa commentedon Nov 7, 2026
Hello and thank you for getting in touch !
sql.js indeed bundles sqlite's old extension-functions.c, which has its own incompatible version of multiple functions.
Removing it is probably good long-term, but this would require a new major version. Users of the version of sign that is currently bundled in sql.js (and of math functions) wouldn't be happy if we changed their behevior under their feet.
Do you actually need that function, or does this come from some automated testing ?
trackoor commentedon Nov 7, 2026
It came from automated testing, I don't depend on
sign()myself. Waiting for a major version makes sense. Until then, a short note in the README thatsign()and the math functions come from extension-functions.c and behave differently from SQLite's built-ins could save people some confusion.lovasoa commentedon Nov 7, 2026
@trackoor #647 adds
initSqlJs({ useOldExtensionFunctions: false }), which leaves SQLite's built-insign()in place, including its result types and deterministic flag. The old override remains the default for backwards compatibility, with warnings on use ahead of changing the default in the next major version.Could you try this mode with your reproduction and schemas containing
sign()indexes or generated columns? I'd appreciate feedback on whether it resolves the compatibility problem you reported, including loading databases created with another SQLite build.trackoor commentedon Nov 7, 2026
Thanks, that was quick! I tried the
distartifact from the #647 CI run (875657b) in Node 26.With
initSqlJs({ useOldExtensionFunctions: false })everything matches SQLite:I also opened a database written by Python's sqlite3 (SQLite 3.40.1) that has
create index ti on t(sign(a))andb generated always as (sign(a)). In the new modePRAGMA integrity_checkisok,INDEXED BY tiworks, the generated values are right, and inserts keep itok. With the default options the same file fails on the first query withmalformed database schema (ti) - non-deterministic functions prohibited in index expressions, so the new mode does fix that case.Two small things I noticed:
initSqlJsreuses the first module it loads, so calling it again in the same process with different options silently keeps the old mode. Probably fine, but maybe worth a line in the README next to the option.