WL#17054: Introduce Support for CHECK/NO CHECK at Table and Column Level, and UPDATE/NO UPDATE at Column Level in Json Duality View

Affects: Server-26.x — Status: Complete

Description
Requirements
High Level Architecture
Low Level Design

1. Overview

This WL strengthens MySQL JSON Relational Duality Views by introducing fine-grained control over optimistic concurrency checks and column updates. It allows customers to decide which relational values protect a JSON document from conflicting changes and which fields may be updated through the duality view.

2. Key Capabilities

2.1 Granular concurrency control

The new CHECK and NO CHECK annotations determine whether a table or column participates in ETAG calculation.

CHECK is the default at the table level.
Untagged columns inherit their table's setting.
A column-level annotation overrides the table setting.
Updating a NO CHECK field does not change the document's ETAG.

This lets customers distinguish business-critical fields from values that should not trigger concurrency conflicts.

2.2 Granular update control

The new column-level UPDATE and NO UPDATE annotations control whether individual fields can be modified through the duality view.

Attempts to update a field whose effective setting is NO UPDATE are rejected. Primary-key columns cannot be marked as updatable, protecting relational identity and referential integrity.

2.3 Safer DML behavior

For INSERT and UPDATE operations:

Every projected value with an effective CHECK annotation must be present in the input document.
Missing checked values cause the operation to fail instead of silently using a base-table default.
Attempts to modify fields marked NO UPDATE fail with a clear error.

These rules make concurrency and update behavior explicit and predictable.

3. Operational Integration

The feature is integrated across the MySQL lifecycle:

INFORMATION_SCHEMA exposes effective CHECK and UPDATE settings.
SHOW CREATE VIEW preserves table- and column-level annotations.
mysqldump preserves annotations and their semantics during dump and restore.
JSON Duality View DDL, including the new annotations, is replicated.
Upgrades from versions without granular annotations are supported.

Downgrading to an older version remains possible, but DDL using syntax unavailable in that version will not execute there.

4. Compatibility and Performance

Existing views remain protected because CHECK is the default. Customers must explicitly select NO CHECK where reduced ETAG sensitivity is desired.

When all projected columns use NO CHECK, SELECT performance is expected to remain comparable to standard JSON views over relational tables.

Referring:

Grammar from HLS/3
Context details from HLS/[1,2,4]

1. Changes in DDL

FR-1 Requirements for table_annotations_expression:

Tables being projected (i.e., via root_table_name or child_table_name clauses) in the duality view must support the optional use of the CHECK table annotation, in addition to the existing INSERT/NO INSERT, UPDATE/NO UPDATE, and DELETE/NO DELETE tags.
If no such tag is specified for the projected table, the table must be treated as if the CHECK tag is applied.
Tag annotations must be specified either individually or as order-agnostic combinations. When using:
1. Multiple annotations must be enclosed in round brackets () and separated by commas: WITH (INSERT, UPDATE, DELETE, CHECK).
2. Single annotation can be specified with or without brackets as follows: WITH CHECK or WITH (CHECK).
Usage of duplicate annotations must result in a syntax error; for example, specifying WITH (CHECK, CHECK) must produce an error.
Usage of a tag together with its restrictive counterpart must result in a syntax error; for example, specifying WITH (CHECK, NO CHECK) must produce an error.
Usage of table annotations is strictly limited to the JSON duality view. Using table annotations for any other purpose must result into a syntax error.

FR-2 Requirements for column_annotations_expression:

Columns being projected as part of column_expression in the JSON duality view must support the optional use of the CHECK and UPDATE tags, as well as their restrictive counterparts, NO CHECK and NO UPDATE, respectively.
If no such tags are specified for the projected column, the column must inherit these values from the projected table to which it belongs. However, an explicit column-level specification must supersede the table-level specification.
Tag annotations must be specified either individually or as order-agnostic combinations. When using:
1. Multiple annotations must be enclosed in round brackets () and separated by commas: WITH (CHECK, UPDATE).
2. Single annotation can be specified with or without brackets as follows: WITH CHECK or WITH (CHECK).
Usage of duplicate annotations must result in a syntax error; for example, specifying WITH (UPDATE, UPDATE) must produce an error.
Usage of a tag together with its restrictive counterpart must result in a syntax error; for example, specifying WITH (CHECK, NO CHECK) must produce an error.
If a column being projected is a primary key column, supplying the UPDATE tag for this column must result in an error.
Usage of column annotations is strictly limited to the JSON duality view. Using column annotations for any other purpose must result into a syntax error.

