Hi there,
I'm wondering if someone could help me understand the restrictions of queries
that mix regular tables with FTS tables.
Let's say you've got the following two tables, which have related records:
CREATE TABLE indexes(recID int, metadata1 int);
CREATE VIRTUAL TABLE texts USING fts3(text1, text2);
INSERT INTO indexes(recID, metadata1) VALUES(3, 24);
INSERT INTO texts(rowid, text1, text2) VALUES(3, "text1-3", "text2-3");
INSERT INTO indexes(recID, metadata1) VALUES(7, 42);
INSERT INTO texts(rowid, text1, text2) VALUES(7, "text1-7", "text2-7");
I find that if I search for:
SELECT * FROM indexes JOIN texts ON texts.docid == indexes.recID WHERE
texts.text1 MATCH "text1-7" OR indexes.metadata1 > 40;
I get:
"Error: unable to use function MATCH in the requested context"
Similarly if I do
SELECT * FROM indexes LEFT OUTER JOIN texts ON texts.docid == indexes.recID
WHERE texts.text1 MATCH "text1-7";
I get the same error. Doing this last query without the "LEFT OUTER" join
specifier succeeds.
These are greatly simplified versions of the queries I'm hoping to do, but I
don't quite see the pattern of when FTS tables can co-mingle with regular
tables in queries. Could someone help clarify the behavior of when MATCH can
be used and when it can't when joining FTS and regular tables?
Thanks!
-Paul
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
