N1QL secondary index not working with parameterized IN clause

SQL++
, ,
1

Using com.couchbase.client, java-client version 2.2.7 I have been unable to get a n1ql secondary index working that uses a parameterized IN clause. See my example index, query, and java code below

Index

CREATE INDEX indexNameONbucketName(id,docType) USING GSI ;

Query

public static final String COUNT_STATEMENT = "select count(*) as count " +
            "from bucketName " +
            "where docType = 'docId' " +
            "and id IN $ids " + 
            "and publishTimestamp between $startTime and $endTime";

Code to submit Query

public int getCountForDuration(Long startTime, Long endTime, Collection<String> ids){
    List<String> idList = new ArrayList<>(ids);
    JsonObject placeHolders = JsonObject.create()
                                        .put("ids", JsonArray.from(idList))
                                        .put("startTime", startTime)
                                        .put("endTime", endTime);
    N1qlQuery query = N1qlQuery.parameterized(COUNT_STATEMENT, placeHolders)            
    N1qlQueryResult result = bucket.query(query);
    ...
}

Before adding parameterization this secondary Index was correctly being used by my query. Also my query works if I use a primary Index.

My question is this how do I create a secondary index which will be used by my query.

2

I resolved this by adding an additional is not missing clause and for some reason this resolved this. I don’t know why the secondary indexes tend to require an is not missing in order to work. The same solution worked in the pass for me. Here is the updated query:

public static final String COUNT_STATEMENT = "select count(*) as count " +
        "from bucketName " +
        "where id is not missing " + 
        "and docType = 'docId' " +
        "and id IN $ids " + 
        "and publishTimestamp between $startTime and $endTime";
1 Like
3

Hi @mlblount45

Which version of Couchbase are you using?

Couchbase 4.5 should use the index without the “id is not missing” clause.
One thing to note is, when you have the predicate: id IN $ids
the index will do the full index scan because id is the leading key.
If you do know the number id’s, you can rewrite the IN clause to OR clause to get better index scan performance.

One alternate to consider:
In 4.5.1 we’ve done improvements to push the specific predicates if you have a predicate like the following:
id IN [$1, $2, $3, $4] and bind those values, index scan will be more efficient.
The tracking bug for this is: https://issues.couchbase.com/browse/MB-20319

1 Like