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
Is this behavior by design? Can this cause problems with indexes,
sorting or grouping by?
I believe the cause of this behavior is the sqlite3MemCompare private
function in the sqlite3 implementation, which compares an integer to a
real by converting the integer to a real. The conversion can lose
precision of the integer, and as a result, the value in the 'd' row
compares equal to the value in the 'c' row, despite that the numeric
values they represent isn't equal. Sadly, comparing an integer to a
floating point number is not easy, so I don't know an easy fix.
I ran the test above with sqlite 3.9.2 built from the amalgamation
source on windows x86_64 with gcc 4.8.3 and the following compiler
options:
gcc -march=core2 -mavx -mvzeroupper -pthread -O2 -g
-DSQLITE_OMIT_LOAD_EXTENSION=1 -o sqlite3 sqlite3.c shell.c
-- Ambrus
