N1QL Update Query mutationCount seems erroneous

N1QL
1

I ran this n1ql query on my dataset:

update arc set box.qty = 25 where type = 'product' and sku like 'UGO-CASE01-RHDL-%';

Here are 2 example product documents for reference:

Product with box.qty already set:

{
	"arc": {
		"box": {
			"qty": 25
		},
		"cbId": "ugobags-uos-product-UGO-CASE01-RHDL-BK-LG",
		"created": "2017-01-13T00:23:52+00:00",
		"for": "CASE01",
		"inventory": {
			"DWH": "",
			"LAO": "",
			"LAW": ""
		},
		"manufacturer": "UGOBAGS",
		"modified": "2017-01-25T21:08:08+00:00",
		"name": "Black Large Retractable Handle",
		"options": {
			"color": "BK",
			"size": "LG"
		},
		"sku": "UGO-CASE01-RHDL-BK-LG",
		"type": "product"
	}
}

Product w/o box.qty set:

{
	"arc": {
		"cbId": "ugobags-uos-product-UGO-CASE01-RHDL-BK-MD",
		"created": "2017-01-13T00:23:52+00:00",
		"for": "CASE01",
		"manufacturer": "UGOBAGS",
		"modified": "2017-01-13T00:23:52+00:00",
		"name": "Black Medium Retractable Handle",
		"options": {
			"color": "BK",
			"size": "MD"
		},
		"sku": "UGO-CASE01-RHDL-BK-MD",
		"type": "product"
	}
}

Here is my result from the update statement:

{
	"results": [],
	"metrics": {
		"elapsedTime": "40.286758ms",
		"executionTime": "40.240108ms",
		"resultCount": 0,
		"resultSize": 0,
		"mutationCount": 30
	}
}

If a run a select statement using the same criteria as in the update, I get 30 documents returned. So the mutationCount is correct on the one hand.

On the other hand, my records were actually NOT updated at all. In other words, the update didn’t even work and the documents without a box.qty value still don’t have one. Yet mutationCount tells me 30 records were updated.

What am I missing? And what did the update query actually do?

2

Good question. The good news is, the behavior and results are correct.

The mutationCount is the number of records that matched the predicate. All these 30 records were updated.

Because you are setting box.qty, only records that already have box will show a difference. They don’t need to have box.qty, but they need to have box. That is, the update will not create missing parent paths. You can do this:

SET box = { "qty" : 25 }

3

Thanks Gerald. This is helpful to know. I figured there was a way to change the schema of my document through N1QL but wasn’t sure how exactly.

This still concerns me, however. In my original case, only 10 of the 30 potentially affected records already contained a “box” field and thus were updated according to my “SET box.qty = 25” statement. But why does CB count the other 20 as having been updated even though they clearly weren’t?

To me, the value of mutationCount is really saying “Here’s how many records were reviewed in the running of your query” while not actually telling me how many were updated.

4

Hi @kevin,

You can always write your WHERE clause to exclude records that you don’t want updated.

A mutation is a mutation, whether or not the new value is the same as the old value. You can see the same behavior in Postgres and commercial RDBMS. Only MySQL is an outlier. For example, this has implications for transactions and locking.

5

I see. Since I come from a MySQL background that would explain my confusion here :slight_smile:

Although I still see it as slightly off-putting (the mutationCount = 30) because as you mentioned originally, since the parent field “box” didn’t exist, Couchbase did not create a child “qty” field. Thus couldn’t we say that the entire document was skipped/bypassed from having been updated? I understand counting the record as having been updated IF the value simply gets set to the same (whereas MySQL does exclude these types of updates). But here we’re talking about NO change AND NO duplication of the same field value.

I’m nitpicking big time here, but simply trying to get as clear as possible. I do appreciate your help of course!

6

From MySQL, aha! :slight_smile:

No worries, we are here to listen and help. Think of the mutationCount as the count of the records that were selected for mutation.

After being selected, they were mutated. Some of those mutations resulted in no change to the record. For us to calculate what you are suggesting, we would have to compare the pre- and post- versions of the record. This is not efficient, and is more expensive with complex data than with flat relational data.

For example, starting with:

{
    name: John
    some_field: 20
}

UPDATE some_bucket
SET some_field = 20;

Did we mutate John? You could say yes or no. It has a new CAS value. How would we know if we made a change? We would have to read the original some_field and compare it to the new some_field. What if some_field was not a simple integer, but was instead a complex object itself. The comparison could be more expensive.

What if our UPDATE statement was updating 50 fields instead of 1. Now, we would have to compare all the mutated fields until we found one that was changed. If none was changed, we would have to compare all 50 to determine that.

And some users may not even need the information! Those who do, like you, can just add it to the WHERE clause :slight_smile:

7

Makes more sense to count the record mutated since the CAS value changes. I hadn’t thought of that. Good to keep in mind that once a doc is valid for the “where” portion of the update query it IS getting updated in some fashion, even if it’s value-content doesn’t change. Thanks again, Gerald.

1 Like
8

I know it’s 2020, but has anyone figured this out? I am also using this to execute directly to the cluster where _N1Q1Query is my update statement to be executed.

            IQueryRequest request = new QueryRequest();
            request
                .Statement(_N1QLQuery)
                .ScanConsistency(ScanConsistency.RequestPlus);

MutationCount returned 1. But the document sometimes update and sometimes do not. Success rate is 80% of the time. It seems like there is a bug in the Couchbase Database Server.

9

Please open new post with full details.