WL#16616: Implement DDL for Json Duality View

Affects: Server-9.x   —   Status: Complete

This WL aims to implement the following DDLs for JSON Duality View:

  1. Support the CREATE OR REPLACE JSON [RELATIONAL] DUALITY VIEW command.

  2. Support the ALTER JSON [RELATIONAL] DUALITY command.

  3. The SHOW CREATE command should produce syntax like 1.

  4. The DROP VIEW command should be able to delete both normal and duality views.

The CREATE OR REPLACE DUALITY VIEW and ALTER JSON DUALITY VIEW commands should enforce a set of syntax-validation and semantics-validation rules. This is achieved by maintaining an internal data structure, JSON Duality Map. The map would maintain the table and columns used in the JSON Duality View at each level.

WL introduces a clause to specify annotations for table at each level. The duality view map will also contain details of annotations defined for the Duality View.

The CREATE and ALTER commands should go through both syntax and semantic validation steps, while for SELECT, only semantic validation should suffice.

This WL will be pushed along with DML changes, INFORMATION_SCHEMA changes and the ETAG support.

Referring:

  1. Grammar from HLS/2

  2. Context details from HLS/3

1. Create the JSON DUALITY VIEW:

1.1: Syntax validation:

FR-1: Requirements for view_name:

  1. It must be a valid identifier [ref].
  2. Its length must be up to 64 characters [ref].
  3. It must collate according to system variable lower_case_table_names:
    1. If lower_case_table_names = 0, view_name equality implies same character case. i.e. view_name stored on disk using the character case specified in the statement. Name comparisons are case-sensitive.
    2. If lower_case_table_names != 0, character case used for view_name does not matter.

FR-2: Name conflict handling for view_name:

  1. If duality view with name view_name is already present in the current schema, and if the statement is not CREATE OR REPLACE, an error must be reported.

  2. If a normal view or table with name view_name are present in the current schema, regardless of whether OR REPLACE clause used or not, an error must be reported.

  3. Vice versa of point-2 is also valid i.e. An attempt to create a normal view with the name already used by a table or a duality view, regardless of whether OR REPLACE clause is used or not, must result into an error.

FR-3: Requirements for schema_name in the view_name_expression:

  1. If schema_name is specified, and doesn't exist, an error must be reported.
  2. if schema_name is not specified:
    1. The duality view must be created in the current schema.
    2. If the current schema is not set, an error must be reported indicating that a schema must be defined.

FR-4: Requirements for JSON_DUALITY_OBJECT:

  1. Introduce new function JSON_DUALITY_OBJECT to create JSON values.
  2. JSON_DUALITY_OBJECT function must be used by the JSON Duality view only. Use of this function in other cases must report an error.
    1. Consequently, attempting to create a regular (non-duality) view based on a JSON Duality view must result in an error.

Note: Refer grammer of JSON_DUALITY_OBJECT function in Section 2.2 of HLS.

FR-5: Requirements for Top-Level SELECT in json_duality_select_statement:

  1. Requirements for SELECT list:

    1. It must contain only one JSON_DUALITY_OBJECT() expression which must be non-empty.
    2. It should be a simple SELECT statement, operators like UNION, INTERSECT, EXCEPT and complex SELECT like CTEs aren't supported and must result into a syntax error if used.

  2. Requirements for FROM clause:

    1. it must contain only one table, which may belong to any schema. If it doesn't belong to the current schema, schema name must be provided explicitly.
    2. The use of other clauses of SELECT statement, like WHERE, JOIN, GROUP BY, ORDER BY, HAVING, WINDOW, LIMIT aren't supported in this context. A syntax error must be reported if they're used.

