SQLite Forum

3.25 math functions vs. extension-functions.c

3.25 math functions vs. extension-functions.c

(1) By Markus Winand (MarkusWinand) on 2021-03-23 07:21:09 [link] [source]

Starting with 3.25.0 I get the errors shown below when compiling SQLite with the extension-functions.c added.

It seems to be a rather trivial problem that some of the functions provided by extension-functions.c are now in the core distribution (in particular: sign). I just manually removed parts referring to "signFunc" from extension-functions.c. That get is compiling. I didn't check yet if other functions provided by extension-functions.c are now also available in the core distributation.

My main question is: What is the proper way to cope with issues in "Contributed Files"?

ps.: This problem happens when building the JDBC driver for 3.35.x .


target/sqlite-3.35.0-Mac-x86_64/sqlite3.c:234703:13: error: redefinition of 'signFunc'
static void signFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
target/sqlite-3.35.0-Mac-x86_64/sqlite3.c:120439:13: note: previous definition is here
static void signFunc(
target/sqlite-3.35.0-Mac-x86_64/sqlite3.c:235909:20: error: invalid application of 'sizeof' to an incomplete type 'const struct FuncDef []'
  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
2 errors generated.

(2) By Larry Brasfield (larrybr) on 2021-03-23 08:47:34 in reply to 1 [link] [source]

I'm guessing that you started seeing those error with the 3.35.0 release.

Yes, the SQLite library has had, since version 3.35.0 as a compile-time option, a set of SQL functions that can also be found in extensions. And, yes, this could create build-time issues due to name collisions.

What is the proper way to cope with such issues?

You mention "the extension-functions.c" as if it is especially relevant. I cannot find it in the SQLite source repository or mentioned in the project documentation. I remember that there was a contributed files page at sqlite.org awhile ago, but I can no longer find it. I also remember that the collection was quite dated, so I am not surprised that it goes unmentioned in the site docs now.

People who wish to use their old extension code (whether obtained from sqlite.org long ago or elsewhere) can compile the SQLite library to leave out most/all of the functions added in version 3.35.0, or they can remove like-named and/or duplicative functions from their extensions. Either way would be effective and proper, IMO.

On your compilation errors:

The name collision causes a short cascade of errors. First, the local signFunc() function fails to compile, which is a legitimate compiler response to a duplicate definition. Second, an array of function pointers, initialized to include a pointer to the signFunc() function which failed to compile, cannot have its sizeof taken. This too is a reasonable compiler response, even if not strictly necessary. The solution is to avoid name collisions and duplicate definitions.

(4) By Markus Winand (MarkusWinand) on 2021-03-23 12:08:17 in reply to 2 [link] [source]

I'm indeed referring to the extension-functions.c file available from the SQLite homepage here:


I'm aware that it is not supported. I've also fixed the problem on my own, but my main question remains how to copy with problems found in files hosted on the SQLite website? I just generally like to push my fixes "upstream".

I just searched a little more and found this: https://gitlab.com/liamh/extension-functions

I'll try to reach out to the original author and see how that goes.

(5) By Stephan (stephancb) on 2021-03-24 07:17:39 in reply to 4 [link] [source]

extension-functions.c include statistical stuff stdev, variance, mode, median, lower_quartile, upper_quartile. I have good use of these, and cannot miss them. Separately compiling extension-functions.c to a shared object and loading it works without problems for me.

I guess that then the math functions of extension-functions.c override those that have been added to the Sqlite core in version 3.35 and higher. Presumably there is no significant difference between them, and this is fine.

(6) By Ulrich Telle (utelle) on 2021-03-24 10:06:57 in reply to 5 [link] [source]

I guess that then the math functions of extension-functions.c override those that have been added to the Sqlite core in version 3.35 and higher.

Yes, and usually this does no harm, because most functions with identical names deliver identical results.

Presumably there is no significant difference between them, and this is fine.

Unfortunately there is a small subtle difference: the log function computes log10 (base 10 logarithm) in the SQLite Math Extension, but ln (natural logarithm) in the Extension Functions. For a full comparison see SQLite Math Extension vs Extension Functions.

For my own project I adjusted the code of the Extension Functions module to peacefully coexist with the new SQLite Math Extension, favoring the latter implementation, if both extensions are enabled.

(7) By Stephan (stephancb) on 2021-03-24 10:43:22 in reply to 6 [link] [source]

Thanks, this is a useful comparison that I was not aware of.

The C standard math library, Matlab, Python, Julia, ... all return the natural logarithm for log, i.e. log(10) -> 2.302585092994046. Does the SQL standard mandate anything different? If not the choice of the SQLite Math Extension seems a bit unfortunate to me (and sorry for not having adviced in time).

(8) By Richard Hipp (drh) on 2021-03-24 11:05:30 in reply to 7 [link] [source]

The new math functions follow the definitions used by PostgreSQL, foremost, but also MySQL and SQL Server where possible.

(9) By Stephan (stephancb) on 2021-03-24 11:13:36 in reply to 8 [link] [source]

Ok, then I guess it made sense that Sqlite adopted the way of other database systems in this respect.

(10) By Ulrich Telle (utelle) on 2021-03-24 13:33:45 in reply to 8 [link] [source]

It is a bit unfortunate that there isn't a common definition for the logarithmic function upon which all database systems agree - for mathematicians the base-e logarithm (LN) is the only natural logarithm, for computer scientist the base-2 logarithm (LOG2) is the only natural logarithm.

PostGreSQL uses indeed LOG for the base-10 logarithm (see PostGreSQL documentation, while MySQL and SQL Server both use LOG for the natural logarithm. Oracle also uses LOG for the natural logarithm.

AFAIK the latest SQL standard defines the name LN for the natural logarithm, but no names for other logarithms.

Fortunately, the SQLite Math Extension implements LN, LOG10, and LOG2 in addition to LOG. So, my recommendation would be:

"Be explicit and use LN, LOG10, or LOG2 depending on the type of logarithm you need. Don't use LOG unless you need to explicitly specify the base."

Note: This does not relieve you from checking and potentially adjusting the SQL code when moving from one database system to another.

(11) By Keith Medcalf (kmedcalf) on 2021-03-24 17:48:46 in reply to 10 [link] [source]

Should not those be LogE, Log10 and Log2 if you want to get right down to it?

I know -- consistency and a neat desk is the hobgoblin of small minds :)

(12) By Ulrich Telle (utelle) on 2021-03-24 20:50:23 in reply to 11 [source]

Well, ln and log_e are equivalent notations in mathematics for the natural logarithm. And in math textbooks you frequently find lg as an equivalent for log_10 resp ld as an equivalent for log_2. Personally, I'm fine with all these notations.

I recommended to use LN, LOG10 and LOG2, because those are the names chosen in the SQLite Math Extension for the variants of the logarithmic function, and because those names are the most common in other database systems, too. Actually, I haven't come across any database system implementing LOGE for the natural logarithm up to now. However, it would be a one-liner to add LOGE to the SQLite Math Extension.

(3) By MBS on 2021-03-23 08:52:24 in reply to 1 [link] [source]

This is already resolved for 3.35.0. At least the copy I downloaded has only one signFunc function.