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
