maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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 <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
> >
> >

-- 
BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net