Re: [sqlite] SQL Features That SQLite Does Not Implement

[James K. Lowden]

On Wed, 22 May 2019 17:56:23 -0700
Jens Alfke  wrote:

> > On May 22, 2019, at 3:55 PM, James K. Lowden
> >  wrote:
> > 
> > I've always thought user-defined types were unnecessary except as a
> > convenience.
> 
> User-defined types are quite important if you?re doing fancy stuff in
> user-defined functions, where data that?s stored in tables as blobs
> has an internal structure visible to those functions. (JSON is a good
> example, and the reason why SQLite added its ?subtypes? feature.)

I see.  So user-defined type here isn't some combination of extant
primitive types, but a blob that *could* be represented by extant types
but is instead represented another way.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

This doesn't solve the problem. The database must work regardless of whether it 
is used within my own app or any other database viewer that might not have the 
extension functions available. In the first case, medians, standard deviations, 
etc. are included in the view, in the second case the values should be just 
NULL.


- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 12:34:39
Subject: [sqlite] SQL Features That SQLite Does Not Implement

On 23 May 2019, at 7:57am, Thomas Kurz  wrote:

> CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

'if' in SQL language is CASE.

Near the beginning of your code, try to execute a function that contains 
stddev().  Make a note of whether it compiles without errors or not.  If it 
compiles, then the function is available.  Store this boolean somewhere and use 
it later on in your code.

What you propose doing: creating a library call which works differently 
depending on whether stddev() is available just leads to infinite regress.  If 
you create such a library call then you'll want to create another library to 
tell whether /your/ library call is available.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Simon Slavin]

On 23 May 2019, at 7:57am, Thomas Kurz  wrote:

> CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

'if' in SQL language is CASE.

Near the beginning of your code, try to execute a function that contains 
stddev().  Make a note of whether it compiles without errors or not.  If it 
compiles, then the function is available.  Store this boolean somewhere and use 
it later on in your code.

What you propose doing: creating a library call which works differently 
depending on whether stddev() is available just leads to infinite regress.  If 
you create such a library call then you'll want to create another library to 
tell whether /your/ library call is available.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Dominique Devienne]

On Thu, May 23, 2019 at 11:09 AM Keith Medcalf  wrote:

> >Keith, as can be seen below, those pragma_*list eponymous vtables are
> >you referring to as not built-in.
>
> Actually they are.  They are the eponymous vtables for the corresponding
> pragma's:
>
> pragma function_list;
> pragma collation_list;
> pragma module_list;
>

C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma function_list;
sqlite>

Now I recall... This is the "official" pre-built binaries from
https://sqlite.org/download.html
which does not turn ON these compile options. That's a shame IMHO.

>I wasn't even aware one could get the function-list at all. Did you
> >implement that as a pure extension w/o changing the official SQLite
> >source code? Are all your extensions Open-Source, on GitHub or
> somewhere?
>
> https://sqlite.org/pragma.html#pragma_function_list


What's strange is that there's zero hit for the string "function_list" in
https://www.sqlite.org/changes.html
When was this introduced again? I also search for "information" and
"schema", and failed to locate the ChangeLog entry.
Even SQLITE_INTROSPECTION_PRAGMAS has no hit in this page in fact! Or
instropection (instrospec does, but unrelated).
Mysterious... --DD

PS: Thanks for info on where to find your (own and collected) extensions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Keith Medcalf]

On Thursday, 23 May, 2019 02:08, Dominique Devienne  wrote:

>On Thu, May 23, 2019 at 7:39 AM Keith Medcalf 
>wrote:

>> You can check if what you need is available on a connection and
>either load it if needed or just abort:

>> sqlite> select * from pragma_function_list order by 1, 2;
>> name   builtin
>> -  --
>> aavg   0
>> ...
>> sqlite> select * from pragma_collation_list order by 1, 2;
>> seqname
>> -  --
>> 0  ROT13
>> ...
>> sqlite> select * from pragma_module_list order by 1;
>> name
>> -
>> approximate_match
>> carray
>> completion
>> ...
>> sqlite> select * from pragma_compile_options;
>> compile_options
>> ALLOW_COVERING_INDEX_SCAN
>> ...

