maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

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 <sergey@xxxxxxxxxxx> 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
>
>