oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: jiangxiu.wt
5
# owner group: SQL1
6
# description: regexp的测试
7

8
--disable_warnings
9
drop table if exists t1;
10
--enable_warnings
11
##
12
#--real_sleep 10
13
create table t1 (a varchar(10) primary key, b int default 0);
14
#--real_sleep 10
15
insert into t1 (a) values ('a'),('abc'),('abcd'),('hello'),('test');
16

17
--error 5813
18
select * from t1 where a regexp 'a(';
19
--error 5813
20
select * from t1 where a regexp 'a(b';
21
--error 1139
22
select * from t1 where a regexp '*';
23
--error 1139
24
select * from t1 where a regexp '+';
25
--error 1139
26
select * from t1 where a regexp '?';
27
--error 1139
28
select * from t1 where a regexp '(*a)';
29
--error 1139
30
select * from t1 where a regexp '(+a)';
31
--error 1139
32
select * from t1 where a regexp '(?a)';
33
--error 1139
34
select * from t1 where a regexp '({1}a)';
35
--error 1139
36
select * from t1 where a regexp '(a|*b)';
37
--error 1139
38
select * from t1 where a regexp '(a|+b)';
39
--error 1139
40
select * from t1 where a regexp '(a|?b)';
41
--error 1139
42
select * from t1 where a regexp '(a|{1}b)';
43

44
select * from t1 where a regexp '^*';
45

46
select * from t1 where a regexp '^+';
47

48
select * from t1 where a regexp '^?';
49

50
select * from t1 where a regexp '^{1}';
51
--error 1139
52
select * from t1 where a regexp '{1';
53
--error 1139
54
select * from t1 where a regexp '{1}';
55
--error 1139
56
select * from t1 where a regexp 'a{1';
57
--error 1139
58
select * from t1 where a regexp 'a{1a';
59
--error 1139
60
select * from t1 where a regexp 'a{1a}';
61
--error 1139
62
select * from t1 where a regexp 'a{1,x}';
63
--error 1139
64
select * from t1 where a regexp 'a{1,x';
65

66
select * from t1 where a regexp 'a{300}';
67
--error 1139
68
select * from t1 where a regexp 'a{1,0}';
69

70
select * from t1 where a regexp 'a++';
71

72
select * from t1 where a regexp 'a*+';
73
--error 1139
74
select * from t1 where a regexp 'a+*';
75
--error 1139
76
select * from t1 where a regexp 'a?*';
77

78
select * from t1 where a regexp 'a?+';
79
--error 1139
80
select * from t1 where a regexp 'a{1}{1}';
81
--error 1139
82
select * from t1 where a regexp 'a*{1}';
83
--error 1139
84
select * from t1 where a regexp 'a+{1}';
85
--error 1139
86
select * from t1 where a regexp 'a?{1}';
87
--error 1139
88
select * from t1 where a regexp 'a{1}*';
89

90
select * from t1 where a regexp 'a{1}+';
91

92
drop table t1;
93

94

95
--disable_warnings
96
drop table if exists t1;
97
--enable_warnings
98
#--real_sleep 10
99
create table t1 (a datetime primary key);
100
#--real_sleep 10
101
insert into t1 values ('2004-03-11 12:00:21');
102
select * from t1 where a regexp '2004-03-11 12:00:21';
103
select * from t1 where a regexp '2004-03-11 ';
104
drop table t1;
105

106
# A case
107
--disable_warnings
108
drop table if exists t1;
109
--enable_warnings
110
create table t1(id int primary key, name varchar(100));
111
insert into t1 values(1, 'hello');
112
insert into t1 values(2, 'hell');
113
insert into t1 values(3, 'hel');
114
insert into t1 values(4, 'hello1');
115
insert into t1 values(5, 'hell1');
116
insert into t1 values(6, 'hel1');
117
insert into t1 values(7, 'hel\n1');
118
insert into t1 values(8, 'he\bl1');
119
insert into t1 values(9, 'hel\t1');
120
insert into t1 values(10, 'hel\r\n1o');
121
insert into t1 values(11, '111 <title>Hello World</title> 222');
122