>Keith, as can be seen below, those pragma_*list eponymous vtables are
>you referring to as not built-in.

Actually they are.  They are the eponymous vtables for the corresponding 
pragma's:

pragma function_list;
pragma collation_list;
pragma module_list;

>I wasn't even aware one could get the function-list at all. Did you
>implement that as a pure extension w/o changing the official SQLite 
>source code? Are all your extensions Open-Source, on GitHub or 
somewhere?

https://sqlite.org/pragma.html#pragma_function_list

The pragma's and the eponymous vtables are built in but only if you compile the 
code with the SQLITE_INTROSPECTION_PRAGMAS defined during the compile (and that 
symbol is not listed as part of the pragma compile_options, unfortunately.  
Perhaps Richard could add than so it would be easier to tell if those were 
available rather than trying to use the pragma or the vtables and getting 
nothing or an error respectively.

You can find the source for all the extensions (and the compiled dll's for 
windows, compiled with MinGW) here (excluding the sqltime.c module which only 
works internally and not as an extension and does require changes to the 
sqlite3.c date.c module, that makes the DateTime output default to having 
miliseconds and a timezone offset, plus the ability to specify IANA timezone 
names as parameters eg datetime('now','america/chicago') -- that requires 
certain tables to be present as well to do the translations):

https://www.dessus.com/files/SQLiteExtensions.zip
https://www.dessus.com/files/SQLiteExtensions64.zip

A bunch of them are copies of the standard SQLite3 ext/misc extensions, some of 
them I wrote, and some of them came from elsewhere and the attributions are in 
the source files and are all freely available (public domain or otherwise).  
Basically I append the whole lot to the end of the sqlite3.c source and then 
add a coreinit.c that uses the builtin SQLITE_EXTRA_INIT=core_init that hooks 
all the init routines to each connection using the sqlite3_auto_extension 
interface when sqlite3 is initialized.

I will eventually get around to publishing copies of them somewhere.

--//-- coreinit.c --//--

#ifdef USE_NUNICODE
extern void* sqlite3_nunicode_init(void*);
#endif

int core_init(const char* dummy)
{
int nErr = 0;

nErr += sqlite3_auto_extension((void*)sqlite3_autobusy_init);
nErr += sqlite3_auto_extension((void*)sqlite3_ipaddress_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlfcmp_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlfunc_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlfwin_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlhash_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlitemprint_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlmath_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqltime_init);

#ifdef USE_NUNICODE
nErr += sqlite3_auto_extension((void*)sqlite3_nunicode_init);
#else
nErr += sqlite3_auto_extension((void*)sqlite3_unifuzz_init);
#endif

nErr += sqlite3_auto_extension((void*)sqlite3_eval_init);
nErr += sqlite3_auto_extension((void*)sqlite3_fileio_init);
nErr += sqlite3_auto_extension((void*)sqlite3_ieee_init);
nErr += sqlite3_auto_extension((void*)sqlite3_nextchar_init);
nErr += sqlite3_auto_extension((void*)sqlite3_percentile_init);
nErr += sqlite3_auto_extension((void*)sqlite3_regexp_init);
nErr += sqlite3_auto_extension((void*)sqlite3_rot_init);
//  nErr += sqlite3_auto_extension((void*)sqlite3_sha_init);
nErr += sqlite3_auto_extension((void*)sqlite3_totype_init);
nErr += sqlite3_auto_extension((void*)sqlite3_zorder_init);

nErr += sqlite3_auto_extension((void*)sqlite3_fossildelta_init);

#ifndef SQLITE_OMIT_VIRTUALTABLE
nErr += sqlite3_auto_extension((void*)sqlite3_amatch_init);
nErr += sqlite3_auto_extension((void*)sqlite3_btreeinfo_init);
nErr += sqlite3_auto_extension((void*)sqlite3_carray_init);
nErr += sqlite3_auto_extension((void*)sqlite3_closure_init);
nErr += sqlite3_auto_extension((void*)sqlite3_csv_init);
nErr += 

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Keith Medcalf]

Just execute the SQL containing the function.  If the function does not exist 
then you will get an error thrown when you attempt to prepare the statement 
containing the function that does not exist ... and your application can parse 
the error message and do the needful.  Of course, just because the function x 
exists doesn't mean that it is the function x that you want, merely that it is 
a function named x ...

SQLite version 3.29.0 2019-05-22 23:12:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(x);
sqlite> insert into t values (10),(20),(30),(40);
sqlite> create view v1 as select stdev(x) from t;
sqlite> create view v2 as select stddev(x) from t;
sqlite> select * from v1;
12.9099444873581
sqlite> select * from v2;
Error: no such function: stddev

 
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Thursday, 23 May, 2019 00:58
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>
>Ok, thank you for that hint. But it is still very unconvenient. How
>can I define a view based on your suggestion? I want to have
>something like
>
>CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null}
>FROM ...
>
>- Original Message -
>From: Keith Medcalf 
>To: SQLite mailing list 
>Sent: Thursday, May 23, 2019, 07:18:45
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>select name from pragma_function_list where name == 'M_Pi' collate
>nocase;
>
>returns the name of the function if it exists.  See pragma
>function_list
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>>Sent: Wednesday, 22 May, 2019 22:19
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>
>>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 
>>To: SQLite mailing list 
>>Sent: Wednesday, May 22, 2019, 22:20:11
>>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Dominique Devienne]

