Details
Critical Description
Filing this for https://www.mail-archive.com/maria-developers@lists.launchpad.net/msg12296.html
At the moment MariaDB requires that the values in DEFAULT clauses are quoted.
Example:
select * |
from |
json_table(
|
'{"intval": 1000}', |
'$' columns( |
col1 int path '$.intval_' |
default '100' on empty |
)
|
) as T; |
here, "100" must be quoted, otherwise one gets a parse error. However, the quoted value is interpreted as an SQL literal. This looks puzzling.
MySQL-8 also requires that the default value is quoted, but they have a (very odd) reason for it: they interpret the default value as JSON:
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/json-table-functions.html
says:
DEFAULT json_string ON EMPTY: the provided json_string is parsed as JSON, as
long as it is valid, and stored instead of the missing value. Column type
rules also apply to the default value.
I am not sure why MySQL chose to do this. Looking into the SQL Standard, one
can see:
<JSON table regular column definition> ::=
|
<column name> <data type>
|
[ PATH <JSON table column path specification> ]
|
[ <JSON table column empty behavior> ON EMPTY ]
|
[ <JSON table column error behavior> ON ERROR ]
|
|
|
<JSON table column empty behavior> ::=
|
ERROR
|
| NULL
|
| DEFAULT <value expression>
|
This doesn't say whether the <value expression> should be interepreted as JSON
or just as a value. But one can find this passage:
Without Feature T826, "General value expression in ON ERROR or ON EMPTY
|
clauses", the <value
|
expression> contained in <JSON table column empty behavior> or <JSON table
|
column error behavior>
|
contained in a <JSON table regular column definition> JTRCD shall be a
|
<literal> that can be cast to the
|
data type specified by the <data type> contained in JTRCD without raising an
|
exception condition
|
according to the General Rules of Subclause 6.13, "<cast specification>".
|
The important part is:
... shall be a <literal> that can be cast to the data type specified ...
|
which means it is not JSON. It is just a literal, and literal can be a string
literal (in quotes, 'string') or an integer literal (without quotes) or other
kind of literal.
Btw, Oracle Database allows non-string literals in the default clause:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9af7e43ede77ee285e1a65f1f419d3bd
Attachments
Activity
And also
commit 7b59d374f3e71c2de0f9e6664b95674686916387 (HEAD -> bb-10.6-MDEV-25822-v2, origin/bb-10.6-MDEV-25822-v2)
|
Author: Sergei Petrunia <sergey@mariadb.com>
|
Date: Tue Jul 23 14:58:24 2024 +0300
|
|
|
Remove unused Table_function_json_table::m_text_literal_cs
|
|
Ok maybe it wasn't explicitly specified but a month ago I took Alexey's patch, reviewed it, applied my own input and committed as
commit eaff5ae82168825f036dff00b5c9cc16a004c4ac (HEAD -> bb-10.6-MDEV-25822, origin/bb-10.6-MDEV-25822)Author: Sergei Petrunia <sergey@mariadb.com>Date: Tue Jul 23 14:33:33 2024 +0300MDEV-25822 JSON_TABLE: default values should allow non-string literals(Polished initial patch by Alexey Botchkov)
added a few more commits and passed to holyfoot to look at. Now the ball is on Alexey's turf.
Now changed issue status to reflect this too.
Notes for the documentation (needs editing):
JSON_TABLE allows to specify default column values (See https://mariadb.com/kb/en/json_table/)
Before this patch, one had to specify default values as string constants.
After this patch, one can specify default values as any literals (string, integer, decimal number, date literal, etc)
NOTE: https://mariadb.com/kb/en/json_table/ needs to be updated to reflect this change!
select * |
from |
json_table('{"a": "123"}', |
'$' columns(col1 INT path '$.a' default 1 on empty)) as T; |
select * |
from |
json_table('{"a": "123"}', |
'$' columns(col1 date path '$.date' default date '2020-01-01' on empty)) as T; |
Merged:
commit eaff5ae82168825f036dff00b5c9cc16a004c4ac (HEAD -> bb-10.6-MDEV-25822, origin/bb-10.6-MDEV-25822)Author: Sergei Petrunia <sergey@mariadb.com>Date: Tue Jul 23 14:33:33 2024 +0300MDEV-25822 JSON_TABLE: default values should allow non-string literals(Polished initial patch by Alexey Botchkov)Make the code handle DEFAULT values of any datatype- Make Json_table_column::On_response::m_default be Item*, not LEX_STRING.- Change the parser to use string literal non-terminals for producingthe DEFAULT value-- Also, stop updating json_table->m_text_literal_cs for the DEFAULTvalue literals as it is not used.Need to check which version to push this into