Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.6, 11.4, 11.7(EOL)
-
None
Description
In CREATE TABLE JSON fields default to utf8mb4, even if not specified, resulting in its overriding the charset of the table. However, the overriding does not happen in ALTER TABLE when the table charset is changed, resulting in the inconsistency shown below:
CREATE TABLE t ( cj JSON ) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
|
select character_set_name from information_schema.columns where table_name = 't' and column_name='cj';
|
character_set_name
|
utf8mb4
|
alter table t CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
|
select character_set_name from information_schema.columns where table_name = 't' and column_name='cj';
|
character_set_name
|
utf8mb3
|
Attachments
Issue Links
- blocks
-
MDEV-34716 Allow arbitrary options in CREATE SERVER
-
- Closed
-
Activity
I'd say that for all practical purposes it behaves as if COLLATE utf8mb4_bin was explicitly specified.
It behaves in CREATE TABLE as a column with explicitly specified COLLATE clause. And it behaves in following ALTER TABLE also as a column with explicitly specified COLLATE clause, as far as I can see. There is no inconsistency.
> I'd say that for all practical purposes it behaves as if COLLATE utf8mb4_bin was explicitly specified.
But it is not explicitly specified, and the kb documentation on CREATE TABLE linked in a previous comment says that "[DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to set a default character set for the table. This is the character set used for all columns where an explicit character set is not specified"
The following example shows that non-JSON columns follow the documentation, but JSON columns don't.
CREATE TABLE t1 ( a text, b varchar(30), c enum(''), d json ) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
|
select column_name, character_set_name from information_schema.columns where table_name = 't1';
|
column_name character_set_name
|
a utf8mb3
|
b utf8mb3
|
c utf8mb3
|
d utf8mb4
|
CREATE TABLE t2 ( a text, b varchar(30), c enum(''), d json ) character set latin1 collate latin1_general_ci;
|
select column_name, character_set_name from information_schema.columns where table_name = 't2';
|
column_name character_set_name
|
a latin1
|
b latin1
|
c latin1
|
d utf8mb4
|
The question remains unanswered (at least in this MDEV, but let me know if it has been answered elsewhere): what is the justification for json having a default character set that overrides the table character set in table creation?
If this difference is expected, then the kb needs to be updated to reflect it.
> JSON columns behave exactly as TEXT columns, except that they get utf8mb4 charset by default.
Sure, but the "except" part is the main point of this issue.