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