The actual value is 5.7646075230342348e+17 or 5.764607523034235e+17 (depending
on compiler, floating point mode, FPU rounding settings, etc.). This is a
common problem with using = with floating point numbers ...
You can find the actual exact value using:
select printf('%!.20e', c1) from t1;
sqlite> select * from t1 not indexed where c1 = 5.764607523034234e+17;
QUERY PLAN
`--SCAN TABLE t1 (~262144 rows)
sqlite> select * from t1 not indexed where c1 = 5.76460752303423488e+17;
QUERY PLAN
`--SCAN TABLE t1 (~262144 rows)
|5.76460752303423e+17
sqlite> select * from t1 where c1 = 5.76460752303423488e+17;
QUERY PLAN
`--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows)
So the issue you found exists, but your example is bad because you are using
the "wrong" floating point value ...
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Manuel Rigger
>Sent: Saturday, 4 May, 2019 10:36
>To: SQLite mailing list
>Subject: [sqlite] Problem with REAL PRIMARY KEY
>
>Hi everyone,
>
>Consider the following example:
>
>CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>
>I would expect the row to be fetched, which is not the case.
>
>I confirmed that the real value stored is indeed equal to the
>constant:
>
>sqlite> SELECT *, typeof(c1) FROM t1;
>|5.76460752303423e+17|real
>
>This is not only the case when using a literal, but also when
>querying the
>value stored in the row in a sub query. So the following expression
>also
>does not fetch the row:
>
>SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);
>
>It seems that the PRIMARY KEY causes the bug. When it is removed (or
>one of
>the columns), the row is returned as expected.
>
>Best,
>Manuel
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
