[MDEV-27906] CREATE TABLE/DATABASE .. CHARSET .. COLLATE is not consistent on errors Created: 2022-02-21  Updated: 2022-06-15  Resolved: 2022-06-07

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Parser
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.9.2

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27896 Wrong result upon `COLLATE latin1_bin... Closed
relates to MDEV-28117 Multiple conflicting table COLLATE cl... Closed
relates to MDEV-27009 Add UCA-14.0.0 collations Closed

 Description   

Table and database level CHARACTER SET and COLLATE clauses can go in any order. So both these syntaxes are correct:

  • CHARACTER SET .. COLLATE
  • COLLATE .. CHARACTER SET

But they can produce errors differently:

CREATE OR REPLACE TABLE t1 (a CHAR) COLLATE latin1_bin CHARACTER SET DEFAULT;


ERROR 1302 (HY000): Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET DEFAULT'


CREATE OR REPLACE  TABLE t1 (a CHAR) CHARACTER SET DEFAULT COLLATE latin1_bin;


Query OK, 0 rows affected (0.014 sec)

The same problem is repeatable on the database level:

CREATE OR REPLACE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;


ERROR 1302 (HY000): Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET DEFAULT'


CREATE OR REPLACE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;


Query OK, 0 rows affected (0.014 sec)


Generated at Thu Feb 08 09:56:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.