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- >boun...@mailinglists.sqlite.org] 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 <kmedc...@dessus.com> >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- >> >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 >> >_______________________________________________ >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
