add support for Built-In Mathematical SQL Functions

[rayures]

per v3.35 sqlite has by default enabled Built-In Mathematical SQL Functions.

https://www.sqlite.org/lang_mathfunc.html

installed sqlite v.3.37 on my grafana host, tried a function, but these do not work.
query does work when executing it directly in sqlite.

[fr-ser]

Can you provide an example query?

[rayures]

i.e:
SELECT (100*POWER(1.01,365)) as balance FROM BALANCE

the 'power' is from the math functions

[fr-ser]

The latest release of the SQLite client that is used by the plugin seems to be on SQLite 3.28.0.
Since I need to use the go client it does not look like I can do anything.

Currently latest release: https://github.com/mattn/go-sqlite3/releases/tag/v1.10.0

[rayures]

Isnt this the latest version?

https://pkg.go.dev/github.com/mattn/go-sqlite3?tab=versions

1.14.9

(Didn't look at the sqlite version in that version)

[fr-ser]

Yeah, you are right. It is just not shown as a Github release. The plugin currently uses this version: v1.14.4

I wouldn't expect significant upgrades to the underlying SQLite version between 1.14.9 and v1.14.4 but if you happen to find which version the latest is running I can try upgrading

[rayures]

Well, based on the code, it's currently at 3.36.0.

https://github.com/mattn/go-sqlite3/blob/master/sqlite3-binding.h

Line 127.

[fr-ser]

Neat. I'll test out the new version later and see if the math functions are included 👌🏻

[fr-ser]

I updated to the newer version of the go library and it still did not recognize the power function.
I tried using the compile time option SQLITE_ENABLE_MATH_FUNCTIONS but I could not find how to pass this for the go client.

I tried using this extension, that was linked in the golang client docs: https://github.com/dinedal/go-sqlite3-extension-functions
But this threw this error: ../../go/pkg/mod/github.com/dinedal/go-sqlite3-extension-functions/go@v0.0.0-20200202173959-679ee1ae1f0b/extension-functions.go:1:18: syntax error: unexpected -, expecting semicolon or newline

To be fair I am not really an expert regarding linking C libraries in go but I am sadly out of idea...

[rayures]

Hmm, to bad. Sort of hoped/expected it would just work with upgrading the sqlite / go package.

mattn/go-sqlite3#926

[fr-ser]

Hmm, weird. They even mention the math functions in the version... I will check again later this week. Maybe I did something wrong during the go package upgrade...

[fr-ser]

I can't get it working... I opened an issue in the underlying library to get see what I am doing wrong...
mattn/go-sqlite3#990

[rayures]

Tnx for the update and all the time and work in it. I will also follow #990

[fardeadok]

I didn't understand: is it possible to use pow and sqrt or not? If not, when will it be fixed? I spent a couple of days trying to figure out why sqllite works differently in different applications!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[bearbin]

There's still a PR pending in go-sqlite3 to add a build tag for maths functions - mattn/go-sqlite3#1037

until then the functions won't work

[fr-ser]

The plugin is using a different sqlite library by now. The linked issue will not have any impact anymore, sadly.

The current library is https://pkg.go.dev/modernc.org/sqlite

[rayures]

Latest does have math functions enabled and they are working (at least the power function).

Just noticing that the grafana widget are loading much slower with latest sqlite Datasource plugin

[bearbin]

@fr-ser didn't see your message for a while, thanks for correcting me. Good to know that the math functions are now working :)

[fr-ser]

Latest does have math functions enabled and they are working (at least the power function).

Haha, I didn't even realize that myself. But with the new sqlite package provider it makes sense that some behavior changed 😅

Just noticing that the grafana widget are loading much slower with latest sqlite Datasource plugin

In case the slow down is noticable and consistent could you open a new issue and describe a bit more what you experience?