maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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 <serg@xxxxxxxxxxx> 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@xxxxxxxxxxx