N1QL secondary index not working with parameterized IN clause

Question

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 `indexName` ON `bucketName`(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.

Are you using a fixed number of parameters on the right hand side of the IN? That is, do you have a fixed number of elements in the array? — geraldss, Aug 02 '16 at 22:55
hey, @geraldss no there is no fixed number in the Collections being passed to me I have no guarantee how many elements a user will pass in the ids variable — Marquis Blount, Aug 02 '16 at 22:59
@geraldss rereading your comment I'm not sure if my example would qualify as fixed or not. at runtime when this method is hit it will be fixed when the method is invoked; but the size can change on different method calls. one call may have 5 ids and the next may only pass in 1 — Marquis Blount, Aug 02 '16 at 23:30

Marquis Blount · Accepted Answer · 2016-08-09T09:06:32.527

I resolved this by adding an additional is not missing clause and for some reason this resolved this. 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";

@Ben Wilde comment -

"The reason the "is missing" is required is because the first entry in an index (in 'this' case id) cannot be missing. So documents that have a missing id will not be in the index so if you do not use a field that is already constrained by conditions set by your index, then you will have to specify that it is not missing to ensure that can go to your secondary index"

score 2 · Answer 2 · answered Aug 09 '16 at 08:37

The first entry in an index (in your case id) cannot be missing. So documents that have a missing id will not be in the index. Therefore if you do not use a field that is already constrained by conditions your index, then you will have to specify that it is not missing to ensure that can go to your secondary index.

e.g. You can query the following index with just type="entityType"

CREATE INDEX `indexName` ON `bucketName`(type) WHERE `type`="entityType"

N1QL secondary index not working with parameterized IN clause

2 Answers2