[MDEV-17399] Add support for JSON_TABLE - Jira

Diego Dupin created issue - 2018-10-08 20:14

Diego Dupin made changes - 2018-10-08 20:14

Field	Original Value	New Value
Link		This issue is part of MDEV-17397 [ MDEV-17397 ]

Ralf Gebhardt made changes - 2019-02-24 21:22

Component/s

JSON [ 13908 ]

Ralf Gebhardt made changes - 2019-03-19 13:41

NRE Projects

RM_105_CANDIDATE

Francisco Dueñas added a comment - 2019-05-15 16:03

This function will be great to implement

Francisco Dueñas added a comment - 2019-05-15 16:03 This function will be great to implement

Ralf Gebhardt made changes - 2019-07-10 14:46

Fix Version/s

10.5 [ 23123 ]

Ralf Gebhardt made changes - 2019-07-10 14:46

Assignee

Alexey Botchkov [ holyfoot ]

Alexey Botchkov made changes - 2019-07-28 12:38

Due Date

2019-09-02

Sergei Golubchik made changes - 2019-08-09 07:40

Link

This issue relates to MDEV-17397 [ MDEV-17397 ]

Sergei Golubchik made changes - 2019-08-09 07:40

Link

This issue is part of MDEV-17397 [ MDEV-17397 ]

Ralf Gebhardt made changes - 2019-08-09 07:57

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2019-08-09 08:08

Priority

Critical [ 2 ]

Major [ 3 ]

Alexey Botchkov made changes - 2019-10-03 12:09

Due Date

2019-09-02

2019-11-25

Julien Fritsch made changes - 2019-10-28 14:02

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2019-11-25 15:05

Due Date

2019-11-25

Alexey Botchkov added a comment - 2019-12-09 13:58

The proper implementation for this task requires adding the new useful thing to MariaDB - table functions. So we'll be able to have functional expressions after the FROM clause.
To implement this we decided to create the new specific table handler - ha_table_function, that provides the typical HANDLER fucntions so the rest of SELECT implementations will work as usual. Then particular table fucntion implementations can iherit from this base class.
That way we don't have to create any temporary table with the function result, which would seriously slow the execution.
Otherwise it's obvious enough how the calculations for particular rows of the JSON_TABLE function should work.

Alexey Botchkov added a comment - 2019-12-09 13:58 The proper implementation for this task requires adding the new useful thing to MariaDB - table functions. So we'll be able to have functional expressions after the FROM clause. To implement this we decided to create the new specific table handler - ha_table_function, that provides the typical HANDLER fucntions so the rest of SELECT implementations will work as usual. Then particular table fucntion implementations can iherit from this base class. That way we don't have to create any temporary table with the function result, which would seriously slow the execution. Otherwise it's obvious enough how the calculations for particular rows of the JSON_TABLE function should work.

Sergei Petrunia made changes - 2019-12-10 12:17

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.

Sergei Petrunia made changes - 2019-12-10 12:33

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.

Sergei Petrunia added a comment - 2019-12-11 14:04

Take-aways from discussion:

A practically-important use case is when JSON_TABLE has arguments that depend on
another table:

select ...

from

t1,

  JSON_TABLE(t1.json_field, /* arguments describing which fields to take */)

where

...

This puts some restrictions on the query plans:

JSON_TABLE(...) may not precede t1 in the join order
- (corollary) outer joins with references from outer side to inner are not
allowed : have t1 RIGHT JOIN JSON_TABLE(t1.col) ON ...
One can't use join buffering for the JSON_TABLE(...) table (at least, without some extra arrangements)

The property also affects the SE API:

when starting a scan, ha_table_function() must get the values of its parameters (either explicitly or implicitly).

Sergei Petrunia added a comment - 2019-12-11 14:04 Take-aways from discussion: A practically-important use case is when JSON_TABLE has arguments that depend on another table: select ... from t1, JSON_TABLE(t1.json_field, /* arguments describing which fields to take */ ) where ... This puts some restrictions on the query plans: JSON_TABLE(...) may not precede t1 in the join order - (corollary) outer joins with references from outer side to inner are not allowed : have t1 RIGHT JOIN JSON_TABLE(t1.col) ON ... One can't use join buffering for the JSON_TABLE(...) table (at least, without some extra arrangements) The property also affects the SE API: when starting a scan, ha_table_function() must get the values of its parameters (either explicitly or implicitly).

Sergei Petrunia added a comment - 2019-12-11 14:11

On limitations:
The SQL Standard defines A LOT of features for JSON_TABLE. MySQL implements a proper subset of those. It looks like PostgreSQL are also targeting a subset. It should be fine for us to implement a subset, also.

Sergei Petrunia added a comment - 2019-12-11 14:11 On limitations: The SQL Standard defines A LOT of features for JSON_TABLE. MySQL implements a proper subset of those. It looks like PostgreSQL are also targeting a subset. It should be fine for us to implement a subset, also.

Sergei Petrunia added a comment - 2019-12-11 14:44

Another observation: MySQL seems to A) use a temporary table and B) support "ref access" on it:

mysql> explain SELECT * FROM  JSON_TABLE('[ {"a":111}, {"a":333},{"a":555},{"a":777}]', '$[*]' COLUMNS (a INT PATH '$.a')) AS tt where a=4\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tt

   partitions: NULL

         type: ref

possible_keys: <auto_key0>

          key: <auto_key0>

      key_len: 5

          ref: const

         rows: 1

     filtered: 100.00

        Extra: Table function: json_table; Using temporary; Using index

