BUG(?) in FTS5

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG(?) in FTS5

mailing lists
Hi,

create and fill the tables:

CREATE TABLE Games (ID INTEGER PRIMARY KEY, WhiteID INTEGER, BlackID INTEGER);
CREATE VIRTUAL TABLE PlayersFTS USING FTS5 (LastName,FirstNames);

INSERT INTO Games (WhiteID,BlackID) VALUES(1,2);
INSERT INTO PlayersFTS (rowid,LastName,FirstNames) VALUES(1,'A','1');
INSERT INTO PlayersFTS (rowid,LastName,FirstNames) VALUES(2,'B','2');

The following SELECT statement fails with the error "unable to use function MATCH in the requested context":

SELECT Games.* FROM Games,PlayersFTS WHERE ((PlayersFTS.rowid=Games.BlackID)OR(PlayersFTS.rowid=Games.WhiteID))AND(PlayersFTS MATCH 'LastName:A');

This SELECT statement works:

SELECT Games.* FROM Games,PlayersFTS WHERE (PlayersFTS.rowid IN(Games.BlackID,Games.WhiteID))AND(PlayersFTS MATCH 'LastName:A');

Regards,
Hartwig

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BUG(?) in FTS5

Jens Alfke-2

> On Jan 23, 2020, at 6:47 AM, mailing lists <[hidden email]> wrote:
>
> The following SELECT statement fails with the error "unable to use function MATCH in the requested context":

This is an annoying but documented limitation of FTS, not a bug. The MATCH operator can’t be used inside an OR expression. It has to be at top-level or in an AND.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BUG(?) in FTS5

mailing lists
Hi Jens,

the MATCH operator is not inside an OR expression. The MATCH operator is in an AND expression, only the rowid request is in an OR expression.

Regards,
Hartwig

PS: In FTS5 since version 3.30.1 also the MATCH operator is allowed in OR statements (try SELECT PlayersFTS.rowid FROM PlayersFTS WHERE (PlayersFTS MATCH 'LastName:B') OR (PlayersFTS MATCH 'FirstNames:2');)

> Am 2020-01-23 um 17:51 schrieb Jens Alfke <[hidden email]>:
>
>
>> On Jan 23, 2020, at 6:47 AM, mailing lists <[hidden email]> wrote:
>>
>> The following SELECT statement fails with the error "unable to use function MATCH in the requested context":
>
> This is an annoying but documented limitation of FTS, not a bug. The MATCH operator can’t be used inside an OR expression. It has to be at top-level or in an AND.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users