FR-6: Requirements for json_duality_key_value_pairs:

  1. Requirements for key_name:

    1. There must be a key named "_id" in root object representing primary key of the root table. Absence of such key_name in the root table json_duality_key_value_pairs must result in an error.

    2. Usage of key name "_id" in sub-objects must result in an error.

    3. Usage of duplicate keys in the same JSON_DUALITY_OBJECT() should report a syntax error.

  2. Requirements for value_expression:

    It must either be a column name, JSON_DUALITY_OBJECT(singleton descedent), or JSON_ARRAYAGG(nested descedent). Usage of any other expression must result in a syntax error.

    1. Requirements for column_name:

      1. It must reference a valid column in the table that is being projected (it may be part of root_table_name or current_table_name clause).
      2. SELECT-ing column_name more than once in a single JSON_DUALITY_OBJECT must result into a syntax error.
      3. Usage of any operators (such as UPPER) on top of a column_name must result into a syntax error.
      4. Projecting columns of type JSON, VECTOR and GEOMETRY (Including all its derivative datatypes like POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION) isn't supported and must result into a semantic error if done.
      5. Projecting generated columns isn't supported and must result into a semantic error if done.
      6. If column_name with the key _id in the root table json_duality_key_value_pairs isn't a primary key of that table, an error must be reported.
      7. If the table is projected multiple times, the set of columns projected must be consistent across all instances of the table projection.

    2. Requirements for singleton_descendent_json_object:

      1. Requirements for SELECT list: Same as FR-5.1

      2. Requirements for FROM clause:

        1. It must contain only one table, which may belong to any schema. If it doesn't belong to the current schema, schema name must be provided explicitly.
        2. If parent_table_name and child_table_name are the same tables, not using alias for at least one of them must result into a semantic error.
        3. The use of clauses like JOIN, GROUP BY, ORDER BY, HAVING, WINDOW isn't supported in this context. A syntax error must be reported if they're used.

        4. Unlike top-level select, WHERE clause is supported here but it must only contain one expression, and that must be of the below form:

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

        usage of any other type of condition or usage of AND/OR operator in the condition must result in a syntax error.

    3. Requirements for nested_descendent_json_objects:

      1. Requirements for SELECT list:

        1. It must contain only one JSON_ARRAYAGG() expression.
        2. The JSON_ARRAYAGG() must contain JSON_DUALITY_OBJECT() and JSON_DUALITY_OBJECT() must not be empty.
        3. JSON constructor null clause ABSENT ON NULL is supported for the JSON_ARRAYAGG() in duality view. By default NULL ON NULL is behavior is applicable i.e. null value is set is array is empty. JSON_ARRAYAGG() should contain ABSENT ON NULL clause to get empty array.
        4. It should be a simple SELECT statement, operators like UNION, INTERSECT, EXCEPT and complex SELECT like CTEs aren't supported and must result into a syntax error if used.

      2. Requirements for FROM clause:

        1. Same as FR-6.2.2.2

FR-7: Requirements for table_annotations_expression:

  1. 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
    2. DELETE
    3. UPDATE

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

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

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

  5. 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-8: Support for column list:

  1. The CREATE OR REPLACE JSON DUALITY VIEW statement does not support column list, using column list in the statement should result in a syntax error.

    View column name data is automatically added for the JSON duality view.

1.2: Semantic Validation

FR-9: Base Table Requirement for Participating Tables:

  1. All participating tables, including the root_table_name and any tables referenced within nested_descendent_json_objects or singleton_descendent_json_object, must be base tables.

  2. Views, Duality views, temporary tables, and other non-base table types are not permitted and should result into a semantic error if used.

FR-10: Primary Key and its Projection Requirement:

  1. All participating tables, including the root_table_name and any tables referenced within nested_descendent_json_objects or singleton_descendent_json_object, must have a defined primary key.

  2. The projection must include the primary key of every participating table.

  3. If any participating table lacks a primary key, or if the primary key is omitted from the projection, a semantic error must be generated.

Note: Unique Key and Composite Primary Key are not supported for now. Supporting these keys is beyond this WL scope.

FR-11: Projection requirements for child tables:

Child tables being projected as part of JSON DUALITY VIEW can be related to parent tables in one of below 2 types:

  1. PK - FK Relationship (or Referential Integrity)
  2. PK - Any Column Relationship

1. PK-FK Relationship (or Referential Integrity):

  1. Singleton descendent JSON Object Projection (singleton_descendent_json_object): When a child table is projected as singleton_descendent_json_object, the WHERE clause must enforce the following format:

    child_table.PK = parent_table.FK

    This ensures that the child_table is the foreign key (FK) parent of the parent_table, creating a sigleton (i.e. 1:1) relationship. A semantic error must be reported if this format is not present, indicating that the WHERE clause does not maintain the required referential integrity.

  2. Nested descendent JSON Object Projection (nested_descendent_json_objects): When a child table is projected as nested_descendent_json_objects, the WHERE clause must enforce the following format:

    child_table.FK = parent_table.PK

    This ensures that the parent_table is the FK parent of the child_table, establishing a nested (i.e. 1:N) relationship. A semantic error must be reported if this format is not present, indicating that the WHERE clause does not maintain the required referential integrity.

    Note: N-1 and N:M relationships are modeled using the combination of aforementioned relationship types.

