1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
# Tests for the binary collations.
--echo #
--echo # Bug#30350111: UTF8MB4_0900_BIN STRINGS CANNOT BE COMPARED WITH
--echo # UTF8MB4_0900_AI_CI STRINGS
--echo #
CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT,
col_aici VARCHAR(10) COLLATE utf8mb4_0900_ai_ci,
col_bin VARCHAR(10) COLLATE utf8mb4_bin,
col_0900_bin VARCHAR(10) COLLATE utf8mb4_0900_bin);
INSERT INTO t(col_aici)
VALUES ('abc'), ('ABC'), ('Abc'), ('abc '), ('ABC '), ('Abc ');
UPDATE t SET col_bin = col_aici, col_0900_bin = col_aici;
# Binary collation wins over non-binary collation. Comparison should
# be done using the utf8mb4_bin collation (PAD SPACE).
SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
FROM t t1, t t2
WHERE t1.col_aici = t2.col_bin
ORDER BY t1.id, t2.id;
# Binary collation wins over non-binary collation. Comparison should
# be done using the utf8mb4_0900_bin collation (NO PAD).
SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
FROM t t1, t t2
WHERE t1.col_aici = t2.col_0900_bin
ORDER BY t1.id, t2.id;
# Two different binary collations. Don't know which to choose.
--error ER_CANT_AGGREGATE_2COLLATIONS
SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
FROM t t1, t t2
WHERE t1.col_bin = t2.col_0900_bin
ORDER BY t1.id, t2.id;
--error ER_CANT_AGGREGATE_2COLLATIONS
SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
FROM t t1, t t2
WHERE t1.col_0900_bin = t2.col_bin
ORDER BY t1.id, t2.id;
# Binary collation wins over non-binary collation. The UNION uses the
# utf8mb4_bin collation (PAD SPACE).
--sorted_result
SELECT col_aici FROM t UNION SELECT col_bin FROM t;
# Binary collation wins over non-binary collation. The UNION uses the
# utf8mb4_0900_bin collation (NO PAD).
--sorted_result
SELECT col_aici FROM t UNION SELECT col_0900_bin FROM t;
# Cannot decide whether to use PAD SPACE or NO_PAD.
--error ER_CANT_AGGREGATE_NCOLLATIONS
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t;
--error ER_CANT_AGGREGATE_NCOLLATIONS
SELECT col_0900_bin FROM t UNION SELECT col_bin FROM t;
--error ER_CANT_AGGREGATE_NCOLLATIONS
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici FROM t;
# Add an explicit collation into the mix. The explicit collation is used.
--sorted_result
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici COLLATE utf8mb4_0900_ai_ci FROM t;
--sorted_result
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici COLLATE utf8mb4_bin FROM t;
--sorted_result
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici COLLATE utf8mb4_0900_bin FROM t;
DROP TABLE t;
# Verify that the deprecated BINARY type attribute uses utf8mb4_bin as
# the binary collation of utf8mb4.
CREATE TABLE t(x VARCHAR(10) BINARY);
SELECT COLLATION(MAX(x)) FROM t;
DROP TABLE t;
|
