Gap in Delete/Select Query with meta().id


#1

I tried to execute the below deletion query I could see that 30915 as mutation count. Does it mean that it has deleted 30915 records?

delete FROM myBucket WHERE ANY d IN myBucket.testNode.delta SATISFIES d.processStartDate IS NOT NULL AND d.keyValue IS NOT NULL and meta().id=“323c9f2-789b-4ddc-afab-d52936023c1e” END

But when I use select instead of delete, it return only one record.

Could you please let me know why there is a big gap?


#2

DELTE/UPDATE mutationCount tells possible mutations attempted actuals may be less.

Post the index definition and EXPLAIN on DELETE.

Also you can add RETURNING META().id clause and see what ids it deleted

Move the meta().id out side like below as it doesn’t depends on the array

 DELETE  FROM myBucket
 WHERE ANY d IN myBucket.testNode.delta SATISFIES d.processStartDate IS NOT NULL AND d.keyValue IS NOT NULL END 
AND meta().id="323c9f2-789b-4ddc-afab-d52936023c1e";

OR

 DELETE  FROM myBucket USE KEYS ["323c9f2-789b-4ddc-afab-d52936023c1e"] 
     WHERE ANY d IN myBucket.testNode.delta SATISFIES d.processStartDate IS NOT NULL AND d.keyValue IS NOT NULL END ;

#3

Thank you.

After executing below query, it has deelted many record from myBucket bucket. But it shouldnt delete more than one (323c9f2-789b-4ddc-afab-d52936023c1e) right ?

Please let me know why its deleting more records?

delete FROM myBucket WHERE ANY d IN myBucket.testNode.delta SATISFIES d.processStartDate IS NOT NULL AND d.keyValue IS NOT NULL and meta().id=“323c9f2-789b-4ddc-afab-d52936023c1e” END


#4

It should not. Post the EXPLAIN of DELETE and index definition. CB version. Sample document of the given document key.
Also try RETURNING META().id clause see what documents are deleted

Looks like you are hitting MB-27815

Following should fix your use case (Move META().id = … predicate outside ANY clause)

DELETE  FROM myBucket
 WHERE ANY d IN myBucket.testNode.delta SATISFIES d.processStartDate IS NOT NULL AND d.keyValue IS NOT NULL END 
AND meta().id="323c9f2-789b-4ddc-afab-d52936023c1e";

#5

Thank you! Appreciate!