2. Changes in DML

FR-3 Requirements for JSON Duality view INSERT/UPDATE

During JSON Duality View DML operations (INSERT or UPDATE), if an UPDATE is attempted on any field whose effective tag (as defined in FR-2) is NO UPDATE, the operation must fail and return an error.
During JSON Duality View DML operations (INSERT or UPDATE), updates to the value of a projected column whose effective tag is NO CHECK, regardless of whether the update is performed through JSON Duality View DML or directly on the underlying base table, must not result in a change to the ETAG value.

3. Behaviour of INFORMATION_SCHEMA tables

FR-4 Requirements for JSON_DUALITY_VIEW_COLUMNS:

A new field ALLOW_CHECK must be added in the JSON_DUALITY_VIEW_COLUMNS table.
The ALLOW_CHECK field must have True or 1 as a default value.
The ALLOW_CHECK field must be populated in accordance with conditions defined for CHECK tag in FR-1 and FR-2.
The ALLOW_UPDATE field must be populated in accordance with conditions defined for UPDATE tag in FR-2.

4. Behaviour of "SHOW CREATE VIEW"

FR-5 Requirements for "SHOW CREATE VIEW":"

The SHOW CREATE VIEW statement must return a complete SQL representation of the JSON Duality View, such that executing the output regenerates the original view. The output must include all table-level and column level tags specified at creation time (including the newly introduced CHECK tag and its restrictive counterpart NO CHECK) and must match the definition originally supplied by the user.

5. Behaviour of Upgrade

FR-6 Requirements for Upgrade:

Upgrade must be possible from a version that did not support the newly introduced table and column tags, to the current version that does.
When downgrading from the current version to an earlier version that does not support granular tag specification and restrictive tags, any DDL statements using either of these features will fail to execute on the downgraded version, as expected (In place downgrades are not possible between innovation releases though, which makes this requirement redundant).

6. Behaviour of mysqldump

FR-7 Requirements for mysqldump:

Dump and restore using mysqldump must be possible from an older version that does not support the newly introduced table- and column-level tags to the current version.
Dump and restore using mysqldump must also be possible from the current version to an older version. Unsupported annotations must be emitted as versioned comments so the older server can ignore them and restore the supported view definition using its own semantics.
Dump and restore between current-version instances must preserve all table- and column-level tags and their semantics.

7. Behaviour of Replication

FR-8 Requirements for Replication:

The CREATE and ALTER statements for duality views, including the newly introduced table and column tags must be replicated.

8. Execution Engine for JDV SELECT Queries

FR-9 Primary-Engine Execution for JDV SELECT Queries

Offloading to HeatWave must error out if a SELECT statement references a JSON Relational Duality View built on base tables that use HeatWave as a secondary engine, and either the view is not single-level or one or more projected columns do not have an effective tag of CHECK.

9. Performance

NFR-1 Performance compared to JSON Views:

If the effective tag for all projected columns is NO CHECK, the performance of SELECT operations on the JSON Duality View (JDV) must be comparable to that of standard JSON VIEWs on relational tables.

1. Introduction

1.1 Oracle DB Behavior:

The Oracle DB JSON Duality views support below tags at table level:

INSERT/NOINSERT

UPDATE/NOUPDATE

DELETE/NODELETE

CHECK [ETAG]/NOCHECK [ETAG]

And below tags at column/field level:

UPDATE/NOUPDATE

CHECK [ETAG]/NOCHECK [ETAG]

1.2 MySQL JSON Duality View behavior to date:

1.2.1 Initial changes

In [MySQL JSON Duality View V1], requirements define the usage and compatibility rules for table tags, which represent a restricted subset of what is supported in Oracle DB's JSON Duality View:

FR-7: Requirements for table_annotations_expression:

Tables being projected (i.e. root_table_name or child_table_name clauses) in the duality view can optionally have table annotations. The following table annotations must be supported: INSERT DELETE UPDATE

There can be order-agnostic combinations of these annotations. These annotations must be enclosed with round brackets i.e. “(” and “)”, and must be > separated by comma “,”: WITH (INSERT, UPDATE, DELETE)

