Nice.
Say goodbye to transitive equality though.
> On 3 Dec 2016, at 5:02 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
>
> feq (and friends) are an extension I wrote that does proper floating point
> comparisons:
>
> /*
> ** 2015-11-09
> **
> ** The author disclaims copyright to this source code. In place of
> ** a legal notice, here is a blessing:
> **
> ** May you do good and not evil.
> ** May you find forgiveness for yourself and forgive others.
> ** May you share freely, never taking more than you give.
> **
> ******************************************************************************
> **
> ** This SQLite extension implements functions for comparison of floating point
> ** double precision numbers x and y where x is within a specified number of
> ** ULPs of y.
> **
> ** The default number of ULPs if not specified is 5.
> ** The size of an ULP is based on the scale of x.
> ** If only one value is specified it will be a y value and x will be 0.0
> **
> ** ulp(x) -> ULP precision of x (increment to next representable number)
> ** epsilon(x) -> ULP precision of x (alias of ulp)
> ** ulps(x, y) -> number of ULP of x in (x - y)
> **
> ** flt(x[, y[, u]]) -> x less than y
> ** fle(x[, y[, u]]) -> x less or equal y
> ** feq(x[, y[, u]]) -> x equal y
> ** fge(x[, y[, u]]) -> x greater or equal y
> ** fgt(x[, y[, u]]) -> x greater than y
> ** fne(x[, y[, u]]) -> x not equal y
> **
> */
> #ifdef __cplusplus
> extern "C" {
> #endif
>
> /*
> ** If the MATH.H is already included, use the math library
> ** otherwise make sure memcpy is defined
> */
>
> #ifndef _MATH_H_
> #include <string.h>
> #endif
>
> #ifndef SQLITE_PRIVATE
> #define SQLITE_PRIVATE static
> #endif
>
> #ifdef SQLITE_CORE
> #include "sqlite3.h"
> #else
> #ifdef _HAVE_SQLITE_CONFIG_H
> #include "config.h"
> #endif
> #include "sqlite3ext.h"
> SQLITE_EXTENSION_INIT1
> #endif
>
> /*
> ** Common Functions
> */
>
> #ifdef _MATH_H_
>
> /*
> ** If we have the math library then use it
> */
>
> static double epsilon(double value)
> {
> int exponent;
> double mantissa = frexp(value, &exponent);
> return ldexp(1.0, exponent - 53);
> }
> #define sqlfcmp_fabs fabs
>
> #else
>
> /*
> ** If we do not have the math library already, do not force it to be included
> */
>
> static double epsilon(double value)
> {
> sqlite_int64 a;
> double r = value;
> if (r < 0)
> r = -r;
> memcpy(&a, &r, sizeof(a));
> if (a == 0)
> a = (970LL << 52);
> else
> a = ((a >> 52) - 52LL) << 52;
> memcpy(&r, &a, sizeof(a));
> return r;
> }
>
> static double sqlfcmp_fabs(double v)
> {
> if (v < 0)
> return -v;
> return v;
> }
> #endif
>
> static double distance(double x, double y)
> {
> return (x - y) / epsilon(x);
> }
>
> /*
> ** Return the distance to the next representable number
> */
>
> SQLITE_PRIVATE void _ulp(sqlite3_context *context, int argc, sqlite3_value
> **argv)
> {
> sqlite3_result_double(context, epsilon(sqlite3_value_double(argv[0])));
> }
>
> /*
> ** Return the number of representable numbers between x and y based on the
> ULP of x
> ** if only one parameter, it is y and x is 0.0
> */
>
> SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value
> **argv)
> {
> double x = 0.0;
> double y = 0.0;
> uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
> if ((argc == 0) || (argc > 2))
> return;
> if (argc == 1)
> y = sqlite3_value_double(argv[0]);
> else
> {
> x = sqlite3_value_double(argv[0]);
> y = sqlite3_value_double(argv[1]);
> }
> sqlite3_result_double(context, distance(x, y));
> }
>
> /*
> ** Perform Floating Point (Double) Comparisons using ULP of first parameter
> ** first parameter will be 0.0 if only one parameter specified, which will
> be y
> ** User Context defines operations
> ** flag == 0 x != y
> ** flag & 1 x < y
> ** flag & 2 x = y
> ** flag & 4 x > y
> ** Flag values are additive
> ** flag & 3 x <= y
> ** flag & 6 x >= y
> ** optional third parameter is ULPs of x to consider equal, defaults to 5
> ** if only one parameter then test if x within 5 ULPs of 0.0
> */
>
> SQLITE_PRIVATE void _fpc(sqlite3_context *context, int argc, sqlite3_value
> **argv)
> {
> double x = 0.0;
> double y = 0.0;
> double ulps = 0.0;
> double delta = 5.0;
> uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
> if ((argc == 0) || (argc > 3))
> return;
> if (argc == 1)
> y = sqlite3_value_double(argv[0]);
> else
> {
> x = sqlite3_value_double(argv[0]);
> y = sqlite3_value_double(argv[1]);
> }
> if (argc > 2)
> delta = sqlfcmp_fabs(sqlite3_value_double(argv[2]));
> ulps = distance(x, y);
> if ( ((flag & 2) && (sqlfcmp_fabs(ulps) <= delta)) /* flag & 2 means
> test equal */
> || ((flag & 1) && (ulps < -delta)) /* flag & 1 means
> test less than */
> || ((flag & 4) && (ulps > delta)) /* flag & 4 means
> test greater than */
> || ((flag == 0) && (sqlfcmp_fabs(ulps) > delta))) /* no flags test
> not equal */
> sqlite3_result_int(context, 1);
> else
> sqlite3_result_int(context, 0);
> }
>
> /*
> ** If compiling as a builtin extension, don't export the initializer -- make
> it SQLITE_PRIVATE
> ** Change name of initializer to sqlite3_init_<nameOfExtension>
> */
>
> /* SQLite invokes this routine once when it loads the extension.
> ** Create new functions, collating sequences, and virtual table
> ** modules here. This is usually the only exported symbol in
> ** the shared library.
> */
>
> #ifdef _WIN32
> #ifndef SQLITE_CORE
> __declspec(dllexport)
> #endif
> #endif
> #ifdef SQLITE_CORE
> static
> #endif
> int sqlite3_sqlfcmp_init(sqlite3 *db, char **pzErrMsg, const
> sqlite3_api_routines *pApi)
> {
> int nErr = 0;
>
> SQLITE_EXTENSION_INIT2(pApi);
>
> nErr += sqlite3_create_function(db, "ulp", 1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _ulp, 0, 0);
> nErr += sqlite3_create_function(db, "epsilon", 1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _ulp, 0, 0);
> nErr += sqlite3_create_function(db, "ulps", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _ulps, 0, 0);
> nErr += sqlite3_create_function(db, "fne", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _fpc, 0, 0);
> nErr += sqlite3_create_function(db, "flt", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, (void*)1, _fpc, 0, 0);
> nErr += sqlite3_create_function(db, "feq", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, (void*)2, _fpc, 0, 0);
> nErr += sqlite3_create_function(db, "fle", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, (void*)3, _fpc, 0, 0);
> nErr += sqlite3_create_function(db, "fgt", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, (void*)4, _fpc, 0, 0);
> nErr += sqlite3_create_function(db, "fge", -1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, (void*)6, _fpc, 0, 0);
>
> return nErr ? SQLITE_ERROR : SQLITE_OK;
> }
>
> #ifdef __cplusplus
> }
> #endif
>
>
>> -----Original Message-----
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Warren Young
>> Sent: Friday, 2 December, 2016 11:37
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Datatype for prices (1,500)
>>
>>> On Nov 30, 2016, at 6:53 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>>>
>>> sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions)
>>
>> What’s feq()? I don’t see it in the SQLite documentation.
>>
>> _______________________________________________
>> 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
