I agree in that not every math function can be included by default. My problem,
however, is that I cannot know whether a user uses my self-compiled version
with built-in extension-functions.c, or a downloaded version from sqlite.org.
It would be very, very helpful (especially regarding views!) to have some kind
of a "check function" so that one could write
SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END FROM bar
----- Original Message -----
From: Keith Medcalf <[email protected]>
To: SQLite mailing list <[email protected]>
Sent: Wednesday, May 22, 2019, 22:20:11
Subject: [sqlite] SQL Features That SQLite Does Not Implement
On Wednesday, 22 May, 2019 11:51, [email protected] wrote:
>Please add a note to the omitted page that many basic math functions
>are NOT supported. (sqrt,mod,power,stdev,etc.)
Traditionally "math library" functions provided by the various language
runtimes were not included becase this would introduce dependancies on a "math
library". While this is available on *most* platforms, it is not available on
*all* platforms which SQLite3 can be compiled for out of the box. Furthermore
the implementation of some transcendentals may be intrinsic on some CPU's and
require huge amounts of library code on others. Statistical functions are not
included because, well, they require complex implementations to get right.
Moreover, even the builtin functions are "lite" (the round function does not
round properly for instance (it does grade-school 4/5 rounding rather than
half-even rounding), the average function is rather simple in implementation
and suffers from trivally triggered sources of computational error (it uses
sum/count rather than successive approximation to the mean), and many other
limitations exist in the builtin implementations of many functions).
All of these issues can be "fixed" however, all you need to do is add the
necessary code via the extension mechanism to add whatever functionality you
require using whatever numerical methods you determine are suitable for your
needs. For example, I have added default support via the extension mechanism
(and the EXTRA_INIT hook) to make all the distributed extensions available on
every connection, to add all the standard platform math functions, to add a
bunch of statistical functions, several platform APIs (Windows in this case),
and to "fix" the builtin round, datetime (to include proper support for instant
times and timezone manipulation using the standard IANA timezone database), and
added support for basic Unicode nocase and noaccent collations without using
the whole ICU library.
The downside of this is that the implementation of all these "goodies"
quadruples the size of the base engine code (sqlite3.obj) and it is no longer
"Lite". There are other drawbacks as well. For example, it is difficult to
make many advanced numerical calculation methods (aggregates) compatible with
window functions as currently implemented.
About the only thing that is missing from SQLite3 is the ability to declare and
implement "user defined types" in a fully integrated way (such as was added to
DB2 back in the late 80's early 90's, and which I do not think anyone else has
implemented as nicely anywhere else).
Really, the issue is that SQLite3 is an SQL based relational storage manager,
and it implements this function very well. It does not provide a huge array of
accoutrements that you may see with other more ex$pen$ive RDMS systems, but
does provide the ability to add (most of) those accoutrements if you wish.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