Usage of duplicate annotations must result in a syntax error: WITH (UPDATE, UPDATE, INSERT, DELETE)

Tables being projected in the duality view must either contain all three annotations (which makes them fully-writeable) or none (which makes them read-only).

Usage of table annotations is strictly limited to the JSON duality view. Using table annotations for any other purpose must result into a syntax error.

Note: [Applicable to FR-6] This WL introduces support for table tag specification in JSON duality view specifications only. While these annotations are crucial for DML operations and ETAG calculations, their specific usage and implementation details are beyond the scope of this WL.

FR-12: Compatibility requirements for table_annotations_expression:

Building on top of FR-6: Usage of the DELETE tag with the child_table must result in a semantic error if:

parent_table is referenced by a child_table.

child_table has its WHERE condition in the below form:

child_table.PK = parent_table.any_column

1.2.2 Subsequent changes

Later restrictive tags NO INSERT, NO UPDATE, and NO DELETE, and relax table tag rules by allowing limited write capabilities through the omission of specific tags or by explicitly specifying these restrictive tags.

FR-1 Requirements for table_annotations_expression:

Tables being projected (i.e. root_table_name or child_table_name clauses) in the duality view can optionally have table annotations. The following table annotations must be supported:

INSERT - Indicates that the INSERT operation IS permitted on the specified table.

UPDATE - Indicates that the UPDATE operation IS permitted on the specified table.

DELETE - Indicates that the DELETE operation IS permitted on the specified table.

NO INSERT - Indicates that the INSERT operation IS NOT permitted on the specified table.

NO UPDATE - Indicates that the UPDATE operation IS NOT permitted on the specified table.

NO DELETE - Indicates that the DELETE operation IS NOT permitted on the specified table.

If no tags are specified for the projected table, it must be treated as equivalent to applying (NO INSERT, NO UPDATE, NO DELETE).

Tag annotations must be specified either individually or as order-agnostic combinations. When using .

Multiple annotations must be enclosed in round brackets () and separated by commas: WITH (INSERT, UPDATE, DELETE).

Single annotation can be specified with or without brackets as follows: WITH INSERT or WITH (INSERT).

Usage of table annotations is strictly limited to the JSON duality view. Using table annotations for any other purpose must result into a syntax error.

FR-2: Compatibility requirements for table_annotations_expression:

Building on top of FR-1: Usage of the DELETE tag with the child_table must result in an error if:

child_table is referenced by a parent_table.

child_table is projected as singleton_descendent_json_object.

child_table has its WHERE condition in the below form:

child_table.PK = parent_table.any_column

Omission of any of INSERT, UPDATE, or DELETE must be interpreted as applying its restrictive counterpart. I.e., specifying (INSERT, UPDATE) is equivalent to (INSERT, UPDATE, NODELETE).

Usage of duplicate annotations must result in a syntax error:

WITH (UPDATE, UPDATE, INSERT, DELETE)

WITH (NO UPDATE, NO UPDATE, INSERT, DELETE)

Usage of a tag with its restrictive counterpart must result in an error:

WITH (UPDATE, NO UPDATE, INSERT, DELETE)

For example:

CREATE TABLE T1 (
  C1 INT PRIMARY KEY,
  C2 INT
);

CREATE TABLE T6 (
  C1 INT PRIMARY KEY, 
  C2 INT
);

CREATE TABLE T2 (
  C1 INT PRIMARY KEY,
  C2 INT REFERENCES T1(C1),
  C4 INT REFERENCES T6(C1)
);

CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = current_user()
SQL SECURITY DEFINER
JSON RELATIONAL DUALITY VIEW dv1
AS SELECT JSON_DUALITY_OBJECT(
    WITH INSERT  -- Use of a single tag (allows only INSERT)
    "_id" : C1,
    "C2"  : C2,
    "T2"  : (
                SELECT JSON_ARRAYAGG(
                            JSON_DUALITY_OBJECT(  
     WITH (NO INSERT, NO UPDATE, NO DELETE) -- Use of restrictive tags (read-only)
                                "C1" : C1,
                                "C2" : C2,
                                "T6" : (
                                            SELECT JSON_DUALITY_OBJECT(
                                                WITH (INSERT, UPDATE, NO DELETE)  -- Does not allow DELETE
                                                "C1" : C1,
                                                "C2" : C2
                                            )
                                            FROM T6
                                            WHERE T6.C1 = T2.C4
                                       )
                            )
                )
                FROM T2
                WHERE T1.C1 = T2.C2
            )
)
FROM T1;

