Ooopsie ... that should have been 1e17, and it appears to be fine, except that:
SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1); does not work ever though the value returned from the subselect should be exactly the value in the index ... A table scan does however work correctly ... sqlite> SELECT ALL * FROM t1 not indexed WHERE c1 = (select c1 from t1); |5.76460752303423e+17 --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Saturday, 4 May, 2019 12:09 >To: SQLite mailing list >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY > > >There is, however, something weird: > >SQLite version 3.29.0 2019-05-04 17:32:07 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. >sqlite> .version >SQLite 3.29.0 2019-05-04 17:32:07 >c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2 >zlib version 1.2.11 >gcc-8.1.0 >sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0)); >sqlite> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);; >sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17; >sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1); >sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1); >sqlite> select c1 from t1; >5.76460752303423e+17 >sqlite> select c1 - 1 from t1; >5.76460752303423e+17 >sqlite> > > >--- >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- >>boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp >>Sent: Saturday, 4 May, 2019 11:49 >>To: SQLite mailing list >>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY >> >>On 5/4/19, Manuel Rigger <rigger.man...@gmail.com> wrote: >>> 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. >> >>But 0x7ffffffffffffff != 5.76460752303423e+17. Try it: >> >> SELECT 0x7ffffffffffffff != 5.76460752303423e+17; >> >>You should get back 0. >> >>The rule of thumb is to never expect the == operator to give a >>meaningful answer for floating-point numbers. Only use <, <=, >, >and >>>=. >> >>-- >>D. Richard Hipp >>d...@sqlite.org >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users