[MDEV-17399] Add support for JSON_TABLE - Jira

XML

Word

Printable

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 10.6.0
Component/s: JSON
Labels:
None

Epic Link:
Implement JSON functions available in MySQL 8.0 but not MariaDB

Description

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

1. Syntax

1.1 Standard compliance

2. JSON_TABLE as table function

3. Implementing a table function

3.1 DDL considerations

3.2 Producing the rows

3.3 Interfacing with the optimizer

3.3.1 Estimates

3.3.2 JSON_TABLE must be after its dependencies

3.3.3 Can't use Join buffering

5. Links

1. Syntax

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

The syntax to be implemented is:

  JSON_TABLE(

    json_text,

    path COLUMNS(columns_list)

  ) [AS] alias

columns_list:

  column [, column[, ... ]]

column:

    name FOR ORDINALITY

  | name type PATH path_str [on_empty_on_error]

  | name type EXISTS PATH path_str [on_empty_on_error]

  | NESTED PATH path COLUMNS (columns_list)

on_empty_on_error:

  [ behavior ON EMPTY ] [ behavior ON ERROR ]

behavior:

   ERROR | NULL | DEFAULT <value_expression>

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

1.1 Standard compliance

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

2. JSON_TABLE as table function

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

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

3. Implementing a table function

3.1 DDL considerations

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

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

3.1.2 Name resolution for JSON_TABLE argument

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

3.2 Producing the rows

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

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

3.3 Interfacing with the optimizer

3.3.1 Estimates

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

3.3.2 JSON_TABLE must be after its dependencies

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

3.3.3 Can't use Join buffering

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

4. Producing JSON_TABLE contents

4.1 FOR ORDINALITY columns

The standard says:

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

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

4.2 NESTED contents

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

5. Links

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.

Attachments

Issue Links

causes

MDEV-31616 Problems with a stored function EMPTY() on upgrade to 10.6

Closed

includes

MDEV-23277 Make "for" sentence support JSON arrays

Closed

is blocked by

MDEV-24385 JSON_TABLE review

Closed

relates to

MDEV-22288 JSON_TABLE: ORDINALITY column starts from 0 instead of 1

Closed

MDEV-22289 JSON_TABLE: Some types for regular columns are not supported by parser

Closed

MDEV-22290 JSON_TABLE: Decimal type with M equal D causes Assertion `scale <= precision' failure

Closed

MDEV-22291 JSON_TABLE: SELECT from json_table does not work without default database

Closed

MDEV-22293 JSON_TABLE: Values for regular columns are truncated for no reason

Closed

MDEV-22294 JSON_TABLE: Cannot create a view selecting from json_table

Closed

MDEV-22295 JSON_TABLE: Server crashes in Select_limit_counters::get_select_limit upon json_table referencing unknown table

Closed

MDEV-22296 JSON_TABLE: Ordinality is calculated incorrectly (counts all rows instead of rows at its level)

Closed

MDEV-22297 JSON_TABLE: ON ERROR clause is used for empty values if ON EMPTY is not specified explicitly

Closed

MDEV-22298 JSON_TABLE: EXISTS PATH value is converted to other types incorrectly

Closed

MDEV-22299 JSON_TABLE: Assertion `!cmp(&table_ref->db, &table_ref->table->s->db) || (table_ref->schema_table && is_infoschema_db(&table_ref->table->s->db)) || table_ref->is_materialized_derived()' failed on NATURAL JOIN between JSON tables

Closed

MDEV-22300 JSON_TABLE: If value extracted for regular column is array, the first element is returned instead of error

Closed

MDEV-22301 JSON_TABLE: Queries are not inserted into query cache

Closed

MDEV-22302 JSON_TABLE: Column privilege is insufficient for query with json_table

Closed

MDEV-25228 JSON_TABLE: Server crashes in Query_cache::unlink_table

Closed

MDEV-25230 JSON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed

Closed

MDEV-25254 JSON_TABLE: Inconsistent name resolution with right joins

Closed

MDEV-25255 JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails

Closed

MDEV-25256 JSON_TABLE: Error ER_VIEW_INVALID upon running query via view

Closed

MDEV-25259 JSON_TABLE: Illegal mix of collations upon executing query with combination of charsets via view

Closed

MDEV-25352 JSON_TABLE: Inconsistent name resolution and ER_VIEW_INVALID upon combination of RIGHT and NATURAL JOIN

