Debian Codegraph @

mysql-test/suite/collations/r/binary.result

raw
1 #
2 # Bug#30350111: UTF8MB4_0900_BIN STRINGS CANNOT BE COMPARED WITH
3 # UTF8MB4_0900_AI_CI STRINGS
4 #
5 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT,
6 col_aici VARCHAR(10) COLLATE utf8mb4_0900_ai_ci,
7 col_bin VARCHAR(10) COLLATE utf8mb4_bin,
8 col_0900_bin VARCHAR(10) COLLATE utf8mb4_0900_bin);
9 INSERT INTO t(col_aici)
10 VALUES ('abc'), ('ABC'), ('Abc'), ('abc '), ('ABC '), ('Abc ');
11 UPDATE t SET col_bin = col_aici, col_0900_bin = col_aici;
12 SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
13 FROM t t1, t t2
14 WHERE t1.col_aici = t2.col_bin
15 ORDER BY t1.id, t2.id;
16 CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
17 'abc' = 'abc'
18 'abc' = 'abc '
19 'ABC' = 'ABC'
20 'ABC' = 'ABC '
21 'Abc' = 'Abc'
22 'Abc' = 'Abc '
23 'abc ' = 'abc'
24 'abc ' = 'abc '
25 'ABC ' = 'ABC'
26 'ABC ' = 'ABC '
27 'Abc ' = 'Abc'
28 'Abc ' = 'Abc '
29 SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
30 FROM t t1, t t2
31 WHERE t1.col_aici = t2.col_0900_bin
32 ORDER BY t1.id, t2.id;
33 CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
34 'abc' = 'abc'
35 'ABC' = 'ABC'
36 'Abc' = 'Abc'
37 'abc ' = 'abc '
38 'ABC ' = 'ABC '
39 'Abc ' = 'Abc '
40 SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
41 FROM t t1, t t2
42 WHERE t1.col_bin = t2.col_0900_bin
43 ORDER BY t1.id, t2.id;
44 ERROR HY000: Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8mb4_0900_bin,IMPLICIT) for operation '='
45 SELECT CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
46 FROM t t1, t t2
47 WHERE t1.col_0900_bin = t2.col_bin
48 ORDER BY t1.id, t2.id;
49 ERROR HY000: Illegal mix of collations (utf8mb4_0900_bin,IMPLICIT) and (utf8mb4_bin,IMPLICIT) for operation '='
50 SELECT col_aici FROM t UNION SELECT col_bin FROM t;
51 col_aici
52 ABC
53 Abc
54 abc
55 SELECT col_aici FROM t UNION SELECT col_0900_bin FROM t;
56 col_aici
57 ABC
58 ABC
59 Abc
60 Abc
61 abc
62 abc
63 SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t;
64 ERROR HY000: Illegal mix of collations for operation 'UNION'
65 SELECT col_0900_bin FROM t UNION SELECT col_bin FROM t;
66 ERROR HY000: Illegal mix of collations for operation 'UNION'
67 SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
68 UNION SELECT col_aici FROM t;
69 ERROR HY000: Illegal mix of collations for operation 'UNION'
70 SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
71 UNION SELECT col_aici COLLATE utf8mb4_0900_ai_ci FROM t;
72 col_bin
73 ABC
74 Abc
75 abc
76 abc
77 SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
78 UNION SELECT col_aici COLLATE utf8mb4_bin FROM t;
79 col_bin
80 ABC
81 Abc
82 abc
83 SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
84 UNION SELECT col_aici COLLATE utf8mb4_0900_bin FROM t;
85 col_bin
86 ABC
87 ABC
88 Abc
89 Abc
90 abc
91 abc
92 DROP TABLE t;
93 CREATE TABLE t(x VARCHAR(10) BINARY);
94 Warnings:
95 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
96 SELECT COLLATION(MAX(x)) FROM t;
97 COLLATION(MAX(x))
98 utf8mb4_bin
99 DROP TABLE t;