> On Thursday, 22 October, 2015, at 13:45, Rousselot, Richard A
> <Richard.A.Rousselot at centurylink.com> said:
> Doing the following math, why is it that the results are not all returning
> "yes"?
>
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
> sometable;
>
> Result
> 25.0 no 23.0 yes 21.0 no
I know this has been discussed to death. I wrote a function called "ulps"
which can be used as an extension to SQLite3 and is defined as follows
(compiles in MSVC and GCC , should work on all compilers I think):
SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
register double x = sqlite3_value_double(argv[0]);
register double y = sqlite3_value_double(argv[1]);
register double u1 = fabs(_nextafter(x, 1.7976931348623157e+308) - x);
register double u2 = fabs(_nextafter(x, -1.7976931348623157e+308) - x);
register double au = (u1 + u2) / 2.0;
sqlite3_result_double(context, (x - y) / au);
}
It computes the absolute distance between x and the next representable double
towards +Inf, and the absolute distance between x and the next representable
double towards -Inf. It then averages these distances and returns the number
of times this interval occurs in the difference between x and y.
Running the original problem:
SQLite version 3.10.0 2015-11-07 01:19:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .header on
sqlite> create table test (x real, y real);
sqlite> insert into test values (9.2 + 7.9 + 0 + 4.0 + 2.6 + 1.3, 25.0);
sqlite> insert into test values (9.2 + 7.8 + 0 + 3.0 + 1.3 + 1.7, 23.0);
sqlite> insert into test values (9.2 + 7.9 + 0 + 1.0 + 1.3 + 1.6, 21.0);
sqlite> select x, y, case when x == y then 'yes' else 'no' end as equal,
ulps(x, y) as ulps from test;
x y equal ulps
---------- ---------- ---------- ----------
25.0 25.0 no 1.0
23.0 23.0 yes 0.0
21.0 21.0 no 1.0
sqlite>
This shows that although the x and y may not be exactly equal, they are merely
1 ULP different from each other using the representation precision of x.
This works for comparing numbers of any scale ... of course if the numbers are
not "relatively equal" the number of ULPS between them may huge.
So, instead of "x == y" using "abs(ulps(x - y)) < 5" is true if x and y are
within 5 ULPS of each other.