123
insert into t1 values (138, 'abc');
124
insert into t1 values (139, 'abc');
125
insert into t1 values (142, 'abc');
126
insert into t1 values (146, 'a(');
127
insert into t1 values (152, 'a)');
128
insert into t1 values (153, ')');
129
insert into t1 values (158, 'ab');
130
insert into t1 values (163, 'a^b');
131
insert into t1 values (165, 'a$b');
132
insert into t1 values (170, '""');
133
insert into t1 values (173, '""');
134
insert into t1 values (174, '""');
135
insert into t1 values (192, 'b');
136
insert into t1 values (203, 'abc');
137
insert into t1 values (272, 'abc');
138
insert into t1 values (273, 'abc');
139
insert into t1 values (287, 'ab');
140
insert into t1 values (289, 'ab');
141
insert into t1 values (291, 'aab');
142
insert into t1 values (299, 'a{,2}');
143
insert into t1 values (301, 'a{,}');
144
insert into t1 values (311, 'abcac');
145
insert into t1 values (313, 'abcac');
146
insert into t1 values (315, 'abbcac');
147
insert into t1 values (317, 'acabc');
148
insert into t1 values (319, 'acabc');
149
insert into t1 values (321, 'abcabbc');
150
insert into t1 values (323, 'abcabbc');
151
insert into t1 values (325, 'a');
152
insert into t1 values (344, 'a{b}');
153
insert into t1 values (384, '-%@a?X-');
154
insert into t1 values (385, '-%@aX0-');
155
insert into t1 values (386, 'aSSTb');
156
insert into t1 values (387, 'aNTb');
157
insert into t1 values (388, 'a019b');
158
insert into t1 values (389, 'Sa%bS');
159
insert into t1 values (390, 'AabC');
160
insert into t1 values (391, 'NaSbN');
161
insert into t1 values (392, 'S%-&T');
162
insert into t1 values (393, 'aSNTb');
163
insert into t1 values (394, 'aBCd');
164
insert into t1 values (395, 'p0f3Cq');
165
insert into t1 values (405, 'abc');
166
insert into t1 values (406, 'abd');
167
insert into t1 values (407, 'abbd');
168
insert into t1 values (409, 'aaaaabaaaabaaaabaaaab');
169
insert into t1 values (411, 'aaaaabaaaabaaaabaaaab');
170
insert into t1 values (413, 'aaaaabaaaabaaaabaaaabweeknights');
171
insert into t1 values (415, 'a12345678901234567890123456789b');
172
insert into t1 values (416, 'a123456789012345678901234567890b');
173
insert into t1 values (417, 'a1234567890123456789012345678901b');
174
insert into t1 values (418, 'a12345678901234567890123456789012b');
175
insert into t1 values (419, 'a123456789012345678901234567890123b');
176
insert into t1 values (421, 'a1234567890123456789012345678901234567890123456789012345678901234567890b');
177
insert into t1 values (423, 'xacegikmoq');
178
insert into t1 values (424, 'xacegikmoq');
179
insert into t1 values (425, 'xacegikmoqy');
180
insert into t1 values (426, 'xacegikmoqy');
181
insert into t1 values (438, 'abc');
182
insert into t1 values (439, 'aba');
183
insert into t1 values (440, 'abc');
184
insert into t1 values (441, 'abd');
185
insert into t1 values (442, 'accd');
186
insert into t1 values (443, 'weeknights');
187
insert into t1 values (444, 'weeknights');
188
insert into t1 values (445, 'xyzaaabcaababdacd');
189
insert into t1 values (446, 'aaabc');
190
insert into t1 values (452, '/*x*/');
191
insert into t1 values (454, '/*x*/y/*z*/');
192
insert into t1 values (456, '/*x*/');
193
insert into t1 values (457, '/*x*/y/*z*/');
194
insert into t1 values (459, '/*x**/y/*z*/');
195
insert into t1 values (461, '/*x*/');
196
insert into t1 values (462, '/*x*/y/*z*/');
197
insert into t1 values (463, '/*x**/y/*z*/');
198
insert into t1 values (464, '/*x****/y/*z*/');
199
insert into t1 values (465, '/*x**x*/y/*z*/');
200
insert into t1 values (466, '/*x***x/y/*z*/');
201
insert into t1 values (469, 'abcd');
202
insert into t1 values (470, 'abc');
203
insert into t1 values (471, 'abd');
204
insert into t1 values (472, 'abbd');
205
insert into t1 values (473, 'acd');
206
insert into t1 values (474, 'ad');
207
insert into t1 values (475, 'abc');
208
insert into t1 values (476, 'ac');
209
insert into t1 values (477, 'abc');
210
insert into t1 values (478, 'abbbc');
211
insert into t1 values (479, 'ac');
212
insert into t1 values (480, 'abcdef');
213
insert into t1 values (482, 'abcdefghijk');
214
insert into t1 values (483, 'abcdefghijkl');
215
insert into t1 values (484, 'abc');
216
insert into t1 values (485, 'ac');
217
insert into t1 values (486, 'abc');
218
insert into t1 values (487, 'abcc');
219
insert into t1 values (488, 'abcbc');
220
insert into t1 values (489, 'abb');
221
insert into t1 values (490, 'abb');
222
insert into t1 values (491, 'abbb');
223
insert into t1 values (492, 'abbb');
224
insert into t1 values (493, 'abcdef');
225
insert into t1 values (494, 'bc');
226
insert into t1 values (497, 'ad');
227
insert into t1 values (498, 'abcd');
228
insert into t1 values (499, 'abd');
229
insert into t1 values (500, 'abcd');
230
insert into t1 values (501, 'ad');
231
insert into t1 values (502, 'abcd');
232
insert into t1 values (503, 'ad');
233
insert into t1 values (504, 'ad');
234
insert into t1 values (505, 'abd');
235
insert into t1 values (506, 'ad');
236
insert into t1 values (507, 'abcd');
237
insert into t1 values (508, 'ad');
238
insert into t1 values (509, 'abcd');
239
insert into t1 values (510, 'abd');
240
insert into t1 values (511, 'acd');
241
insert into t1 values (512, 'abd');
242
insert into t1 values (513, 'abcd');
243
insert into t1 values (514, 'abd');
244
insert into t1 values (515, 'abcd');
245
insert into t1 values (516, 'acbd');
246
insert into t1 values (517, 'abcd');
247
insert into t1 values (518, 'abcd');
248
insert into t1 values (519, 'abcbd');
249
insert into t1 values (520, 'abcbcd');
250
insert into t1 values (521, 'abcd');
251
insert into t1 values (522, 'abcbd');
252
insert into t1 values (523, 'abd');
253
insert into t1 values (524, 'abcd');
254
insert into t1 values (567, 'A1');
255
insert into t1 values (571, 'CC11');
256
insert into t1 values (573, 'ab');
257
select * from t1 where name rlike '.*h.*';
258
select * from t1 where name rlike '.*hel.*';
259
select * from t1 where name rlike '.*hell.*';
260
select * from t1 where name regexp '.*hello.*';
261
select * from t1 where name regexp '^h.*';
262

