is the functionality exactly equivalent? Or will this break existing applications that use the old functions?

I compared the extension-functions.c function definition with the official documentation. Which shows that it would definitely be a breaking change. It also shows that the current state of sql.js is missing some SQL specified math functions. I didn't find any reported issues that noticed that the handful of math functions are missing.

missing in SQLITE_ENABLE_MATH_FUNCTIONS

aliases

• atn2 (atan2 is official)
• square (sqrt is official)
• charindex (instr is official)

functionality

scalar

• cot
• coth
• replicate
• difference
• sign
• leftstr
• rightstr
• reverse
• proper
• padl
• padr
• padc
• strfilter

aggregate

• stdev
• variance
• mode
• median
• lower_quartile
• upper_quartile

missing in extension-functions.c

aliases

• ceiling
• pow

functionality

• ln
• log(B,X) (only has log(X))
• log2
• mod
• trunc

Looks like extension functions.c is better, right?

Some of the missing features in MATH_FUNCTIONS would be hard to reproduce, whereas things like mod or log bases are easy to reproduce.

It greatly depends on the goal of the package. The name being sql.js and not something containing SQLite means that it is totally up to you to decide what is the correct behavior.

The trigger for me to make this PR is that we have a test suite that checks that our databases support the SQL specification. We currently are using better-sqlite3 for the node runtime, but would also like to support other runtimes by using sql.js as a substitute. With these missing functions being the only gap in our test suite.

Probably the best solution would be to support all functions, but the feature flag and the extension define the same functions. I am not sure whether that will cause compilation problems. 🤷

It is also not even a show stopper for our project. As we know the difference in behavior between the implementations and can either opt for documenting the missing functions or decide to use create_function to polyfill the missing functions.

Yes, I think create_function is the way to go for you ! It looks like it's much easier to fill the gap from extension-functions.c to SQLITE_ENABLE_MATH_FUNCTIONS than the opposite.

We're using sql.js in place of native SQLite in our test environment, and we've been bitten several times by the fact that its math functions are almost, but not fully, identical to the standard ones. We would greatly appreciate seeing this merged!

@DanielSWolf : doesn't create_function work for you ?

@DanielSWolf : doesn't create_function work for you ?

@lovasoa It almost does, but not fully. I can use create_function to define all math functions that take a fixed number of arguments. But log allows either 1 or 2 arguments. Unless I'm missing something, the sql.js implementation of create_function reads the arity of the given JS function using func.length, then passes this value as the 3rd argument to sqlite3_create_function_v2 (source). This means that there's no way to specify a third argument of -1, which would be needed for vararg functions (SQLite docs).