Does matchinfo function works in query containg multiple SELECT statements?

Question

I worked with SQLite FTS VIRTUAL TABLE query with matchinfo function and it worked perfectly. The query is like below:

SELECT 
    docid as _id,
    KEY_NAME,
    KEY_INDEX,
    KEY_TEXT,
    KEY_TRANS,
    hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES
     from FTS_VIRTUAL_TABLE
     where KEY_TEXT MATCH '"\""+inputText+"\"*"+" ';

Now I have to implement matchinfo in same TABLE query with multiple SELECT statements. But it crashes the App. My code is below:

SELECT 
    docid as _id,
    KEY_NAME,
    KEY_INDEX,
    KEY_TEXT,
    KEY_TRANS,
    from FTS_VIRTUAL_TABLE
    where docid in (
    SELECT 
        docid 
        hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES    **//it does not work here also**

        from FTS_VIRTUAL_TABLE
        where KEY_TEXT MATCH '"\""+inputText+"\"*"+" '

    union

    SELECT 
        docid 
        hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES    **//nor here**

        from FTS_VIRTUAL_TABLE
        where KEY_TRANS MATCH '"\""+inputText+"\"*"+" ');

The lines of codes commented in above code result in App Crash. Any help will be highly appreciated. Thanks!

Edit

The stack trace gives following error message

android.database.sqlite.SQLiteException: Only a single result allowed for a SELECT that is part of an Expression (code 1): , while compiling SELECT docid as _id, KEY_NAME, kEY_INDEX, KEY_TEXT, KEY_TRANS, from FTS_VIRTUAL_TABLE where docid in ( SELECT docid hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES from FTS_VIRTUAL_TABLE where KEY_TEXT MATCH '"\""+inputText+"\""+" ' union SELECT docid hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES from FTS_VIRTUAL_TABLE where KEY_TRANS MATCH '"\""+inputText+"\""+" ');

@CL I could not understand where is duplication between App crash with **matchinfo function in SQLite VIRTUAL TABLE query with multiple SELECT statements** (_The only single cause of app crash_) and App Crash without mentioning any reason or without showing your code or stack trace (_covers almost uncountable reasons_) — Mansoor, Nov 07 '14 at 14:22
I don't need to show stack trace as I mentioned the three lines of code are basically the cause of crash. — Mansoor, Nov 07 '14 at 14:52
If you think you don't need to show the error message, nobody will be able to help you. — CL., Nov 07 '14 at 19:55
I CANNOT ATTACH IMAGES DUE TO ACCOUNT RESTRICTIONS. I EDITED QUESTION HOWEVER TO SHOW WHAT THE STACK TRACE GIVES THE ERROR. THANKS FOR YOUR TIME! — Mansoor, Nov 08 '14 at 04:42
`Error: near "from": syntax error` Show the query that you are actually using. — CL., Nov 08 '14 at 08:38
As it was not possible to paste my actual query here due to length, I pasted same in question with **edit 2**. Thanks in advance! — Mansoor, Nov 08 '14 at 14:56

score 0 · Answer 1 · answered Nov 13 '14 at 20:59

The problem is the structure of your sub-queries. When you use a SELECT to get a list of items to check against with an IN, SLQ expects a simple 1 dimensional result (ie {'a','b','c'}). Since you have multiple terms in your inner SELECT queries, the IN statement is getting a list of complex results (ie. {{'a', 1},{'b',2},{'c',3}} ). If you want to use inner SELECTS this way and you don't need the matchinfo, you have to remove

hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES

from your inner SELECT queries. If you actually do need the matchinfo as part of your UNION (it is hard to tell without more context), then you need yet another sub-query that wraps the whole UNION and returns only the docids to the top level query. Something like this:

 SELECT 
    docid as _id, KEY_NAME, KEY_INDEX, KEY_TEXT, KEY_TRANS,
    from FTS_VIRTUAL_TABLE
    where docid in (
    --wrap the UNION so that only the docids are returned to the IN
    SELECT docid from(
    SELECT 
        docid 
        hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES
        from FTS_VIRTUAL_TABLE
        where KEY_TEXT MATCH '"\""+inputText+"\"*"+" '

    union

    SELECT 
        docid 
        hex(matchinfo(FTS_VIRTUAL_TABLE)) AS KEY_OCCURRENCES
        from FTS_VIRTUAL_TABLE
        where KEY_TRANS MATCH '"\""+inputText+"\"*"+" '
   )
);

When you say 'it does not work either' what do you mean? Are you still getting the same error? Have you tried running each subquery on its own to try to isolate which part is causing the problem? — pbuchheit, Nov 19 '14 at 19:23
my previous query's matchinfo function of inner select clauses was causing app crash. Your one does not cause app crash as long as I don't access matchinfo results. — Mansoor, Nov 19 '14 at 20:52
I'm still not clear. Is your query still failing or is the query working now and something else in your app is causing an error? If you run your query directly against the db in something like sqlitebrowser what happens? — pbuchheit, Nov 19 '14 at 21:23

Does matchinfo function works in query containg multiple SELECT statements?

1 Answers1