Re: [Maria-developers] JSON_TABLE: on default values
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 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:
> >
> > ::=
> >
> > [ PATH ]
> > [ ON EMPTY ]
> > [ ON ERROR ]
> >
> > ::=
> > ERROR
> > | NULL
> > | DEFAULT
> >
> > ...
> > This doesn't say whether the 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 > expression> contained in or > column error behavior>
> > contained in a JTRCD shall be a
> > that can be cast to the
> > data type specified by the contained in JTRCD without raising
> > an exception condition
> > according to the General Rules of Subclause 6.13, “”.
> >
> >
> > The important part is:
> >
> > ... shall be a 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=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 : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] JSON_TABLE: on default values
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 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:
>
> ::=
>
> [ PATH ]
> [ ON EMPTY ]
> [ ON ERROR ]
>
> ::=
> ERROR
> | NULL
> | DEFAULT
>
> ...
> This doesn't say whether the 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 expression> contained in or column error behavior>
> contained in a JTRCD shall be a
> that can be cast to the
> data type specified by the contained in JTRCD without raising
> an exception condition
> according to the General Rules of Subclause 6.13, “”.
>
>
> The important part is:
>
> ... shall be a 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=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
>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] JSON_TABLE: on default values
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:
::=
[ PATH ]
[ ON EMPTY ]
[ ON ERROR ]
::=
ERROR
| NULL
| DEFAULT
...
This doesn't say whether the 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 contained in or
contained in a JTRCD shall be a
that can be cast to the
data type specified by the contained in JTRCD without raising an
exception condition
according to the General Rules of Subclause 6.13, “”.
The important part is:
... shall be a 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=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
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
