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]

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](https://github.com/xerial/sqlite-jdbc) for 3.35.x .

# Errors

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]

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]

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](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

`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]

> 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](https://github.com/utelle/SQLite3MultipleCiphers/issues/22).

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]

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]

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]

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]

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](https://www.postgresql.org/docs/devel/functions-math.html), while [**MySQL**](https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_log) and [**SQL Server**](https://docs.microsoft.com/de-de/sql/t-sql/functions/log-transact-sql?view=sql-server-ver15) 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]

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 [link]

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]

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