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