1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM  JSON_TABLE('[ {"a":111}, {"a":333},{"a":555},{"a":777}]', '$[*]' COLUMNS (a INT PATH '$.a')) AS tt where a<4\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tt

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2

     filtered: 50.00

        Extra: Table function: json_table; Using temporary; Using where

1 row in set, 1 warning (0.00 sec)

I'm not sure how big is the benefit from ref access here (they have to parse the whole JSON text anyway... after that, they can determine that a given row is not a match faster... but still they'll need to read all of the JSON data?)

Sergei Petrunia added a comment - 2019-12-11 14:44 Another observation: MySQL seems to A) use a temporary table and B) support "ref access" on it: mysql> explain SELECT * FROM JSON_TABLE('[ {"a":111}, {"a":333},{"a":555},{"a":777}]', '$[*]' COLUMNS (a INT PATH '$.a')) AS tt where a=4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tt partitions: NULL type: ref possible_keys: <auto_key0> key: <auto_key0> key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Table function: json_table; Using temporary; Using index 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT * FROM JSON_TABLE('[ {"a":111}, {"a":333},{"a":555},{"a":777}]', '$[*]' COLUMNS (a INT PATH '$.a')) AS tt where a<4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tt partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 50.00 Extra: Table function: json_table; Using temporary; Using where 1 row in set, 1 warning (0.00 sec) I'm not sure how big is the benefit from ref access here (they have to parse the whole JSON text anyway... after that, they can determine that a given row is not a match faster... but still they'll need to read all of the JSON data?)

Alexey Botchkov made changes - 2019-12-17 09:24

Status

Open [ 1 ]

In Progress [ 3 ]

Ralf Gebhardt made changes - 2020-03-24 10:13

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.5 [ 23123 ]

Ralf Gebhardt made changes - 2020-03-27 10:57

Link

This issue relates to MENT-659 [ MENT-659 ]

Alexey Botchkov added a comment - 2020-03-29 21:53

https://github.com/MariaDB/server/commit/654fdfee33e3eafe3b7f25d7e213717c22ea1e18

Alexey Botchkov added a comment - 2020-03-29 21:53 https://github.com/MariaDB/server/commit/654fdfee33e3eafe3b7f25d7e213717c22ea1e18

Alexey Botchkov made changes - 2020-03-29 21:53

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Petrunia added a comment - 2020-04-03 14:50

The patch implements this syntax:

  JSON_TABLE(

    expr,

    path COLUMNS(columns_list)

  ) AS alias

#note "AS alias" while in other dbs it's just 'alias'?

columns_list:

  column [, column[, ... ]]

column:

    name FOR ORDINALITY

  | name type PATH path_str [on_empty] [on_error]

  | name type EXISTS PATH path_str

  | NESTED PATH path_str

  | columns

Note: NESTED PATH definition is different from MySQL one.

Type is a type definition like in the table DDL.

type: numeric | temporal | string

Sergei Petrunia added a comment - 2020-04-03 14:50 The patch implements this syntax: JSON_TABLE( expr, path COLUMNS(columns_list) ) AS alias #note "AS alias" while in other dbs it's just 'alias'? columns_list: column [, column [, ... ]] column : name FOR ORDINALITY | name type PATH path_str [on_empty] [on_error] | name type EXISTS PATH path_str | NESTED PATH path_str | columns Note: NESTED PATH definition is different from MySQL one. Type is a type definition like in the table DDL. type: numeric | temporal | string

Sergei Petrunia made changes - 2020-04-03 14:58

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.

Sergei Petrunia added a comment - 2020-04-03 19:02

Review for the patch: https://lists.launchpad.net/maria-developers/msg12162.html

Sergei Petrunia added a comment - 2020-04-03 19:02 Review for the patch: https://lists.launchpad.net/maria-developers/msg12162.html

Julien Fritsch made changes - 2020-04-10 10:37

Assignee

Sergei Petrunia [ psergey ]

Julien Fritsch [ julien.fritsch ]

Julien Fritsch made changes - 2020-04-10 10:38

