oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: luofan.zp
5
# owner group: SQL3
6
# description: collation of all expression operator
7
# Author: zhuweng.yzf
8
# ....yzf....Fri, 21 Aug 2015....17:55....
9

10
# set-up
11
--disable_warnings
12
drop table if exists coll_test;
13
--enable_warnings
14
create table coll_test(pk bigint primary key, uc varchar(10) collate utf8_general_ci, ub varchar(10) collate utf8_bin, b varbinary(10));
15
--source mysql_test/include/show_create_table_old_version.inc
16
--source mysql_test/include/show_create_table_old_version_replica2.inc
17
show create table coll_test;
18
insert into coll_test values (1314, 'abc', 'def', 'xyz');
19
select * from coll_test;
20
################################################################
21
# concat
22
select collation(concat(null)) from coll_test;
23
select collation(concat(uc, ub)) from coll_test;
24
select collation(concat(uc, b)) from coll_test;
25
select collation(concat(uc, x'41')) from coll_test;
26
select collation(concat('abc', x'41')) from coll_test;
27
select collation(concat('abc' collate utf8mb4_general_ci, x'41')) from coll_test;
28
select collation(concat(1, 2)) from coll_test;
29
# @bug collation is wrong for NULL result (zhuweng.yzf) ....yzf....Fri, 21 Aug 2015....18:17....
30
select collation(concat(1, null)) from coll_test;
31

32
# group_concat
33
select collation(group_concat(null)) from coll_test;
34
select collation(group_concat(uc, ub)) from coll_test;
35
select collation(group_concat(uc, b)) from coll_test;
36
select collation(group_concat(uc, x'41')) from coll_test;
37
select collation(group_concat('abc', x'41')) from coll_test;
38
select collation(group_concat('abc' collate utf8mb4_general_ci, x'41')) from coll_test;
39
select collation(group_concat(1, 2)) from coll_test;
40
select collation(group_concat(1, null)) from coll_test;
41

42
# concat_ws
43
select collation(concat_ws(',', null)) from coll_test;
44
select collation(concat_ws(',', uc, ub)) from coll_test;
45
select collation(concat_ws(',', uc, b)) from coll_test;
46
select collation(concat_ws(',', uc, x'41')) from coll_test;
47
select collation(concat_ws(',', 'abc', x'41')) from coll_test;
48
select collation(concat_ws(',', 'abc' collate utf8mb4_general_ci, x'41')) from coll_test;
49
select collation(concat_ws(',', 1, 2)) from coll_test;
50
select collation(concat_ws(',', 1, null)) from coll_test;
51

52
# reverse
53
select collation(reverse(null)) from coll_test;
54
select collation(reverse(uc)) from coll_test;
55
select collation(reverse(ub)) from coll_test;
56
select collation(reverse(b)) from coll_test;
57
select collation(reverse(pk)) from coll_test;
58
select collation(reverse(X'41')) from coll_test;
59

60
# lower
61
select collation(lower(null)) from coll_test;
62
select collation(lower(uc)) from coll_test;
63
select collation(lower(ub)) from coll_test;
64
select collation(lower(b)) from coll_test;
65
select collation(lower(pk)) from coll_test;
66
select collation(lower(X'41')) from coll_test;
67

68
# upper
69
select collation(upper(null)) from coll_test;
70
select collation(upper(uc)) from coll_test;
71
select collation(upper(ub)) from coll_test;
72
select collation(upper(b)) from coll_test;
73
select collation(upper(pk)) from coll_test;
74
select collation(upper(X'41')) from coll_test;
75

76
# right
77
select collation(right(null, 2)) from coll_test;
78
select collation(right(uc, 2)) from coll_test;
79
select collation(right(ub, 2)) from coll_test;
80
select collation(right(b, 2)) from coll_test;
81
select collation(right(pk, 2)) from coll_test;
82
select collation(right(X'41', 2)) from coll_test;
83

84
#substr
85
select collation(substr(null, 2)) from coll_test;
86
select collation(substr(uc, 2)) from coll_test;
87
select collation(substr(ub, 2)) from coll_test;
88
select collation(substr(b, 2)) from coll_test;
89
select collation(substr(pk, 2)) from coll_test;
90
select collation(substr(X'41', 2)) from coll_test;
91