On Thu, May 23, 2019 at 7:39 AM Keith Medcalf  wrote:

> You can check if what you need is available on a connection and either
> load it if needed or just abort:
>
> sqlite> select * from pragma_function_list order by 1, 2;
> name   builtin
> -  --
> aavg   0
> ...
> sqlite> select * from pragma_collation_list order by 1, 2;
> seqname
> -  --
> 0  ROT13
> ...



> sqlite> select * from pragma_module_list order by 1;
> name
> -
> approximate_match
> carray
> completion
> ...
>


> sqlite> select * from pragma_compile_options;
> compile_options
> ALLOW_COVERING_INDEX_SCAN
> ...
>

Keith, as can be seen below, those pragma_*list eponymous vtables are you
referring to as not built-in.
I wasn't even aware one could get the function-list at all. Did you
implement that as a pure extension w/o
changing the official SQLite source code? Are all your extensions
Open-Source, on GitHub or somewhere?

Thanks, --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select * from pragma_module_list;
Error: no such table: pragma_module_list
sqlite> select * from pragma_function_list;
Error: no such table: pragma_function_list
sqlite> select * from pragma_compile_options;
COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

Ok, thank you for that hint. But it is still very unconvenient. How can I 
define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement

>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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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.





>__

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Keith Medcalf]

yn 0
zeroblob   1
zipfile0
zipfile_cds0
zorder 0

sqlite> select * from pragma_collation_list order by 1, 2;
seqname
-  --
0  ROT13
1  NUMERICS
2  UNACCENTED
3  NAMES
4  NOCASEU
5  IPADDRESS
6  RTRIM
7  NOCASE
8  BINARY

sqlite> select * from pragma_module_list order by 1;
name
-
approximate_match
carray
completion
csv
dbstat
delta_parse
fsdir
fts3
fts3tokenize
fts4
fts4aux
fts5
fts5vocab
fuzzer
generate_series
geopoly
interpolate
json_each
json_tree
pragma_collation_list
pragma_function_list
pragma_module_list
prefixes
rtree
rtree_i32
spellfix1
sqlite_btreeinfo
sqlite_dbdata
sqlite_dbpage
sqlite_dbptr
sqlite_memstat
sqlite_stmt
swarmvtab
transitive_closure
unionvtab
wholenumber
zipfile

