Weird behaviour by couchbase node sdk when running a query with escaped _ in it

Node.js SDK
1

SELECT * FROM USE INDEX() WHERE docType=‘group’ AND lower(groupName) LIKE lower(’%team1\_%’) ORDER BY groupName LIMIT 10 OFFSET 0;

I ran the above query against the couchbase DB directly and it is giving the expected result.Whereas while querying the same through the node sdk it is not returning any results which is a weird behaviour.I think it is a bug in the node sdk.

2

Hey @manoj.sureddi,

Can you show us the code you are using?

Cheers, Brett

3

Just created a sample of the actual code and m posting it

const N1qlQuery = Couchbase.N1qlQuery;

xName=xName.replace(new RegExp("", ‘g’), "\");
var query = “SELECT * FROM WHERE docType=‘x’ AND _id != {xIdToIgnore} AND lower(xName) LIKE lower({name}) ORDER BY xName”;

return readByQuery(couchbaseDocTypes.scmx, query, {
xIdToIgnore: xIdToIgnore,
name: “%” + xName + “%” || “”
});

var readByQuery=function(type,query,params){
this._cluster = new Couchbase.Cluster(this._settings.connectionString);

const fullQuery = "SELECT * FROM <bucketname> WHERE docType='x' AND lower(xName) LIKE lower("+name+") ORDER BY xName"
const nQuery = N1qlQuery.fromString(fullQuery)
  .consistency(N1qlQuery.Consistency.REQUEST_PLUS)
  .adhoc(true);
this._cluster.openBucket(docTypesBucketMappings[docType]);

return retryHelper.retryOnError(this._bucket.query, this._bucket, nQuery);

}

4

Hi Brett,I provided the code.Did you find anything ?

5

Hey @manoj.sureddi,

Can you execute query.toString() on the N1qlQuery object and paste the results of this? It should display to you all of the data that it is sending to N1QL as part of your query.

Cheers, Brett

6

This is the query that is generated and sent

SELECT * FROM MGM USE INDEX(in_gsi) WHERE docType=‘group’ AND lower(groupName) LIKE lower(’%team1\\_%’) ORDER BY groupName LIMIT 10 OFFSET 0;

7

Hey @manoj.sureddi,

Does this query run correctly in the Couchbase Console? If not, then it is indeed possible that there is a client bug that is causing this. Can you provide some additional details on what Node.js version and Server version you are using?

Cheers, Brett

8

Yes the query runs properly in the couchbase console.
Node version : 6.4.0
couchbase : 4.5.1

9

Hey @manoj.sureddi,

We cannot seem to identify any possible cause for your query to not work. The query statements are sent directly to the server once they reach the toObject() stage of execution. Are you certain that you’re escaping of the query is correct and that the result of the toObject() call works in the Couchbase Console without any modification (but fails in the SDK).

Cheers, Brett

10

Yes I am certain about it.I even checked the SDK myself I also got stuck as the SDK directly passes the query to the server.And yes the query passes successfully in the console but fails through the SDK.

11

Hey,

Before you run your application, can you try and set the environment variable LCB_LOGLEVEL to 9, and then run your application again, and then paste the logs messages once the error occurs? Also, can you tell me what errors are being returned by the SDK, or are there no errors, and just no results. Also, are you sure you’re running the application against the correct bucket with the correct credentials?

Cheers, Brett