92
#trim
93
select collation(trim('a' from null)) from coll_test;
94
select collation(trim('a' from uc)) from coll_test;
95
select collation(trim('a' from ub)) from coll_test;
96
select collation(trim('a' from b)) from coll_test;
97
select collation(trim('a' from pk)) from coll_test;
98
select collation(trim('a' from X'41')) from coll_test;
99

100
#repeat
101
select collation(repeat(null, 2)) from coll_test;
102
select collation(repeat(uc, 2)) from coll_test;
103
select collation(repeat(ub, 2)) from coll_test;
104
select collation(repeat(b, 2)) from coll_test;
105
select collation(repeat(pk, 2)) from coll_test;
106
select collation(repeat(X'41', 2)) from coll_test;
107

108
# rpad
109
select collation(rpad(null, 2, 'a')) from coll_test;
110
select collation(rpad(uc, 2, ub)) from coll_test;
111
select collation(rpad(ub, 2, b)) from coll_test;
112
select collation(rpad(b, 2, uc)) from coll_test;
113
select collation(rpad(pk, 2, uc)) from coll_test;
114
select collation(rpad(X'41', 2, uc)) from coll_test;
115

116
#replace
117
select collation(replace(null, b, 'a')) from coll_test;
118
select collation(replace(uc, b, ub)) from coll_test;
119
select collation(replace(ub, uc, ub)) from coll_test;
120
select collation(replace(uc, 'a', 'b')) from coll_test;
121
select collation(replace(pk, 1, 2)) from coll_test;
122
select collation(replace(X'41', 'a', 'b')) from coll_test;
123

124
#replace
125
select collation(replace(null, b, 'a')) from coll_test;
126
select collation(replace(uc, b, ub)) from coll_test;
127
select collation(replace(ub, uc, ub)) from coll_test;
128
select collation(replace(uc, 'a', 'b')) from coll_test;
129
select collation(replace(pk, 1, 2)) from coll_test;
130
select collation(replace(X'41', 'a', 'b')) from coll_test;
131

132
#substring_index
133
select collation(substring_index(null, b, 2)) from coll_test;
134
select collation(substring_index(uc, b, 2)) from coll_test;
135
select collation(substring_index(ub, uc, 2)) from coll_test;
136
select collation(substring_index(ub, b, 2)) from coll_test;
137
select collation(substring_index(uc, 'a', 2)) from coll_test;
138
select collation(substring_index(pk, 1, 2)) from coll_test;
139
select collation(substring_index(X'41', 'a', 2)) from coll_test;
140

141
# locate
142
select cmp_meta(locate('b' collate utf8mb4_general_ci, 'aBc' collate utf8mb4_general_ci));
143
select cmp_meta(locate('b' collate utf8mb4_bin, 'aBc' collate utf8mb4_bin));
144
select cmp_meta(locate('b', 'aBc'));
145
select cmp_meta(locate('b' collate utf8mb4_general_ci, 'aBc' collate utf8mb4_general_ci, 1));
146
select cmp_meta(locate('b' collate utf8mb4_bin, 'aBc' collate utf8mb4_bin, 1));
147
select cmp_meta(locate('b', 'aBc', 1));
148
select cmp_meta(locate(uc, ub)) from coll_test;
149
select cmp_meta(locate(uc, b)) from coll_test;
150
select cmp_meta(locate(b, b)) from coll_test;
151
select cmp_meta(locate(b, pk)) from coll_test;
152

153
# instr
154
select cmp_meta(instr('abc' collate utf8_bin, 'B' collate utf8_bin));
155
select cmp_meta(instr('abc' collate utf8_general_ci, 'B' collate utf8_general_ci));
156
select cmp_meta(instr('abc', 'B'));
157

158
# current_user
159
select collation(current_user());
160
select coercibility(current_user());
161

162
# database
163
select collation(database());
164
select coercibility(database());
165

166
# conv
167
select collation(conv(null, 10, 8));
168
select collation(conv(1024, 10, 8));
169

170
# bin
171
select collation(bin(null));
172
select collation(bin(uc)) from coll_test;
173
select collation(bin(pk))  from coll_test;
174
select collation(bin(b))  from coll_test;
175

176
# effective_tenant
177
select collation(effective_tenant());
178
select coercibility(effective_tenant());
179

180
# like
181
select collation(uc like b) from coll_test;
182
select cmp_meta(uc like b) from coll_test;
183
select cmp_meta(uc like ub) from coll_test;
184
select cmp_meta(b like b) from coll_test;
185
select cmp_meta(uc like b) from coll_test;
186

