Hi Alexey, Ok I've filed https://jira.mariadb.org/browse/MDEV-25822 for this. Let me review the patch.
On Sun, May 30, 2021 at 06:44:19AM +0400, Alexey Botchkov wrote: > 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/9c518e4cc9b0569cae2daa5a4024e209293eca45 > > Best regards. > HF > > > On Wed, May 26, 2021 at 8:01 PM Sergey Petrunia <[email protected]> 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 > > > > -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
