Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17399

Add support for JSON_TABLE

Details

    • Task
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.6.0
    • JSON
    • None

    Description

      MySQL 8.0 has JSON_TABLE. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

      1. Syntax
      		 
      1.1 Standard compliance
      		 
       
      		 
      2. JSON_TABLE as table function
      		 
      3. Implementing a table function
      		 
      3.1 DDL considerations
      		 
      3.2 Producing the rows
      		 
      3.3 Interfacing with the optimizer
      		 
      3.3.1 Estimates 
      3.3.2 JSON_TABLE must be after its dependencies
      		 
      3.3.3 Can't use Join buffering 
      5. Links

      1. Syntax

      SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

      The syntax to be implemented is:

        JSON_TABLE(
      		 
          json_text,
      		 
          path COLUMNS(columns_list) 
        ) [AS] alias


      columns_list:  
        column [, column[, ... ]]


      column:
      		 
          name FOR ORDINALITY 
        | name type PATH path_str [on_empty_on_error]
      		 
        | name type EXISTS PATH path_str [on_empty_on_error]
      		 
        | NESTED PATH path COLUMNS (columns_list)


      on_empty_on_error:
      		 
        [ behavior ON EMPTY ] [ behavior ON ERROR ]


      behavior:
      		 
         ERROR | NULL | DEFAULT <value_expression>

      Here, JSON_TABLE(...) is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

      1.1 Standard compliance

      The above is a subset of the syntax allowed by the SQL Standard, with exception of name type EXISTS PATH path_str which is a non-standard extension in (Oracle database and Oracle MySQL?)

      2. JSON_TABLE as table function

      JSON_TABLE is the first table function - a table whose contents depends on its arguments.

      The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

      3. Implementing a table function

      3.1 DDL considerations

      A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

      TODO: At which point should this temporary table be created? It is apparent that this should happen before any Item::fix_fields() calls for the ON/WHERE/etc clauses. It should also happen before Item::fix_fields() calls made for the first parameter of any JSON_TABLE(...).

      3.1.2 Name resolution for JSON_TABLE argument

      The name resolution context should be ... "the same as for the ON expression" ?

      3.2 Producing the rows

      In order to produce rows, a Storage Engine will be implemented, ha_json_table. That is, the temporary table will use the ha_json_table engine.
      (This could be generalized to a generic ha_table_function Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

      The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

      3.3 Interfacing with the optimizer

      3.3.1 Estimates

      The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

      3.3.2 JSON_TABLE must be after its dependencies

      Second, the join optimizer will need to be aware that
      JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
      This will be done as follows:
      Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

      3.3.3 Can't use Join buffering

      Join buffering will be disabled for table JSON_TABLE(...).

      4. Producing JSON_TABLE contents

      4.1 FOR ORDINALITY columns

      The standard says:

      An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.

      The numbering seems to be "global", that is, it doesn't restart across table re-reads.

      4.2 NESTED contents

      The code has a "primary" JSON parser which scans through the JSON document and locates nodes that represent the rows to be produced by JSON_TABLE.
      In order to locate contents of the NESTED construct, we create another instance of JSON parser, point it at the current node, and then let it locate the pointed by the path. It's obvious that this can handle as many NESTED constructs as needed, and the "primary" parser state is not affected by them.

      5. Links

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31616 Problems with a stored function EMPTY() on upgrade to 10.6

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          includes

          Task - A task that needs to be done. MDEV-23277 Make "for" sentence support JSON arrays

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          is blocked by

          Technical task - A technical task. MDEV-24385 JSON_TABLE review

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22288 JSON_TABLE: ORDINALITY column starts from 0 instead of 1

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22289 JSON_TABLE: Some types for regular columns are not supported by parser

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22290 JSON_TABLE: Decimal type with M equal D causes Assertion `scale <= precision' failure

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22291 JSON_TABLE: SELECT from json_table does not work without default database

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22293 JSON_TABLE: Values for regular columns are truncated for no reason

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22294 JSON_TABLE: Cannot create a view selecting from json_table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22295 JSON_TABLE: Server crashes in Select_limit_counters::get_select_limit upon json_table referencing unknown table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22296 JSON_TABLE: Ordinality is calculated incorrectly (counts all rows instead of rows at its level)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22297 JSON_TABLE: ON ERROR clause is used for empty values if ON EMPTY is not specified explicitly

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22298 JSON_TABLE: EXISTS PATH value is converted to other types incorrectly

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22299 JSON_TABLE: Assertion `!cmp(&table_ref->db, &table_ref->table->s->db) || (table_ref->schema_table && is_infoschema_db(&table_ref->table->s->db)) || table_ref->is_materialized_derived()' failed on NATURAL JOIN between JSON tables

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22300 JSON_TABLE: If value extracted for regular column is array, the first element is returned instead of error

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22301 JSON_TABLE: Queries are not inserted into query cache

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22302 JSON_TABLE: Column privilege is insufficient for query with json_table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25228 JSON_TABLE: Server crashes in Query_cache::unlink_table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25230 JSON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25254 JSON_TABLE: Inconsistent name resolution with right joins

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25255 JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25256 JSON_TABLE: Error ER_VIEW_INVALID upon running query via view

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25259 JSON_TABLE: Illegal mix of collations upon executing query with combination of charsets via view

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25352 JSON_TABLE: Inconsistent name resolution and ER_VIEW_INVALID upon combination of RIGHT and NATURAL JOIN

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25353 JSON_TABLE: Illegal mix of collations upon executing PS once, or SP/function twice

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25377 JSON_TABLE: Wrong value with implicit conversion

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25379 JSON_TABLE: ERROR ON clauses are ignored if a column is not on select list

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25380 JSON_TABLE: Assertion `join->best_read < double(1.797...) fails upon JSON_TABLE in subquery

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25381 JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25397 JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS upon query with JOIN

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25406 JSON_TABLE: VARBINARY column in view definition causes syntax error and assertion failure

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25408 JSON_TABLE: AddressSanitizer CHECK failed in Binary_string::realloc_raw

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25452 JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp=OFF

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25528 Document JSON_TABLE

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-25727 Add formatted column support to JSON_TABLE

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25881 JSON_TABLE can't handle an array properly

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-17397 Implement JSON functions available in MySQL 8.0 but not MariaDB

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25138 JSON_TABLE: A space between JSON_TABLE and opening bracket causes syntax error

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25139 JSON_TABLE: Non-descriptive ER_PARSE_ERROR error upon a missing alias

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25140 JSON_TABLE: Success of query execution depends on the outcome of previous queries

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25141 JSON_TABLE: SELECT into outfile bypasses file privilege check

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25142 JSON_TABLE: CREATE VIEW involving EXISTS PATH ends up with invalid frm

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25143 JSON_TABLE: Server crashes in handler::print_error / hton_name upon ERROR ON EMPTY

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25144 JSON_TABLE: Assertion `thd->is_error()' failed in convert_error_to_warning

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25145 JSON_TABLE: Assertion `fixed == 1' failed in Item_load_file::val_str on 2nd execution of PS

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25146 JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25149 JSON_TABLE: Inconsistency in implicit data type conversion

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25150 JSON_TABLE: Some data types cause parsing error: ENUM, SET, geometry, INET6

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25151 JSON_TABLE: Unexpectedly padded values in a PATH column

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25154 JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25155 JSON_TABLE: Status variable Feature_json is not incremented

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25178 JSON_TABLE: ASAN use-after-poison in my_fill_8bit / Json_table_column::On_response::respond

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25183 JSON_TABLE: CREATE VIEW involving NESTED PATH ends up with invalid frm

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25186 JSON_TABLE: ASAN global-buffer-overflow in my_strnncoll_binary upon inserting query with join into query cache

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25188 JSON_TABLE: ASAN use-after-poison in Field_long::reset / Table_function_json_table::setup or malloc(): invalid size

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25189 JSON_TABLE: Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' failed upon CREATE .. SELECT

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25192 JSON_TABLE: ASAN use-after-poison in field_conv_memcpy / Create_tmp_table::finalize upon query with derived table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25202 JSON_TABLE: Early table reference leads to unexpected result set, server crash in st_join_table::fix_splitting or failing Assertion `join->best_read < double(1.797...)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-25421 Documentation for JSON_TABLE for MariaDB Server 10.6

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26257 JSON_TABLE() does not return ER_JSON_TABLE_MULTIPLE_MATCHES when column path has multiple matches

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27898 CREATE VIEW AS SELECT involving JSON column requires root privileges

          • Major - Major loss of function.
          • Confirmed

          Activity

            Ascending order - Click to sort in descending order
            diego dupin Diego Dupin created issue -
            diego dupin Diego Dupin made changes -
            Field Original Value New Value
            ralf.gebhardt Ralf Gebhardt made changes -
            Component/s JSON [ 13908 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            NRE Projects RM_105_CANDIDATE
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.5 [ 23123 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Alexey Botchkov [ holyfoot ]
            holyfoot Alexey Botchkov made changes -
            Due Date 2019-09-02
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            holyfoot Alexey Botchkov made changes -
            Due Date 2019-09-02 2019-11-25
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            julien.fritsch Julien Fritsch made changes -
            Due Date 2019-11-25
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2, and PostgreSQL 12

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2, and PostgreSQL 12

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            holyfoot Alexey Botchkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.5 [ 23123 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            julien.fritsch Julien Fritsch made changes -
            Assignee Sergei Petrunia [ psergey ] Julien Fritsch [ julien.fritsch ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Julien Fritsch [ julien.fritsch ] Alexey Botchkov [ holyfoot ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            holyfoot Alexey Botchkov made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            holyfoot Alexey Botchkov made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            BTW many IS tables can be easily redone with this new handler
            so no need to fill the memory tables.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it is not in the 12th release?)

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            BTW many IS tables can be easily redone with this new handler
            so no need to fill the memory tables.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            h2. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            h2. JSON_TABLE as table function

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            BTW many IS tables can be easily redone with this new handler
            so no need to fill the memory tables.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            h2. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            h2. JSON_TABLE as table function

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            BTW many IS tables can be easily redone with this new handler
            so no need to fill the memory tables.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            h2. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. JSON_TABLE as table function

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            BTW many IS tables can be easily redone with this new handler
            so no need to fill the memory tables.
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            h2. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. JSON_TABLE as table function

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.

            h2. Other notes
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.


            To implement this we create the new table handler that returns
            the calculated rows. Could be ha_table_function, bug now it's
            the ha_json_table as we don't have other table functions yet.
            The JSON_TABLE creates the temporary table of that ha_json_table
            type. We need to set dep_tables properly so this table is read
            tables mentioned in JSON_TABLE arguments are read, and any
            caching is disallowed for this table.
            BTW many IS tables can be easily redone with this new handler
            so no need to fill the memory tables.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            h2. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents
            depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which
            causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. Implementing a table function

            h3. DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. Interfacing with the optimizer

            h4. Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            h2. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents
            depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which
            causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. Implementing a table function

            h3. DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. Interfacing with the optimizer

            h4. Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. Links
            * MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            4. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents
            depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which
            causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            4. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                path_expr,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents
            depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which
            causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            4. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents
            depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which
            causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            4. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents
            depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which
            causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str [on_empty] [on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty] [on_error]
              | name type EXISTS PATH path_str [on_empty] [on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}}
            engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            holyfoot Alexey Botchkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)
             
            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            1.1 Standard compliance

            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)

            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            serg Sergei Golubchik made changes -
            Rank Ranked higher
            julien.fritsch Julien Fritsch made changes -
            Assignee Sergei Petrunia [ psergey ] Julien Fritsch [ julien.fritsch ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Julien Fritsch [ julien.fritsch ] Alexey Botchkov [ holyfoot ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            1.1 Standard compliance

            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)

            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            1.1 Standard compliance

            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)

            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h4 3.1.1 Steps

            TODO: How about references to columns of JSON_TABLE(...) which are not yet created? It possible?

            h4. 3.1.2

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            psergei Sergei Petrunia made changes -
            Description MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            1.1 Standard compliance

            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)

            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            h4 3.1.1 Steps

            TODO: How about references to columns of JSON_TABLE(...) which are not yet created? It possible?

            h4. 3.1.2

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.             		MySQL 8.0 has [JSON_TABLE|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]. These is also available in Oracle, DB2. (PostgreSQL has a patch under development but it seems it is not in the 12th or 13th release)

            {code}
            1. Syntax
            1.1 Standard compliance

            2. JSON_TABLE as table function
            3. Implementing a table function
            3.1 DDL considerations
            3.2 Producing the rows
            3.3 Interfacing with the optimizer
            3.3.1 Estimates
            3.3.2 JSON_TABLE must be after its dependencies
            3.3.3 Can't use Join buffering
            5. Links
            {code}

            h2. 1. Syntax

            SQL Standard defines a lot of features for JSON_TABLE. This task will implement a practically-important subset (roughly the same what MySQL-8 supports)

            The syntax to be implemented is:

            {code:sql}
              JSON_TABLE(
                json_text,
                path COLUMNS(columns_list)
              ) [AS] alias
            {code}

            {code:sql}
            columns_list:
              column [, column[, ... ]]
            {code}

            {code:sql}
            column:
                name FOR ORDINALITY
              | name type PATH path_str [on_empty_on_error]
              | name type EXISTS PATH path_str [on_empty_on_error]
              | NESTED PATH path COLUMNS (columns_list)
            {code}

            {code}
            on_empty_on_error:
              [ behavior ON EMPTY ] [ behavior ON ERROR ]
            {code}

            {code}
            behavior:
               ERROR | NULL | DEFAULT <value_expression>
            {code}

            Here, {{JSON_TABLE(...)}} is a table function and is allowed wherever a table reference is allowed, except for the context which imply that the referred table is to be modified.

            h3. 1.1 Standard compliance
            The above is a subset of the syntax allowed by the SQL Standard, with exception of {{name type EXISTS PATH path_str}} which is a non-standard extension in (Oracle database and Oracle MySQL?)

            h2. 2. JSON_TABLE as table function
            JSON_TABLE is the first *table function* - a table whose contents depends on its arguments.

            The arguments may be non-constant (e.g. columns of other tables), which causes the JSON_TABLE table to have LATERAL-like dependencies on its arguments.

            h2. 3. Implementing a table function

            h3. 3.1 DDL considerations

            A temporary table [definition] will be created with appropriate set of columns with appropriate attributes. This will allow for references to the columns of JSON_TABLE(...) to be resolved using the regular name resolution process.

            TODO: At which point should this temporary table be created? It is apparent that this should happen before any Item::fix_fields() calls for the ON/WHERE/etc clauses. It should also happen before Item::fix_fields() calls made for the first parameter of any JSON_TABLE(...).

            h4. 3.1.2 Name resolution for JSON_TABLE argument

            The name resolution context should be ... "the same as for the ON expression" ?

            h3. 3.2 Producing the rows

            In order to produce rows, a Storage Engine will be implemented, {{ha_json_table}}. That is, the temporary table will use the {{ha_json_table}} engine.
            (This could be generalized to a generic {{ha_table_function}} Storage Engine for table function, but it was decided not to do that until we have other examples of table functions).

            The engine will only support full table scans (rnd_init/rnd_next). rnd_init() call will implicitly evaluate the JSON_TABLE's parameters and set the storage engine to produce the rows that come from the parameters.

            h3. 3.3 Interfacing with the optimizer

            h4. 3.3.1 Estimates
            The optimizer will need to be aware that the table representing the output of JSON_TABLE(...) does not have any contents during the optimization phase. We will still need to provide some numbers for expected #rows and read_time. JSON documents are typically small, so a hard-coded constant describing a reasonably-sized JSON document would be sufficient.

            h4. 3.3.2 JSON_TABLE must be after its dependencies
            Second, the join optimizer will need to be aware that
            JSON_TABLE(tbl1.column_x, ... ) can only be accessed when the current row for table tbl1 is available.
            This will be done as follows:
            Table dependencies will be set such that JSON_TABLE(...) depends on tbl1.

            h4. 3.3.3 Can't use Join buffering
            Join buffering will be disabled for table JSON_TABLE(...).

            h2. 4. Producing JSON_TABLE contents

            h3. 4.1 FOR ORDINALITY columns
            The standard says:
            {quote}
            An ordinality column provides a sequential numbering of rows. Row numbering is 1-based.
            {quote}
            The numbering seems to be "global", that is, it doesn't restart across table re-reads.

            h3. 4.2 NESTED contents

            The code has a "primary" JSON parser which scans through the JSON document and locates nodes that represent the rows to be produced by JSON_TABLE.
            In order to locate contents of the NESTED construct, we create another instance of JSON parser, point it at the current node, and then let it locate the pointed by the path. It's obvious that this can handle as many NESTED constructs as needed, and the "primary" parser state is not affected by them.

            h2. 5. Links
            * JSON_TABLE in MySQL-8: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
            * SQL Standard: This page https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
            has a link to http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
            which in section 5.3.4 has a specification for JSON_TABLE function.
            * SQL:2016, as well as SQL Standard drafts from as early as 2014-11-10 cover JSON_TABLE.
            * PostgreSQL were working on this, see e.g. https://pgconf.ru/media/2019/02/20/postgresql-12-pgconfru-2019-v2.pdf , but currently they don't seem to have this.
            julien.fritsch Julien Fritsch made changes -
            julien.fritsch Julien Fritsch made changes -
            Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Alexey Botchkov [ holyfoot ] Julien Fritsch [ julien.fritsch ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Julien Fritsch [ julien.fritsch ] Alexey Botchkov [ holyfoot ]
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Alexey Botchkov [ holyfoot ] Elena Stepanova [ elenst ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            psergei Sergei Petrunia made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.6.0 [ 24431 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            greenman Ian Gilfillan made changes -
            markus makela markus makela made changes -
            deepak deepak made changes -
            GeoffMontee Geoff Montee (Inactive) made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 89978 ] MariaDB v4 [ 133710 ]
            alice Alice Sherepa made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Epic Link MDEV-17397 [ 70163 ]
            bar Alexander Barkov made changes -

            People

              psergei Sergei Petrunia
              diego dupin Diego Dupin
              Votes:
              31 Vote for this issue
              Watchers:
              32 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.