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