This WL aims to inject ETAG into the JSON object fetched using duality view.

The ETAG (hash) value is calculated based on column values in every level. This work affects the Json Objects selected via SELECT, UPDATE and DELETE command.

FR-1: The SELECT query on a JSON DUALITY VIEW should include the ETAG hash in string format for each row. This hash should be present in the etag field within the _metadata sub-object of the result set.

1. Optimistic Concurrency Control in JSON Duality Views

Optimistic concurrency control (OCC) in JSON duality views allows writing or updating JSON documents only if no other session has modified them concurrently. It uses ETAG values to detect conflicting updates. ETAG values stored in the etag field of _metadata sub-object in the JSON documents. The ETAG field represents a hash of the document's current state (excluding _metadata by default).

1.1 How concurrency is handled?

1. User reads the data (using SELECT) and stores it locally.
2. User Modifies the local copy, keeping the generated etag unchanged.

3. Execution of UPDATE command re-constructs the object using SELECT and persists changes only if the reconstructed state (i.e. the etag from reconstructed object) matches the last-read state (i.e. one which was provided by user).

   If not, the database raises an error. Applications can then handle this by re-reading the data and retrying the operation.

1.2 ETAG Calculation:

The ETAG calculation typically takes into consideration all fields of JSON result-set except those excluded explicitly (exclusion of fields is not supported in current version).

Example:

mysql> CREATE TABLE T1 (C1 INT PRIMARY KEY, C2 INT, C3 INT);
mysql> INSERT INTO T1 VALUES (1,1,1), (2,2,2), (3,3,3);
mysql> CREATE JSON DUALITY VIEW DB1.DV1 AS SELECT JSON_OBJECT("C1", T1.C1, "C2", T1.C2) FROM DB1.T1;

mysql> SELECT data FROM DB1.DV1 LIMIT 1 \G
data: {
    "C1": 1, 
    "C2": 1, 
    "_metadata": {"etag": "16927677231443708220"}
}
1 row in set (0.01 sec)

1.3 Impact on Replication:

This WL does not impact replication. Below are some notes regarding the same:

• Is the ETAG updated/persisted on UPDATE or is it only a control value?

  The ETAG is not persisted; it is merely a control value. It is calculated as part of the SELECT statement, which can be executed independently or as part of an UPDATE operation.

• The ETAG calculation typically takes into consideration all fields of the JSON result set except those excluded explicitly (exclusion of fields is not supported in the current version). Are any of these fields server/configuration dependent?

  No, none of these fields are server or configuration dependent, as far as is known.

• Example: We have a source -> replicate topology that has the same persisted data. Can we have a different ETAG value if we select the same JSON on source and replica?

  No, if the persisted data is the same, the ETAG value should also be the same.

1. Change in the structure of Item_func_json_row_object

The member variable m_inject_object_hash was added to Item_func_json_row_object to determine whether JSON_OBJECT is a candidate for ETAG calculation:

diff --git a/sql/item_json_func.h b/sql/item_json_func.h
index 0f01e280c0e..7f18799e558 100644
--- a/sql/item_json_func.h
+++ b/sql/item_json_func.h
@@ -764,6 +764,7 @@ class Item_func_json_array final : public Item_json_func {
*/
class Item_func_json_row_object final : public Item_json_func {
String tmp_key_value;
+  bool m_inject_object_hash{false};

public:
Item_func_json_row_object(THD *thd, const POS &pos, PT_item_list *a)
@@ -783,6 +784,8 @@ class Item_func_json_row_object final : public Item_json_func {
    return false;
}

+  void set_inject_object_hash(bool inject) { m_inject_object_hash = inject; }
+
bool val_json(Json_wrapper *wr) override;
};

2. Identifying whether it is JSON DUALITY VIEW being SELECT-ed

In the SELECT call flow, within the setup_fields function, we check whether the query is selecting a JSON DUALITY VIEW. If it is, we set m_inject_object_hash for top-level JSON_OBJECT to true:

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index eabbefe5fe5..58cdc39508e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -107,6 +107,7 @@
#include "sql/item.h"
#include "sql/item_cmpfunc.h"  // Item_func_eq
#include "sql/item_func.h"
+#include "sql/item_json_func.h"
#include "sql/item_subselect.h"
#include "sql/lock.h"  // mysql_lock_remove
#include "sql/log.h"
@@ -9129,6 +9130,15 @@ bool setup_fields(THD *thd, Access_bitmask want_privilege, bool allow_sum_func,
    memset(ref_item_array.array(), 0, sizeof(Item *) * num_visible_fields);
}

+  Query_expression *master_query_expr = select->master_query_expression();
+  bool is_json_duality_view = false;
+  if (master_query_expr) {
+    Table_ref *tr = master_query_expr->derived_table;
+    if (tr && tr->is_view() && tr->is_json_duality_view) {
+      is_json_duality_view = true;
+    }
+  }
+
Ref_item_array ref = ref_item_array;

mem_root_deque<Item *>::const_iterator typed_it;
@@ -9248,6 +9258,17 @@ bool setup_fields(THD *thd, Access_bitmask want_privilege, bool allow_sum_func,
    }
    }

+    // Mark Json_object to generate etag.
+    if (is_json_duality_view == true) {
+      Item *it = item;
+      if (it->type() == Item::REF_ITEM) it = it->real_item();
+      if (is_function_of_type(it, Item_func::JSON_OBJECT_FUNC)) {
+        Item_func_json_row_object *jro =
+            down_cast<Item_func_json_row_object *>(it);
+        jro->set_inject_object_hash(true);
+      }
+    }
+
    select->select_list_tables |= item->used_tables();

    if (old_size != fields->size()) {

3. Calculating the ETAG

Finally, towards the tail of the SELECT call flow, in the Item_func_json_row_object::val_json function, we determine whether an ETAG should be computed for this JSON_OBJECT.

diff --git a/sql/item_json_func.cc b/sql/item_json_func.cc
index cce7682dc7d..345e283eb47 100644
--- a/sql/item_json_func.cc
+++ b/sql/item_json_func.cc
@@ -2534,6 +2534,16 @@ bool Item_func_json_row_object::val_json(Json_wrapper *wr) {
        return error_json(); /* purecov: inspected */
    }

+    if (m_inject_object_hash == true) {
+      std::string etag_hash;
+      calculate_etag(object, etag_hash);
+      Json_object *metadata = new (std::nothrow) Json_object();
+      if (metadata->add_alias("etag", create_dom_ptr<Json_string>(
+                                          etag_hash.c_str(), etag_hash.size())))
+        return error_json();
+      if (object->add_alias("_metadata", metadata)) return error_json();
+    }
+]