Assignee	Julien Fritsch [ julien.fritsch ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Petrunia added a comment - 2020-04-12 08:19

Second part of the review: https://lists.launchpad.net/maria-developers/msg12172.html

Sergei Petrunia added a comment - 2020-04-12 08:19 Second part of the review: https://lists.launchpad.net/maria-developers/msg12172.html

Alexey Botchkov made changes - 2020-04-17 11:06

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.

Alexey Botchkov made changes - 2020-04-17 11:09

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.

Sergei Petrunia made changes - 2020-04-18 11:15

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.

Sergei Petrunia made changes - 2020-04-18 11:18

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.

Elena Stepanova made changes - 2020-04-18 13:20

Link

This issue relates to TODO-2305 [ TODO-2305 ]

Elena Stepanova made changes - 2020-04-18 14:36

Link

This issue relates to ~~MDEV-22288~~ [ ~~MDEV-22288~~ ]

Elena Stepanova made changes - 2020-04-18 14:50

Link

This issue relates to ~~MDEV-22289~~ [ ~~MDEV-22289~~ ]

Elena Stepanova made changes - 2020-04-18 15:02

Link

This issue relates to ~~MDEV-22290~~ [ ~~MDEV-22290~~ ]

Elena Stepanova made changes - 2020-04-18 15:08

Link

This issue relates to ~~MDEV-22291~~ [ ~~MDEV-22291~~ ]

Sergei Petrunia made changes - 2020-04-18 15:37

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.

Sergei Petrunia made changes - 2020-04-18 15:39

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.

Sergei Petrunia made changes - 2020-04-18 16:08

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.

Elena Stepanova made changes - 2020-04-18 16:15

Link

This issue relates to ~~MDEV-22293~~ [ ~~MDEV-22293~~ ]

Elena Stepanova made changes - 2020-04-18 16:36

Link

This issue relates to ~~MDEV-22294~~ [ ~~MDEV-22294~~ ]

Elena Stepanova made changes - 2020-04-18 17:06

Link

This issue relates to ~~MDEV-22295~~ [ ~~MDEV-22295~~ ]

Elena Stepanova made changes - 2020-04-18 18:58

Link

This issue relates to ~~MDEV-22296~~ [ ~~MDEV-22296~~ ]

Elena Stepanova made changes - 2020-04-18 19:38

Link

This issue relates to ~~MDEV-22297~~ [ ~~MDEV-22297~~ ]

Elena Stepanova made changes - 2020-04-18 19:49

Link

This issue relates to ~~MDEV-22298~~ [ ~~MDEV-22298~~ ]

Elena Stepanova made changes - 2020-04-18 22:52

Link

This issue relates to ~~MDEV-22299~~ [ ~~MDEV-22299~~ ]

Elena Stepanova made changes - 2020-04-18 23:44

Link

This issue relates to ~~MDEV-22300~~ [ ~~MDEV-22300~~ ]

Elena Stepanova made changes - 2020-04-18 23:53

Link

This issue relates to ~~MDEV-22301~~ [ ~~MDEV-22301~~ ]

Elena Stepanova made changes - 2020-04-19 00:34

Link

This issue relates to ~~MDEV-22302~~ [ ~~MDEV-22302~~ ]

Sergei Petrunia made changes - 2020-04-19 14:33

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.

Sergei Petrunia made changes - 2020-04-19 19:39

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.

Sergei Petrunia made changes - 2020-04-19 20:01

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.

Sergei Petrunia made changes - 2020-04-19 20:02

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.

Sergei Petrunia made changes - 2020-04-19 20:05

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.

Sergei Petrunia added a comment - 2020-05-11 15:54

Review part #4: https://lists.launchpad.net/maria-developers/msg12225.html

Sergei Petrunia added a comment - 2020-05-11 15:54 Review part #4: https://lists.launchpad.net/maria-developers/msg12225.html

Sergei Petrunia added a comment - 2020-05-11 15:58

Notes to self (for next review iteration)

we will need EXPLAIN to indicate that a table function is used.
JSON_TABLE inside a re-usable CTEs seems to work but I don't quite understand how

Sergei Petrunia added a comment - 2020-05-11 15:58 Notes to self (for next review iteration) we will need EXPLAIN to indicate that a table function is used. JSON_TABLE inside a re-usable CTEs seems to work but I don't quite understand how

Alexey Botchkov made changes - 2020-05-19 11:16

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Petrunia added a comment - 2020-05-26 14:58

Interesting, this query

select *

from

  json_table('[{"color": "blue", "price": 50},

               {"color": "red"}]',

             '$[*]' columns( price varchar(255) path '$.price' default 'aaa' on empty )) as T;

produces an error in MySQL 8:

ERROR 3141 (22032): Invalid JSON text in argument 1 to function JSON_TABLE: "Invalid value." at position 0.

But runs successfully in Oracle 18c: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=af4084b9b8abc04281bc5effa6d10249 .

It runs in MariaDB, too.

Sergei Petrunia added a comment - 2020-05-26 14:58 Interesting, this query select * from json_table( '[{"color": "blue", "price": 50}, {"color": "red"}]' , '$[*]' columns( price varchar (255) path '$.price' default 'aaa' on empty )) as T; produces an error in MySQL 8: ERROR 3141 (22032): Invalid JSON text in argument 1 to function JSON_TABLE: "Invalid value." at position 0. But runs successfully in Oracle 18c: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=af4084b9b8abc04281bc5effa6d10249 . It runs in MariaDB, too.

Sergei Petrunia added a comment - 2020-05-26 18:47

Another observation is that Oracle allows DEFAULT's value-expression to be, for example, integer:

select *

from

  json_table('[{"color": "blue", "price": 50},

               {"color": "red"}]',

             '$[*]' columns( price int path '$.price' default 12345 on empty )) as T;

while MySQL 8 does not.

I'm reading the part of the standard this MDEV links to, and it has this:

<JSON table column empty behavior> ::=

ERROR

| NULL

| DEFAULT <value expression>

but I don't see anything about what type <value expression> should have. Interesting that NULL is mentioned separately, this means <value expression> cannot be NULL?

Sergei Petrunia added a comment - 2020-05-26 18:47 Another observation is that Oracle allows DEFAULT's value-expression to be, for example, integer: select * from json_table( '[{"color": "blue", "price": 50}, {"color": "red"}]' , '$[*]' columns( price int path '$.price' default 12345 on empty )) as T; while MySQL 8 does not. I'm reading the part of the standard this MDEV links to, and it has this: <JSON table column empty behavior> ::= ERROR | NULL | DEFAULT <value expression> but I don't see anything about what type <value expression> should have. Interesting that NULL is mentioned separately, this means <value expression> cannot be NULL?

Sergei Petrunia added a comment - 2020-05-26 18:53 - edited

there's this:

