On 2015/11/05 4:55 PM, Richard Hipp wrote:
> 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.
>
fwiw - I don't get the same result, here is the same script running in
Win32-SQLitespeed-via-SQLite-3.9.1-DLL (the standard pre-compiled one
from sqlite.org) on an in-memory Database, This one seems to magically
get it right:
-- 2015-11-05 16:41:54.666 | [Info] Script Initialized,
Started executing...
--
================================================================================================
create table tb(n, v);
insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) + 1e-7), ('d',
(1<<58) + 1);
select n, v, typeof(v) from tb;
-- n | v | typeof(v)
-- ----- | ---------------------- | ---------
-- b | 288230376151711744 | integer
-- c | 2.88230376151712e+17 | real
-- d | 288230376151711745 | integer
select l.n, r.n, l.v = r.v from tb as l, tb as r;
-- n | n | l.v = r.v
-- ----- | ----- | ---------
-- b | b | 1
-- b | c | 1
-- b | d | 0
-- c | b | 1
-- c | c | 1
-- c | d | 0
-- d | b | 0
-- d | c | 0
-- d | d | 1
-- 2015-11-05 16:41:54.675 | [Success] Script Success.
And to answer the OP's other question - This doesn't matter, a Primary
key using FLOAT values is rather risky, but any float that isn't
represented exactly the same as another will have a different bit
pattern. Mixing floats and ints in a PK however, might be disastrous.