263
select * from t1 where name rlike null;
264

265
select * from t1 where name regexp 'abc|de';
266
select * from t1 where name regexp 'a|b|c';
267
select * from t1 where name regexp 'a(b)c';
268
select * from t1 where name regexp 'a\\(';
269
select * from t1 where name regexp 'a()b';
270
select * from t1 where name regexp 'a^b';
271
select * from t1 where name regexp 'a$b';
272
select * from t1 where name regexp '$^';
273
select * from t1 where name regexp '^^';
274
select * from t1 where name regexp '$$';
275
select * from t1 where name regexp 'a*(^b$)c*';
276
select * from t1 where name regexp '()';
277
select * from t1 where name regexp 'ab+c';
278
select * from t1 where name regexp 'ab?c';
279
select * from t1 where name regexp 'a{1}b';
280
select * from t1 where name regexp 'a{1,}b';
281
select * from t1 where name regexp 'a{1,2}b';
282
--error 1139
283
select * from t1 where name regexp 'a{,2}';
284
--error 1139
285
select * from t1 where name regexp 'a{,}';
286
select * from t1 where name regexp 'ab{0,0}c';
287
select * from t1 where name regexp 'ab{0,1}c';
288
select * from t1 where name regexp 'ab{0,3}c';
289
select * from t1 where name regexp 'ab{1,1}c';
290
select * from t1 where name regexp 'ab{1,3}c';
291
select * from t1 where name regexp 'ab{2,2}c';
292
select * from t1 where name regexp 'ab{2,4}c';
293
select * from t1 where name regexp '((a{1,10}){1,10}){1,10}';
294
--error 1139
295
select * from t1 where name regexp 'a*{b}';
296
select * from t1 where name regexp '[[:alnum:]]+';
297
select * from t1 where name regexp '[[:alpha:]]+';
298
select * from t1 where name regexp '[[:blank:]]+';
299
select * from t1 where name regexp '[[:cntrl:]]+';
300
select * from t1 where name regexp '[[:digit:]]+';
301
select * from t1 where name regexp '[[:graph:]]+';
302
select * from t1 where name regexp '[[:lower:]]+';
303
select * from t1 where name regexp '[[:print:]]+';
304
select * from t1 where name regexp '[[:punct:]]+';
305
select * from t1 where name regexp '[[:space:]]+';
306
select * from t1 where name regexp '[[:upper:]]+';
307
select * from t1 where name regexp '[[:xdigit:]]+';
308
select * from t1 where name regexp 'a(((b)))c';
309
select * from t1 where name regexp 'a(b|(c))d';
310
select * from t1 where name regexp 'a(b*|c)d';
311
select * from t1 where name regexp 'a[ab]{20}';
312
select * from t1 where name regexp 'a[ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab]';
313
select * from t1 where name regexp 'a[ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab][ab](wee|week)(knights|night)';
314
select * from t1 where name regexp '12345678901234567890123456789';
315
select * from t1 where name regexp '123456789012345678901234567890';
316
select * from t1 where name regexp '1234567890123456789012345678901';
317
select * from t1 where name regexp '12345678901234567890123456789012';
318
select * from t1 where name regexp '123456789012345678901234567890123';
319
select * from t1 where name regexp '1234567890123456789012345678901234567890123456789012345678901234567890';
320
select * from t1 where name regexp '[ab][cd][ef][gh][ij][kl][mn]';
321
select * from t1 where name regexp '[ab][cd][ef][gh][ij][kl][mn][op]';
322
select * from t1 where name regexp '[ab][cd][ef][gh][ij][kl][mn][op][qr]';
323
select * from t1 where name regexp '[ab][cd][ef][gh][ij][kl][mn][op][q]';
324
select * from t1 where name regexp '[a]b[c]';
325
select * from t1 where name regexp '[a]b[a]';
326
select * from t1 where name regexp '[abc]b[abc]';
327
select * from t1 where name regexp '[abc]b[abd]';
328
select * from t1 where name regexp 'a(b?c)+d';
329
select * from t1 where name regexp '(wee|week)(knights|night)';
330
select * from t1 where name regexp '(we|wee|week|frob)(knights|night|day)';
331
select * from t1 where name regexp 'a[bc]d';
332
select * from t1 where name regexp 'a[ab]c';
333

