Confirming it's doing the same thing for me. Taking out the distinct keyword will return a bunch of 1's, adding it in doesn't show them. Definitely something buggy here.
D:\Temp>sqlite3 "analyze_problem - Copy.db" SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. sqlite> .timer off sqlite> .eqp off sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer) index|t1_1|t1|738|CREATE INDEX t1_1 on t1 (t2_id asc) table|t2|t2|1409|CREATE TABLE t2 (id integer primary key, a integer) index|t2_1|t2|1423|CREATE INDEX t2_1 on t2 (a asc) sqlite> pragma integrity_check; integrity_check ok sqlite> .eqp full sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where t1.t2_id <> -1; --EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1 --EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 19 0 00 Start at 19 1 Null 1 5 0 08 r[5]=NULL 2 OpenRead 3 1423 0 k(2,,) 00 root=1423 iDb=0; t2_1 3 OpenRead 4 738 0 k(2,,) 00 root=738 iDb=0; t1_1 4 Explain 0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1 00 5 Rewind 3 18 1 0 00 6 Explain 0 1 0 SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?) 00 7 IdxRowid 3 1 0 00 r[1]=rowid 8 SeekGE 4 17 1 1 00 key=r[1] 9 IdxGT 4 17 1 1 00 key=r[1] 10 Column 4 0 2 00 r[2]=t1.t2_id 11 Eq 3 16 2 (BINARY) 54 if r[2]==r[3] goto 16 12 Column 3 0 4 00 r[4]=t2.a 13 Eq 4 16 5 (BINARY) 80 if r[5]==r[4] goto 16 14 Copy 4 5 0 00 r[5]=r[4] 15 ResultRow 4 1 0 00 output=r[4] 16 Next 4 9 1 00 17 Next 3 6 0 01 18 Halt 0 0 0 00 19 Transaction 0 0 4 0 01 usesStmtJournal=0 20 Integer -1 3 0 00 r[3]=-1 21 Goto 0 1 0 00 1 sqlite> .eqp off sqlite> analyze; sqlite> select * from sqlite_stat1; tbl|idx|stat t2|t2_1|6152 6152 t1|t1_1|248503 41 sqlite> pragma integrity_check; integrity_check ok sqlite> .eqp full sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where t1.t2_id <> -1; --EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1 --EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 22 0 00 Start at 22 1 Null 1 5 0 08 r[5]=NULL 2 OpenRead 3 1423 0 k(2,,) 00 root=1423 iDb=0; t2_1 3 OpenRead 4 738 0 k(2,,) 00 root=738 iDb=0; t1_1 4 Explain 0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1 00 5 Rewind 3 21 1 0 00 6 Explain 0 1 0 SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?) 00 7 IdxRowid 3 1 0 00 r[1]=rowid 8 SeekGE 4 17 1 1 00 key=r[1] 9 IdxGT 4 17 1 1 00 key=r[1] 10 Column 4 0 2 00 r[2]=t1.t2_id 11 Eq 3 16 2 (BINARY) 54 if r[2]==r[3] goto 16 12 Column 3 0 4 00 r[4]=t2.a 13 Eq 4 16 5 (BINARY) 80 if r[5]==r[4] goto 16 14 Copy 4 5 0 00 r[5]=r[4] 15 ResultRow 4 1 0 00 output=r[4] 16 Next 4 9 1 00 17 Column 3 0 6 00 r[6]= 18 SeekGT 3 21 6 1 00 key=r[6] 19 Goto 1 6 0 00 20 Next 3 6 0 01 21 Halt 0 0 0 00 22 Transaction 0 0 5 0 01 usesStmtJournal=0 23 Integer -1 3 0 00 r[3]=-1 24 Goto 0 1 0 00 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ralf Junker Sent: Monday, November 20, 2017 10:40 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SELECT result different after ANALYZE I am presenting a scenario where a SELECT produces a different result after running ANALYZE. To reproduce, download this database file (5.6MB, SHA1 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now): https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html This SQL returns a single result row with a value of 1: SELECT DISTINCT t2.a FROM t1 INNER JOIN t2 ON t1.t2_id = t2.id WHERE t1.t2_id <> -1; Then run ANALYZE and run the above select again. This time I receive no result. Assuming that SQL SELECTs should always return the same results regardless of optimization, I assume that this might be a bug in SQLite. Tested with the SQLite 3.21.0 CLI on Windows. Ralf _______________________________________________ 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
