As Dan pointed out, the check is there in line 1. I think it is a result of the query compiler very cleverly separating out the constant expressions and computing those once, before any table gets opened.
Im 3.7.14 (the version I am using), it also leaves in the check at each record, which is omitted in the code I suspect was generated by a newer version. Maybe some time in the future , the unreachable sequence from lines 2-6 will be eliminated. That wouldlleave the arena set to remove the TableLock (because the table is no longer referenced) and the Close and the Transaction (because no tables are used). Eventually, with branch pruning we would be left with 0 Init 0 2 0 00 NULL 1 Halt 0 0 0 00 NULL 2 Goto 0 1 0 00 NULL -----Urspr?ngliche Nachricht----- Von: nomad at null.net [mailto:nomad at null.net] Gesendet: Freitag, 12. Juni 2015 12:45 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Contstant WHERE terms still require table scan? On Fri Jun 12, 2015 at 09:49:29AM +0000, Hick Gunter wrote: > Seems the correct code is already generated... Thanks Hick, that shows a bit more detail I didn't think to look for. It seems that this only works for bind values, as the comparison and goto statements aren't present when the term is defined at prepare time: .width 4 10 4 4 4 10 2 10 EXPLAIN SELECT x.id FROM x WHERE 1=0 ; addr opcode p1 p2 p3 p4 p5 comment ---- ---------- ---- ---- ---- ---------- -- ---------- 0 Init 0 9 0 00 NULL 1 Ne 2 7 1 51 NULL 2 OpenRead 0 2 0 0 00 NULL 3 Rewind 0 7 0 00 NULL 4 Rowid 0 3 0 00 NULL 5 ResultRow 3 1 0 00 NULL 6 Next 0 4 0 01 NULL 7 Close 0 0 0 00 NULL 8 Halt 0 0 0 00 NULL 9 Transactio 0 0 1 0 01 NULL 10 TableLock 0 2 0 x 00 NULL 11 Integer 1 1 0 00 NULL 12 Integer 0 2 0 00 NULL 13 Goto 0 1 0 00 NULL That makes me think that for the 1=0 case the scan occurs anyway? -- Mark Lawrence _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
