Inconsistency result when adhoc set to false

When querying using the following with the same value for each of the parameters, it returns different results for each run. However once I set the adhoc to true, it returns consistent result.

** we are using java-client 2.4.5
** there are no mutation of the content while doing so.

SELECT 1 AS authorized FROM zsUSE INDEX (zst USING GSI) WHERE (SELECT RAW 1 FROM (SELECT metadata.deleted AS deleted FROM zs AS ls USE INDEX (zst USING GSI) WHERE tag.token = $token AND tag.expiryepoch > $published ORDER BY metadata.published DESC LIMIT 1) AS lo WHERE deleted = FALSE)[0] = 1 AND tag.token = $token AND outline.createresource = TRUE LIMIT 1

Hello @wai.kwang.mak, What version of Couchbase Server are you using? A bug in Prepared Statements (adhoc=false) was fixed in Couchbase Server 4.6.2. MB-22273.

Hi @jkurtz, we are using 4.6.2-3905 Enterprise Edition (build-3905)

Hmmm. That’s the server version I tested when I saw the same behavior a couple weeks ago. It made the issue go away for me. I was using an older Java client, 2.4.2.

Can you check any panics in query.log

I tried the same with the older Java client, 2.4.2. Unfortunately it is still showing inconsistent result. I wonder if it has to do with the cover index with select 1 as part of the query.

I had since slightly adjusted the query to
SELECT * FROM zs USE INDEX (zst USING GSI) WHERE (SELECT RAW 1 FROM (SELECT metadata.deleted AS deleted FROM zs AS ls USE INDEX (zst USING GSI) WHERE tag.token = $token AND tag.expiryepoch > $published ORDER BY metadata.published DESC LIMIT 1) AS lo WHERE deleted = FALSE)[0] = 1 AND tag.token = $token AND outline.createresource = TRUE LIMIT 1

And it started ‘working’. It consistently returned the wrong result for the first time, but subsequent query returns the correct result.

@vsr1 I will check the log once I gain access to the box.

To add, once I removed the raw 1, it returns consistent result. I also wonder if it has to do with
https://issues.couchbase.com/browse/MB-24594

MB-24594 will not cause this issue. That is for INDEX JOINs and your query will not have INDEX JOIN. This is more seems like MB-25395 if there panic it will confirm.

Alright, I will check once I gain access to the box. I should be able to do so by Monday.

I downloaded the logs from the couchbase folder in /opt/couchbase/var/lib/couchbase/logs. I am unable to find ‘panic’ in any of the logs file.

I am happy to share the logs, but unfortunately it is 16MB after zip.

Without panic also MB-25395 can cause inconsistent results.

Fair enough. If so, may I ask when will the fix be released?

Currently working on the fix depends on that it will be decided.

I am experiencing the same this with the .Net SDK ver 2.5.0 / 4.6.2

Any updates on a fix?

Fixed in 4.6.3 and you can try 4.6.3. If yo are using subquery expression you have to wait 5.0.0 or set adhoc =true

When will 5.x be released then? We definitely need this bug fix as it impacts one of my most common queries in a performance critical part of the code.

I had tried 4.6.3, it doesn’t work.

Hi @wai.kwang.mak,

Public availability of Couchbase Server 5.0.0 is ~tentatively planned for end of October or early November 2017 timeframe.

Hope that helps!

Anil Kumar
Product Management

1 Like

@wai.kwang.mak:

Did you get a chance to try 5.0?
Would you be able to share the full repro (with your sample data) so we can debug this fully? Thank you.

@keshav_m: I did not try the query with 5.0 as it is not easy to do that (install new package on any environment for testing and even if it works, install new couchbase in UAT/PROD environment) in the organization where I am working in. I am happy to share to the sample data along with index details but am quite tight up with work now. I will work on that once I have a breather (say before end of next week)