maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

== Short ==

I've discovered another mismatch between MariaDB implementation and MySQL.
I don't think we need to fix it.

== Long ==

select * from
json_table(
  '[{"a":"aa"}, {"b":"bb"}]',
  '$[*]' columns (
      col1 varchar(100) path '$.b' default 'bbb' on empty
   )
) as tt;

In MySQL, this gives an error:

ERROR 3141 (22032): Invalid JSON text in argument 1 to function JSON_TABLE: "Invalid value." at position 0.

and the cause is that MySQL expects the default value to be JSON. Their
documentation says:

  DEFAULT json_string ON EMPTY

I wasn't able to find the reasoning behind this.
MariaDB's behavior makes more sense to me:

MariaDB: 
+------+
| col1 |
+------+
| bbb  |
| bb   |
+------+

Oracle DB produces the same.

In order to get "bbb" as the default for MySQL, one needs to quote it:

select * from
json_table(
  '[{"a":"aa"}, {"b":"bb"}]',
  '$[*]' columns (
      col1 varchar(100) path '$.b' default '"bbb"' on empty
   )
) as tt;

MySQL:
+------+
| col1 |
+------+
| bbb  |
| bb   |
+------+

MariaDB and OracleDB: 
+-------+
| col1  |
+-------+
| "bbb" |
| bb    |
+-------+


== Attempt to locate where this is described in the SQL Standard ==

Section 7.11, <JSON table>, General Rules:

e, ii, 2:

2) If JTCDi is a <JSON table regular column definition>, then:
A)  ... let ZBi be the <JSON table column empty behavior> ...
...
C) The General Rules of Subclause 9.40, “Casting an SQL/JSON sequence to an SQL
type”, are applied with ST1 as STATUS IN, SEQ as SQL/JSON SEQUENCE, ZBi as
EMPTY BEHAVIOR ...

Looking at that section: 9.40 Casting an SQL/JSON sequence to an SQL type

4) If TEMPST is successful completion, then:
...
b) If the length of SEQ is 1 (one), then let I be the SQL/JSON item in SEQ.
...
iii) Otherwise, let IDT be the data type of I.
...
2) Otherwise, let X be an SQL variable whose value is I. Let V be the value of
CAST (X AS DT)


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