maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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