Re: [Maria-discuss] JSON in MariaDB 10.2.7 and MySQL

Hi, Peter! Okay. I've created https://jira.mariadb.org/browse/MDEV-13313 But I'd really like other users to comment on this too. Is it a generally useful thing, is it what you would expect? On Jul 13, Peter Laursen wrote:

I personally think like this

...

col1 JSON

being equal to

col1 LONGTEXT CHARACTER SET utf8mb4 CHECK(JSON_VALID(col1))

.. would be nice.

-- Peter

On Thu, Jul 13, 2017 at 12:03 PM, Sergei Golubchik wrote:

...

Hi, Peter!

On Jul 13, Peter Laursen wrote:

...

-- MariaDB 10.2.7 CREATE TABLE `jsontst`( `id` INT NOT NULL AUTO_INCREMENT, `str` JSON, PRIMARY KEY (`id`) ) ENGINE=Aria CHARSET=latin1; SHOW CREATE TABLE `jsontst`; /* returns CREATE TABLE `jsontst2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 */

readings: https://jira.mariadb.org/browse/MDEV-9144 https://bugs.mysql.com/bug.php?id=86875

As you see except for JSON in MariaDB just being a synonym of TEXT there are two more significant differences 1) length: in MySQL JSON has the lenght of a LONGTEXT - in MariaDB it is a (plain) TEXT 2) encoding: in MySQL the utf8mb4 character set is enforced for JSON data - in MariaDB you may specify any character set.

I think in particular 2nd point could cause problems (unicode garbling) when loading a dump with JSON data from MySQL. Basically because latin1 is still default character set in MariaDB.

Are you aware of the 2 differences? I think the major reason for introducing JSON in MariaDB is compability concerns with MySQL. But the 2 points here are IMO incompabilites that could have been avoided?

JSON functions were added for SQL Standard and for MySQL compatibility.

But JSON alias for TEXT - it was only for MySQL compatibility, no other reasons. So you're right, these issues should be fixed.

1. LONGTEXT - no questions here, we'll fix it.

2. charset. If one would write

col1 JSON CHARACTER SET latin1

That'll be an error, right?

3. I could make JSON to imply validity checks too.

col1 JSON

being equal to

col1 LONGTEXT CHARACTER SET utf8mb4 CHECK(JSON_VALID(col1))

is it what users would expect for compatibility reasons?

Regards, Sergei Chief Architect MariaDB and security@mariadb.org