334
select * from t1 where name regexp null;
335

336
select * from t1 where name regexp '/\\*.*\\*/';
337
select * from t1 where name regexp '/\\*.*\\*/';
338
select * from t1 where name regexp '/\\*([^*]|\\*[^/])*\\*/';
339
select * from t1 where name regexp '/\\*([^*]|\\*[^/])*\\*/';
340
select * from t1 where name regexp '/\\*([^*]|\\*[^/])*\\*/';
341
select * from t1 where name regexp '/\\*([^*]|\\*+[^*/])*\\*+/';
342
select * from t1 where name regexp '/\\*([^*]|\\*+[^*/])*\\*+/';
343
select * from t1 where name regexp '/\\*([^*]|\\*+[^*/])*\\*+/';
344
select * from t1 where name regexp '/\\*([^*]|\\*+[^*/])*\\*+/';
345
select * from t1 where name regexp '/\\*([^*]|\\*+[^*/])*\\*+/';
346
select * from t1 where name regexp '/\\*([^*]|\\*+[^*/])*\\*+/';
347
select * from t1 where name regexp 'a(b)(c)d';
348
select * from t1 where name regexp 'a(((b)))c';
349
select * from t1 where name regexp 'a(b|(c))d';
350
select * from t1 where name regexp 'a(b*|c|e)d';
351
select * from t1 where name regexp 'a(b*|c|e)d';
352
select * from t1 where name regexp 'a(b*|c|e)d';
353
select * from t1 where name regexp 'a(b?)c';
354
select * from t1 where name regexp 'a(b?)c';
355
select * from t1 where name regexp 'a(b+)c';
356
select * from t1 where name regexp 'a(b+)c';
357
select * from t1 where name regexp 'a(b*)c';
358
select * from t1 where name regexp '(a|ab)(bc([de]+)f|cde)';
359
select * from t1 where name regexp 'a(b)(c)(d)(e)(f)(g)(h)(i)(j)k';
360
select * from t1 where name regexp 'a(b)(c)(d)(e)(f)(g)(h)(i)(j)(k)l';
361
select * from t1 where name regexp 'a([bc]?)c';
362
select * from t1 where name regexp 'a([bc]?)c';
363
select * from t1 where name regexp 'a([bc]+)c';
364
select * from t1 where name regexp 'a([bc]+)c';
365
select * from t1 where name regexp 'a([bc]+)bc';
366
select * from t1 where name regexp 'a(bb+|b)b';
367
select * from t1 where name regexp 'a(bbb+|bb+|b)b';
368
select * from t1 where name regexp 'a(bbb+|bb+|b)b';
369
select * from t1 where name regexp 'a(bbb+|bb+|b)bb';
370
select * from t1 where name regexp '(.*).*';
371
select * from t1 where name regexp '(a*)*';
372
select * from t1 where name regexp 'a(b|c)*d';
373
select * from t1 where name regexp 'a(b|c)*d';
374
select * from t1 where name regexp 'a(b|c)+d';
375
select * from t1 where name regexp 'a(b|c)+d';
376
select * from t1 where name regexp 'a(b|c?)+d';
377
select * from t1 where name regexp 'a(b|c?)+d';
378
select * from t1 where name regexp 'a(b|c){0,0}d';
379
select * from t1 where name regexp 'a(b|c){0,1}d';
380
select * from t1 where name regexp 'a(b|c){0,1}d';
381
select * from t1 where name regexp 'a(b|c){0,2}d';
382
select * from t1 where name regexp 'a(b|c){0,2}d';
383
select * from t1 where name regexp 'a(b|c){0,}d';
384
select * from t1 where name regexp 'a(b|c){0,}d';
385
select * from t1 where name regexp 'a(b|c){1,1}d';
386
select * from t1 where name regexp 'a(b|c){1,1}d';
387
select * from t1 where name regexp 'a(b|c){1,2}d';
388
select * from t1 where name regexp 'a(b|c){1,2}d';
389
select * from t1 where name regexp 'a(b|c){1,}d';
390
select * from t1 where name regexp 'a(b|c){1,}d';
391
select * from t1 where name regexp 'a(b|c){2,2}d';
392
select * from t1 where name regexp 'a(b|c){2,2}d';
393
select * from t1 where name regexp 'a(b|c){2,4}d';
394
select * from t1 where name regexp 'a(b|c){2,4}d';
395
select * from t1 where name regexp 'a(b|c){2,4}d';
396
select * from t1 where name regexp 'a(b|c){2,}d';
397
select * from t1 where name regexp 'a(b|c){2,}d';
398
select * from t1 where name regexp 'a(b+|((c)*))+d';
399
select * from t1 where name regexp 'a(b+|((c)*))+d';
400
select * from t1 where name regexp '(A[1])|(A[2])|(A[3])|(A[4])|(A[5])|(A[6])|(A[7])|(A[8])|(A[9])|(A[A])';
401
select * from t1 where name regexp 'CC[13]1|a{21}[23][EO][123][Es][12]a{15}aa[34][EW]aaaaaaa[X]a';
402
select * from t1 where name regexp 'a?b';
403

