DROP DATABASE IF EXISTS `mcol_4899`;
CREATE DATABASE `mcol_4899` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
USE `mcol_4899`;
SELECT @cs_conn := @@character_set_connection;
@cs_conn := @@character_set_connection
latin1
SET character_set_connection=utf8mb4;
CREATE TABLE collation_test (LETTERS TEXT NULL)ENGINE=ColumnStore;
INSERT INTO collation_test (LETTERS) VALUES ('a'),('A'),('b'),('B');
SELECT * FROM collation_test;
LETTERS
a
A
b
B
SELECT * FROM collation_test WHERE LETTERS LIKE 'a';
LETTERS
a
SELECT * FROM collation_test WHERE LETTERS IN ('a','B');
LETTERS
a
B
SELECT * FROM collation_test WHERE LETTERS IN ('a' COLLATE utf8mb4_bin);
LETTERS
a
DROP TABLE collation_test;
CREATE TABLE collation_test (LETTERS TEXT NULL)ENGINE=ColumnStore DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;
INSERT INTO collation_test (LETTERS) VALUES ('a'),('A'),('b'),('B');
SELECT * FROM collation_test;
LETTERS
a
A
b
B
SELECT * FROM collation_test WHERE LETTERS LIKE 'a';
LETTERS
a
SELECT * FROM collation_test WHERE LETTERS IN ('a','B');
LETTERS
a
B
SELECT * FROM collation_test WHERE LETTERS IN ('a' COLLATE utf8mb4_bin);
LETTERS
a
DROP TABLE collation_test;
CREATE TABLE collation_test (LETTERS TEXT NULL)ENGINE=ColumnStore DEFAULT CHARSET=utf8mb4;
INSERT INTO collation_test (LETTERS) VALUES ('a'),('A'),('b'),('B');
SELECT * FROM collation_test;
LETTERS
a
A
b
B
SELECT * FROM collation_test WHERE LETTERS LIKE 'a';
LETTERS
a
A
SELECT * FROM collation_test WHERE LETTERS IN ('a','B');
LETTERS
a
A
b
B
DROP DATABASE `mcol_4899`;
SET character_set_connection=@cs_conn;