Closed

MDEV-25353 JSON_TABLE: Illegal mix of collations upon executing PS once, or SP/function twice

Closed

MDEV-25377 JSON_TABLE: Wrong value with implicit conversion

Closed

MDEV-25379 JSON_TABLE: ERROR ON clauses are ignored if a column is not on select list

Closed

MDEV-25380 JSON_TABLE: Assertion `join->best_read < double(1.797...) fails upon JSON_TABLE in subquery

Closed

MDEV-25381 JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view

Closed

MDEV-25397 JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS upon query with JOIN

Closed

MDEV-25406 JSON_TABLE: VARBINARY column in view definition causes syntax error and assertion failure

Closed

MDEV-25408 JSON_TABLE: AddressSanitizer CHECK failed in Binary_string::realloc_raw

Closed

MDEV-25452 JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp=OFF

Open

MDEV-25528 Document JSON_TABLE

Closed

MDEV-25727 Add formatted column support to JSON_TABLE

Open

MDEV-25881 JSON_TABLE can't handle an array properly

Closed

MDEV-17397 Implement JSON functions available in MySQL 8.0 but not MariaDB

Open

MDEV-25138 JSON_TABLE: A space between JSON_TABLE and opening bracket causes syntax error

Closed

MDEV-25139 JSON_TABLE: Non-descriptive ER_PARSE_ERROR error upon a missing alias

Closed

MDEV-25140 JSON_TABLE: Success of query execution depends on the outcome of previous queries

Closed

MDEV-25141 JSON_TABLE: SELECT into outfile bypasses file privilege check

Closed

MDEV-25142 JSON_TABLE: CREATE VIEW involving EXISTS PATH ends up with invalid frm

Closed

MDEV-25143 JSON_TABLE: Server crashes in handler::print_error / hton_name upon ERROR ON EMPTY

Closed

MDEV-25144 JSON_TABLE: Assertion `thd->is_error()' failed in convert_error_to_warning

Closed

MDEV-25145 JSON_TABLE: Assertion `fixed == 1' failed in Item_load_file::val_str on 2nd execution of PS

Closed

MDEV-25146 JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object

Closed

MDEV-25149 JSON_TABLE: Inconsistency in implicit data type conversion

Closed

MDEV-25150 JSON_TABLE: Some data types cause parsing error: ENUM, SET, geometry, INET6

Open

MDEV-25151 JSON_TABLE: Unexpectedly padded values in a PATH column

Closed

MDEV-25154 JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such

Closed

MDEV-25155 JSON_TABLE: Status variable Feature_json is not incremented

Closed

MDEV-25178 JSON_TABLE: ASAN use-after-poison in my_fill_8bit / Json_table_column::On_response::respond

Closed

MDEV-25183 JSON_TABLE: CREATE VIEW involving NESTED PATH ends up with invalid frm

Closed

MDEV-25186 JSON_TABLE: ASAN global-buffer-overflow in my_strnncoll_binary upon inserting query with join into query cache

Closed

MDEV-25188 JSON_TABLE: ASAN use-after-poison in Field_long::reset / Table_function_json_table::setup or malloc(): invalid size

Closed

MDEV-25189 JSON_TABLE: Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' failed upon CREATE .. SELECT

Closed

MDEV-25192 JSON_TABLE: ASAN use-after-poison in field_conv_memcpy / Create_tmp_table::finalize upon query with derived table

Closed

MDEV-25202 JSON_TABLE: Early table reference leads to unexpected result set, server crash in st_join_table::fix_splitting or failing Assertion `join->best_read < double(1.797...)

Closed

MDEV-25421 Documentation for JSON_TABLE for MariaDB Server 10.6

Closed

MDEV-26257 JSON_TABLE() does not return ER_JSON_TABLE_MULTIPLE_MATCHES when column path has multiple matches

Open

MDEV-27898 CREATE VIEW AS SELECT involving JSON column requires root privileges

Confirmed

(56 relates to)

Sub-Tasks

There are no Sub-Tasks for this issue.

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Diego Dupin

Votes:: 31 Vote for this issue

Watchers:: 32 Start watching this issue

Dates

Created:: 2018-10-08 20:14

Updated:: 2023-07-04 08:14

Resolved:: 2021-04-26 16:56

Git Integration

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