Motivation

WL#9807 introduces an "upsert" (insert-or-update) functionality for Documents.

Following functional requirement of WL#9807:

FR2 - When an upsert leads to an Update, the primary key of the original item must be preserved

which leads to the upserted document to loose its document-id:

Given collection contains unique-index on field "name"
  And collection contains a document with { _id = "a", name = "foo", city = "Rome" }
 When user sends insert-or-update { _id = "b", name = "foo", city = "Rio" }
 Then ...

Expected result:

Insert document "b" and Error (unique-key violation on "name")

Current result:

Update document "a": _id = "a" (or "b"), name = "foo", city = "Rio"

Goal

Redefine the functional requirements in a way that upsert triggered by secondary key causes error when primary keys of both documents doesn't match.

FR1 - When a document inserted, if another item already exists with the same unique key value (that is, inserting it would cause a duplicate key error), the document should replace the original duplicate item.

FR2 - When an upsert leads to an Update, the primary key of the original item must match the new primary key from the new document.

FR3 - Upsert operations on tables should be rejected with an Error.

FR4 - If an upsert on collections with one or more unique keys in addition to the PK results in multiple matches/duplicate keys on the same row, the operation must succeed.

FR5 - If an upsert on collections with one or more unique keys in addition to the PK results in multiple matches/duplicate keys on different rows, the operation must fail with an error.

Notes

Most requirements are reused from WL#9807. Specification parts that were changed are bloded.

Protocol

There in no change in definition X Protocol messaged that triggers upsert, only behavior triggered by it, is impacted.

Behavior

The following table hashes out the expected action for different combinations of duplicate primary key (PK) and duplicate UNIQUE secondary key cases when there are 0, 1 and 2+ unique key conflicts.

Legend:

• N/A - No key
• O - OK, no conflict, the value is unique
• X - Conflict
• Y - Conflict with different rows

If there are multiple secondary keys, the case where they all match the same row or none at all is treated the same as having a single secondary key match (X X). If the secondary keys match different rows, it is treated as matches on different rows (X Y).

SQL Mapping

If the upsert flag is true, the INSERT statement shall generate with following " ON DUPLICATE KEY UPDATE" clause:

INSERT INTO collection (doc) VALUES (val1)
   ON DUPLICATE KEY UPDATE
     doc = IF(JSON_EXTRACT(doc, '$._id') = JSON_EXTRACT(VALUES(doc), 
                                                           '$._id'),
              VALUES(doc),
              mysqlx_error(127))");