Re: [sqlite] FTS3 bug with MATCH plus OR

[Ralf Junker]

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

[Dan Kennedy]

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

[Scott Hess]

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