N1QL returns a different CAS than the driver functions

An application I’m working on needs to be very careful about conflict resolution, so I’m making as much use of CAS as I can, but one problem I’m having is that using Bucket.Get() returns a different CAS value than using an N1QL query. At first the issue was that the json.RawMessage on the QueryResult object was returning it in scientific notation, so I moved from the community edition (4.0) to the enterprise edition (4.5.0). The value being returned in the raw JSON message now comes through as a standard number, but it’s still wrong.

I tried unmarshalling into several different types including gocb.Cas, uint64, []byte, [4]byte, string (using the TOSTRING function in the query), and json.Number. All brought back the same wrong value, so that was when I decided to check the raw message again and found the value was unmarshalling correctly, it’s the actual returned value that is wrong.

I think it may be a rounding/format error on the actual server or somewhere in gocb because even when I print the RawMessage bytes as a string, the last 6 or 7 digits of the raw returned value are always different from the ones retrieved from the Bucket.Get() function, and the last two digits (sometimes 3) are ALWAYS 0.

When I try and retrieve the same record using both the SDK and N1QL and then update them (using the driver functions), if I supply the CAS values, the N1QL one always fails and the SDK one always succeeds, indicating the CAS value returned by N1QL is indeed the incorrect one.

Here is a snippet of the code I’m working with from an abstraction layer:

/*GetOne returns a single record whose ID matches the one provided, along with an error if one occurred.*/
func (os OrganisationService) GetOne(orgId id.ID) (*users.Organisation, error) {

logutil.BeginFunction("couchbase", "OrganisationService.GetOne")
defer logutil.ExitFunction("couchbase", "OrganisationService.GetOne")

//Perform a get operation using the key/value store method.
var retrievedVal *users.Organisation
cas, err := manager.buckets["customers"].Get(orgId.String(), &retrievedVal)

//If there was an error retrieving it, handle that error and return the client-friendly one the handler function returns.
if err != nil {
	return nil, handleCouchbaseError(err)
}

//Check if org ID matches calling user org ID or system user ID.
if retrievedVal.Id() == os.callingUserOrgId || os.callingUserOrgId == id.SystemUser {
	retrievedVal.LockingValue = uint64(cas)
	return retrievedVal, nil
} else {
	return nil, handleCouchbaseError(gocb.ErrKeyNotFound)
}

}

/*
GetByName returns a record where there is a field called "name" and its value is equal to the search
term. If there are multiple records with the same name (uniqueness is not necessarily required) then
there is no guarantee it will return the same record every time.
*/
func (os OrganisationService) GetByName(recordName string) (*users.Organisation, error) {

logutil.BeginFunction("couchbase", "OrganisationService.GetByName")
defer logutil.ExitFunction("couchbase", "OrganisationService.GetByName")

//We will have to perform this operation using the query provider, so it may be marginally slower than the other get operations.
queryString := "SELECT *, meta().cas AS cas FROM `customers` WHERE `name` = $name AND `metadata`.`customerId` = $cid LIMIT 1"
query := gocb.NewN1qlQuery(queryString).AdHoc(false)

//Put the parameters in a slice and pass that through to bind the variables, rather than doing the ol' string concatenation and looking like a right fool.
params := make(map[string]interface{})
params["name"] = recordName
params["cid"] = os.callingUserOrgId.String()
params["creds"] = []struct {
	Username string `json:"user"`
	Password string `json:"pass"`
}{{
	Username: "user",
	Password: "password",
}}
//TODO change the above to NOT be hardcoded, but do that after we've confirmed a security strategy for the DB.
rows, err := manager.buckets["customers"].ExecuteN1qlQuery(query, params)

//Check if an error occurred running the query.
if err != nil {
	return nil, handleCouchbaseError(err)
}
defer rows.Close()
fmt.Print(rows)

//Try and put the record returned (if there is one) into a record struct.
var results struct {
	Inner        *users.Organisation `json:"customers"`
	LockingValue gocb.Cas            `json:"cas"`
}
err = rows.One(&results)
if err != nil {
	return nil, handleCouchbaseError(err)
}

results.Inner.LockingValue = uint64(results.LockingValue)
return results.Inner, nil

}

