FTS index issue when using a JOIN

Mobile
1

I am using Couchbase mobile version 3.0.2

I would like to use the FTS index in a query with a join.

Example:

select
        * 
    from
        _ as cat 
    left join
        _ as dog 
            on cat.favouriteHuman = dog.favouriteHuman 
    where
        MATCH(ftsIndex, "Name*")

This will return an error:
Error: property 'ftsIndex.' does not begin with a declared 'AS' alias

The query does work if I remove the join.

Should the FTS index work when using joins?

2

Yes it should, this is a known issue (though it’s not exactly apparent by the issue description). As a suggested workaround, you can add one of the aliases to your ftsIndex (e.g. cat.ftsIndex). Starting in 3.1.0, with collection support and indexes being per collection, this becomes much more important. 3.0.2 is affected because the underlying core library is already using a collection in preparation for 3.1.0.

1 Like