maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #04700
Re: JSON in MariaDB 10.2.7 and MySQL
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@xxxxxxxxxxx
Follow ups
References