Hi, Sergey! I meditated about it for some time. I remember i was thinking on that part before and did that so for some reason. Though either i was wrong or didn't finish what i planned. This time i'd say we should allow numeric constants there too. Here's the patch i'd push to fix this: https://github.com/MariaDB/server/commit/9c518e4cc9b0569cae2daa5a4024e209293... Best regards. HF On Wed, May 26, 2021 at 8:01 PM Sergey Petrunia <sergey@mariadb.com> wrote:
Hi Alexey,
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:
<quote> 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>”. </quote>
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
What are your thoughts on this? Is MariaDB's behavior intentional? Should we follow the standard and allow all kinds of literals? What was the reason for the limitation that default values are quoted?
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net