Feature T826, “General value expression in ON ERROR or ON EMPTY clauses”
•
Without this feature, the user specified value expression in the ON ERROR clause or ON EMPTY
clause in JSON_VALUE or on a regular column definition in JSON_TABLE can only be a literal.

So,

Generally, these expressions dont have to be literal
We can impose a limitation that they are literals.
As far as I understand, "literal" includes string/integer/etc literals. So, the current patch imposes an additional limitation by supporting only string literals.

Sergei Petrunia added a comment - 2020-05-26 18:53 - edited there's this: Feature T826, “General value expression in ON ERROR or ON EMPTY clauses” • Without this feature, the user specified value expression in the ON ERROR clause or ON EMPTY clause in JSON_VALUE or on a regular column definition in JSON_TABLE can only be a literal. So, Generally, these expressions dont have to be literal We can impose a limitation that they are literals. As far as I understand, "literal" includes string/integer/etc literals. So, the current patch imposes an additional limitation by supporting only string literals.

Sergei Petrunia added a comment - 2020-05-29 10:19

Review input part #5 : https://www.mail-archive.com/maria-developers@lists.launchpad.net/msg11827.html

Sergei Petrunia added a comment - 2020-05-29 10:19 Review input part #5 : https://www.mail-archive.com/maria-developers@lists.launchpad.net/msg11827.html

Sergei Petrunia added a comment - 2020-06-22 14:16

Review input part #6: https://lists.launchpad.net/maria-developers/msg12295.html

Sergei Petrunia added a comment - 2020-06-22 14:16 Review input part #6: https://lists.launchpad.net/maria-developers/msg12295.html

Sergei Petrunia added a comment - 2020-06-22 14:16

Also, a question: https://lists.launchpad.net/maria-developers/msg12296.html

Sergei Petrunia added a comment - 2020-06-22 14:16 Also, a question: https://lists.launchpad.net/maria-developers/msg12296.html

Sergei Petrunia made changes - 2020-06-30 14:04

