This function will be great to implement

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.

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

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.

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?)

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

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

1. Note: NESTED PATH definition is different from MySQL one.

Type is a type definition like in the table DDL.

type: numeric | temporal | string

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

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

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

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

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.

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?

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.

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

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

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

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

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

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.

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', ....).

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.

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.

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>

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

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?

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

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.

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

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

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

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.

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.

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

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

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>

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.

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

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

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

The last round of tests on https://github.com/MariaDB/server/commit/c3cb41111c4bf27c5b0f378a4c5283956d34ed52 didn't reveal any significant issues related to the scope of the task. In my opinion it can be released with a beta-version of the server.

This is a great feature. Will make a lot of people happy.

On Mon, Apr 26, 2021, 12:57 PM Sergei Petrunia (Jira) <jira@mariadb.org>