Re: [sqlite] FTS3 bug with MATCH plus OR
Thanks to both Scott and Dan for your answers! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 bug with MATCH plus OR
On Mar 3, 2010, at 6:26 PM, Ralf Junker wrote: > The recent changes to FTS3 fixed a long standing problem with MATCH > and > AND operators combined. Take this schema: > > drop table if exists myfts; > create virtual table myfts using fts3 (a); > insert into myfts values ('one'); > insert into myfts values ('two'); > > This following query produced an "unable to use function MATCH in the > requested context" error up to 3.6.21, IIRC. The workaround was to > add a > + sign in front of the rowid. Since 3.6.22 it gladly works even > without > the + sign: > > select * from myfts where (myfts MATCH 'one') and (rowid=1); > > However, a similiar problem is still present using "or" instead "and". > Even more problematic, the +rowid workaround no longer helps. Both > these > queries fail: > > select * from myfts where (myfts MATCH 'one') or (rowid=1); > select * from myfts where (myfts MATCH 'one') or (+rowid=1); > > Is this something that should be addressed? Unfortunately it's the nature of the virtual table interface that not all queries that include MATCH operators can be implemented. In theory this particular case could be supported, but it would involve some difficult to test changes to the query planner. And there would still be other expressions with MATCH that would not work. Best approach is probably to use a "rowid IN (...sub-select...)" clause as Scott suggested. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 bug with MATCH plus OR
I can't speak to the question of whether it's a real problem, but I bet you can work around with a sub-select. Something like: select * from myfts where rowid = 1 OR rowid IN (select rowid from myfts where (myfts MATCH 'one')); -scott On Wed, Mar 3, 2010 at 3:26 AM, Ralf Junker wrote: > The recent changes to FTS3 fixed a long standing problem with MATCH and > AND operators combined. Take this schema: > > drop table if exists myfts; > create virtual table myfts using fts3 (a); > insert into myfts values ('one'); > insert into myfts values ('two'); > > This following query produced an "unable to use function MATCH in the > requested context" error up to 3.6.21, IIRC. The workaround was to add a > + sign in front of the rowid. Since 3.6.22 it gladly works even without > the + sign: > > select * from myfts where (myfts MATCH 'one') and (rowid=1); > > However, a similiar problem is still present using "or" instead "and". > Even more problematic, the +rowid workaround no longer helps. Both these > queries fail: > > select * from myfts where (myfts MATCH 'one') or (rowid=1); > select * from myfts where (myfts MATCH 'one') or (+rowid=1); > > Is this something that should be addressed? > > Ralf > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 bug with MATCH plus OR
The recent changes to FTS3 fixed a long standing problem with MATCH and AND operators combined. Take this schema: drop table if exists myfts; create virtual table myfts using fts3 (a); insert into myfts values ('one'); insert into myfts values ('two'); This following query produced an "unable to use function MATCH in the requested context" error up to 3.6.21, IIRC. The workaround was to add a + sign in front of the rowid. Since 3.6.22 it gladly works even without the + sign: select * from myfts where (myfts MATCH 'one') and (rowid=1); However, a similiar problem is still present using "or" instead "and". Even more problematic, the +rowid workaround no longer helps. Both these queries fail: select * from myfts where (myfts MATCH 'one') or (rowid=1); select * from myfts where (myfts MATCH 'one') or (+rowid=1); Is this something that should be addressed? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users