2. PK - Any column Relationship: In this type of relationship, child_table can be projected as singleton_descendent_json_object or nested_descedent_json_objects with this relationship.

For singleton_descendent_json_object representation, the WHERE clause must follow this format:

child_table.PK = parent_table.any_column

For nested_descedent_json_objects representation, the WHERE clause must follow this format:

child_table.any_column = parent_table.PK

FR-12: Compatiablity 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:

  1. parent_table is referenced by a child_table.

  2. child_table has its WHERE condition in the below form: 

    child_table.PK = parent_table.any_column

FR-13: Restrictions for creation options of JSON DUALITY VIEW:

  1. The ALGORITHM = TEMPTABLE option is not supported and must result into a semantic error if used.

  2. The [WITH [CASCADED | LOCAL] CHECK OPTION] clause is not supported and must report a semantic error if used.

2. Alter the JSON DUALITY VIEW definition:

FR-14: Existence Check for JSON Duality Views in the ALTER Statement:

  • During the execution of ALTER statement, if view_name does not exist or isn't a duality view, a syntax error should be generated.

FR-15: Restrictions on the ALTER Statement for Duality Views:

  1. The ALTER statement for JSON duality views does not support specifying a column list. For example, the following syntax is not allowed:

    ALTER JSON DUALITY VIEW v4(col_name) AS SELECT JSON_DUALITY_OBJECT("f1", t1.f1) FROM t1;

    Any attempts to modify columns using a column list must result in a syntax error.

  2. ALTER-ing a JSON DUALITY VIEW with a normal VIEW definition and vice versa is not supported, and must result into an error if attempted.

FR-16: Compliance of json_duality_select_statement with Defined Syntax and Semantic Rules:

  • The json_duality_select_statement must adhere to the syntax and semantic rules defined in FR-1 to FR-13.

3. Verify the JSON DUALITY VIEW definition:

FR-17: Requirements for SHOW CREATE TABLE/VIEW:

  1. The statement SHOW CREATE VIEW view_name must return the CREATE JSON DUALITY VIEW statement that was used to create the specified view.

  2. The output must contain table annotations specified in the duality view definition.

Note: It follows the same SHOW CREATE TABLE syntax and semantics.

FR-18: Requirements for INFORMATION_SCHEMA.VIEWS:

  1. The INFORMATION_SCHEMA.VIEWS must contain the details about duality views. i.e. A query like below should work:

    SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='table_schema_name' \G

4. Drop the JSON DUALITY VIEW:

FR-19: Requirements for DROP VIEW:

  1. If a duality view with the view_name is present, regardless of whether IF EXISTS clause is there or not, the statement DROP VIEW must drop the view.

  2. If duality view with the view_name is not present and IF EXISTS flag:

    1. is supplied, the statement must succeed with warning.
    2. is not supplied, the statement must report an error.

Note: It follows the same DROP VIEW syntax and semantics.

5. Duality views in mysqldump

FR-20: Requirements for mysqldump:

  1. The mysqldump tool must be able to recognise and dump the duality views definitions.

Note: The dump and restore utilities for JSON DUALITY VIEW DDLs follow the same behavior as normal VIEW DDLs.

6. Replication

FR-21: Requirements for Replication:

  1. The CREATE, ALTER, and DROP statements for duality views must be replicated.

Notes: Replication for JSON DUALITY VIEW DDLs follow the same behavior as normal VIEW DDLs. Below are some notes about behavior of 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 order must be followed. First, the replica servers should be upgraded to the version that supports JSON DUALITY VIEWS. After the replicas have been successfully upgraded, the source server can then be upgraded. This order is important to prevent the source from replicating statements that are not supported on the older version of the replica servers.

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. The behavior will be consistent with that of normal views. But if the source server is running the latest version that supports JSON DUALITY VIEWS while the replicas are operating on an older version, any DDL statements related to JSON DUALITY VIEWS will fail on the replicas. This is expected for any new feature introducing syntax level changes.

  • Any impact on upgrade/downgrade? Which?

    There is no impact on the upgrade or downgrade process; it will behave the same as normal views. When downgrading from a version that supports JSON DUALITY VIEWS to a version that does not, DDLs will any way fail on target version.

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

7. Content Tree

NFR-1 Requirements for Content Tree: A JSON duality view Content tree must be generated both during view creation and when a view is opened for DDL or DML operations.

Note: Content tree is essential for semantic validation, and therefore, it must be prepared during duality view syntax validation.

1. Introduction

Citing OracleDB Docs:

Duality views combine the advantages of using JSON documents with those of the relational model, while avoiding the limitations of each.

JSON RELATIONAL DUALITY VIEW or JSON DUALITY VIEW allows the same data to be accessed as relational tables or JSON documents, supporting both approaches simultaneously.

  • JSON Document: Self-contained, hierarchical, schema-flexible, but lacks built-in relationships between documents, leading to data duplication and potential inconsistencies.

  • Relational Model: Normalized, independent, consistent, and efficient for combining data. Built-in ACID properties ensure transactional reliability, consistency, and durability, but mapping objects to tables can hinder agile development.

Duality View Benefits:

  • Dual Representation: Data stored in relational tables is exposed as JSON documents, generated on demand. Applications can work with JSON documents via APIs or SQL/JSON functions, while analytics and reporting tools can access the same data relationally.

  • Flexibility: Different JSON hierarchies can be defined over shared table data, supporting agile development and schema changes.

  • Updatability: Changes to JSON documents or underlying relational tables are automatically reflected in the other representation. These updates are ACID-compliant.

JSON-relational duality views retain the ACID properties of relational databases, making them reliable for diverse use cases, from document-centric applications to table-centric analytics.

Example:

CREATE SCHEMA car_racing;
USE car_racing; 

CREATE TABLE team (
  team_id INT AUTO_INCREMENT PRIMARY KEY, 
  name VARCHAR(255), 
  points INT NOT NULL
);

CREATE TABLE driver (
    driver_id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255), 
    points INT NOT NULL, 
    team_id INT NULL, 
    FOREIGN KEY (team_id) REFERENCES team(team_id)
);

CREATE TABLE race (
    race_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    laps INT NOT NULL,
    race_date DATE
);

CREATE TABLE driver_race_map (
    driver_race_map_id INT AUTO_INCREMENT PRIMARY KEY,
    race_id INT NOT NULL,
    driver_id INT NOT NULL,
    position INT NOT NULL,
    CONSTRAINT driver_race_map_uk UNIQUE (race_id, driver_id),
    FOREIGN KEY (race_id) REFERENCES race(race_id),
    FOREIGN KEY (driver_id) REFERENCES driver(driver_id)
);

-- child table can be JSON_DUALITY_OBJECT i.e. key 'team' 
-- OR it can be JSON_ARRAYAGG i.e. key 'race_details'
-- It depends on what kind of relationship parent table
-- has with the child table. 
CREATE JSON DUALITY VIEW driver_dv AS 
SELECT 
JSON_DUALITY_OBJECT(
  '_id' :  driver.driver_id, 
  'name'      : driver.name, 
  'points'    : driver.points, 
  'team'      : (
      SELECT JSON_DUALITY_OBJECT(
          'team_id' : team.team_id, 
          'name'    : team.name, 
          'points'  : team.points
      )
      FROM team WHERE driver.team_id = team.team_id
  ), 
  'race_details': (
    SELECT JSON_ARRAYAGG(
        JSON_DUALITY_OBJECT(
            'driver_race_map_id' : driver_race_map.driver_race_map_id, 
            'position'           : driver_race_map.position, 
            'race'               : (
                SELECT JSON_DUALITY_OBJECT(
                    'race_id'   : race.race_id, 
                    'name'      : race.name, 
                    'laps'      : race.laps, 
                    'race_date' : race.race_date 
                )
                FROM race 
                WHERE driver_race_map.race_id = race.race_id
            )
        )
      )
      FROM driver_race_map 
      WHERE driver_race_map.driver_id = driver.driver_id
  )
)
FROM driver;
SHOW CREATE VIEW driver_dv;

2. Grammar

2.1 High Level structure of DDL statements

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

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

2.1.3 Verify the JSON DUAILTY VIEW definition:

The SHOW CREATE VIEW statement should follow below grammar:

show_create_view ::= 
SHOW CREATE VIEW view_name_expression

Follows the standard SHOW CREATE VIEW behavior.

2.1.4 Drop the JSON DUALITY VIEW:

The DROP VIEW statement should follow below grammar: 

drop_view ::= 
DROP VIEW [IF EXISTS]
view_name_expression [, view_name_expression] ...
[RESTRICT | CASCADE]

Follows the standard DROP VIEW behavior.

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

To represent JSON-like key-value pairs and accommodate the use case of specifying table tags, the JSON_DUALITY_OBJECT() utility function has been introduced. It is based on the original JSON_OBJECT() function but features a slightly different syntax. The syntax for the same (i.e. json_duality_object_expression) is outlined below: 