Assignee	Sergei Petrunia [ psergey ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexey Botchkov made changes - 2020-07-14 12:50

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexey Botchkov made changes - 2020-07-27 23:45

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Petrunia added a comment - 2020-07-30 19:50

Review input part #7: https://lists.launchpad.net/maria-developers/msg12334.html
Review input part #8: https://lists.launchpad.net/maria-developers/msg12336.html

Sergei Petrunia added a comment - 2020-07-30 19:50 Review input part #7: https://lists.launchpad.net/maria-developers/msg12334.html Review input part #8: https://lists.launchpad.net/maria-developers/msg12336.html

Sergei Petrunia added a comment - 2020-08-03 15:06

Review input part #9: https://lists.launchpad.net/maria-developers/msg12344.html

Sergei Petrunia added a comment - 2020-08-03 15:06 Review input part #9: https://lists.launchpad.net/maria-developers/msg12344.html

Sergei Petrunia made changes - 2020-08-05 20:12

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.

Sergei Petrunia made changes - 2020-08-05 20:14

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.

Sergei Petrunia added a comment - 2020-08-05 20:27

Followup to optimizer call on Tuesday:

SQL Standard, section 7.6 <table reference>:

<table primary> ::=

<table or query name>

[ <query system time period specification> ]

[ <correlation or recognition> ]

| <derived table> <correlation or recognition>

| <lateral derived table> <correlation or recognition>

| <collection derived table> <correlation or recognition>

| <table function derived table> <correlation or recognition>

| <only spec> [ <correlation or recognition> ]

| <data change delta table> [ <correlation or recognition> ]

| <JSON table> <correlation or recognition>

| <JSON table primitive> <correlation name>

| <parenthesized joined table>

<table function derived table> ::=

TABLE <left paren> <collection value expression> <right paren>

Section 7.11 <JSON table>:

<JSON table> ::=

  JSON_TABLE <left paren> ....... <right paren>

That is, JSON_TABLE and TABLE (...) are two different productions that have nothing in common.

Sergei Petrunia added a comment - 2020-08-05 20:27 Followup to optimizer call on Tuesday: SQL Standard, section 7.6 <table reference>: <table primary> ::= <table or query name> [ <query system time period specification> ] [ <correlation or recognition> ] | <derived table> <correlation or recognition> | <lateral derived table> <correlation or recognition> | <collection derived table> <correlation or recognition> | <table function derived table> <correlation or recognition> | <only spec> [ <correlation or recognition> ] | <data change delta table> [ <correlation or recognition> ] | <JSON table> <correlation or recognition> | <JSON table primitive> <correlation name> | <parenthesized joined table> <table function derived table> ::= TABLE <left paren> <collection value expression> <right paren> Section 7.11 <JSON table>: <JSON table> ::= JSON_TABLE <left paren> ....... <right paren> That is, JSON_TABLE and TABLE (...) are two different productions that have nothing in common.

Sergei Golubchik made changes - 2020-08-16 20:00

Rank

Ranked higher

Julien Fritsch made changes - 2020-09-11 15:31

Assignee

Sergei Petrunia [ psergey ]

Julien Fritsch [ julien.fritsch ]

Julien Fritsch made changes - 2020-09-11 15:32

Assignee	Julien Fritsch [ julien.fritsch ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2020-09-14 12:56

Link

This issue includes ~~MDEV-23277~~ [ ~~MDEV-23277~~ ]

Robert Dyas added a comment - 2020-09-22 19:49 - edited

I would really like to see this implemented with json_text being able to be an S3 bucket reference as follows:

JSON_TABLE(

    json_text | 's3://my-bucket/path',

    path COLUMNS(columns_list)

  ) [AS] alias

This would make it super easy to stage lots of large json log files in an S3 bucket and then injest them into column store with a command like CREATE TABLE blah AS SELECT * FROM JSON_TABLE('s3://my-bucket/path', ....).

Robert Dyas added a comment - 2020-09-22 19:49 - edited I would really like to see this implemented with json_text being able to be an S3 bucket reference as follows: JSON_TABLE( json_text | 's3://my-bucket/path' , path COLUMNS(columns_list) ) [ AS ] alias This would make it super easy to stage lots of large json log files in an S3 bucket and then injest them into column store with a command like CREATE TABLE blah AS SELECT * FROM JSON_TABLE('s3://my-bucket/path', ....).

Robert Dyas added a comment - 2020-09-22 20:04

Also, while we are at it, it would be very nice to have a CSV_TABLE function with similar syntax such that

CREATE TABLE t1 AS SELECT * FROM CSV_TABLE('s3://bucket/mycsvfile.csv', ...)

With the path COLUMNS(columns_list) argument taking a list of columns in the CSV file. Great way of injecting large CSV files from S3 into column store.

Robert Dyas added a comment - 2020-09-22 20:04 Also, while we are at it, it would be very nice to have a CSV_TABLE function with similar syntax such that CREATE TABLE t1 AS SELECT * FROM CSV_TABLE('s3://bucket/mycsvfile.csv', ...) With the path COLUMNS(columns_list) argument taking a list of columns in the CSV file. Great way of injecting large CSV files from S3 into column store.

Francisco Dueñas added a comment - 2020-09-23 02:18

The functionality to get data from a CSV file already exists using CONNECT engine.
Althought it is not as dynamic as JSON_TABLE (or CSV_TABLE), you can create a Stored Procedure to read data from a CSV file by creating a virtual table that reads CSV data.

Check the CONNECT engine documentation for more info.

Francisco Dueñas added a comment - 2020-09-23 02:18 The functionality to get data from a CSV file already exists using CONNECT engine. Althought it is not as dynamic as JSON_TABLE (or CSV_TABLE), you can create a Stored Procedure to read data from a CSV file by creating a virtual table that reads CSV data. Check the CONNECT engine documentation for more info.

Robert Dyas added a comment - 2020-09-23 02:40

from an s3 bucket efficiently ? lots of experience with CONNECT engine and
performance is not its strength. this needs to be efficient for ingesting
LOTS of data from s3 into columnstore.

On Tue, Sep 22, 2020, 10:19 PM Francisco Dueñas (Jira) <jira@mariadb.org>

Robert Dyas added a comment - 2020-09-23 02:40 from an s3 bucket efficiently ? lots of experience with CONNECT engine and performance is not its strength. this needs to be efficient for ingesting LOTS of data from s3 into columnstore. On Tue, Sep 22, 2020, 10:19 PM Francisco Dueñas (Jira) <jira@mariadb.org>

Alexey Botchkov added a comment - 2020-09-27 13:13

https://github.com/MariaDB/server/commit/fbcd1908ebb9278e2a073341c18f4e896fda2e1d

Alexey Botchkov added a comment - 2020-09-27 13:13 https://github.com/MariaDB/server/commit/fbcd1908ebb9278e2a073341c18f4e896fda2e1d

Alexey Botchkov made changes - 2020-09-27 13:13

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Justin Swanhart added a comment - 2020-10-29 16:19

If table functions are going to be added, making the interface generic such that UDF table functions and/or using stored routines as table functions (since they can return resultsets) would be really nice. Perhaps that should be a prequisite MDEV for this feature?

Justin Swanhart added a comment - 2020-10-29 16:19 If table functions are going to be added, making the interface generic such that UDF table functions and/or using stored routines as table functions (since they can return resultsets) would be really nice. Perhaps that should be a prequisite MDEV for this feature?

Sergei Petrunia added a comment - 2020-11-15 13:19

greenlion, one problem with developing generic interfaces is answering the question what should be supported. Taking Stored Routines as an example - we won't be able have an "opaque" table function that calls SPs in it, due to the process called "prelocking".

The big decision that this MDEV code has made is that the table function also uses a table handler to provide rows. This allows for interesting opportunities like pushing conditions down into table functions, and/or table functions' tables providing indexes.

Compare that to MySQL's implementation - they just fill a temporary table and then read from there (an example stack trace : https://gist.github.com/spetrunia/1c38564a7ead92562fd984cbf48c0655) . OTOH, using a temporary may make some implementation simpler - one doesn't need to support ::position or ::rnd_pos.)

Sergei Petrunia added a comment - 2020-11-15 13:19 greenlion , one problem with developing generic interfaces is answering the question what should be supported. Taking Stored Routines as an example - we won't be able have an "opaque" table function that calls SPs in it, due to the process called "prelocking". The big decision that this MDEV code has made is that the table function also uses a table handler to provide rows. This allows for interesting opportunities like pushing conditions down into table functions, and/or table functions' tables providing indexes. Compare that to MySQL's implementation - they just fill a temporary table and then read from there (an example stack trace : https://gist.github.com/spetrunia/1c38564a7ead92562fd984cbf48c0655 ) . OTOH, using a temporary may make some implementation simpler - one doesn't need to support ::position or ::rnd_pos.)

