#
# MDEV-27896 Wrong result upon COLLATE latin1_bin CHARACTER SET latin1 on the table or the database level
#
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET latin1;
SHOW CREATE DATABASE db1;
Database	Create Database
db1	CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */
DROP DATABASE db1;
CREATE VIEW cscl AS
SELECT
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM
INFORMATION_SCHEMA.SCHEMATA
WHERE
SCHEMA_NAME='db1';
SET collation_server=utf8mb4_unicode_ci;
CREATE DATABASE db1 COMMENT 'test';
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_unicode_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE latin1_bin;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
latin1	latin1_bin
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET latin1;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
latin1	latin1_swedish_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET latin1 COLLATE DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
latin1	latin1_swedish_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET latin1 COLLATE latin1_bin;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
latin1	latin1_bin
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE DEFAULT CHARACTER SET latin1;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
latin1	latin1_swedish_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET latin1;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
latin1	latin1_bin
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE DEFAULT CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT CHARACTER SET utf8mb4;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET utf8mb4 CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT CHARACTER SET latin1;
ERROR HY000: Conflicting declarations: 'CHARACTER SET DEFAULT (utf8mb4)' and 'CHARACTER SET latin1'
CREATE DATABASE db1 CHARACTER SET latin1 CHARACTER SET DEFAULT;
ERROR HY000: Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET DEFAULT (utf8mb4)'
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE utf8mb4_bin;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_bin
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE utf8mb4_bin CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME
utf8mb4	utf8mb4_bin
DROP DATABASE db1;
DROP VIEW cscl;
#
# MDEV-28117 Multiple conflicting table COLLATE clauses are not rejected
#
CREATE DATABASE db1 COLLATE latin1_swedish_ci COLLATE latin1_bin;
ERROR HY000: Conflicting declarations: 'COLLATE latin1_swedish_ci' and 'COLLATE latin1_bin'
#
# MDEV-27906 CREATE TABLE/DATABASE .. CHARSET .. COLLATE is not consistent on errors
#
SET collation_server=utf8mb4_unicode_ci;
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
SET collation_server=DEFAULT;
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;
SHOW CREATE DATABASE db1;
Database	Create Database
db1	CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;
SHOW CREATE DATABASE db1;
Database	Create Database
db1	CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */
DROP DATABASE db1;