> Behalf Of Dan Kennedy > Sent: Tuesday, October 17, 2017 11:58 AM ... > > I think the exception is queries with OR terms. With > FTS[345], if you do > something like: > > CREATE VIRTUAL TABLE t1 USING fts5(x); > EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def'; > > You can see the Rowid opcodes. > > SQLite runs two separate queries on the virtual table - one > with "MATCH > 'abc'" and the other with "MATCH 'def'". It uses the rowids for each > matched row to avoid returning duplicates. If the xRowid > method always > returned 0, then only the first set of matches would be returned > (because SQLite would deem the second set to be duplicates of the > first). Or if xRowid returned arbitrary values your results might > include duplicates. etc. > > Same applies to other virtual table types. ...
FYI FWIW, I had a moment to play with this a little. I was able to reproduce Dan's case, however I'm not so sure that it is due to the OR (or at least not only that). I think it maybe has more to do with the OR of MATCH's. I tried with one of my vtables using an 'OR' clause, and I got no rowid opcodes. I was using equality, however. There were two scenarios: 1) OR clause on a column that is indexed this generated two table scans, with different filter values 2) OR clause on a column that was /not/ indexed this generated one table scan, with both conditionals evaluated on the same row Those seemed like sane plans. For fun I also tried 'IN' with the exact same results. I'm less familiar with MATCH, but I understand what Dan is saying about de-duping. I don't understand why the planner would have chosen to realize OR as a set union, but I'm sure it has it's reasons. I should study the query planner implementation one day when I have some time.... Cheers! -dave _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