Sergei Petrunia added a comment - 2020-11-15 13:27

On some previous optimizer call, I was asked if MySQL's way of making Table Function's columns available for Name Resolution is any different from MariaDB's.

Did some debugging and the answer is NO. Both use the same approach: create a temporary table (incl. a TABLE object), and then the TABLE_LIST representing the table function has table_list->table pointing to the created table, which allows Name Resolution functions to work as usual. A Table Function call is just another base table for it.

Sergei Petrunia added a comment - 2020-11-15 13:27 On some previous optimizer call, I was asked if MySQL's way of making Table Function's columns available for Name Resolution is any different from MariaDB's. Did some debugging and the answer is NO. Both use the same approach: create a temporary table (incl. a TABLE object), and then the TABLE_LIST representing the table function has table_list->table pointing to the created table, which allows Name Resolution functions to work as usual. A Table Function call is just another base table for it.

Justin Swanhart added a comment - 2020-11-15 23:26 - edited

@sergeyp

This is just a suggestion. I think a stored proc interface could be created like:
This is pseudo code where TABLE_FUNC(X, Y, Z) is the stored table function

-- creates a temporary table and returns the fully qualified temporary table name that stored table function results will be stored in

create stored function table_func_init() RETURNS string

  -- mariadb could provide a optional function GENERATE_TMP_TABLE_NAME() that generates random table names

  set @tmp_table_name = GENERATE_TMP_TABLE_NAME();

  prepare stmt from concat('create temporary table ', @tmp_table_name, ' ( ....)');;

  exec stmt;

  deallocate prepare stmt;

  return temporary_table_name;

end;;

  -- clears the table function temporary table before each call [for each row input to the function]

create stored function table_func_clear(TABLE_NAME)

  -- truncate or delete the contents of the temporary table

end;;

create stored function table_func_deinit(TABLE_NAME)

 -- drop the temporary table

end;;

create stored function table_func(table_name, x, y, z)

  -- populate table_name with results for x, y, z params

end;

Then for select * from TABLE_FUNC(x,y,z) , some_table the query execution engine does:

table_func_init is called at parse and returns temp table name

for each row

  table_func_clear(...) -- removes rows from temp table

  table_func(x,y,z) -- populates temp table

  -- iterate temporary_table with rnd_init, rnd_next, rnd_end

end for each row;;

table_func_deinit(TABLE_NAME) -- cleanup

  set @drop_stmt = concat('drop temporary table ', TABLE_NAME);

end;;

  prepare stmt from @drop_stmt

Just a thought, but that seems like a reasonably flexible interface that will support arbitrary table functions.}}

as for "prelocking" any tables used in the init func, clear func, or the func itself, as well as the temporary table being used, can be locked just like normal stored functions. all the table names are known at parse time in this case... I think that satisfies the requirements for prelocking. Please correct me if I'm wrong.

Justin Swanhart added a comment - 2020-11-15 23:26 - edited @sergeyp This is just a suggestion. I think a stored proc interface could be created like: This is pseudo code where TABLE_FUNC(X, Y, Z) is the stored table function -- creates a temporary table and returns the fully qualified temporary table name that stored table function results will be stored in create stored function table_func_init() RETURNS string -- mariadb could provide a optional function GENERATE_TMP_TABLE_NAME() that generates random table names set @tmp_table_name = GENERATE_TMP_TABLE_NAME(); prepare stmt from concat('create temporary table ', @tmp_table_name, ' ( ....)');; exec stmt; deallocate prepare stmt; return temporary_table_name; end;; -- clears the table function temporary table before each call [for each row input to the function] create stored function table_func_clear(TABLE_NAME) -- truncate or delete the contents of the temporary table end;; create stored function table_func_deinit(TABLE_NAME) -- drop the temporary table end;; create stored function table_func(table_name, x, y, z) -- populate table_name with results for x, y, z params end; Then for select * from TABLE_FUNC(x,y,z) , some_table the query execution engine does: table_func_init is called at parse and returns temp table name for each row table_func_clear(...) -- removes rows from temp table table_func(x,y,z) -- populates temp table -- iterate temporary_table with rnd_init, rnd_next, rnd_end end for each row;; table_func_deinit(TABLE_NAME) -- cleanup set @drop_stmt = concat('drop temporary table ', TABLE_NAME); end;; prepare stmt from @drop_stmt Just a thought, but that seems like a reasonably flexible interface that will support arbitrary table functions.}} as for "prelocking" any tables used in the init func, clear func, or the func itself, as well as the temporary table being used, can be locked just like normal stored functions. all the table names are known at parse time in this case... I think that satisfies the requirements for prelocking. Please correct me if I'm wrong.

Sergei Petrunia added a comment - 2020-11-16 11:10

holyfoot, review input: https://lists.launchpad.net/maria-developers/msg12465.html
https://lists.launchpad.net/maria-developers/msg12466.html

Sergei Petrunia added a comment - 2020-11-16 11:10 holyfoot , review input: https://lists.launchpad.net/maria-developers/msg12465.html https://lists.launchpad.net/maria-developers/msg12466.html

Sergei Petrunia added a comment - 2020-11-18 16:25

More: https://lists.launchpad.net/maria-developers/msg12467.html

Sergei Petrunia added a comment - 2020-11-18 16:25 More: https://lists.launchpad.net/maria-developers/msg12467.html

Sergei Petrunia made changes - 2020-11-18 16:48

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.

Sergei Petrunia made changes - 2020-11-18 16:58

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.

