Need to tweak my FTS4 SQLite query

Question

The query below doesn't work on Android and throws the following exception

Unable to use function MATCH in the requested context

It is a pretty complicated query so I'm looking into ways of simplifying it. For info, this query works on the same database on my computer with the sqlite-jdbc-3.715 driver

SELECT * FROM stop_times WHERE 
    departure_time  >= 1000-125
    AND 
    departure_time <= 1000+180 
    AND stop_id IN 
        (SELECT _id FROM stop_sequences WHERE stop_ids MATCH '"642," OR "642," OR ",642"')
    AND _id IN 
        (SELECT _id FROM trips WHERE service_id IN (SELECT _id FROM calendar WHERE wednesday=1))
ORDER BY departure_time ASC

I think my FTS table declaration is OK:

CREATE VIRTUAL TABLE stop_sequences USING fts4(
    _id INTEGER PRIMARY KEY,
    stop_ids TEXT
);

Could an SQL guru help me out? Thanks

EDIT: I found out that even the simplest query such as

SELECT _id FROM stop_sequences WHERE stop_ids MATCH '"642";

fails with the same error. I already use MATCH statements on another database somewhere else in my code and it doesn't complain at all. Does the context mentionned in the error message has anything to do with Android's Context?

I think that would have a bad performance hit and my stopsequences table is quite large — chopchop, May 02 '13 at 00:33
You make want to take a look at this link http://www.mailinglistarchive.com/html/sqlite-users@sqlite.org/2010-03/msg00073.html — Hoan Nguyen, May 02 '13 at 00:48
Could you please create a [Fiddle](http://www.sqlfiddle.com/) with sample data? Makes it easier to find a solution. — Linus Caldwell, May 04 '13 at 19:48
Building my schema in Fiddle hangs as I think there's some unsupported sql modules that I use. I've uploaded my database here if you want to have a look https://www.dropbox.com/s/5qaid3ooi0huyeg/test.db Thanks! — chopchop, May 06 '13 at 00:48
I guess it's a dumb question, but `stop_ids TEXT` looks like it should hold foreign keys. If so, why don't you use an additional table to associate `n:n` relations? — Linus Caldwell, May 07 '13 at 06:50

score 1 · Accepted Answer · answered May 06 '13 at 23:25

1

When you created this table, you specified USING fts4(), right? If not, you're full-text queries will not work at all.

answered May 06 '13 at 23:25

hd1

33,938
5
80
91

Yeah my CREATE is in the question. Also, this query works when I run it in my shell using sqlite3 command – chopchop May 06 '13 at 23:41
1

Ok I'm really stupid, I was using an old version of the database that I forgot to update to the new one with the fts4 table. Your answer made me check so should I accept it? – chopchop May 06 '13 at 23:56
Don't feel bad, @chopchop, we all have similar issues sometimes. – hd1 May 07 '13 at 07:15

score 0 · Answer 2 · answered May 06 '13 at 15:31

You can try to change query with union

SELECT * FROM stop_times WHERE 
    departure_time  >= 1000-125
    AND 
    departure_time <= 1000+180 
    AND stop_id IN 
        (SELECT _id FROM stop_sequences 
         WHERE stop_ids 
         MATCH '"642,"'
         UNION
         SELECT _id FROM stop_sequences 
         WHERE stop_ids 
         MATCH '",642"'
         )
    AND _id IN 
        (SELECT _id FROM trips WHERE service_id IN (SELECT _id FROM calendar WHERE wednesday=1))
ORDER BY departure_time ASC

Need to tweak my FTS4 SQLite query

2 Answers2