2. What's new?

This WL introduces the CHECK tag and its restrictive counterpart NO CHECK at both the table and column levels (i.e., as part of value_expression). It also adds the UPDATE tag and its counterpart NO UPDATE at the column level.

The CHECK tag determines whether a given table (when specified at the table level) or a projected column (when specified at the column level) should be included in ETAG hash calculation. The UPDATE tag specifies whether a projected column can accept updates.

By default, CHECK is assumed at the table level unless NO CHECK is explicitly specified, while NO UPDATE is assumed at the table level unless UPDATE is explicitly specified. For both tags, column-level values inherit their defaults from the table-level settings; however, any explicit column-level specification overrides the corresponding table-level value.

NO UPDATE restricts modification of an existing base-table value; it does not prevent the field from being populated when JSON Duality View DML inserts a new row. Child-row DML can use INSERT ... ON DUPLICATE KEY UPDATE; in that case, fields with effective NO UPDATE are included in the insert values but excluded from the duplicate-key update assignments. If the supplied value differs from the existing value, the DML operation is rejected.

3. Grammar

3.1 High Level structure of DDL statements

3.1.1 Create the JSON DUAILTY VIEW:

A CREATE JSON DUAILTY VIEW statement creates a new JSON DUALITY VIEW (duality view from here onwards) object:

create_json_duality_view ::= 
  CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [DEFINER = user]
  [SQL SECURITY { DEFINER | INVOKER }]
  JSON [RELATIONAL] DUALITY VIEW 
  [IF NOT EXISTS] [schema_name .] view_name 
  AS json_duality_select_statement
  [WITH [CASCADED | LOCAL] CHECK OPTION]

3.1.2 Alter the JSON DUALITY VIEW definition:

The ALTER JSON DUALITY VIEW statement should follow below grammar:

alter_json_duality_view ::= 
  ALTER 
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [DEFINER = user]
  [SQL SECURITY { DEFINER | INVOKER }]
  JSON DUALITY VIEW view_name_expression
  AS json_duality_select_statement
  [WITH [CASCADED | LOCAL] CHECK OPTION]

3.2 Grammar:

The grammar commonly used for CREATE, ALTER of Duality views is outlined below:

json_duality_select_statement ::= 
SELECT json_duality_object_expression
FROM [schema_name .] root_table_name [AS table_alias_name]

json_duality_object_expression ::= 
JSON_DUALITY_OBJECT( 
  [WITH table_annotations_expression] json_duality_key_value_pairs 
)

table_annotations_expression ::=
table_annotations 
| (table_annotations [,table_annotations]...)

table_annotations ::= {INSERT | UPDATE | DELETE | CHECK | NO INSERT | NO UPDATE | NO DELETE | NO CHECK}

json_duality_key_value_pairs ::= 
json_name_and_value [ , json_name_and_value ]...

json_name_and_value ::= key_name : value_expression

value_expression ::= 
column_expression
| ( singleton_descendent_json_object ) 
| ( nested_descendent_json_objects  ) 

column_expression ::= column_name [WITH column_annotations_expression]

column_annotations_expression ::=
column_annotations 
| (column_annotations [,column_annotations]...)

column_annotations ::= {UPDATE | CHECK | NO UPDATE | NO CHECK}

singleton_descendent_json_object ::= 
SELECT json_duality_object_expression
FROM child_table_name [AS table_alias_name] 
WHERE json_duality_join_condition

nested_descendent_json_objects ::= 
SELECT JSON_ARRAYAGG( 
    json_duality_object_expression
    [json_constructor_null_clause]
) 
FROM child_table_name [AS table_alias_name] 
WHERE json_duality_join_condition

json_constructor_null_clause ::= NULL ON NULL | ABSENT ON NULL 

json_duality_join_condition ::= 
[schema_name .] child_table_name . column_name = 
[schema_name .] parent_table_name . column_name

The grammar commonly used for CREATE and ALTER of duality views is outlined below:

view_name_expression ::= [schema_name.] view_name

