On 11/5/15, Zsb?n Ambrus <ambrus at math.bme.hu> wrote:
> Dear SQLite,
>
> It seems that equality of numeric values isn't transitive, when both
> integers and reals are involved. Here's an example output from the
> shell, which shows that the numeric value in the 'c' row is equal to
> both the value in the 'b' and the 'd' rows, but the value in the 'b'
> row isn't equal to the value in the 'd' row. Neither null values nor
> collations seem to be involved here.
>
> SQLite version 3.9.2 2015-11-02 18:31:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table tb(n, v);
> sqlite> insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) +
> 1e-7), ('d', (1<<58) + 1);
> sqlite> select n, v, typeof(v) from tb;
> b|288230376151711744|integer
> c|2.88230376151712e+17|real
> d|288230376151711745|integer
> sqlite> select l.n, r.n, l.v = r.v from tb as l, tb as r;
> b|b|1
> b|c|1
> b|d|0
> c|b|1
> c|c|1
> c|d|1
> d|b|0
> d|c|1
> d|d|1
> sqlite> .quit
The following C program gives the same answer (using gcc 4.8.4 on ubuntu):
#include <stdio.h>
typedef long long int i64;
int main(int argc, char **argv){
i64 b = 1LL << 58;
double c = (1LL << 58) + 1e-7;
i64 d = (1LL << 58) + 1;
printf("b==b: %d\n", b==b);
printf("b==c: %d\n", b==c);
printf("b==d: %d\n", b==d);
printf("c==b: %d\n", c==b);
printf("c==c: %d\n", c==c);
printf("c==d: %d\n", c==d);
printf("d==b: %d\n", d==b);
printf("d==c: %d\n", d==c);
printf("d==d: %d\n", d==d);
return 0;
}
>
> Can this cause problems with indexes,
> sorting or grouping by?
>
You should not compare floating-point numbers for equality.
Floating-point numbers are, by definition, approximations. When you
compare floating-point numbers, therefore, you get an approximate
answer.
--
D. Richard Hipp
drh at sqlite.org
