Why is a multiple MATCH clause not allowed in an FTS query?

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

Why is a multiple MATCH clause not allowed in an FTS query?

mailing lists
Because of my (obviously wrong) automatic query generator I came across this issue:

CREATE  VIRTUAL TABLE myData USING FTS3 (content, body);

SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two');


What is the reason that the above query is not allowed and that

SELECT * FROM myData WHERE myData MATCH 'content: one body: two';

has to be used? Performance issues do not matter in this case.

Regards,
Hardy
_______________________________________________
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: Why is a multiple MATCH clause not allowed in an FTS query?

mailing lists
Hi,

I think that I have to be more specific here. Only under certain configurations this MATCH-clause does not work (example):

CREATE VIRTUAL TABLE myDATA USING FTS3 (one,two,three,four);
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' AND m2.myData MATCH 'two: alpha';
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' OR m2.myData MATCH 'two: alpha';

Everything is OK so far. Now:
INSERT INTO myData VALUES('alpha','beta',NULL,NULL);
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' AND m2.myData MATCH 'two: alpha';
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' OR m2.myData MATCH 'two: alpha';
Error: unable to use function MATCH in the requested context

To solve the issue again:
DELETE FROM myData;
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' AND m2.myData MATCH 'two: alpha';
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' OR m2.myData MATCH 'two: alpha';

Strange…
Hardy

PS: sqlite3 version 3.24.0

> Am 2019-08-30 um 09:43 schrieb mailing lists <[hidden email]>:
>
> Because of my (obviously wrong) automatic query generator I came across this issue:
>
> CREATE  VIRTUAL TABLE myData USING FTS3 (content, body);
>
> SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two');
>
>
> What is the reason that the above query is not allowed and that
>
> SELECT * FROM myData WHERE myData MATCH 'content: one body: two';
>
> has to be used? Performance issues do not matter in this case.
>
> Regards,
> Hardy
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Why is a multiple MATCH clause not allowed in an FTS query?

Jens Alfke-2
In reply to this post by mailing lists


> On Aug 30, 2019, at 12:43 AM, mailing lists <[hidden email]> wrote:
>
> SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two');
>
> What is the reason that the above query is not allowed

I'm curious too. This limitation is documented somewhere, but not the reason why.
For programs that generate queries automatically or by translating a different input form, it's annoying to work around.

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