sqlite> select * from pragma_compile_options;
compile_options
ALLOW_COVERING_INDEX_SCAN
ALLOW_URI_AUTHORITY
COMPILER=gcc-8.1.0
DATETIME_NEW
DEFAULT_CACHE_SIZE=262144
DEFAULT_FOREIGN_KEYS
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PROXYDIR_PERMISSIONS=0755
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_WAL_AUTOCHECKPOINT=256
DEFAULT_WAL_SYNCHRONOUS=1
DEFAULT_WORKER_THREADS=8
ENABLE_8_3_NAMES=1
ENABLE_API_ARMOR
ENABLE_COLUMN_METADATA
ENABLE_COLUMN_USED_MASK
ENABLE_COSTMULT
ENABLE_CURSOR_HINTS
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_LOCKING_STYLE=1
ENABLE_MEMORY_MANAGEMENT
ENABLE_NORMALIZE
ENABLE_PREUPDATE_HOOK
ENABLE_RBU
ENABLE_RTREE
ENABLE_STAT4
ENABLE_STMTVTAB
ENABLE_STMT_SCANSTATUS
EXPLAIN_ESTIMATED_ROWS
EXTRA_INIT=core_init
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_ATTACHED=15
MAX_WORKER_THREADS=8
PRECISE_TIME
SOUNDEX
TEMP_STORE=1
THREADSAFE=1
USE_URI

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>
>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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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 

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Keith Medcalf]

select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>
>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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

> exact numeric representations.  

+1 for that as had already been in consideration for version 4

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

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 
To: SQLite mailing list 
Sent: Wednesday, May 22, 2019, 22:20:11
Subject: [sqlite] SQL Features That SQLite Does Not Implement


On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

Before starting to support SQL2016 features, I would suggest support for 
missing features of older SQL standard versions first ;)

- Original Message - 
From: sky5w...@gmail.com 
To: SQLite mailing list 
Sent: Wednesday, May 22, 2019, 21:29:40
Subject: [sqlite] SQL Features That SQLite Does Not Implement

Yes, I expected some pushback. However, my post was to save the next person
having to search for answers. I did not expect to requote the top line of
the page I listed?
"SQLite implements most of the common features of SQL. Rather than try to
list all the features of SQL that SQLite does support, *it is much easier
to list those that it does not.* Unsupported features of SQL are shown
below."

I am asking for a followup to the missing features list.
Similar to this: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

Yes, I am aware of the extension capabilities and will augment them with my
own.
Expect more questions in that effort. ;)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Jens Alfke]

> On May 22, 2019, at 3:55 PM, James K. Lowden  wrote:
> 
> I've always thought user-defined types were unnecessary except as a 
> convenience.


User-defined types are quite important if you’re doing fancy stuff in 
user-defined functions, where data that’s stored in tables as blobs has an 
internal structure visible to those functions. (JSON is a good example, and the 
reason why SQLite added its ‘subtypes’ feature.)

The biggest limitation with ‘subtypes’ is that they only exist during the 
execution of a query; they’re not visible in the final results. So when using 
the result of a query, one only sees a blob, with no indication what subtype of 
blob it is. In the project I work on, this has caused us some problems and 
required inelegant workarounds.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Brent Wood]

Umm...
geometries I use with Postgis & Spatialite - I don't have a database without 
them.
Can anyone come up with a data domain for which location has no relevance? 
I used to think I had some (like taxonomies & dictionaries), but I've been 
corrected in each case :-)

also IP addresses
Brent Wood
  From: James K. Lowden 
 To: sqlite-users@mailinglists.sqlite.org 
 Sent: Thursday, May 23, 2019 10:56 AM
 Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
   
On Wed, 22 May 2019 14:20:11 -0600
"Keith Medcalf"  wrote:

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

That's an interesting aside.  It would make an interesting OT thread,
if you're inclined to start it.  ;-)  

I've always thought user-defined types were unnecessary except as a
convenience.  There are few new primitive types; most user-defined
types I can think of are "structures" -- sets of columns -- that one
might like to name and constrain as a new type that may appear in many
tables.  About the only primitive type I can imagine are mathematical:
complex numbers or exact numeric representations.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[James K. Lowden]

On Wed, 22 May 2019 14:20:11 -0600
"Keith Medcalf"  wrote:

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

That's an interesting aside.  It would make an interesting OT thread,
if you're inclined to start it.  ;-)  

I've always thought user-defined types were unnecessary except as a
convenience.  There are few new primitive types; most user-defined
types I can think of are "structures" -- sets of columns -- that one
might like to name and constrain as a new type that may appear in many
tables.  About the only primitive type I can imagine are mathematical:
complex numbers or exact numeric representations.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Keith Medcalf]

