Query fails from SDK with query options

SQL++
, ,
1

Hi guys,

Wanted to run this issue that I am facing by you. I have the following java instruction:

cluster.query(
        "SELECT RAW META().id FROM bucket123 WHERE  stateId = $stateId  AND  docType = $docType ",
        QueryOptions.queryOptions().parameters(
                JsonObject.create().put("docType", "notes").put("stateId", "ddbacbc1-a950-4d38-afc8-3a2eb58db89b")
        )
).get();

which when its executed ends up in failure:

com.couchbase.client.core.error.PlanningFailureException: The server failed planning the query {"completed":true,"coreId":"0x9b378d9e00000001","errors":[{"code":4000,"message":"No index available on keyspace bucket123 that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.","retry":false}],"httpStatus":404,"idempotent":false,"lastDispatchedFrom":"10.77.25.54:55635","lastDispatchedTo":"host","requestId":9766,"requestType":"QueryRequest","retried":0,"service":{"operationId":"ddbacbc1-a950-4d38-afc8-3a2eb58db89b","statement":"SELECT RAW META().id FROM bucket123 WHERE  stateId = $stateId  AND  docType = $docType ","type":"query"},"timeoutMs":60000,"timings":{"dispatchMicros":107332,"totalDispatchMicros":107332,"totalMicros":1244477}}

meanwhile if I run this the query executes with no errors:

cluster.query(
        "SELECT RAW META().id FROM bucket123 WHERE  stateId = \"ddbacbc1-a950-4d38-afc8-3a2eb58db89b\"  AND  docType = \"notes\"" 
).get();

So this clearly indicates that the index is fine, as I can also run the query with all the parameters substituted in the couchbase UI.

Not sure what I am doing wrong with this one, this should technically work with no issues.
I am running the java SDK 3.2.6 and the couchbase server is on Enterprise Edition 6.6.2 build 9588

Any help would be greatly appreciated.

2

@clsanc This might be a case where it cannot determine whether the index can satisfy the query without actual parameter values, because indexes won’t cover documents where the fields are missing. You can try adding predicates “AND stateId IS NOT MISSING AND stateId IS NOT NULL AND docType IS NOT MISSING AND docType IS NOT NULL” to your first query so the optimizer knows the parameters won’t be substituted with those.

3

Ok, let me try that, will update you

4

Unfortunately that did not work, but I think your idea is right, so maybe its because of the type of index it is?
Following this line of thought I decided to leave one property with an actual value in the query to act as a type of anchor to help the query interpreter, something like this:

cluster.query(
        "SELECT RAW META().id FROM bucket123 WHERE  stateId = $stateId  AND  docType = \"log\" ",
        QueryOptions.queryOptions().parameters(
                JsonObject.create().put("docType", "notes").put("stateId", "ddbacbc1-a950-4d38-afc8-3a2eb58db89b")
        )
).get();

This turned out to be ok, query runs fine and any other named parameters are resolved.

This is the index that I am relying on:

CREATE INDEX `idx_name_123` ON `bucket123`((all (pairs({"identificationId1": `identificationId1`, "identificationId2": `identificationId2`, "identificationId3": `identificationId3`, "docType": `docType`, "pId": `pId`, "stateId": `stateId`})))) WHERE (`docType` = "logs")
5

Index selection is done at prepare time. If index has WHERE clause that must be present in query as is with out any named/positional parameter.
In above case index WHERE (docType = “logs”) , i.e. Index has only those documents. Query can pick this index only when it has predicate docType = “logs” , If named parameter if runtime changes the value to “notes” index will not have any matching entry and gives no-result. Due to that index will not qualify for selection

 AND  docType = "log"  needs to be "logs"
AND  docType = "notes"  -- doesn't match
AND docType = $docType  -- can't use named parameter unless adhoc=true and $docType="logs"
1 Like
6

Thank you for the explanation, wanted to ask, do you have any docs about the lifecycle of a query? Would like to read more about this.