404
##bug:
405
select * from t1 where name regexp 'a' "bc" '|de';
406
select * from t1 where name regexp "a" '|' "b" '|' "c";
407
select * from t1 where name regexp "a"'()'"b";
408
select * from t1 where name regexp "$" "$";
409
select * from t1 where name regexp 'a' "*" '(' "^" 'b' "$" ')' "c" '*';
410
select * from t1 where name regexp 'a' "b" "{" "0" ',' "0" '}' "c";
411
select * from t1 where name regexp '[' "[" ':' "a" "lnum" ':' "]" ']' "+";
412
select * from t1 where name regexp 'a' "(" "(" '(' "b" ')' ")" ')' 'c';
413
select * from t1 where name regexp 'a'"("'b'"|"'('"c"')'")"'d';
414
select * from t1 where name regexp 'a'""'('"b"'*'"|"'c'")"'d';
415
select * from t1 where name regexp '[ab]'"[cd]"'[ef]'"[gh]"'[ij]'"[kl]"'[mn]';
416
select * from t1 where name regexp '[a]'"b"'[c]';
417
select * from t1 where name regexp '[abc]'"b"'[abc]';
418
select * from t1 where name regexp 'a'"(b?c)"'+'"d";
419
select * from t1 where name regexp '(wee|week)'"(knights"'|'"night)";
420
select * from t1 where name regexp 'a'"[ab]"'c';
421
select * from t1 where name regexp '(a|ab)'"(bc([de]+)"'f|cde)';
422
select * from t1 where name regexp 'a(bbb'"+"'|bb'"+"'|b'")b";
423
select * from t1 where name regexp 'a'"("'b'"|"'c'")"'{'"1"','"}"'d';
424

