[sqlite-dev] Implementing POWER() and SQRT()
Hawkins, Daniel Steven
I was poking around and noticed that several functions that are purportedly standard since SQL:2011 or earlier are missing in SQLite. Among these are POWER() and SQRT(). There are many extensions that define them, yes,
but to me it feels backward that an extension be loaded to handle standard functionality.
If someone were to attempt to implement these in SQLite itself, would they be allowed to use the compiler's math library for them? Or, since it does not appear that libm is linked in the current build config, should these be implemented in a manner that does not utilize libm (likely from odd behavior on certain supported platforms or some other quirks I am not familiar with)?
I actually built something for POWER() and SQRT() that does not use libm, but I'm getting only about nine digits of precision out of my test cases. Before I try to tamper too extensively with my code to improve the precision, I wanted to make sure that having non-libm code was a sensible solution to begin with.
To -lm or not to -lm,
Daniel Hawkins
R Smith
Hi Daniel,
This discussion is right up the ally of the SQLite-users list here: sqlite...@mailinglists.sqlite.org
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.https://www.sqlite.org/loadext.html
(I realize this is the bit that feels "backward" to you, but alas, it's the way of the sea).
https://www.sqlite.org/c3ref/create_function.html
Ryan
_______________________________________________ sqlite-dev mailing list sqlit...@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev
Hawkins, Daniel Steven
That answers my first question, and tells me I might be on the right track by implementing the methods without using any math library. This actually moves my questions into more technical territory that may be better suited to this list. I was thinking maybe, if sqrt() and power() (and, due to the methodologies I used to implement these, exp() and ln()) could be implemented mainline, a lot of cases I keep seeing crop up where people run into snags due to no exponentiation could be solved. For power users who don't need that, they could be inside an opt-out compiler define.
So, it sounds like the use of long double would also run counter to the cross-platform equivalency goal. I had some helper functions I wrote that were using long doubles in order to improve accuracy where available. But, with the higher precision technically comes a different answer. Would a difference in the number of precise digits of a function's result violate the goal of equivalency on all platforms as stated above?
Daniel Hawkins
________________________________________
From: sqlite-dev <sqlite-de...@mailinglists.sqlite.org> on behalf of R Smith <rsm...@rsweb.co.za>
Sent: Monday, May 27, 2019 5:15:17 PM
To: sqlit...@mailinglists.sqlite.org
Subject: Re: [sqlite-dev] Implementing POWER() and SQRT()
Hi Daniel,
This discussion is right up the ally of the SQLite-users list here: sqlite...@mailinglists.sqlite.org<mailto:sqlite...@mailinglists.sqlite.org>
You will have a lot more luck there, and in fact, you've just missed a rather big recent discussion on this very topic.
That said and while I'm already replying, SQLite's code is very cross-platform, as in any OS, any device, any processor, and more importantly, almost any C compiler - so using compiler-specific math library by default is not an easy option, but of course can be easily added when you roll your own. There are math libraries that can be compiled-in if you do self-compile:
https://www.sqlite.org/contrib<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_contrib&d=DwMD-g&c=nE__W8dFE-shTxStwXtp0A&r=zU3kukDUAVVe9JrOn-QB5w&m=uzqPlfnl4yTmx-P15ktr5X-LbYyVrt3iFLWcSA06m7U&s=lrlVCh4RQneRA0QwQlyD_fg0j6Df8QRsb8tYmP20ems&e=>
extension-functions.c<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_contrib_download_extension-2Dfunctions.c-3Fget-3D25&d=DwMD-g&c=nE__W8dFE-shTxStwXtp0A&r=zU3kukDUAVVe9JrOn-QB5w&m=uzqPlfnl4yTmx-P15ktr5X-LbYyVrt3iFLWcSA06m7U&s=bmuk6ktn9ARDSR9OVu5nrUYw5Nni9W-4TTKv_mF0Xsw&e=> (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.
And if you prefer to use the dynamic-linked libraries, you can at runtime load any library (such as a math library) should you choose to:
https://www.sqlite.org/loadext.html
<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_loadext.html&d=DwMD-g&c=nE__W8dFE-shTxStwXtp0A&r=zU3kukDUAVVe9JrOn-QB5w&m=uzqPlfnl4yTmx-P15ktr5X-LbYyVrt3iFLWcSA06m7U&s=N3qIXkTxAfy_zGy14I9Z8sL3C8oppORewZBI1mgzQXg&e=>(I realize this is the bit that feels "backward" to you, but alas, it's the way of the sea).
Lastly, you may of course program and add your very own functions from your program code:
https://www.sqlite.org/c3ref/create_function.html<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_c3ref_create-5Ffunction.html&d=DwMD-g&c=nE__W8dFE-shTxStwXtp0A&r=zU3kukDUAVVe9JrOn-QB5w&m=uzqPlfnl4yTmx-P15ktr5X-LbYyVrt3iFLWcSA06m7U&s=Emgdh2d231Kg3neMXu-fWIFr84CZ4xzi_tI8qEa_BbA&e=>
Also, the functions you mention to be "purportedly" standard SQL since 2011, is indeed only purportedly so. The latest, to my reading, makes no mention of a POWER function (unless I missed it completely), it seems rather hesitant to trespass on the domain of math functions in general and merely describes /how/ such functions should be implemented, more than having thoughts on /which/ functions should be implemented. Operators are a different story, it has a lot to say about those, ditto for Java and XML manipulation functions. And yet, even if it did explicitly call for a POWER function, it's more of a guide than actual rules and every engine implements the standard a bit differently (and mostly incompletely) - a habit, I might add, in no small part due to the vagueness and ambiguities in the standard itself.
There are many more reasons not to do it, and good reasons to do it, but I've used up enough writing-space. Post this on the sqlite-users forum for more traction on the subject (the Devs read that forum too, plus there you will find some people who hold opposing views to mine). :)
Cheers!
Ryan
On 2019/05/27 9:22 PM, Hawkins, Daniel Steven wrote:
I was poking around and noticed that several functions that are purportedly standard since SQL:2011 or earlier are missing in SQLite. Among these are POWER() and SQRT(). There are many extensions that define them, yes, but to me it feels backward that an extension be loaded to handle standard functionality.
If someone were to attempt to implement these in SQLite itself, would they be allowed to use the compiler's math library for them? Or, since it does not appear that libm is linked in the current build config, should these be implemented in a manner that does not utilize libm (likely from odd behavior on certain supported platforms or some other quirks I am not familiar with)?
I actually built something for POWER() and SQRT() that does not use libm, but I'm getting only about nine digits of precision out of my test cases. Before I try to tamper too extensively with my code to improve the precision, I wanted to make sure that having non-libm code was a sensible solution to begin with.
To -lm or not to -lm,
Daniel Hawkins
_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org<mailto:sqlit...@mailinglists.sqlite.org>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev<https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Ddev&d=DwMD-g&c=nE__W8dFE-shTxStwXtp0A&r=zU3kukDUAVVe9JrOn-QB5w&m=uzqPlfnl4yTmx-P15ktr5X-LbYyVrt3iFLWcSA06m7U&s=M3hq8Kxjqw7cLHCbfYsqAP6MFt_Gf-1eMk_yxwLRVH4&e=>
Hawkins, Daniel Steven
Looking for feedback on how to improve it. The five or six digits at the end of the standard float printout that are inaccurate bugs me, but it'll have to do unless someone identifies a better method to estimate EXP() and/or LN().
Think of this as a code review.
Also, I am releasing the code to the public domain so it *could* be used with SQLite.
Daniel Hawkins
________________________________________
From: sqlite-dev <sqlite-de...@mailinglists.sqlite.org> on behalf of Hawkins, Daniel Steven <hawk...@msu.edu>
Sent: Monday, May 27, 2019 7:46:45 PM
Subject: Re: [sqlite-dev] Implementing POWER() and SQRT()
Hmm... so I read that, and it sounds like it is expected that someone who builds these functions would thereby run into problems with non-uniform behavior in the math library used. Given that a goal of SQLite is to provide a consistent experience across platform, reliance on a platform-dependent math library would run counter to that goal.
That answers my first question, and tells me I might be on the right track by implementing the methods without using any math library. This actually moves my questions into more technical territory that may be better suited to this list. I was thinking maybe, if sqrt() and power() (and, due to the methodologies I used to implement these, exp() and ln()) could be implemented mainline, a lot of cases I keep seeing crop up where people run into snags due to no exponentiation could be solved. For power users who don't need that, they could be inside an opt-out compiler define.
So, it sounds like the use of long double would also run counter to the cross-platform equivalency goal. I had some helper functions I wrote that were using long doubles in order to improve accuracy where available. But, with the higher precision technically comes a different answer. Would a difference in the number of precise digits of a function's result violate the goal of equivalency on all platforms as stated above?
Daniel Hawkins
________________________________________
From: sqlite-dev <sqlite-de...@mailinglists.sqlite.org> on behalf of R Smith <rsm...@rsweb.co.za>
Sent: Monday, May 27, 2019 5:15:17 PM
To: sqlit...@mailinglists.sqlite.org
Subject: Re: [sqlite-dev] Implementing POWER() and SQRT()
Hi Daniel,
This discussion is right up the ally of the SQLite-users list here: sqlite...@mailinglists.sqlite.org<mailto:sqlite...@mailinglists.sqlite.org>
You will have a lot more luck there, and in fact, you've just missed a rather big recent discussion on this very topic.
That said and while I'm already replying, SQLite's code is very cross-platform, as in any OS, any device, any processor, and more importantly, almost any C compiler - so using compiler-specific math library by default is not an easy option, but of course can be easily added when you roll your own. There are math libraries that can be compiled-in if you do self-compile:
extension-functions.c<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_contrib_download_extension-2Dfunctions.c-3Fget-3D25&d=DwMD-g&c=nE__W8dFE-shTxStwXtp0A&r=zU3kukDUAVVe9JrOn-QB5w&m=uzqPlfnl4yTmx-P15ktr5X-LbYyVrt3iFLWcSA06m7U&s=bmuk6ktn9ARDSR9OVu5nrUYw5Nni9W-4TTKv_mF0Xsw&e=> (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.
And if you prefer to use the dynamic-linked libraries, you can at runtime load any library (such as a math library) should you choose to:
Lastly, you may of course program and add your very own functions from your program code:
Also, the functions you mention to be "purportedly" standard SQL since 2011, is indeed only purportedly so. The latest, to my reading, makes no mention of a POWER function (unless I missed it completely), it seems rather hesitant to trespass on the domain of math functions in general and merely describes /how/ such functions should be implemented, more than having thoughts on /which/ functions should be implemented. Operators are a different story, it has a lot to say about those, ditto for Java and XML manipulation functions. And yet, even if it did explicitly call for a POWER function, it's more of a guide than actual rules and every engine implements the standard a bit differently (and mostly incompletely) - a habit, I might add, in no small part due to the vagueness and ambiguities in the standard itself.
There are many more reasons not to do it, and good reasons to do it, but I've used up enough writing-space. Post this on the sqlite-users forum for more traction on the subject (the Devs read that forum too, plus there you will find some people who hold opposing views to mine). :)
Cheers!
Ryan
On 2019/05/27 9:22 PM, Hawkins, Daniel Steven wrote:
I was poking around and noticed that several functions that are purportedly standard since SQL:2011 or earlier are missing in SQLite. Among these are POWER() and SQRT(). There are many extensions that define them, yes, but to me it feels backward that an extension be loaded to handle standard functionality.
If someone were to attempt to implement these in SQLite itself, would they be allowed to use the compiler's math library for them? Or, since it does not appear that libm is linked in the current build config, should these be implemented in a manner that does not utilize libm (likely from odd behavior on certain supported platforms or some other quirks I am not familiar with)?
I actually built something for POWER() and SQRT() that does not use libm, but I'm getting only about nine digits of precision out of my test cases. Before I try to tamper too extensively with my code to improve the precision, I wanted to make sure that having non-libm code was a sensible solution to begin with.
To -lm or not to -lm,
Daniel Hawkins
_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org<mailto:sqlit...@mailinglists.sqlite.org>
_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org