WL#17053: Support Granular Table tags for Json Duality View

Affects: Server-9.x — Status: Complete

Description
Requirements
High Level Architecture
Low Level Design

The Json Duality View accept DML tags for tables. E.g,

CREATE OR REPLACE JSON DUALITY VIEW dv1 AS SELECT JSON_DUALITY_OBJECT( WITH (INSERT, UPDATE, DELETE) "_id" : C1, "C2" : C2 ) FROM T1;

The current behavior needs user to supply all the three tags in order to enable DML on 'dv1'. E.g., We do not support " WITH (INSERT, DELETE)", without UPDATE tag currently.

This work log should, - Enable DDL to accept sub-set of three tags. - Reject or accept DML operations based on supplied tags. - Sub-set of these tags can be supplied at each object level in Json Duality. - Consider Oracle JDV behavior while designing the feature. With good reason, we can deviate behavior as needed.

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. root_table_name or child_table_name clauses) in the duality view can optionally have table annotations. The following table annotations must be supported:
1. INSERT - Indicates that the INSERT operation IS permitted on the specified table.
2. UPDATE - Indicates that the UPDATE operation IS permitted on the specified table.
3. DELETE - Indicates that the DELETE operation IS permitted on the specified table.
4. NO INSERT - Indicates that the INSERT operation IS NOT permitted on the specified table.
5. NO UPDATE - Indicates that the UPDATE operation IS NOT permitted on the specified table.
6. 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 .
1. Multiple annotations must be enclosed in round brackets () and separated by commas: WITH (INSERT, UPDATE, DELETE).
2. 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:
1. child_table is referenced by a parent_table.
2. child_table is projected as singleton_descendent_json_object.
3. 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)

2. Behaviour of INFORMATION_SCHEMA tables

FR-3 Requirements for JSON_DUALITY_VIEWS table:

Based on the tags specified for each projected table (as interpreted in FR-1):

The ALLOW_INSERT, ALLOW_UPDATE and ALLOW_DELETE fields in the JSON_DUALITY_VIEWS view should be set to 1 if any projected table in the view hierarchy (root or child) is explicitly tagged to allow INSERT, UPDATE and DELETE, respectively.
The READ_ONLY field must be set to 1 if none of the INSERT, UPDATE, or DELETE operations are permitted in any of the projected table.

FR-4 Requirements for JSON_DUALITY_VIEW_TABLES table:

In accordance with the tags specified for each projected table (as defined in FR-1), the ALLOW_INSERT, ALLOW_UPDATE, and ALLOW_DELETE fields in JSON_DUALITY_VIEW_TABLES must be set appropriately for each table to reflect the allowed operations.
The READ_ONLY field must be set to 1 if none of the INSERT, UPDATE, or DELETE operations are permitted.

FR-5 Requirements for JSON_DUALITY_VIEW_COLUMNS:

Based on the tags defined for each projected table—as outlined in FR-1, the ALLOW_UPDATE field in the JSON_DUALITY_VIEW_COLUMNS view must accurately indicate whether the UPDATE operation is permitted for each column. Specifically, ALLOW_UPDATE should be set to 1 only if:
1. The associated table is not marked as read-only (i.e., it supports UPDATE operations).
2. The column is not a primary key column, as updates to primary key columns are typically restricted to preserve data integrity.
The READ_ONLY field must be set to 1 if the ALLOW_UPDATE is set to 0.

3. Behaviour of "SHOW CREATE VIEW"

FR-6 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 tags specified at creation time (including the newly introduced restrictive tags such as NO INSERT, NO UPDATE, and NO DELETE) and must match the definition originally supplied by the user. E.g.

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

mysql> CREATE OR REPLACE JSON DUALITY VIEW dv AS

-> SELECT JSON_DUALITY_OBJECT(
->     WITH (NO INSERT, NO UPDATE, NO DELETE)
->     "_id" : C1, 
->     "C2"  : C2
-> )
-> FROM T1;
Query OK, 0 rows affected (0.015 sec)

mysql> SHOW CREATE VIEW dv \G
*************************** 1. row ***************************
View: dv
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER JSON RELATIONAL DUALITY VIEW `dv` AS select json_duality_object( WITH (NO INSERT,NO UPDATE,NO DELETE) '_id':`t1`.`C1`,'C2':`t1`.`C2`) AS `Name_exp_1` from `t1`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.001 sec)

mysql> CREATE OR REPLACE JSON DUALITY VIEW dv AS
-> SELECT JSON_DUALITY_OBJECT(
->     "_id" : C1, 
->     "C2"  : C2
-> )
-> FROM T1;
Query OK, 0 rows affected (0.017 sec)

mysql> SHOW CREATE VIEW dv \G
*************************** 1. row ***************************
View: dv
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER JSON RELATIONAL DUALITY VIEW `dv` AS select
json_duality_object('_id':`t1`.`C1`,'C2':`t1`.`C2`) 
AS `Name_exp_1` from `t1`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.005 sec)

