[moved: JavaSDK->CouchbaseServer] N1QL for 4.1.X count(*) problem

[egrep]

UPDATE: this post was moved to CouchbaseServer branch, because, as it finally was determined, there was no problem with JavaSDK, but there was (and still is) a problem for COUNT(*) N1QL query for prepared queries in 4.1.X versions.

For TL;DR case => skip first 10 posts or use direct link: [moved: JavaSDK->CouchbaseServer] N1QL for 4.1.X count(*) problem - #11 by egrep

Hello!
Java SDK 2.2.6 + 1.2.7, rxjava 1.1.2

    Observable
        .defer(() -> b.query(q))
        .flatMap(AsyncN1qlQueryResult::rows)
        .observeOn(getScheduler())
        .doOnCompleted(onCompleted)
        .doOnError(onError)
        .doOnNext(onN1QLRowAsBytes)
        .subscribe();

1. Q = {“args”:[“A”,“B”],“statement”:“select count(*) from bucket.name WHERE z=$1 and g=$2”,“scan_consistency”:“not_bounded”} - this works fine.
2. Q = {“args”:[“A”,“B”],“statement”:“select count(*) from bucket.name WHERE z=$1 and g=$2”,“scan_consistency”:“request_plus”} - this could return no result or just “hang” (exactly: result is returned after ~ 6 minutes)
   I think, the problem is with “request_plus” scan consistency, but can not determine exactly, is it Java SDK problem or server problem.

[UPDATE] found REQUEST_PLUS does not work as expected - #10 by moon0326
[UPDATE2], yes, Loading... seems like my case: all this happen after flush

[simonbasle]

One way of verifying if it was the SDK hangin would have been to use cURL or similar:

curl -X POST -H "Content-Type: application/json" -H "Cache-Control: no-cache" -d '{"args":["A","B"],"statement":"select count(*) from default WHERE z=$1 and g=$2","scan_consistency":"request_plus"}' "http://localhost:8093/query/service"

Ok so if it looks like MB-16957, it would seem that in Couchbase 4.5 the core engineers couldn’t reproduce the problem. Can you confirm which version of the server you’re using?

[egrep]

@simonbasle,
I’m on 4.1.0 (correction: src-based 4.1.0), and now i’m “wating for 2 min” to become sure that this is MB-16957.
[UPDATE] Cannot understand, why Loading... is “RESOLVED” …

[egrep]

@simonbasle,
it’s reproduceable, but, my 4.1.0 is src-based, so i need to re-install it with 4.1.0-EE
And, by the way , could you please save my time and tell (if it is easy enough) how to pass bucket auth with CURL-call you specified, because now my bucket is password-protected and:

"errors": [
    {
        "code": 10000,
        "msg": "Authorization Failed Keyspace keyspace.name"
    }
],

[simonbasle]

yeah, just provide the name of the bucket and the password as HTTP basic authentication. In cUrl this would be done with the -u bucketName:bucketPassword flag.

Note: I must admit I don’t use cUrl a whole bunch but instead use the chrome application Postman, which was also able to produce the first curl command above for me

[egrep]

@simonbasle,

Index problem with flush + select ... [request_plus | not_bounded]

Hello! Keep in mind https://issues.couchbase.com/browse/MB-16957 UPDATE: “Cannot Reproduce” for MB-16957 should be revised now, i think @jliang , @prataprc for your attention Cluster 4.1.0-EE (4CPU, 4Gb) x 3 Initialize a cluster, Create default bucket (1024M, no password, value ejection, 1 replica, view index replicas, high priority, flush enable) Edit attached script (inside .zip) with host, password and bucket name and run it (sorry, this is not python, it’s bash, but at least it’s…

[egrep]

@simonbasle,
it is weird, but is it possible for “select count(*) statement” (with SC=not_bounded) to return nothing ?
i.e for code

Observable
    .defer(() -> b.query(q))
    .flatMap(AsyncN1qlQueryResult::rows)
    .observeOn(getScheduler())
    .doOnCompleted(onCompleted)
    .doOnError(onError)
    .doOnNext(onN1QLRowAsBytes)
    .subscribe();