/*
UpdateOne replaces an existing record with an updated version of itself. It will fail if the record
does not exist in the database or the locking value does not match (meaning the record has been
edited since it was retrieved).
*/
func (os OrganisationService) UpdateOne(o *users.Organisation) results.SaveResult {

logutil.BeginFunction("couchbase", "UpdateOne")
defer logutil.ExitFunction("couchbase", "UpdateOne")

if !o.Id().IsNull() {

	if os.callingUserOrgId == o.Id() || os.callingUserOrgId == id.SystemUser {

		o.SetUpdateValues(os.callingUserId)

		//Attempt to update the record in the database.
		cas, err := manager.buckets["customers"].Replace(o.Id().String(), o, gocb.Cas(o.LockingValue), 0) //BUG for some reason (probably JSON related), because CAS is rounded, replace on anything retrieved from query will always result in CAS mismatch.
		if err != nil {
			fmt.Println(o)
			fmt.Println(err)
			return results.NewSaveResult(false, handleCouchbaseError(err), o.Id())
		}

		o.LockingValue = uint64(cas)
		isSuccess := (err == nil)
		updatedId := o.Id()

		return results.NewSaveResult(isSuccess, err, updatedId)
	} else {

		return results.NewSaveResult(false, fmt.Errorf(msgOrgUpdatePermissionErr), o.Id())

	}
} else {

	return results.NewSaveResult(false, fmt.Errorf(msgIdOnUpdateErr), o.Id())

}

}`

So the saved record already exists, I get one copy of it using GetOne which uses the SDK, and GetByName which uses N1QL. The *users.Organisation retrieved with GetByName always fails to update using UpdateOne() due to a CAS mismatch.

For reference, manager is a utility struct that just helps keep passing buckets around clean and managing connections. I have seen similar issues raised with other SDKs (specifically Node.js and the Java one), but they’re all a year or two old.

Hi @Leylandski,

Yes, it is a rounding error. Internally, N1QL represents all numbers as float64, and this causes a problem for large CAS values. Several users are running into this issue, so we are thinking about how to address it.

For your application for now, is it possible to use SDK for all CAS-based updates?

Gerald

Hi, thanks for the swift reply.

I thought it must be on the server but wasn’t 100% sure as I struggled to find posts detailing the same specific problem. The application is far from finished, so it’s no problem to take everything that needs N1QL and just put that at the back of the queue. In the meantime I’ll keep my eyes out for updates.

What I’ve done now to mitigate it is at the end of GetByName, rather than returning the struct returned by the query, just use the ID returned to subsequently call GetOne() and hey presto, correct CAS with only a small performance hit.

JIRA ticket: https://issues.couchbase.com/browse/MB-20164

Hi,

Not sure if this the proper way to do it, I would love anyone’s comment on this, but what I’m doing to get a proper CAS value with N1QL is to wrap META in ENCODE_JSON like this:
SELECT b.*, ENCODE_JSON(META().cas) AS cas FROM ...

This returns a correct CAS value (as far as my tests go).
Hope this helps.

PS:I don’t know what is wrong in my setup, I see the above Jira ticket as being closed (MB-20164), but for me, N1QL returns wrong CAS value (rounded number). I’m using Couchbase Community 4.6.0 and couchbase@2.3.2.
PS2: using TOSTRING instead of ENCODE_JSON still produces a badly rounded cas value

Can you post return/ correct cas values

Sure, I get 1492855384243896327 with ENCODE_JSON and 1492855384243896300 without.

Just noticed that I’m in the “Go SDK” category, whereas I’m working with Node.js SDK.

Can you run the query with cbq shell or through curl and confirm the results. So that we can decide it is N1QL issue or Node.js issue

Ran the query through cbq shell and the results are:
SELECT b.*, META().cas AS cas FROM ... => cas value is ok (1492855384243896332)
SELECT b.*, ENCODE_JSON(META().cas) AS cas FROM ... => same as above
SELECT b.*, TOSTRING(META().cas) AS cas FROM ... => cas value is NOT OK (1492855384243896300)

also, if I run the same query on Query Workbench in the Web Console, it does not return the correct cas value without ENCODE_JSON.
If I look at the Dev Tools/Network tab in the browser, the response for the query is coming in correctly, so this looks like it’s a JavaScript rounding issue in both NodeJS and Web Console. (the response has a cas value of 1492855384243896332 whereas the web console is showing me 1492855384243896300)

@mpricob,
TOSTRING() conversion is bug https://issues.couchbase.com/browse/MB-24048.
Query Workbench the issue has been fixed in 5.0.0 https://issues.couchbase.com/browse/MB-20352
On nodeJs side cc’ed @ingenthr.

Thank you.

I believe that’s a known limitation of JavaScript numbers. With the KV service, there is special handling of CAS. @brett19, can you review?

The Query Workbench fix for this issue was also back ported to 4.6.1.