Debian Codegraph @

mysql-test/suite/collations/t/binary.test

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