onCompleted is triggered, but onN1QLRowAsBytes is not triggered.
[UPDATE] Logs:

Thread-4 LOG ERROR: 1-X, Q = {“args”:[“A”,“B”],“statement”:“select count(*) from bucket.name WHERE a=$1 and b=$2”,“scan_consistency”:“not_bounded”}
Thread-4 LOG ERROR: 2-1 // this means, that call “onCompeted()” was triggered and “onError()” was not
Thread-4 LOG ERROR: 2-2 size = 0 // this means, that size of “result storage”, containing accumulated results is empty, i.e. nothing was returned.

[simonbasle]

mmh might it be that the rows() Observable is empty but the errors() Observable is not?

Querying asynchronously will only trigger onError if there are top-level errors (like connection cannot be established, or similar), while N1QL execution errors are represented as JsonObject in the errors() observable, for the sake of allowing partial results to come in and be processed…

We are aware this API is not an ideal one in its asynchronous form (and we plan on introducing an helper class that would allows to switch back to semantics that are “more Rx”).

[egrep]

@simonbasle,
ah, that’s why official example (Couchbase SDKs) is “not in rx-java style”, i thought, it’s a kind of “volatile interpretation” (i did not read comment below code).
After a little bit ugly hack

    Observable
        .defer(() -> b.query(q))
        .flatMap(r -> { return Observable.merge(r.errors(), r.rows()); })
        ...

i see error message:

{“msg”:“Encoded plan parameter does not match encoded plan of 6d44613366b67887374b910295a09171847a79c1”,“code”:4080}

I use prepared statements in this way:

private static final String N1QL_COUNT_X_BY_A_AND_B =
“select count(*) from bucket.name WHERE a=$1 and b=$2”;

public final ParameterizedN1qlQuery pQuery(Stirng a, String b) {
final N1qlParams p = N1qlParams
.build()
.adhoc(false)
.consistency(ScanConsistency.NOT_BOUNDED);
final JsonArray ja = JsonArray.empty().add(a).add(b);
return N1qlQuery.parameterized(N1QL_COUNT_X_BY_A_AND_B, ja , p);
}

Am i doing something wrong ?

[simonbasle]

Mmh looks like there is a problem with that prepared statement. I wonder if the count(*) isn’t problematic… Did you use adhoc(true) before testing with adhoc(false)?

[egrep]

@simonbasle,
i replaced adhoc to “true” just after i saw this error and then everything had become fine. But:

1. how can i benefit from prepared statement in this case, i.e. how to skip plan preparation phase ?
2. should i use count(a) or count(*) is “smart enough” to pre-optimize query and use only secondary index data to count ? Index = “create index on bucket(a,b) where a is valued”.
3. Where can i find docs about returned messages error codes detailed description ?

[simonbasle]

@keshav_m or @geraldss can you provide guidance? I think I remember a bug with count and prepared statements but I thought it was fixed in 4.1.0…

[geraldss]

Hi @egrep,

If you are building from source, can you use 4.5? The GA will be out shortly and has major improvements.

Gerald

[egrep]

@geraldss,
4.5 is “too heavy” for me now, 4.1.x is preferable. But i think, 4.5 vs 4.1.X discussion is out of bounds for this topic. And my questions are still same:

1. how can i benefit from prepared statement in this case, i.e. how to skip plan preparation phase ?
2. should i use count(a) or count(*) is “smart enough” to pre-optimize query and use only secondary index data to count ? Index = “create index on bucket(a,b) where a is valued”.
3. Where can i find docs about returned messages error codes detailed description ?

• 4. What about this error:

{“msg”:“Encoded plan parameter does not match encoded plan of 6d44613366b67887374b910295a09171847a79c1”,“code”:4080}

What is wrong ? If it is a bug, can you please provide a JIRA ticket link (i’ll try to investigate further, because @simonbasle mentioned, that it was fixed in 4.1.0)

[geraldss]

Hi @egrep,

Can you please try

SELECT COUNT(1) ...

There were some gaps in the COUNT() optimizations in 4.1, and we fixed those in 4.5.

Thanks,
Gerald

[egrep]

