N1QL scan consistency doesn't provide the same result as ViewQuery with Stale=false

SQL++
,
1

Hi,

I am running the following N1QL query.
val sqlcontext = new SQLContext(sc)
val vtapk = sc.couchbaseQuery(N1qlQuery.simple(query, N1qlParams.build().consistency(ScanConsistency.REQUEST_PLUS)))

I intentionally set the ScanConsistency level to the strictest REQUEST_PLUS and hope it can return similar results as the ViewQuery.from().stale(Stale.FALSE).

It turned out that I can get over 7+million records from the ViewQuery, but just 25k records from N1QL.

Is it expected for the N1QL to return just small portion of the data, or is there more configs or bucket flush needs to be done before making such queries.

best,
edward

2

Hi @edwardzhong,

Perhaps your two queries are not logically equivalent. Can you check them and verify?

3

Thanks @geraldss!

My view is defined as retrieving the keys if type==“certain value”.
My N1ql is defined as “select some fields where type==“certain value””.

Even the fields selected are different, they essentially retrieve data from the same bucket with the same where clause.

The only thing I can think of is the N1QL relies on the GSI, while View relies on different mechanism.
However, it is hard to believe the index hasn’t been updated.

Maybe the question is how is the GSI updated, automatically or on demand?

best,

4

Ok. If you’d like, you can post your view function and GSI index definition here. You should be getting the same results.

5

Sure,
My GSI definition:
CREATE INDEX idx_tx_track_type ON tx_track(type) USING GSI

Ok, I realized that it is just defined on the type field without where clause. But I do have the where clause in the N1QL query statement.

My view definition:
function (doc, meta) {
if (doc.type==“sometype”){
emit(meta.id, null);
}
}

6

Ok. Can you try issuing the query using cbq shell. In the 4.5 shell, you can first issue:

\set -scan_consistency “REQUEST_PLUS”

And then issue your query. After that, you can post your query here.

7

I am on couchbase 4.0. It always complained about the following when I tried to set the value.
cbq> \set -scan_consistency “REQUEST_PLUS” ;
{
“requestID”: “ffbfc456-201f-4609-a359-94e3eab51227”,
“errors”: [
{
“code”: 3000,
“msg”: “syntax error - at set”
}
],
“status”: “fatal”,
“metrics”: {
“elapsedTime”: “684.618µs”,
“executionTime”: “573.491µs”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}

I was able to set the value in the scala code using the following statement:
sc.couchbaseQuery(N1qlQuery.simple(query, N1qlParams.build().consistency(ScanConsistency.REQUEST_PLUS)))

Am I doing anything wrong here?

8

The new cbq shell is only in 4.5.

9

I wonder whether the index scan time out has anything to do with this.

In cbq (couchbase 4.1), I ran the following query
cbq> select count(*) as c from bucketname use index (idx_tx_track_type using GSI) where type==“mytype”;
{
“requestID”: “89e46b33-f6e4-42a0-bede-e5ce0e424ac2”,
“signature”: {
“c”: “number”
},
“results”: [
{
“c”: 7503496
}
],
“errors”: [
{
“code”: 12015,
“msg”: “Index scan timed out - cause: Index scan timed out”
}
],
“status”: “errors”,
“metrics”: {
“elapsedTime”: “2m0.007169057s”,
“executionTime”: “2m0.007070668s”,
“resultCount”: 1,
“resultSize”: 36,
“errorCount”: 1
}
}

If I just count the records, I can get 7.5 million even with timeout. but if I retrieve certain fields, it just return around 30k rows.
After I set the serverTimeout to 5 minutes, I am able to get 85888 rows.

From the experiments above, I think N1QL doesn’t work well, if lots of data are expected to be retrieved.

Thus, can anyone confirm this or provide some best practice of using N1QL?

thanks