Extension functions vs SQLite Math Functions

[utelle]

The upcoming version 3.35.0 of SQLite will provide a new official extension implementing many built-in mathematical SQL functions.

Unfortunately, this new SQLite extension is not equivalent to the Extension Functions extension which is included in SQLite3 Multiple Ciphers.

This issue lists all provided SQL functions and the discrepancies compared to the official SQLite.

Mathematical functions

Function	Description	SQLite Math Extension (SME)	Extension Functions (EF)	Remarks
acos(X)	arccosine of X	✔️	✔️
acosh(X)	hyperbolic arccosine of X	✔️	✔️
asin(X)	arcsine of X	✔️	✔️
asinh(X)	hyperbolic arcsine of X	✔️	✔️
atan(X)	arctangent of X	✔️	✔️
atanh(X)	hyperbolic arctangent of X	✔️	✔️
atan2(X,Y)	arctangent of Y/X	✔️	✔️
atn2(X,Y)	alias for atan2	❌	✔️
ceil(X)	next larger integer value above X	✔️	✔️
ceiling(X)	alias for ceil	✔️	✔️
cos(X)	cosine of X	✔️	✔️
cosh(X)	hyperbolic cosine of X	✔️	✔️
cot(X)	cotangent of X	❌	✔️
coth(X)	hyperbolic cotangent of X	❌	✔️
degrees(X)	convert X from radians to degrees	✔️	✔️
exp(X)	e raised to the power X	✔️	✔️
floor(X)	next integer value less than X	✔️	✔️
ln(X)	natural logarithm of X	✔️	✔️
log(B,X)	base-B logarithm of X	✔️	❌
log(X)	base-10 logarithm of X	✔️	❗	EF calculates natural logarithm.
log10(X)	base-10 logarithm of X	✔️	✔️
log2(X)	base-2 logarithm of X	✔️	❌
mod(X,Y)	remainder after dividing X by Y	✔️	❌
pi()	approximation for π	✔️	✔️
pow(X,Y)	X raised to Y-th power	✔️	❌
power(X,Y)	alias for pow	✔️	✔️
radians(X)	convert X from degrees into radians	✔️	✔️
sign(X)	sign of X	✔️	✔️
sin(X)	sine of X	✔️	✔️
sinh(X)	hyperbolic sine of X	✔️	✔️
sqrt(X)	square root of X	✔️	✔️
square(X)	square of X	❌	✔️
tan(X)	tangent of X	✔️	✔️
tanh(X)	hyperbolic tangent of X	✔️	✔️
trunc(X)	truncate X to an integer value	✔️	❌

The Extension Functions extension already provides most functions included in the upcoming SQLite Math Extension.

Options to handle math functions in the future:

1. Activate the new SQLite Math Extension, and remove duplicates from the Extension Functions extension.
2. Implement missing functions in the Extension Functions extension, and do NOT activate the new SQLite Math Extension.

🛑 At the moment I prefer the first variant, but there may be criteria to prefer the second variant over the first. Please give feedback, if you are aware of any such criteria.

Unfortunately, there is one hard conflict regarding the log function. While the Extension Functions extension calculates the natural logarithm, the new SQLite Math Extension calculates the base-10 logarithm. The conflict cannot be resolved without breaking compatibility.

🛑 At the moment I intend to switch the implementation in the Extension Functions extension from natural logarithm to base-10 logarithm for the sake of future compatibility with SQLite. Please let me know, if there are reasons to keep the current definition.

String functions

Function	Description	SQLite	Extension Functions (EF)
charindex(S1,S2)	find position of S1 in S2	❌	✔️
charindex(S1,S2,N)	find position of S1 in S2 starting at position N	❌	✔️
leftstr(S,N)	N leftmost characters of S	❌	✔️
padc(S,N)	center pad S to length N	❌	✔️
padl(S,N)	left pad S to length N	❌	✔️
padr(S,N)	right pad S to length N	❌	✔️
proper(S)	capitalize all words in S	❌	✔️
replicate(S,N)	replicate S N times	❌	✔️
reverse(S)	reverse S	❌	✔️
rightstr(S,N)	N rightmost characters of S	❌	✔️
strfilter(S1,S2)	remove from S1 all characters not in S2	❌	✔️

No string function of the Extension Functions extension is currently in conflict with SQLite.

Aggregate functions

Function	Description	SQLite	Extension Functions (EF)
lower_quartile(X)	25% quartile of X	❌	✔️
median(X)	median of X	❌	✔️
mode(X)	most frequent value of X	❌	✔️
stdev(X)	standard deviation of X	❌	✔️
upper_quartile(X)	75% quartile of X	❌	✔️
variance(X)	variance of X	❌	✔️

No aggregate function of the Extension Functions extension is currently in conflict with SQLite.

[Willena]

Here is my opinion on that subject:

• Activate SME as soon as possible and remove duplicates from the EF implementation.
• I personally think that switching log(x) from natural logarithm to base 10 logarithms is a good thing, even if it breaks compatibility

[utelle]

@Willena thanks for your feedback.

• Activate SME as soon as possible and remove duplicates from the EF implementation.

The estimated release date for SQLite 3.35.0 (which includes the new math functions) is end of March 2021. Shortly after the release I will make it available in SQLite3 Multiple Ciphers.

• I personally think that switching log(x) from natural logarithm to base 10 logarithms is a good thing, even if it breaks compatibility

Well, for mathematicians the only natural logarithm is that to the base e and they tend to call it log; on the other hand for computer scientists the only natural logarithm is that to the base 2. 😄

Therefore, the best approach seems to be compatible with the official SQLite distribution.

[pawelsalawa]

+1 for official SME.

As for the compatibility issues - there could be a solution to that, but I guess it would require significant effort. The solution would be to register additional pragma (or custom SQL function) like prefer_mc_functions(true) that would make Sqlite3MC register its own log(X) function. As we know, whenever a SQL function with same name and number of arguments is registered, it replaces (covers) the previous version of the same signature, thus making the MC function to be fired from that point of time.

[utelle]

IMHO it is not worth the effort to implement a dedicated pragma for this purpose. I suppose that only a small minority of database applications actually use the log function. And if a developer decides to upgrade to a newer SQLite version, he/she is free to decide which extension to use. The default will be now the official math extension, but setting the appropriate compile time symbols the developer can override this.

[utelle]

Release of SQLite3 Multiple Ciphers version 1.2.0 (based on SQLite version 3.35.0) now includes the new SQLite Math Extension.