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.
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.
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.
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.
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.
@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)