@geraldss,
First of all: “There were some gaps in the COUNT() optimizations in 4.1, and we fixed those in 4.5.” sounds very strange, because 4.1.1 is RECOMMENDED now as stable (as 4.1.0 was recommended before), and 4.5 is still beta. I REALLY think, that “gaps fixes” that you’ve mentioned, shoud be backported to 4.1.X branch. Because it’s really weird: to know about a problem, declare “4.1.0/4.1.1 is stable now” and don’t mention this bug as “known issues” for 4.1.0/4.1.1 (Couchbase SDKs). So there is a good chance to backport this to 4.1.2, if, of course, management team will allow this.

ok, technical aspects: i wrote a simple sync-test (code) below, and multiple runs tells me that there are stable 2 errors for COUNT(1) per each 100 calls for separate run:

0 =>[{“msg”:“Encoded plan parameter does not match encoded plan of 3e5d691a6480801871cae0bffb4814ab34d2befc”,“code”:4080}]
1 =>[{“msg”:“Encoded plan parameter does not match encoded plan of 3e5d691a6480801871cae0bffb4814ab34d2befc”,“code”:4080}]
DONE with i = 100

Create 3 indexes like:
“create index i1 on bucket.name(a,b) where a is valued;”
“create index i2 on bucket.name(a,b) where a is valued;”
“create index i3 on bucket.name(a,b) where a is valued;”

You can use even empty buckets. Then run the code :

package n1qlcount;

import com.couchbase.client.java.Bucket;
import com.couchbase.client.java.Cluster;
import com.couchbase.client.java.CouchbaseCluster;
import com.couchbase.client.java.document.json.JsonArray;
import com.couchbase.client.java.env.CouchbaseEnvironment;
import com.couchbase.client.java.env.DefaultCouchbaseEnvironment;
import com.couchbase.client.java.query.N1qlParams;
import com.couchbase.client.java.query.N1qlQuery;
import com.couchbase.client.java.query.N1qlQueryResult;
import com.couchbase.client.java.query.consistency.ScanConsistency;
import java.util.Collections;
import java.util.LinkedList;

public class N1QLCount {

public static void main(String[] args) {
    CouchbaseEnvironment ce = DefaultCouchbaseEnvironment.create();
    final LinkedList<String> nodes = new LinkedList();
    nodes.add("A");
    nodes.add("B");
    nodes.add("C");
    Cluster cluster = CouchbaseCluster.create(ce, nodes);
    final String bucketName = "bucket.name";
    final String bucketPass = "password";
    final String fieldA = "a";
    final String fieldB = "b";
    Bucket b = cluster.openBucket(bucketName, bucketPass);
    final N1qlParams p = N1qlParams
            .build()
            .adhoc(false)
            .consistency(ScanConsistency.REQUEST_PLUS);
    final String Q = 
            "SELECT COUNT(1) from `" 
            + bucketName 
            + "` WHERE " 
            + fieldA 
            + "=$1 AND " 
            + fieldB 
            + "=$2;";
    JsonArray ja = null;
    N1qlQueryResult r = null;
    int count = 100, i = 0;
    for(i = 0; i< count; i++) {
        ja = JsonArray.empty().add("A" + i).add("B" + i);
        r = b.query(N1qlQuery.parameterized(Q, ja , p));
        if(r.errors().size() > 0) {
            System.out.println(i + " =>" + r.errors().toString());
        }
    }
    System.out.println("DONE with i = " + i);
    b.close();
}

}

So, it’s defenetly a bug for 4.1.x. The question is “what we gonna do with this” ?

[geraldss]

Hi @egrep,

Thank you for the feedback and analysis. I am forwarding this to my team so we can look into it further. I was focusing on the COUNT() pushdown, which is a performance improvement. But you are right, if the prepared statements don’t work correctly, that would be a bug. We will respond on this thread.

Thanks,
Gerald

[egrep]

@geraldss,
“Good system is measured not by number of crashes, but by reaction for each crash”.
Thanks for first reaction
Looking forward and hope that 4.1.X will get this bug fixed.

[egrep]

@geraldss,
Are there any news or JIRA ticket for this issue ?

[egrep]

@geraldss, @simonbasle, @cihangirb
? Is there anything at all ?