[MDEV-28767] Collation "binary" is not accepted for databases, tables, columns Created: 2022-06-07  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Character Sets, Parser
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.11

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

binary character set is accepted in in CREATE statements and such, but binary collation isn't, it requires backticks:

MariaDB [test]> create table t (a char(1) collate binary);
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary)' at line 1


MariaDB [test]> create table t (a char(1) collate `binary`);
 
Query OK, 0 rows affected (0.033 sec)

Maybe semantically binary collation doesn't make much sense, but not being able to parse it can cause a problem loading a dump from MySQL 8.0, as it can produce such structures:

8.0.28

 
MySQL [test]> create table t (a enum('a') character set binary, b char(8)) character set latin1;
 
Query OK, 0 rows affected (0.194 sec)
 
 
 
MySQL [test]> show create table t \G
 
*************************** 1. row ***************************
 
       Table: t
 
Create Table: CREATE TABLE `t` (
 
  `a` enum('a') CHARACTER SET binary COLLATE binary DEFAULT NULL,
 
  `b` char(8) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.001 sec)


Generated at Thu Feb 08 10:03:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.