Re: [sqlite] Restrictions on JOINs with FTS tables

2013-04-05 Thread Clemens Ladisch 
Paul Vercellotti wrote:
> SELECT * FROM indexes JOIN texts ON texts.docid == indexes.recID WHERE 
> texts.text1 MATCH "text1-7" OR indexes.metadata1 > 40;

Please note that in SQL, the equality comparison operator is =, not ==,
and that strings use 'single quotes', not "double quotes".

> "Error: unable to use function MATCH in the requested context"

MATCH can be used only with FTS table.  In this query, you are trying
to apply it to the result of the JOIN.

Do the FTS query first (in a subquery), then join the result.

In this particular case, the OR complicates things; you probably want
something like this:

  SELECT * FROM texts WHERE text1 MATCH 'text1-7'
  UNION
  SELECT * FROM texts WHERE docid IN (SELECT recID FROM indexes WHERE metadata1 
> 40)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Restrictions on JOINs with FTS tables

2013-04-04 Thread Paul Vercellotti 



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