[MDEV-27896] Wrong result upon `COLLATE latin1_bin CHARACTER SET latin1` on the table or the database level Created: 2022-02-19  Updated: 2022-11-12  Resolved: 2022-05-24

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:
Blocks
blocks MDEV-27009 Add UCA-14.0.0 collations Closed
Problem/Incident
causes MDEV-30003 Assertion failure upon 2nd execution ... Closed
Relates
relates to MDEV-7387 [PATCH] Alter table xxx CHARACTER SET... Closed
relates to MDEV-27009 Add UCA-14.0.0 collations Closed
relates to MDEV-27906 CREATE TABLE/DATABASE .. CHARSET .. C... Closed
relates to MDEV-28644 Unexpected error on ALTER TABLE t1 CO... Closed

 Description   

The order of the CHARACTER SET and the COLLATE clauses is not strict on the table and the database level.
If COLLATE goes before CHARACTER SET, the COLLATE is ignored.

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


+-------+---------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                          |
 
+-------+---------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` char(1) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+---------------------------------------------------------------------------------------+

Notice, the COLLATE latin1_bin was ignored.

CREATE OR REPLACE DATABASE d1 COLLATE latin1_bin CHARACTER SET latin1;
 
SHOW CREATE DATABASE d1;


+----------+---------------------------------------------------------------+
 
| Database | Create Database                                               |
 
+----------+---------------------------------------------------------------+
 
| d1       | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
 
+----------+---------------------------------------------------------------+

Notice, the COLLATE latin1_bin was ignored.
Generated at Thu Feb 08 09:56:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.