425
--disable_warnings
426
drop table if exists t1;
427
--enable_warnings
428
create table t1 (a varchar(50) primary key)  ;
429
insert into t1 values('abcdef');
430
insert into t1 values('_bcdef');
431
insert into t1 values('a_cdef');
432
insert into t1 values('ab_def');
433
insert into t1 values('abc_ef');
434
insert into t1 values('abcd_f');
435
insert into t1 values('abcde_');
436
# should return ab_def
437
 select a as c1u from t1 where a rlike 'ab\_def';
438
drop table t1;
439

440
##bug:
441
--disable_warnings
442
drop table if exists t;
443
--enable_warnings
444

445
create table t (c1 char(20));
446
insert into t values ('');
447
select c1 regexp 'ddd' from t;
448

449

450
--disable_warnings
451
drop table if exists t1;
452
--enable_warnings
453

454
create table t1(c1 blob);
455
insert into t1 values('UNPRESS123');
456
insert into t1 values('UNPRESS456');
457
select * from t1 where c1 regexp '^U';
458

459

460

461
##bug48378677
462
set names gbk;
463
select 'a' collate gbk_bin regexp 'A';
464
select 'a' collate gbk_chinese_ci regexp 'A';
465
select 'a' regexp 'A' collate gbk_chinese_ci;
466
select 'a' regexp 'A' collate gbk_bin;
467
select 'a' collate gbk_bin regexp 'A' collate gbk_bin;
468
select 'a' collate gbk_chinese_ci regexp 'A' collate gbk_chinese_ci;
469
--error 1267
470
select 'a' collate gbk_bin regexp 'A' collate gbk_chinese_ci;
471
--error 1267
472
select 'a' collate gbk_chinese_ci regexp 'A' collate gbk_bin;
473