On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[sky5walk]

Yes, I expected some pushback. However, my post was to save the next person
having to search for answers. I did not expect to requote the top line of
the page I listed?
"SQLite implements most of the common features of SQL. Rather than try to
list all the features of SQL that SQLite does support, *it is much easier
to list those that it does not.* Unsupported features of SQL are shown
below."

I am asking for a followup to the missing features list.
Similar to this: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

Yes, I am aware of the extension capabilities and will augment them with my
own.
Expect more questions in that effort. ;)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Reid Thompson]

On Wed, 2019-05-22 at 14:24 -0400, Reid Thompson wrote:
> On Wed, 2019-05-22 at 19:05 +0100, Simon Slavin wrote:
> > [EXTERNAL SOURCE]
> > 
> > 
> > 
> > On 22 May 2019, at 6:51pm, sky5w...@gmail.com wrote:
> > 
> > > Please add a note to the omitted page that many basic math functions are 
> > > NOT supported. (sqrt,mod,power,stdev,etc.)
> > 
> > Which ones should the documentation mention ?  Can you find us a 
> > specification of SQL which includes that these functions ?  I'm not talking 
> > about a SQL engine you can use, but one of the SQL
> > standards which mentions them, so we know what SQLite leaves out.
> > 
> > Compare SQLite with PostgreSQL:
> > 
> > <
> > 
> 
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_current_functions-2Dmath.html=DwIGaQ=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8=jbjQXvGZh8bReMAfosQNap-8S64F4Q3WwjrDjOA9gGM=F9oXfi9o0vpUEQ6Qv_pxcKMHwuLHHbrqTRyq_uqq7_8=
> > 
> > PostgreSQL lacks sqrt (though you can do it with power() or with '|/') and 
> > stdev (which you can't do).  Both SQLite and PostgreSQL do modulo, using 
> > the '%' operator.
> > ___
> 
> 
> the referenced postgresql page lists sqrt in Mathematical Functions
> sqrt(dp or numeric)   (same as input) square root sqrt(2.0)   
> 1.4142135623731
> 
> 
> 

additionally as info,
 https://www.postgresql.org/docs/9.1/functions-aggregate.html

 lists several stddev variants in 

 Table 9-44. Aggregate Functions for Statistics


ala
[local:/home/rthompso/var]:5432 lite@lite=# select stddev(column1) from (select 
* from (values (10),(10),(100),(10)) as points) as foo;
   stddev
-
 45.
(1 row)

[local:/home/rthompso/var]:5432 lite@lite=# select stddev_pop(column1) from 
(select * from (values (10),(10),(100),(10)) as points) as foo;
 stddev_pop  
-
 38.9711431702997391
(1 row)

[local:/home/rthompso/var]:5432 lite@lite=# select stddev_samp(column1) from 
(select * from (values (10),(10),(100),(10)) as points) as foo;
 stddev_samp 
-
 45.
(1 row)





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[R Smith]

How we can anyone document functions that do not exist?, they are 
infinite and many of them (most) are not currently known to humanity. 
Also, if somewhere it said "SQLite cannot do sqr() it would lie - sure 
vanilla sqlite might not, but there are many ways in which it does.


!. Add-ons - there are many great sqlite add-ons (for lack of a better 
word) via externally run-time loadable extensions.

See: https://www.sqlite.org/loadext.html

You can add any of this easily to your sqlite, or if you compile your 
own, even add them to the source code. One great add-on is precisely the 
math library that supports all (and more) of the mentioned functions.


Example extension via code:
From this Page: https://www.sqlite.org/contrib

extension-functions.c 
 
(50.96 KB) contributed by Liam Healy on 2010-02-06 15:45:07


   Provide mathematical and string extension functions for SQL queries
   using the loadable extensions mechanism. Math: acos, asin, atan,
   atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos,
   sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign,
   sqrt, square, ceil, floor, pi. String: replicate, charindex,
   leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper,
   padl, padr, padc, strfilter. Aggregate: stdev, variance, mode,
   median, lower_quartile, upper_quartile.


Apart from that, you can add your own functions to sqlite (which may be 
hard if you are working through a wrapper, but real easy if you access 
the library or code directly).