187
# cast
188
select collation(cast(uc as binary)) from coll_test;
189
select collation(cast(pk as char)) from coll_test;
190
select uc, collation(binary uc) from coll_test;
191
select collation(binary binary uc collate utf8_bin) from coll_test;
192

193
# user
194
select collation(user());
195
select coercibility(user());
196

197
# version
198
select collation(version());
199
select coercibility(version());
200

201
# unhex
202
select collation(unhex('42'));
203
select collation(unhex(null));
204

205
# regexp
206
--error 3995
207
select collation(uc regexp b) from coll_test;
208
--error 3995
209
select cmp_meta(uc regexp b) from coll_test;
210
select cmp_meta(uc regexp ub) from coll_test;
211
select cmp_meta(b regexp b) from coll_test;
212
--error 3995
213
select cmp_meta(uc regexp b) from coll_test;
214
select cmp_meta(uc regexp 'abc') from coll_test;
215

216
# quote
217
select collation(quote(uc)) from coll_test;
218
select collation(quote(ub)) from coll_test;
219
select collation(quote(b)) from coll_test;
220
select collation(quote(pk)) from coll_test;
221
select collation(quote(null)) from coll_test;
222

223
# md5
224
select collation(md5(uc)) from coll_test;
225
select collation(md5(ub)) from coll_test;
226
select collation(md5(b)) from coll_test;
227
select collation(md5(pk)) from coll_test;
228
select collation(md5(null)) from coll_test;
229

230
# dump
231
select collation(dump(null)) from coll_test;
232

233
# hex
234
select collation(hex(uc)) from coll_test;
235
select collation(hex(ub)) from coll_test;
236
select collation(hex(b)) from coll_test;
237
select collation(hex(pk)) from coll_test;
238
select collation(hex(null)) from coll_test;
239

240
# int2ip
241
select collation(int2ip(pk)) from coll_test;
242
select collation(int2ip(null)) from coll_test;
243

244
# date_format
245
SELECT collation(DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'));
246

247
# all implicit cast should use the connection_collation as the result collation
248
set collation_connection = utf8mb4_general_ci;
249
select collation(cast(1 as char));
250
SELECT collation(DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'));
251
select collation(cast('A' as char)), cast('A' as char) < 'a';
252

253
set collation_connection = utf8mb4_bin;
254
select collation(cast(1 as char));
255
SELECT collation(DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'));
256
select collation(cast('A' as char)), cast('A' as char) < 'a';
257

258
################################################################
259
# tear-down
260
drop table coll_test;
261

262

263
select CAST('Ab123人' AS char CHAR SET utf8mb4);
264
SELECT collation(CAST('Ab123人' AS char CHAR SET utf8mb4));
265
SELECT length(CAST('Ab123人' AS char CHAR SET utf8mb4));
266

267
select CAST('Ab123人' AS char CHAR SET binary);
268
SELECT collation(CAST('Ab123人' AS char CHAR SET binary));
269
SELECT length(CAST('Ab123人' AS char CHAR SET binary));
270

271
select CAST('Ab123人' AS char CHAR SET gbk);
272
SELECT collation(CAST('Ab123人' AS char CHAR SET gbk));
273
SELECT length(CAST('Ab123人' AS char CHAR SET gbk));
274

275
create table coll_test(c1 char(10) collate utf8mb4_bin, c2 char(10) collate gbk_bin, c3 char(10) char set binary);
276
insert into coll_test values('Ab123人', 'Ab123人', 'Ab123人');
277
select cast(c1 as char(20)), cast(c2 as char(20)), cast(c3 as char(20)) from coll_test;
278
select collation(cast(c1 as char(20))), collation(cast(c2 as char(20))), collation(cast(c3 as char(20))) from coll_test;
279

280

281
create or replace view v1(c1,c2,c3,c4) as select cast('abc' as char), cast('abc' as char char set utf8mb4),
282
        cast('abc' as char char set binary),  cast('abc' as binary);
283
show create view v1;
284

285
create or replace view v1(c1,c2,c3,c4) as select cast('abc' as char(10)), cast('abc' as char(10) char set utf8mb4),
286
        cast('abc' as char(10) char set binary),  cast('abc' as binary(10));
287
show create view v1;
288
drop table coll_test;
289
drop view v1;