474
set names latin1;
475
select 'a' collate latin1_bin regexp 'A';
476
select 'a' collate latin1_swedish_ci regexp 'A';
477
select 'a' regexp 'A' collate latin1_swedish_ci;
478
select 'a' regexp 'A' collate latin1_bin;
479
select 'a' collate latin1_bin regexp 'A' collate latin1_bin;
480
select 'a' collate latin1_swedish_ci regexp 'A' collate latin1_swedish_ci;
481
--error 1267
482
select 'a' collate latin1_bin regexp 'A' collate latin1_swedish_ci;
483
--error 1267
484
select 'a' collate latin1_swedish_ci regexp 'A' collate latin1_bin;
485

486

487
##bug:
488

489
--error 0,1008
490
drop database ly;
491

492
create database ly character set GB18030;
493
use ly;
494

495
DROP TABLE IF EXISTS `table10_bigint`;
496
CREATE TABLE `table10_bigint` (
497
  `col_smallint_signed` smallint(6) DEFAULT NULL,
498
  `col_decimal_20_0_signed` decimal(20,0) DEFAULT NULL,
499
  `col_tinyint_unsigned` tinyint(3) unsigned DEFAULT NULL,
500
  `col_mediumint_signed` mediumint(9) DEFAULT NULL,
501
  `col_decimal_30_5_signed` decimal(30,5) DEFAULT NULL,
502
  `col_int` int(11) DEFAULT NULL,
503
  `col_smallint_unsigned` smallint(5) unsigned DEFAULT NULL,
504
  `col_decimal_30_5` decimal(30,5) DEFAULT NULL,
505
  `col_tinyint_signed` tinyint(4) DEFAULT NULL,
506
  `col_decimal_20_0` decimal(20,0) DEFAULT NULL,
507
  `pk` bigint(20) NOT NULL,
508
  `col_int_unsigned` int(10) unsigned DEFAULT NULL,
509
  `col_datetime` datetime DEFAULT NULL,
510
  `col_mediumint_unsigned` mediumint(8) unsigned DEFAULT NULL,
511
  `col_bigint_signed` bigint(20) DEFAULT NULL,
512
  `col_integer_unsigned` int(10) unsigned DEFAULT NULL,
513
  `col_char_30` char(30) DEFAULT NULL,
514
  `col_integer` int(11) DEFAULT NULL,
515
  `col_bigint_unsigned` bigint(20) unsigned DEFAULT NULL,
516
  `col_int_signed` int(11) DEFAULT NULL,
517
  `col_timestamp` timestamp NULL DEFAULT NULL,
518
  `col_tinyint` tinyint(4) DEFAULT NULL,
519
  `col_char_20` char(20) DEFAULT NULL,
520
  `col_decimal_20_0_unsigned` decimal(20,0) unsigned DEFAULT NULL,
521
  `col_smallint` smallint(6) DEFAULT NULL,
522
  `col_decimal_30_5_unsigned` decimal(30,5) unsigned DEFAULT NULL,
523
  `col_date` date DEFAULT NULL,
524
  `col_integer_signed` int(11) DEFAULT NULL,
525
  `col_mediumint` mediumint(9) DEFAULT NULL,
526
  `col_bigint` bigint(20) DEFAULT NULL,
527
  PRIMARY KEY (`pk`)
528
) DEFAULT CHARSET = gb18030 ROW_FORMAT = DYNAMIC ;
529

530