4. Behaviour of Upgrade

FR-7 Requirements for Upgrade:

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 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.

5. Behaviour of mysqldump

FR-8 Requirements for mysqldump:

Dump and restore using the mysqldump tool must be possible from an older version that did not support the newly introduced restrictive table tags (such as NO INSERT, NO UPDATE, and NO DELETE) or granular tag specification to the current version. Additionally, dump and restore must also work seamlessly between current-version instances to preserve all tags and semantics.

6. Behaviour of Replication

FR-9 Requirements for Replication:

The CREATE and ALTER statements for duality views, including the newly introduced table tags (NO INSERT, NO UPDATE, and NO DELETE) and support for granular specification, must be replicated.

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 V1 behavior:

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

The current version specifies that a projected table must either support full write capability (i.e., INSERT, UPDATE, and DELETE) or be strictly read-only (i.e., no tags used), except the case of limited write-capability. 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, UPDATE, DELETE)  -- Use of all tags (Full write-capability)
    "_id" : C1,
    "C2"  : C2,
    "T2"  : (
                SELECT JSON_ARRAYAGG(
                            JSON_DUALITY_OBJECT(  -- Use of no tags (read-only)
                                "C1" : C1,
                                "C2" : C2,
                                "T6" : (
                                            SELECT JSON_DUALITY_OBJECT(
                                                WITH (INSERT, UPDATE)  -- (limited write capability)
                                                "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 new restrictive tags NO INSERT, NO UPDATE, and NO DELETE, and relaxes table tag rules by enabling limited write capability through both omission of specific tags and the use of these new restrictive tags:

Allowing the omission of one or more tags i.e. allowing combinations like (UPDATE, INSERT)/(UPDATE, DELETE)/(INSERT,DELETE)
Introducing new tags such as NO INSERT, NO UPDATE, and NO DELETE i.e. allowing combinations like (INSERT, NO UPDATE, NO DELETE)

When no tags are specified, it is treated as equivalent to specifying all restrictive tags i.e., (NO INSERT, NO UPDATE, NO DELETE). Similarly, omitting any of INSERT, UPDATE, or DELETE is interpreted as applying its restrictive counterpart. For example, specifying (INSERT, UPDATE) is equivalent to (INSERT, UPDATE, NO DELETE).

Note: Lower-level details about these newly added restrictive tags are available in the LLD section.

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 | NO INSERT | NO UPDATE | NO DELETE}

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

json_name_and_value ::= key_name : value_expression

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

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. 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.

To support restrictive tags such as NO INSERT, NO UPDATE, and NO DELETE, the existing symbol NO_SYM was reused in combination with the standard operation tags: INSERT_SYM, UPDATE_SYM, and DELETE_SYM.

The grammar rules for clauses like jdv_table_tag and jdv_table_tags were extended accordingly. Their definitions now look as follows:

opt_jdv_table_tags:
        %empty { $$ = 0; }
      | WITH jdv_table_tag { $$ = $2; }
      | WITH '(' jdv_table_tags ')' { $$ = $3; }
      ;

jdv_table_tag:
        INSERT_SYM           { $$ = jdv::DVT_INSERT;  }
      | UPDATE_SYM           { $$ = jdv::DVT_UPDATE;  }
      | DELETE_SYM           { $$ = jdv::DVT_DELETE;  }
      | NO_SYM INSERT_SYM    { $$ = jdv::DVT_NOINSERT; }
      | NO_SYM UPDATE_SYM    { $$ = jdv::DVT_NOUPDATE; }
      | NO_SYM DELETE_SYM    { $$ = jdv::DVT_NODELETE; }
      ;

jdv_table_tags:
      jdv_table_tag { $$ = $1; }
    | jdv_table_tags ',' jdv_table_tag
      {
        // Reject conflicting or duplicate tags
        if (($$ & $3) ||
            (
              ($3 == jdv::DVT_INSERT && $$ & jdv::DVT_NOINSERT) ||
              ($3 == jdv::DVT_NOINSERT && $$ & jdv::DVT_INSERT) ||
              ($3 == jdv::DVT_UPDATE && $$ & jdv::DVT_NOUPDATE) ||
              ($3 == jdv::DVT_NOUPDATE && $$ & jdv::DVT_UPDATE) ||
              ($3 == jdv::DVT_DELETE && $$ & jdv::DVT_NODELETE) ||
              ($3 == jdv::DVT_NODELETE && $$ & jdv::DVT_DELETE)
            )
        ) {
          my_error(ER_JDV_INVALID_DEFINITION_WRONG_ANNOTATIONS, MYF(0));
          MYSQL_YYABORT;
        }
        $$ |= $3;
      }
    ;

This design allows flexibility by supporting both permissive and restrictive forms of table-level operation tags, while also ensuring that conflicting annotations (e.g., INSERT and NO INSERT) are properly detected and rejected at parse time.