It seems that this example now works as expected: CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0)); INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);; SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);
I guess that this test case triggered the same issue as the one I later reported in the email "Series of statements results in a malformed database disk image" [1], which has been fixed (see https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7). So I think that this bug report can be considered a duplicate of the later one. [1] http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-May/084483.html On Sat, May 4, 2019 at 8:43 PM Keith Medcalf <[email protected]> wrote: > > I should think so yes ... The query only appears to work if the index on > the real value is not used -- but there appears no way to do that when > using the IN operator ... > > QUERY PLAN > |--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~240 > rows) > `--LIST SUBQUERY 1 > `--SCAN TABLE t1 (~1048576 rows) > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 37 0 00 Start at 37 > 1 OpenRead 2 3 0 k(3,,,) 02 root=3 iDb=0; > sqlite_autoindex_t1_1 > 2 ColumnsUsed 2 0 0 3 00 > 3 Explain 3 0 0 SEARCH TABLE t1 USING COVERING > INDEX sqlite_autoindex_t1_1 (c1=?) (~240 rows) 00 > 4 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 5 Integer 21 2 0 00 r[2]=21; return > address > 6 Once 0 21 0 00 > 7 OpenEphemeral 3 1 0 k(1,B) 00 nColumn=1; > Result of SELECT 1 > 8 OpenRead 1 2 0 2 00 root=2 iDb=0; t1 > 9 ColumnsUsed 1 0 0 2 00 > 10 Explain 10 0 0 SCAN TABLE t1 (~1048576 rows) 00 > 11 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 12 Rewind 1 20 0 00 > 13 Noop 0 0 0 00 Begin > WHERE-core > 14 Column 1 1 3 00 r[3]=t1.c1 > 15 RealAffinity 3 0 0 00 > 16 MakeRecord 3 1 4 C 00 > r[4]=mkrec(r[3]) > 17 IdxInsert 3 4 3 1 00 key=r[4] > 18 Noop 0 0 0 00 End WHERE-core > 19 Next 1 13 0 01 > 20 Noop 0 0 0 00 End WHERE-loop0: > t1 > 21 Return 2 0 0 00 > 22 Rewind 3 35 0 00 > 23 Column 3 0 1 00 r[1]= > 24 IsNull 1 34 0 00 if r[1]==NULL > goto 34 > 25 SeekGE 2 34 1 1 00 key=r[1] > 26 IdxGT 2 34 1 1 00 key=r[1] > 27 Noop 0 0 0 00 Begin > WHERE-core > 28 Column 2 1 5 00 r[5]=t1.c0 > 29 Column 2 0 6 00 r[6]=t1.c1 > 30 RealAffinity 6 0 0 00 > 31 ResultRow 5 2 0 00 > output=r[5..6] > 32 Noop 0 0 0 00 End > WHERE-core > 33 Next 2 26 0 00 > 34 Next 3 23 0 00 > 35 Noop 0 0 0 00 End WHERE-loop0: > t1 > 36 Halt 0 0 0 00 > 37 Transaction 0 0 1 0 01 usesStmtJournal=0 > 38 Goto 0 1 0 00 > sqlite> > > sqlite> SELECT * FROM t1 NOT INDEXED WHERE c1 IN (SELECT c1 FROM t1); > QUERY PLAN > |--SCAN TABLE t1 (~983040 rows) > `--USING INDEX sqlite_autoindex_t1_1 FOR IN-OPERATOR > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 23 0 00 Start at 23 > 1 OpenRead 0 2 0 2 00 root=2 iDb=0; t1 > 2 ColumnsUsed 0 0 0 3 00 > 3 Explain 3 0 0 SCAN TABLE t1 (~983040 rows) 00 > 4 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 5 Rewind 0 21 0 00 > 6 Noop 0 0 0 00 begin IN expr > 7 Once 0 9 0 00 > 8 OpenRead 2 3 0 k(3,,,) 00 root=3 iDb=0; > sqlite_autoindex_t1_1 > 9 Column 0 1 1 00 r[1]=t1.c1 > 10 RealAffinity 1 0 0 00 > 11 IsNull 1 20 0 00 if r[1]==NULL > goto 20 > 12 Affinity 1 1 0 C 00 affinity(r[1]) > 13 NotFound 2 20 1 1 00 key=r[1]; end > IN expr > 14 Noop 0 0 0 00 Begin > WHERE-core > 15 Column 0 0 2 00 r[2]=t1.c0 > 16 Column 0 1 3 00 r[3]=t1.c1 > 17 RealAffinity 3 0 0 00 > 18 ResultRow 2 2 0 00 output=r[2..3] > 19 Noop 0 0 0 00 End WHERE-core > 20 Next 0 6 0 01 > 21 Noop 0 0 0 00 End WHERE-loop0: > t1 > 22 Halt 0 0 0 00 > 23 Transaction 0 0 1 0 01 usesStmtJournal=0 > 24 Goto 0 1 0 00 > > sqlite> SELECT * FROM t1 not indexed WHERE c1 IN (SELECT c1 FROM t1 not > indexed); > QUERY PLAN > |--SCAN TABLE t1 (~983040 rows) > `--USING INDEX sqlite_autoindex_t1_1 FOR IN-OPERATOR > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 23 0 00 Start at 23 > 1 OpenRead 0 2 0 2 00 root=2 iDb=0; t1 > 2 ColumnsUsed 0 0 0 3 00 > 3 Explain 3 0 0 SCAN TABLE t1 (~983040 rows) 00 > 4 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 5 Rewind 0 21 0 00 > 6 Noop 0 0 0 00 begin IN expr > 7 Once 0 9 0 00 > 8 OpenRead 2 3 0 k(3,,,) 00 root=3 iDb=0; > sqlite_autoindex_t1_1 > 9 Column 0 1 1 00 r[1]=t1.c1 > 10 RealAffinity 1 0 0 00 > 11 IsNull 1 20 0 00 if r[1]==NULL > goto 20 > 12 Affinity 1 1 0 C 00 affinity(r[1]) > 13 NotFound 2 20 1 1 00 key=r[1]; end > IN expr > 14 Noop 0 0 0 00 Begin > WHERE-core > 15 Column 0 0 2 00 r[2]=t1.c0 > 16 Column 0 1 3 00 r[3]=t1.c1 > 17 RealAffinity 3 0 0 00 > 18 ResultRow 2 2 0 00 output=r[2..3] > 19 Noop 0 0 0 00 End WHERE-core > 20 Next 0 6 0 01 > 21 Noop 0 0 0 00 End WHERE-loop0: > t1 > 22 Halt 0 0 0 00 > 23 Transaction 0 0 1 0 01 usesStmtJournal=0 > 24 Goto 0 1 0 00 > sqlite> > > sqlite> SELECT * FROM t1 NOT INDEXED WHERE c1 == (SELECT c1 FROM t1); > QUERY PLAN > |--SCAN TABLE t1 (~262144 rows) > `--SCALAR SUBQUERY 1 > `--SCAN TABLE t1 (~1048576 rows) > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 35 0 00 Start at 35 > 1 OpenRead 0 2 0 2 00 root=2 iDb=0; t1 > 2 ColumnsUsed 0 0 0 3 00 > 3 Explain 3 0 0 SCAN TABLE t1 (~262144 rows) 00 > 4 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 5 Rewind 0 33 0 00 > 6 Column 0 1 1 00 r[1]=t1.c1 > 7 RealAffinity 1 0 0 00 > 8 Integer 24 3 0 00 r[3]=24; > return address > 9 Once 0 24 0 00 > 10 Null 0 4 4 00 r[4..4]=NULL; > Init subquery result > 11 Integer 1 5 0 00 r[5]=1; LIMIT > counter > 12 OpenRead 1 2 0 2 00 root=2 iDb=0; > t1 > 13 ColumnsUsed 1 0 0 2 00 > 14 Explain 14 0 0 SCAN TABLE t1 (~1048576 rows) 00 > 15 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 16 Rewind 1 23 0 00 > 17 Noop 0 0 0 00 Begin > WHERE-core > 18 Column 1 1 4 00 r[4]=t1.c1 > 19 RealAffinity 4 0 0 00 > 20 DecrJumpZero 5 24 0 00 if > (--r[5])==0 goto 24 > 21 Noop 0 0 0 00 End > WHERE-core > 22 Next 1 17 0 01 > 23 Noop 0 0 0 00 End > WHERE-loop0: t1 > 24 Return 3 0 0 00 > 25 Ne 4 32 1 (BINARY) 53 if r[1]!=r[4] > goto 32 > 26 Noop 0 0 0 00 Begin > WHERE-core > 27 Column 0 0 6 00 r[6]=t1.c0 > 28 Column 0 1 7 00 r[7]=t1.c1 > 29 RealAffinity 7 0 0 00 > 30 ResultRow 6 2 0 00 output=r[6..7] > 31 Noop 0 0 0 00 End WHERE-core > 32 Next 0 6 0 01 > 33 Noop 0 0 0 00 End WHERE-loop0: > t1 > 34 Halt 0 0 0 00 > 35 Transaction 0 0 1 0 01 usesStmtJournal=0 > 36 Goto 0 1 0 00 > |5.76460752303424e+17 > sqlite> > > sqlite> SELECT * FROM t1 WHERE c1 == (SELECT c1 FROM t1); > QUERY PLAN > |--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10 > rows) > `--SCALAR SUBQUERY 1 > `--SCAN TABLE t1 (~1048576 rows) > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 35 0 00 Start at 35 > 1 OpenRead 2 3 0 k(3,,,) 02 root=3 iDb=0; > sqlite_autoindex_t1_1 > 2 ColumnsUsed 2 0 0 3 00 > 3 Explain 3 0 0 SEARCH TABLE t1 USING COVERING > INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows) 00 > 4 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 5 Integer 21 2 0 00 r[2]=21; return > address > 6 Once 0 21 0 00 > 7 Null 0 3 3 00 r[3..3]=NULL; > Init subquery result > 8 Integer 1 4 0 00 r[4]=1; LIMIT > counter > 9 OpenRead 1 2 0 2 00 root=2 iDb=0; t1 > 10 ColumnsUsed 1 0 0 2 00 > 11 Explain 11 0 0 SCAN TABLE t1 (~1048576 rows) 00 > 12 Noop 0 0 0 00 Begin > WHERE-loop0: t1 > 13 Rewind 1 20 0 00 > 14 Noop 0 0 0 00 Begin > WHERE-core > 15 Column 1 1 3 00 r[3]=t1.c1 > 16 RealAffinity 3 0 0 00 > 17 DecrJumpZero 4 21 0 00 if (--r[4])==0 > goto 21 > 18 Noop 0 0 0 00 End WHERE-core > 19 Next 1 14 0 01 > 20 Noop 0 0 0 00 End WHERE-loop0: > t1 > 21 Return 2 0 0 00 > 22 IsNull 3 33 0 00 if r[3]==NULL > goto 33 > 23 Affinity 3 1 0 E 00 affinity(r[3]) > 24 SeekGE 2 33 3 1 00 key=r[3] > 25 IdxGT 2 33 3 1 00 key=r[3] > 26 Noop 0 0 0 00 Begin > WHERE-core > 27 Column 2 1 5 00 r[5]=t1.c0 > 28 Column 2 0 6 00 r[6]=t1.c1 > 29 RealAffinity 6 0 0 00 > 30 ResultRow 5 2 0 00 output=r[5..6] > 31 Noop 0 0 0 00 End WHERE-core > 32 Next 2 25 0 00 > 33 Noop 0 0 0 00 End WHERE-loop0: > t1 > 34 Halt 0 0 0 00 > 35 Transaction 0 0 1 0 01 usesStmtJournal=0 > 36 Goto 0 1 0 00 > 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- > >[email protected]] On Behalf Of Manuel Rigger > >Sent: Saturday, 4 May, 2019 12:25 > >To: SQLite mailing list > >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY > > > >Sorry, I should have anticipated that we get slightly different > >values. > >Shouldn't the query "SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM > >t1);" > >return a result though? > > > >Best, > >Manuel > > > >On Sat, May 4, 2019 at 8:17 PM Keith Medcalf <[email protected]> > >wrote: > > > >> > >> 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- > >> >[email protected]] 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- > >> >>[email protected]] 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 <[email protected]> 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 > >> >>[email protected] > >> >>_______________________________________________ > >> >>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 > >> > >> > >> > >> _______________________________________________ > >> 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 > > > > _______________________________________________ > 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