Sergei Petrunia added a comment - 2020-11-18 18:37 - edited

Interesting, MySQL has this limitation: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

expr: This is an expression that returns JSON data. This can be a constant ('{"a":1}'), a column (t1.json_data, given table t1 specified prior to JSON_TABLE() in the FROM clause) ...

This frees them from the need to detect circular dependencies:

one cannot construct a loop of JSON_TABLE referring to each other
one cannot construct a JSON_TABLE dependency contradicting LEFT JOIN , STRAIGHT JOIN or RIGHT JOIN dependencies ( for RIGHT JOIN, the left and right sides are swapped at the parser level).

I cannot find any mention of such limitation in the SQL Standard, though.

AFAIU, something like this could be supported:

create table t11 (a varchar(10));

select * from

  json_table(t11.a, '$[*]' COLUMNS (id INT PATH '$')) AS OT1,

t11

but it is not.

Sergei Petrunia added a comment - 2020-11-18 18:37 - edited Interesting, MySQL has this limitation: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html expr: This is an expression that returns JSON data. This can be a constant ('{"a":1}'), a column (t1.json_data, given table t1 specified prior to JSON_TABLE() in the FROM clause ) ... This frees them from the need to detect circular dependencies: one cannot construct a loop of JSON_TABLE referring to each other one cannot construct a JSON_TABLE dependency contradicting LEFT JOIN , STRAIGHT JOIN or RIGHT JOIN dependencies ( for RIGHT JOIN, the left and right sides are swapped at the parser level). I cannot find any mention of such limitation in the SQL Standard, though. AFAIU, something like this could be supported: create table t11 (a varchar (10)); select * from json_table(t11.a, '$[*]' COLUMNS (id INT PATH '$' )) AS OT1, t11 but it is not.

Sergei Petrunia added a comment - 2020-11-18 18:51 - edited

Note: name resolution does not seem to be "like in an ON expression".
Here is an example:

create table t10 (a varchar(10));