531
INSERT INTO `table10_bigint` VALUES (9,3,NULL,7,6.00000,NULL,NULL,NULL,NULL,8,1,4,'2007-12-25 08:02:41',2,8,NULL,'kqpbiirnugot',NULL,NULL,0,'2006-02-12 16:48:16',NULL,'get',NULL,NULL,NULL,'2006-10-20',NULL,5,3),(NULL,NULL,NULL,NULL,7.00000,7,9,NULL,9,2,2,NULL,'2000-08-08 04:39:35',7,NULL,NULL,'',5,NULL,NULL,'2003-10-07 10:09:45',1,'r',5,NULL,NULL,'2002-10-27',NULL,7,NULL),(NULL,6,NULL,NULL,1.00000,NULL,NULL,8.00000,4,8,3,NULL,'2004-08-13 10:53:30',5,8,6,'d',NULL,NULL,9,'2007-07-26 10:08:34',NULL,NULL,9,NULL,NULL,'2003-05-10',NULL,NULL,NULL),(3,2,NULL,NULL,1.00000,6,5,5.00000,NULL,NULL,4,NULL,'2000-06-15 20:10:58',NULL,NULL,0,'come',3,9,3,'2005-02-03 16:00:00',NULL,'',NULL,3,0.00000,'2001-07-11',NULL,NULL,3),(6,NULL,NULL,9,NULL,NULL,8,4.00000,8,NULL,5,NULL,'2000-11-19 22:25:40',NULL,1,6,'j',7,8,5,'2005-03-27 15:01:52',8,'w',NULL,5,NULL,'2003-02-27',6,NULL,NULL),(NULL,5,4,1,3.00000,NULL,NULL,NULL,2,6,6,NULL,'2009-03-12 00:00:00',8,NULL,4,'',6,3,NULL,'2002-05-23 11:10:50',NULL,'now',2,4,NULL,'2004-04-03',NULL,5,6),(7,NULL,4,NULL,1.00000,NULL,NULL,NULL,5,NULL,7,NULL,'2006-09-25 12:07:13',7,NULL,NULL,'z',7,NULL,9,'2001-01-07 16:00:00',0,'want',5,0,3.00000,'2009-07-12',NULL,NULL,3),(NULL,NULL,8,7,NULL,NULL,NULL,8.00000,NULL,1,8,0,'2001-10-23 00:00:00',1,6,NULL,'',NULL,8,7,'2008-02-20 03:04:52',2,'b',NULL,9,NULL,'2008-01-22',4,7,6),(NULL,9,NULL,8,7.00000,0,8,5.00000,3,8,9,7,'2002-07-27 22:52:55',4,NULL,NULL,'yes',NULL,3,NULL,'2008-02-24 08:16:18',9,'',NULL,0,NULL,'2003-11-02',6,9,NULL),(2,NULL,7,NULL,2.00000,1,NULL,2.00000,6,6,10,NULL,'2007-06-02 02:12:10',NULL,NULL,NULL,'',NULL,NULL,1,'2009-12-16 00:47:00',2,'I',NULL,3,1.00000,'2008-02-26',NULL,8,5);
532

533
set names GB18030;
534

535
SELECT TRIM( SUBSTRING( SUBSTRING( '∷ ∶ ∫ ∮ ∝ ∞ ∧ ∨ ∑ ∏ ∪ ∩ ∈ ∵ ∴ ⊥ ∥ ∠ ⌒ ⊙ √∟⊿ ㏒ ㏑ % ‰' , 1, LEAST( 228, 20 ) ) , 1 , LEAST( 228, 20 ) ) ) NOT REGEXP CONCAT( 'tu', '%' ) FROM table10_bigint WHERE col_int NOT IN ( 5387431054241955840 , 13633803446934044672 , 65066 , 45808 , 6755399441055744000 ) ORDER BY col_char_20 DESC , pk ASC LIMIT 6 OFFSET 1;
536

537
drop database ly;
538