[MDEV-11439] No data type JSON, but CAST(something AS JSON) pretends to work Created: 2016-12-01  Updated: 2017-02-14  Resolved: 2017-02-14

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2
Fix Version/s: 10.2.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: 10.2-rc, json-10.2

Issue Links:
Relates
relates to MDEV-9143 JSON_xxx functions Closed

 Description    

MariaDB [test]> SELECT CAST('foo' AS JSON);
 
+---------------------+
 
| CAST('foo' AS JSON) |
 
+---------------------+
 
| foo                 |
 
+---------------------+
 
1 row in set (0.00 sec)

I don't think it makes any sense. We don't even have format JSON, how can we pretend we can CAST to it? And it's not even a valid JSON text.

 Comments   
Comment by Elena Stepanova [ 2016-12-13 ]

serg,

Have you approved this?
What compatibility are we talking about? We cannot use type JSON and don't worry about compatibility, but suddenly for CAST it becomes so important that we fake it (of course, again, without any warnings which a sensible admin could have converted to errrors)?

MariaDB 10.2

 
MariaDB [test]> create table t1 as select cast('{"a":"b"}' as json) as a;
 
Query OK, 1 row affected (0.45 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table t1;
 
+-------+-----------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                            |
 
+-------+-----------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` varbinary(27) NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> insert into t1 values ('a');
 
Query OK, 1 row affected (0.07 sec)

How does it make any sense at all, and in what way it's compatible with MySQL?

MySQL 5.7

 
MySQL [test]> create table t1 as select cast('{"a":"b"}' as json) as a;
 
Query OK, 1 row affected (0.65 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MySQL [test]> show create table t1 \G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `a` json DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> insert into t1 values ('a');
 
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 't1.a'.

More about "compatibility":

MySQL 5.7

 
MySQL [test]> select CAST(ST_GeomFromText('POINT(1 1)') AS JSON);
 
+---------------------------------------------+
 
| CAST(ST_GeomFromText('POINT(1 1)') AS JSON) |
 
+---------------------------------------------+
 
| {"type": "Point", "coordinates": [1, 1]}    |
 
+---------------------------------------------+
 
1 row in set (0.24 sec)

MariaDB 10.2

 
MariaDB [test]> select CAST(ST_GeomFromText('POINT(1 1)') AS JSON);
 
+---------------------------------------------+
 
| CAST(ST_GeomFromText('POINT(1 1)') AS JSON) |
 
+---------------------------------------------+
 
|              �?      �?                     |
 
+---------------------------------------------+
 
1 row in set (0.00 sec)

Comment by Sergei Golubchik [ 2016-12-14 ]

This pseudo-cast is for cases like

select json_object('a', '{"b": "abcd"}');

What should be the result be,

{
 
  "a" : '{"b": "abcd"}'
 
}

or

{
 
  "a" :
 
    {
 
      "b": "abcd"
 
    }
 
}

That is, what is the second argument of json_object — a string or a json? This pseudo-cast solves this.

Although I would prefer a standard solution instead:

select json_object('a', '{"b": "abcd"}' FORMAT JSON);

Comment by Elena Stepanova [ 2016-12-14 ]

serg, so, why not reserve the "room" for the standard FORMAT JSON if there is no time to implement it, and in the spirit of our current endeavor to follow standard defaults, imply the default, which is, coincidentally, FORMAT JSON? It will be different from MySQL, but at least it won't be difficult to explain (assuming it's documented), and for once it will be better than MySQL (and MariaDB) does now, by returning the first variant, only in an uglier form of

MariaDB [test]> select json_object('a', '{"b": "abcd"}');
 
+-----------------------------------+
 
| json_object('a', '{"b": "abcd"}') |
 
+-----------------------------------+
 
| {"a": "{\"b\": \"abcd\"}"}        |
 
+-----------------------------------+
 
1 row in set (0.00 sec)

Fake CAST goes against everything, not just common sense – it does not improve compatibility with MySQL because results and types are different anyway; it falls out of the standard which does not suggest any explicit CAST there; and it contradicts the definition of the function CAST itself, which is supposed to produce a result value of the specified type (not shape or format).

Comment by Sergei Golubchik [ 2016-12-18 ]

I agree, I don't like CAST. The standard specifies that if FORMAT JSON is not specified, then the second argument of json_object is a string, unless it's an output of another function that returns JSON.

So, I'd rather implement FORMAT JSON now. This is, basically, just a syntax sugar.

Comment by Alexey Botchkov [ 2017-02-14 ]

http://lists.askmonty.org/pipermail/commits/2017-February/010687.html

Generated at Thu Feb 08 07:49:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.