json_duality_object_expression ::= 
JSON_DUALITY_OBJECT( 
  [table_annotations_expression] json_duality_key_value_pairs 
)

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

table_annotations ::= {INSERT | UPDATE | 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, ALTER, SHOW CREATE and DROP 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 */

3. Appendix

3.1 Table Annotations

3.1.1 Read-Write Attributes

A table used in a JSON duality view can be designated as either updatable or read-only through the use of table annotations.

Read-write attributes for a table can be configured using the following annotations:

  • INSERT
  • UPDATE
  • DELETE

For now, combinations of these annotations which are supported are: All three i.e. (INSERT, UPDATE, DELETE) or None. In the later case, the duality view is considered read-only (The only exception being that DELETE tag is not supported in case of the PK sub-object).

For example:

CREATE JSON DUALITY VIEW dv1 AS
SELECT JSON_DUALITY_OBJECT(
           WITH (INSERT, UPDATE, DELETE) 
           ...
        ) 
FROM tab1;

Table annotations in the duality view syntax are supported as part of this WL. Read-write attributes are used during DML operations and this is out of the scope of this WL.

3.2 Content Tree

While creating or opening a view for DDL or DML operations, an in-memory representation "Content Tree" is constructed.

The Content Tree stores information about the root and child queries in a node-based representation. Each node encompasses the following information:

  • Table information:
    • Table name
    • Database name
    • Table annotations
  • JSON attribute to table column.
  • Relationship type:
    • Singleton
    • Nested
  • Join condition used in child node.
  • Parent node reference.
  • Child node references.

The Content Tree is used to:

  1. Validate the semantics of a duality view during both creation and DDL/DML operations.
  2. Internally support DML operations on duality views.
  3. Populate the INFORMATION_SCHEMA views related to duality views.

3.3 General notes

  1. In this WL content, terms child and parent mean child and parent table probjections (or objects) unless specified otherwise.

  2. PK stands for primary key.

  3. FK stands for foreign key.

  4. jdv stands for JSON DUALITY VIEW.

3.4. Future Work

  1. Table Tags: Table tags (i.e., table_annotations_expression) are applied to participating tables using the WITH clause. The following tags are currently not supported:

    • CHECK / NOCHECK
    • NOUPDATE
    • NODELETE
    • NOINSERT

  2. Column Tags: Column tags are applied to column projections of participating tables using the WITH clause. The following tags are currently not supported:

    • CHECK / NOCHECK
    • UPDATE / NOUPDATE
    • DELETE / NODELETE
    • INSERT / NOINSERT

  3. Complementary Tags: Ideally, the use of complementary tags (e.g., WITH UPDATE, NOUPDATE) should not be allowed. However, since NO[INSERT/UPDATE/DELETE] tags (at both table and column levels) are not supported, handling this scenario is currently beyond the scope of this Worklog (WL).

  4. NEST-ing/UNNEST-ing: Nesting of specific columns projections from a table at same level, or the UNNEST keyword to flatten column projections across different levels in the duality view, is not supported.

  5. JSON Columns: Projecting columns of the JSON type is not supported.

  6. Composite Primary Keys: The use of composite primary keys is not supported.

  7. Unique Key: Unique keys cannot be used as the sole keys in participating tables. While tables can have unique keys, they must also have a primary key. It is mandatory for every participating table to include a primary key.

  8. WHERE Clause in the Root Object: The use of the WHERE clause in the root table projection is not supported.

  9. ORDER BY Clause: The use of the ORDER BY clause with JSON_ARRAYAGG() or the root JSON_DUALITY_OBJECT() is not supported.

  10. WITH [CASCADED | LOCAL] CHECK OPTION Clause: The WITH [CASCADED | LOCAL] CHECK OPTION clause is not supported in CREATE and ALTER commands for duality views (i.e., create_json_duality_view and alter_json_duality_view).

  11. Creation of a Duality View Descendant Type (Singleton or Array) Regardless of Relationship Type: Oracle DB allows the creation of a singleton_descendent_json_object regardless of the relationship between the child and parent tables (e.g., even if the child table is a foreign key child of the parent table). This flexibility is not supported in MySQL.

  12. Expressions in Duality View Definition: The current version only supports projecting columns of participating tables. The use of expressions involving one or more columns is not supported.

  13. SELECT as a Prepared Statement: Prepared SELECT statements are not supported.



Copyright (c) 2000, 2026, Oracle Corporation and/or its affiliates. All rights reserved.