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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
#
# Bug#30350111: UTF8MB4_0900_BIN STRINGS CANNOT BE COMPARED WITH
# UTF8MB4_0900_AI_CI STRINGS
#
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;
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;
CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
'abc' = 'abc'
'abc' = 'abc '
'ABC' = 'ABC'
'ABC' = 'ABC '
'Abc' = 'Abc'
'Abc' = 'Abc '
'abc ' = 'abc'
'abc ' = 'abc '
'ABC ' = 'ABC'
'ABC ' = 'ABC '
'Abc ' = 'Abc'
'Abc ' = 'Abc '
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;
CONCAT('''', t1.col_aici, ''' = ''', t2.col_aici, '''')
'abc' = 'abc'
'ABC' = 'ABC'
'Abc' = 'Abc'
'abc ' = 'abc '
'ABC ' = 'ABC '
'Abc ' = 'Abc '
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 HY000: Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8mb4_0900_bin,IMPLICIT) for operation '='
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;
ERROR HY000: Illegal mix of collations (utf8mb4_0900_bin,IMPLICIT) and (utf8mb4_bin,IMPLICIT) for operation '='
SELECT col_aici FROM t UNION SELECT col_bin FROM t;
col_aici
ABC
Abc
abc
SELECT col_aici FROM t UNION SELECT col_0900_bin FROM t;
col_aici
ABC
ABC
Abc
Abc
abc
abc
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t;
ERROR HY000: Illegal mix of collations for operation 'UNION'
SELECT col_0900_bin FROM t UNION SELECT col_bin FROM t;
ERROR HY000: Illegal mix of collations for operation 'UNION'
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici FROM t;
ERROR HY000: Illegal mix of collations for operation 'UNION'
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici COLLATE utf8mb4_0900_ai_ci FROM t;
col_bin
ABC
Abc
abc
abc
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici COLLATE utf8mb4_bin FROM t;
col_bin
ABC
Abc
abc
SELECT col_bin FROM t UNION SELECT col_0900_bin FROM t
UNION SELECT col_aici COLLATE utf8mb4_0900_bin FROM t;
col_bin
ABC
ABC
Abc
Abc
abc
abc
DROP TABLE t;
CREATE TABLE t(x VARCHAR(10) BINARY);
Warnings:
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
SELECT COLLATION(MAX(x)) FROM t;
COLLATION(MAX(x))
utf8mb4_bin
DROP TABLE t;
|