See: https://www.sqlite.org/c3ref/create_function.html


Good luck and Happy SQLite-ing,
Ryan

On 2019/05/22 7:51 PM, sky5w...@gmail.com wrote:

I'm often scrambling to decide whether to do complicated queries in SQL or
my own code?
...Getting oh so close to a working query, only to fail at function not
defined?!!

Please add a note to the omitted page that many basic math functions are
NOT supported. (sqrt,mod,power,stdev,etc.)
https://www.sqlite.org/omitted.html
I am curious why these are not available when there are switches for much
larger search functions like FTS345?

...now reading how to build my own extensions...
Thanks for SQLite!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Reid Thompson]

On Wed, 2019-05-22 at 19:05 +0100, Simon Slavin wrote:
> [EXTERNAL SOURCE]
> 
> 
> 
> On 22 May 2019, at 6:51pm, sky5w...@gmail.com wrote:
> 
> > Please add a note to the omitted page that many basic math functions are 
> > NOT supported. (sqrt,mod,power,stdev,etc.)
> 
> Which ones should the documentation mention ?  Can you find us a 
> specification of SQL which includes that these functions ?  I'm not talking 
> about a SQL engine you can use, but one of the SQL
> standards which mentions them, so we know what SQLite leaves out.
> 
> Compare SQLite with PostgreSQL:
> 
> <
> 
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_current_functions-2Dmath.html=DwIGaQ=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8=jbjQXvGZh8bReMAfosQNap-8S64F4Q3WwjrDjOA9gGM=F9oXfi9o0vpUEQ6Qv_pxcKMHwuLHHbrqTRyq_uqq7_8=
> >
> 
> PostgreSQL lacks sqrt (though you can do it with power() or with '|/') and 
> stdev (which you can't do).  Both SQLite and PostgreSQL do modulo, using the 
> '%' operator.
> ___



the referenced postgresql page lists sqrt in Mathematical Functions
sqrt(dp or numeric) (same as input) square root sqrt(2.0)   
1.4142135623731




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Donald Griggs]

Regarding: ... many basic math functions are NOT supported.
(sqrt,mod,power,stdev,etc.)...I am curious why these are not available  ...now
reading how to build my own extensions...

I'm not addressing the documentation question, but extended math functions
have been available since 2010.

On the *contributors* page, https://www.sqlite.org/contrib  Liam Healy
contributed
a runtime-loadable extension.

Windows users can even find pre-compiled versions at:
https://github.com/sqlitebrowser/sqlitebrowser/files/1559077/extension-functions.zip


extension-functions.c

(50.96 KB)
contributed by Liam Healy on 2010-02-06 15:45:07

Provide mathematical and string extension functions for SQL queries using
the loadable extensions mechanism. Math: acos, asin, atan, atn2, atan2,
acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot,
cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil,
floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim,
trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate:
stdev, variance, mode, median, lower_quartile, upper_quartile.


>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Features That SQLite Does Not Implement

[Simon Slavin]

On 22 May 2019, at 6:51pm, sky5w...@gmail.com wrote:

> Please add a note to the omitted page that many basic math functions are NOT 
> supported. (sqrt,mod,power,stdev,etc.)

Which ones should the documentation mention ?  Can you find us a specification 
of SQL which includes that these functions ?  I'm not talking about a SQL 
engine you can use, but one of the SQL standards which mentions them, so we 
know what SQLite leaves out.

Compare SQLite with PostgreSQL:



PostgreSQL lacks sqrt (though you can do it with power() or with '|/') and 
stdev (which you can't do).  Both SQLite and PostgreSQL do modulo, using the 
'%' operator.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL Features That SQLite Does Not Implement

[sky5walk]

I'm often scrambling to decide whether to do complicated queries in SQL or
my own code?
...Getting oh so close to a working query, only to fail at function not
defined?!!

Please add a note to the omitted page that many basic math functions are
NOT supported. (sqrt,mod,power,stdev,etc.)
https://www.sqlite.org/omitted.html
I am curious why these are not available when there are switches for much
larger search functions like FTS345?

...now reading how to build my own extensions...
Thanks for SQLite!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users