[sqlite] Non-transitive numeric equality
On 6 Nov 2015, at 2:20pm, Wade, William wrote: > I have an engineering application, where double can be part of a key. So do I, using Longitude and Latitude in GPS coordinates. A lot of things done inside GPS devices amount to "List everything in this rectangle". This is the problem. Theoretically we are saying "You cannot test for equality in REAL so we should remove COLLATE for REAL numbers.". But there are thousands of apps out there which use REALs in keys, get useful results fast, and don't care how edge cases are handled. So we carry on as before, occasionally telling someone new why the carpet's a bit lumpy over by the coat rack. Simon.
[sqlite] Non-transitive numeric equality
I have an engineering application, where double can be part of a key. In cases where I do a comparison that implies a test for equality, I don't necessarily care what happens in the really close cases. SELECT x,y FROM mytable WHERE x >= 1234 AND x < 5678 There may be about a 1 records in that time range, and for engineering purposes when I write that I don't particularly care whether I am getting 1, 10001 or 10002 entries. However I do depend on getting consistent answers, and on (x >= 1234) being disjoint from (x < 1234) and that those two ranges cover the number line. I suspect that sqlite makes that kind of guarantee, at least if I explicitly cast values to double whenever I do an INSERT or UPDATE or WHERE, although that seems like an easy thing to forget to do. However, nothing in the sqlite documentation promises that sqlite won't internally perform an equality test on primary keys, so it seems that I have to worry that if equality is not transitive, my database can be in arbitrarily bad shape (select gives answers that aren't even close to looking right). Is the answer for this kind of thing to cast all of my values to double when feeding them to sqlite, if I want it to do math using "double" rules? I suspect sqlite could get the "correct" answers when doing comparisons between two types where one type is not a superset of the other by converting both values to bigint rationals and comparing those (I think the sqlite numeric types are all representable as bigint rationals). That may be overkill (and not all that lite). Regards -Original Message- From: Richard Hipp [mailto:d...@sqlite.org] Sent: Thursday, November 05, 2015 8:55 AM To: SQLite mailing list Subject: Re: [sqlite] Non-transitive numeric equality On 11/5/15, Zsb?n Ambrus wrote: >... It seems that equality of numeric values isn't transitive, when both > integers and reals are involved... 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 ** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **
[sqlite] Non-transitive numeric equality
On 11/6/2015 9:20 AM, Wade, William wrote: > However I do depend on getting consistent answers, and on (x >= 1234) being > disjoint from (x < 1234) and that those two ranges cover the number line. I > suspect that sqlite makes that kind of guarantee, at least if I explicitly > cast values to double whenever I do an INSERT or UPDATE or WHERE, although > that seems like an easy thing to forget to do. Declare your columns with REAL affinity ( http://www.sqlite.org/datatype3.html ). Such columns never contain integers, only floats (any attempt to insert an integer coerces it to a float, possibly losing precision). One of the problems in the OPs example is that columns have no declared type, and thus BLOB affinity (which pretty much means, anything goes and no conversions are performed). -- Igor Tandetnik
[sqlite] Non-transitive numeric equality
On 2015/11/05 4:55 PM, Richard Hipp wrote:
> On 11/5/15, Zsb?n Ambrus 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
> 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.
[sqlite] Non-transitive numeric equality
On Thu, Nov 5, 2015 at 3:36 PM, Zsb?n Ambrus wrote: > It seems that equality of numeric values isn't transitive, when both > integers and reals are involved. See this really, really, really, really long thread on that topic from a couple weeks ago for far, far, far more information than you could possible want on the reason: http://www.mail-archive.com/sqlite-users at mailinglists.sqlite.org/msg04466.html -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Non-transitive numeric equality
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
[sqlite] Non-transitive numeric equality
On 11/5/15, Zsb?n Ambrus 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
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
