Node SDK does not support N1ql with error code 4070

Couchbase Server
,
1

Hi all:

I am using Couchbase server(4.5 Enterprise) Node SDK(v2.3.0) to execute some N1ql.

When I use couchbase web workbench to execut the following N1ql, all works fine. Custom index ‘groups’ is found and applied automatically by couhcbase, even I did not name it in the N1ql.

SELECT meta().id, * FROM `Sample-Bucket` where owner != 'userid' AND recycled IS MISSING AND ANY g IN groups SATISFIES g = 'groupid' END

Here is the index:

CREATE INDEX `#groups` ON `Sample-Bucket`((distinct (array `g` for `g` in `groups` end)))

While I use node SDK with the same N1ql string as follows:

const QUERY_BASE  = 'SELECT meta().id, * FROM `Sample-Bucket` where owner != $userid AND recycled IS     MISSING AND ANY g IN groups SATISFIES g = $groupid END'

let query = n1ql.fromString(QUERY_BASE).adhoc(false);

params = {
    userid: 'userid',
    groupid: 'groupid'
};

cluster.executeN1ql(query, params, (err, results) => {
    console.log(err);
})

I got error:

{ [ErrInternalError: Unexpected Database Error]
status: 500,
message: 'Unexpected Database Error',
name: 'ErrInternalError',
_trace: true,
_cause: 
 { [Error: Unable to decode prepared statement - cause: Unrecognizable    prepared statement - cause: JSON  unmarshalling error: No operator for name DistinctScan]
 requestID: '2d3d28f9-7298-4d4f-93f8-cd35aab04077',
 code: 4070,
 otherErrors: [] } }

Question is, why the same N1ql works in workbench but not in SDK? Are we able to use n1ql via Node SDK without naming the custom index?

Any ideas?

2

I think the problem is likely a defect in prepared statement marshaling in Server 4.5. There’ve been a few such issues. Some are fixed in 4.6 and some others are to be fixed soon. You might just try 4.6.1 quickly if it’s easy for you to do so or have a look at the release notes.

Another workaround might be to remove the .adhoc(false) there.

3

@ingenthr Thank you again, I removed .adhoc(false) there and it works fine, no error any more. But I have two concerns that (1) will this removal reduce performance of query? (2) How can I see which index this query is actually using since I do not name the index explicitely? We have multiple indexes for one bucket. Thanks!

4
  1. It can reduce performance of query if you’re running this same query multiple times, yes. Going to a 4.6 release with the fix is the best way to mitigate this. Note that in current 4.6.1 there are some known issues with prepared statements, but I believe those are to be solved in a 4.6 update. @keshav_m or @marcoeg can give more info if issues.couchbase.com doesn’t indicate what you need to know.
  2. Running an EXPLAIN of the query will tell you which index the planner is using.
5

@ingenthr, really appreciated, it helps a lot and makes things clear!