create table t11 (a varchar(10));

 select *

 from

  t10 join

  ( t11 join

     json_table(t10.a, '$[*]' COLUMNS (id INT PATH '$')) AS OT1

     on t11.a is not null /* and t10.a is not null */

  on t10.a is not null;

referring to t10.a as an argument to JSON_TABLE is accepted, but if one un-comments t10.a is not null, that produces an error.

Sergei Petrunia added a comment - 2020-11-18 18:51 - edited Note: name resolution does not seem to be "like in an ON expression". Here is an example: create table t10 (a varchar (10)); create table t11 (a varchar (10)); select * from t10 join ( t11 join json_table(t10.a, '$[*]' COLUMNS (id INT PATH '$' )) AS OT1 on t11.a is not null /* and t10.a is not null */ ) on t10.a is not null ; referring to t10.a as an argument to JSON_TABLE is accepted, but if one un-comments t10.a is not null , that produces an error.

Julien Fritsch made changes - 2020-12-10 15:28

Link

This issue is blocked by ~~MDEV-24385~~ [ ~~MDEV-24385~~ ]

Julien Fritsch made changes - 2020-12-10 15:29

Assignee

Sergei Petrunia [ psergey ]

Alexey Botchkov [ holyfoot ]

Julien Fritsch made changes - 2020-12-10 15:29

Assignee

Alexey Botchkov [ holyfoot ]

Julien Fritsch [ julien.fritsch ]

Julien Fritsch made changes - 2020-12-10 15:30

Assignee

Julien Fritsch [ julien.fritsch ]

Alexey Botchkov [ holyfoot ]

Sergei Petrunia added a comment - 2021-01-15 19:12

... well, Oracle 18C is a bad example, as it also allows cases that we didn't want to allow:

create table t1 (item_name varchar(32), item_props varchar(1024));

insert into t1 values ('Laptop', '{"color": "black", "price": 1000}');

select *

from

  t1 right join json_table(t1.item_props,

                           '$' columns( color varchar(100) path '$.color')

                           ) as T  on t1.item_name=T.color ;

-- This should produce an error

-- But it is accepted and produces an empty set.

Sergei Petrunia added a comment - 2021-01-15 19:12 ... well, Oracle 18C is a bad example, as it also allows cases that we didn't want to allow: create table t1 (item_name varchar(32), item_props varchar(1024)); insert into t1 values ('Laptop', '{"color": "black", "price": 1000}'); select * from t1 right join json_table(t1.item_props, '$' columns( color varchar(100) path '$.color') ) as T on t1.item_name=T.color ; -- This should produce an error -- But it is accepted and produces an empty set.

Alexey Botchkov made changes - 2021-01-26 09:18

Assignee

Alexey Botchkov [ holyfoot ]

Sergei Petrunia [ psergey ]

Sergei Petrunia added a comment - 2021-02-06 14:59

Review input for the last version of the patch:

https://lists.launchpad.net/maria-developers/msg12521.html
https://lists.launchpad.net/maria-developers/msg12522.html
https://lists.launchpad.net/maria-developers/msg12523.html
https://lists.launchpad.net/maria-developers/msg12524.html
https://lists.launchpad.net/maria-developers/msg12525.html
https://lists.launchpad.net/maria-developers/msg12526.html

Sergei Petrunia added a comment - 2021-02-06 14:59 Review input for the last version of the patch: https://lists.launchpad.net/maria-developers/msg12521.html https://lists.launchpad.net/maria-developers/msg12522.html https://lists.launchpad.net/maria-developers/msg12523.html https://lists.launchpad.net/maria-developers/msg12524.html https://lists.launchpad.net/maria-developers/msg12525.html https://lists.launchpad.net/maria-developers/msg12526.html

Sergei Petrunia added a comment - 2021-02-08 22:15

As for name resolution, the closest thing the SQL standard has to say is section 7.6 <table reference>,
p 418:

If TR is simply contained in a <from clause> FC, then the scope of the range variables of TR is every
<lateral derived table> that is simply contained in FC and is preceded by TR, and every <collection
derived table> that is simply contained in FC and is preceded by TR, and every <table function derived
table> that is simply contained in FC and is preceded by TR, and the <join condition> of all <joined
table>s contained in FC that contain TR.

That is, Table Reference is visible in every <lateral derived table> that follows it.
The same for <collection derived table>.
The same for <table function derived table>.

Note that the above list does NOT include JSON_TABLE:

 <table primary> ::=

...

  | <lateral derived table> <correlation or recognition>

  | <collection derived table> <correlation or recognition>

  | <table function derived table> <correlation or recognition>

...

  | <JSON table> <correlation or recognition>

Sergei Petrunia added a comment - 2021-02-08 22:15 As for name resolution, the closest thing the SQL standard has to say is section 7.6 <table reference>, p 418: If TR is simply contained in a <from clause> FC, then the scope of the range variables of TR is every <lateral derived table> that is simply contained in FC and is preceded by TR, and every <collection derived table> that is simply contained in FC and is preceded by TR, and every <table function derived table> that is simply contained in FC and is preceded by TR, and the <join condition> of all <joined table>s contained in FC that contain TR. That is, Table Reference is visible in every <lateral derived table> that follows it. The same for <collection derived table>. The same for <table function derived table>. Note that the above list does NOT include JSON_TABLE: <table primary> ::= ... | <lateral derived table> <correlation or recognition> | <collection derived table> <correlation or recognition> | <table function derived table> <correlation or recognition> ... | <JSON table> <correlation or recognition>

Sergei Petrunia added a comment - 2021-02-09 11:15

More review input: https://lists.launchpad.net/maria-developers/msg12531.html

I don't have any other input for now, need to take another look after things reported so far have been addressed.

Sergei Petrunia added a comment - 2021-02-09 11:15 More review input: https://lists.launchpad.net/maria-developers/msg12531.html I don't have any other input for now, need to take another look after things reported so far have been addressed.

Sergei Petrunia made changes - 2021-02-11 09:18

Assignee	Sergei Petrunia [ psergey ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexey Botchkov added a comment - 2021-02-14 13:07

https://github.com/MariaDB/server/commit/4d2f5a23de4304d756263d2f170889c586a48680

Alexey Botchkov added a comment - 2021-02-14 13:07 https://github.com/MariaDB/server/commit/4d2f5a23de4304d756263d2f170889c586a48680

Alexey Botchkov made changes - 2021-02-14 13:07

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Petrunia added a comment - 2021-02-22 19:05

The latest patch was: https://github.com/MariaDB/server/commit/85757ecbef44484270e385a8d52998c67f72d11a
Review input for it: https://lists.launchpad.net/maria-developers/msg12539.html

Sergei Petrunia added a comment - 2021-02-22 19:05 The latest patch was: https://github.com/MariaDB/server/commit/85757ecbef44484270e385a8d52998c67f72d11a Review input for it: https://lists.launchpad.net/maria-developers/msg12539.html

Sergei Petrunia added a comment - 2021-03-15 14:13

Pushed the fix for the last issue, and another code cleanup fix.
Re-based on the latest 10.6
Post-rebase fix for json_table_mysql.test

The result is at: https://github.com/mariadb/server/tree/bb-10.6-mdev17399-psergey2

Sergei Petrunia added a comment - 2021-03-15 14:13 Pushed the fix for the last issue, and another code cleanup fix. Re-based on the latest 10.6 Post-rebase fix for json_table_mysql.test The result is at: https://github.com/mariadb/server/tree/bb-10.6-mdev17399-psergey2

Sergei Petrunia made changes - 2021-03-16 14:31

Assignee	Sergei Petrunia [ psergey ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Julien Fritsch made changes - 2021-03-19 15:43

Assignee

Alexey Botchkov [ holyfoot ]

Elena Stepanova [ elenst ]

Elena Stepanova made changes - 2021-03-19 15:47

Link

This issue relates to ~~MDEV-25138~~ [ ~~MDEV-25138~~ ]

Elena Stepanova made changes - 2021-03-19 15:48

Link

This issue relates to ~~MDEV-25178~~ [ ~~MDEV-25178~~ ]

Elena Stepanova made changes - 2021-03-19 15:48

Link

This issue relates to ~~MDEV-25188~~ [ ~~MDEV-25188~~ ]

Elena Stepanova made changes - 2021-03-19 15:48

Link

This issue relates to ~~MDEV-25149~~ [ ~~MDEV-25149~~ ]

Elena Stepanova made changes - 2021-03-19 15:48

Link

This issue relates to ~~MDEV-25155~~ [ ~~MDEV-25155~~ ]

Elena Stepanova made changes - 2021-03-19 15:49

Link

This issue relates to ~~MDEV-25141~~ [ ~~MDEV-25141~~ ]

Elena Stepanova made changes - 2021-03-19 15:49

Link

This issue relates to ~~MDEV-25151~~ [ ~~MDEV-25151~~ ]