schema_name ::= /* identifier for schema */
view_name ::= /* identifier for view name */
root_table_name ::= /* identifier for root table */
key_name ::= /* valid JSON key name */
child_table_name ::= /* identifier for child table */
table_alias_name ::= /* identifier for table alias */
parent_table_name ::= /* identifier for parent table */
column_name ::= /* identifier for a column in a table */

4. Primary-Engine Execution for JDV SELECT Queries

A SELECT statement that references a JSON Relational Duality View built on base tables using HeatWave as a secondary engine may be offloaded to HeatWave only when the view is single-level and every projected column has an effective tag of CHECK.

5. Restoring Dumps to Older MySQL Versions

Annotations are emitted in SHOW CREATE VIEW output as /*!261000 ... */ versioned comments. Newer servers process them; older servers ignore unsupported annotations and restore the supported JSON Relational Duality View definition.

For example:

JSON_DUALITY_OBJECT(
  WITH UPDATE
  "status": status /*!261000 WITH (NO UPDATE) */
)

Table-level CHECK and NO CHECK annotations are emitted similarly:

JSON_DUALITY_OBJECT(
  WITH (UPDATE /*!261000 , CHECK */)
  "status": status
)

JSON_DUALITY_OBJECT(
  WITH (UPDATE /*!261000 , NO CHECK */)
  "status": status
)

An older server ignores the column or table annotation and restores the equivalent of:

JSON_DUALITY_OBJECT(
  WITH UPDATE
  "status": status
)

The restored view therefore follows the target server’s semantics. It may differ from the source view in field mutability, ETAG participation, concurrency behavior, and DML validation.

For example, ignored table-level or column-level NO CHECK annotations can cause a field to participate in ETAG calculation on the older server, and ignored NO UPDATE annotations can allow a field to inherit table-level update permission.

These differences are expected during downgrade restore: guarantees that exist only in the newer source version cannot be preserved by an older target version.

6. Replication

What are the Replication/High-Availability considerations with respect to deployment and installation?

Are there extra steps needed to install the feature?

No extra steps are required for the installation of the feature.
Is there a specific order for those steps? For instance, need to be installed first on replica servers and only then on source servers.

Yes, a specific upgrade order must be followed. First, upgrade the replica servers to a version that supports the newly introduced tags and their granular specification in JSON Duality Views. Once all replicas are successfully upgraded, the source server can then be upgraded. This order is essential to avoid the source generating replication statements that the older replica versions cannot understand or execute.

How does the feature operate on distributed environment?

Does the feature rely on server local configuration? That is, do source and replica server need to have the same configuration, plugins and or components?

The feature does not rely on local configurations, whether session or global, nor does it depend on specific plugins or components. Therefore, the source and replica servers do not need to have identical configurations or components for the feature to function properly.
What is the expected behavior when the configuration does not match?

NA
Does the feature rely on session configuration? Does that session configuration need to be replicated together with data?

The feature does not rely on session configuration. As such, session configuration does not need to be replicated alongside the data, allowing for flexibility in session settings between the source and replica servers.

Upgrade/Downgrade and Cross-Version Replication

Any impact on cross-version replication? Which?

There is no impact on cross-version replication in general. However, if the source server is running a newer version that supports JSON Duality Views with the newly introduced tags and granular specification, while the replicas are on an older version, any DDL statements related to JSON Duality Views will fail on the replicas.This is expected behavior for features that introduce syntax-level changes.
Any impact on upgrade/downgrade? Which?

Upgrade must be possible from a version that did not support the newly introduced restrictive table tags (such as NO INSERT, NO UPDATE, and NO DELETE) or allow granular tag specification, to the current version that does. When downgrading from a version that supports these new tag semantics to one that does not, DDL execution will fail on the target version as expected.
What about rolling upgrades? How is the user able to do rolling upgrades? Please consider LTS and non-LTS for all bullets in this section https://dev.mysql.com/doc/refman/8.4/en/mysql-releases.html

There is no impact on the rolling update.

Behavior Change

Is there anything the user could do before the worklog, which has a different effect after the worklog?

There is no such behavior. Actions taken by the user before the worklog will have the same effect after the worklog.
Is there anything the user could do before the worklog, which cannot be done after the worklog?

There is no such behavior. All actions that can be performed by the user before the worklog can also be performed after the worklog. There's no such behaviour.