~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
# Description
2
# -----------
3
# Testing string functions
4
5
--disable_warnings
6
drop table if exists t1,t2;
7
--enable_warnings
8
9
set names latin1;
10
11
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
12
select 'hello' 'monty';
13
select length('\n\t\r\b\0\_\%\\');
14
select bit_length('\n\t\r\b\0\_\%\\');
15
select char_length('\n\t\r\b\0\_\%\\');
16
select length(_latin1'\n\t\n\b\0\\_\\%\\');
17
select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');
18
select hex(char(256));
19
select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
20
select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE'); 
21
select position(binary 'll' in 'hello'),position('a' in binary 'hello');
22
#
23
# Bug#11728 string function LEFT,
24
# strange undocumented behaviour, strict mode
25
#
26
select left('hello',null), right('hello',null);
27
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
28
select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
29
select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
30
select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
31
select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
32
select substring_index('aaaaaaaaa1','a',1);
33
select substring_index('aaaaaaaaa1','aa',1);
34
select substring_index('aaaaaaaaa1','aa',2);
35
select substring_index('aaaaaaaaa1','aa',3);
36
select substring_index('aaaaaaaaa1','aa',4);
37
select substring_index('aaaaaaaaa1','aa',5);
38
select substring_index('aaaaaaaaa1','aaa',1);
39
select substring_index('aaaaaaaaa1','aaa',2);
40
select substring_index('aaaaaaaaa1','aaa',3);
41
select substring_index('aaaaaaaaa1','aaa',4);
42
select substring_index('aaaaaaaaa1','aaaa',1);
43
select substring_index('aaaaaaaaa1','aaaa',2);
44
select substring_index('aaaaaaaaa1','1',1);
45
select substring_index('aaaaaaaaa1','a',-1);
46
select substring_index('aaaaaaaaa1','aa',-1);
47
select substring_index('aaaaaaaaa1','aa',-2);
48
select substring_index('aaaaaaaaa1','aa',-3);
49
select substring_index('aaaaaaaaa1','aa',-4);
50
select substring_index('aaaaaaaaa1','aa',-5);
51
select substring_index('aaaaaaaaa1','aaa',-1);
52
select substring_index('aaaaaaaaa1','aaa',-2);
53
select substring_index('aaaaaaaaa1','aaa',-3);
54
select substring_index('aaaaaaaaa1','aaa',-4);
55
select substring_index('the king of thethe hill','the',-2);
56
select substring_index('the king of the the hill','the',-2);
57
select substring_index('the king of the  the hill','the',-2);
58
select substring_index('the king of the  the hill',' the ',-1);
59
select substring_index('the king of the  the hill',' the ',-2);
60
select substring_index('the king of the  the hill',' ',-1);
61
select substring_index('the king of the  the hill',' ',-2);
62
select substring_index('the king of the  the hill',' ',-3);
63
select substring_index('the king of the  the hill',' ',-4);
64
select substring_index('the king of the  the hill',' ',-5);
65
select substring_index('the king of the.the hill','the',-2);
66
select substring_index('the king of thethethe.the hill','the',-3);
67
select substring_index('the king of thethethe.the hill','the',-1);
68
select substring_index('the king of the the hill','the',1);
69
select substring_index('the king of the the hill','the',2);
70
select substring_index('the king of the the hill','the',3);
71
72
select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');
73
select concat(':',trim(leading from '  left  '),':',trim(trailing from '  right  '),':');
74
select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
75
select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
76
select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
77
select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
78
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
79
80
select concat_ws(', ','monty','was here','again');
81
select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
82
select concat_ws(',','',NULL,'a');
83
SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
84
85
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
86
select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
87
select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
88
select md5('hello');
89
select crc32("123");
90
select aes_decrypt(aes_encrypt('abc','1'),'1');
91
select aes_decrypt(aes_encrypt('abc','1'),1);
92
select aes_encrypt(NULL,"a");
93
select aes_encrypt("a",NULL);
94
select aes_decrypt(NULL,"a");
95
select aes_decrypt("a",NULL);
96
select aes_decrypt("a","a");
97
select aes_decrypt(aes_encrypt("","a"),"a");
98
select repeat('monty',5),concat('*',space(5),'*');
99
select reverse('abc'),reverse('abcd');
100
select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
101
select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
102
select rpad(741653838,17,'0'),lpad(741653838,17,'0');
103
select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
104
select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
105
select rpad('STRING', 20, CONCAT('p','a','d') );
106
select lpad('STRING', 20, CONCAT('p','a','d') );
107
108
select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
109
select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0");
110
111
select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
112
select decode(encode("abcdef","monty"),"monty")="abcdef";
113
114
select quote('\'\"\\test');
115
select quote(concat('abc\'', '\\cba'));
116
select quote(1/0), quote('\0\Z');
117
select length(quote(concat(char(0),"test")));
118
select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
119
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
120
select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
121
select length(unhex(md5("abrakadabra")));
122
123
#
124
# Bug #6564: QUOTE(NULL
125
#
126
127
select concat('a', quote(NULL));
128
129
#
130
# Wrong usage of functions
131
#
132
133
select reverse("");
134
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
135
select elt(2,1),field(NULL,"a","b","c"),reverse("");
136
select locate("a","b",2),locate("","a",1);
137
select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
138
select concat("1","2")|0,concat("1",".5")+0.0;
139
select substring_index("www.tcx.se","",3);
140
select length(repeat("a",100000000)),length(repeat("a",1000*64));
141
select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
142
select position(("1" in (1,2,3)) in "01");
143
select length(repeat("a",65500)),length(concat(repeat("a",32000),repeat("a",32000))),length(replace("aaaaa","a",concat(repeat("a",10000)))),length(insert(repeat("a",40000),1,30000,repeat("b",50000)));
144
select length(repeat("a",1000000)),length(concat(repeat("a",32000),repeat("a",32000),repeat("a",32000))),length(replace("aaaaa","a",concat(repeat("a",32000)))),length(insert(repeat("a",48000),1,1000,repeat("a",48000)));
145
146
#
147
# Problem med concat
148
#
149
150
create table t1 ( domain char(50) );
151
insert into t1 VALUES ("hello.de" ), ("test.de" );
152
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';
153
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
154
drop table t1;
155
156
#
157
# Test bug in concat_ws
158
#
159
160
CREATE TABLE t1 (
161
  id int(10) unsigned NOT NULL,
162
  title varchar(255) default NULL,
163
  prio int(10) unsigned default NULL,
164
  category int(10) unsigned default NULL,
165
  program int(10) unsigned default NULL,
166
  bugdesc text,
167
  created datetime default NULL,
168
  modified timestamp NOT NULL,
169
  bugstatus int(10) unsigned default NULL,
170
  submitter int(10) unsigned default NULL
171
) ENGINE=MyISAM;
172
173
INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
174
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
175
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
176
SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
177
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
178
drop table t1;
179
180
#
181
# Test bug in AES_DECRYPT() when called with wrong argument
182
#
183
184
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;
185
INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
186
SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
187
DROP TABLE t1;
188
189
CREATE TABLE t1 (
190
  wid int(10) unsigned NOT NULL auto_increment,
191
  data_podp date default NULL,
192
  status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',
193
  PRIMARY KEY(wid)
194
);
195
196
INSERT INTO t1 VALUES (8,NULL,'real');
197
INSERT INTO t1 VALUES (9,NULL,'nowy');
198
SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;
199
DROP TABLE t1;
200
201
#
202
# test for #739
203
204
CREATE TABLE t1 (title text) ENGINE=MyISAM;
205
INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
206
INSERT INTO t1 VALUES ('House passes the CAREERS bill');
207
SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
208
DROP TABLE t1;
209
210
#
211
# test for Bug #2290 "output truncated with ELT when using DISTINCT"
212
#
213
214
CREATE TABLE t1 (i int, j int);
215
INSERT INTO t1 VALUES (1,1),(2,2);
216
SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
217
DROP TABLE t1;
218
219
#
220
# bug #3756: quote and NULL
221
#
222
223
create table t1(a char(4));
224
insert into t1 values ('one'),(NULL),('two'),('four');
225
select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;
226
drop table t1;
227
228
#
229
# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str
230
#
231
232
select trim(trailing 'foo' from 'foo');
233
select trim(leading 'foo' from 'foo');
234
235
#
236
# crashing bug with QUOTE() and LTRIM() or TRIM() fixed
237
# Bug #7495
238
#
239
240
select quote(ltrim(concat('    ', 'a')));
241
select quote(trim(concat('    ', 'a')));
242
243
# Bad results from QUOTE(). Bug #8248
244
CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
245
SELECT QUOTE('A') FROM t1;
246
DROP TABLE t1;
247
248
# Test collation and coercibility
249
#
250
251
select 1=_latin1'1';
252
select _latin1'1'=1;
253
select _latin2'1'=1;
254
select 1=_latin2'1';
255
--error 1267
256
select _latin1'1'=_latin2'1';
257
select row('a','b','c') = row('a','b','c');
258
select row('A','b','c') = row('a','b','c');
259
select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');
260
select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');
261
--error 1267
262
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');
263
264
--error 1267
265
select concat(_latin1'a',_latin2'a');
266
--error 1270
267
select concat(_latin1'a',_latin2'a',_latin5'a');
268
--error 1271
269
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
270
--error 1267
271
select concat_ws(_latin1'a',_latin2'a');
272
273
#
274
# Test FIELD() and collations
275
#
276
select FIELD('b','A','B');
277
select FIELD('B','A','B');
278
select FIELD('b' COLLATE latin1_bin,'A','B');
279
select FIELD('b','A' COLLATE latin1_bin,'B');
280
--error 1270
281
select FIELD(_latin2'b','A','B');
282
--error 1270
283
select FIELD('b',_latin2'A','B');
284
select FIELD('1',_latin2'3','2',1);
285
286
select POSITION(_latin1'B' IN _latin1'abcd');
287
select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
288
select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
289
--error 1267
290
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
291
--error 1267
292
select POSITION(_latin1'B' IN _latin2'abcd');
293
294
select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
295
296
# fix this:
297
--disable_parsing
298
select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin);
299
select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d');
300
--enable_parsing
301
302
--error 1267
303
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
304
--error 1267
305
select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
306
307
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
308
309
# fix this:
310
--disable_parsing
311
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2);
312
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2);
313
--enable_parsing
314
315
--error 1267
316
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
317
--error 1267
318
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
319
320
select _latin1'B' between _latin1'a' and _latin1'c';
321
select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
322
select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
323
select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
324
--error 1270
325
select _latin2'B' between _latin1'a' and _latin1'b';
326
--error 1270
327
select _latin1'B' between _latin2'a' and _latin1'b';
328
--error 1270
329
select _latin1'B' between _latin1'a' and _latin2'b';
330
--error 1270
331
select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
332
333
select _latin1'B' in (_latin1'a',_latin1'b');
334
select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
335
select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
336
select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
337
--error 1270
338
select _latin2'B' in (_latin1'a',_latin1'b');
339
--error 1270
340
select _latin1'B' in (_latin2'a',_latin1'b');
341
--error 1270
342
select _latin1'B' in (_latin1'a',_latin2'b');
343
--error 1270
344
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
345
--error 1270
346
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
347
348
select collation(bin(130)), coercibility(bin(130));
349
select collation(oct(130)), coercibility(oct(130));
350
select collation(conv(130,16,10)), coercibility(conv(130,16,10));
351
select collation(hex(130)), coercibility(hex(130));
352
select collation(char(130)), coercibility(hex(130));
353
select collation(format(130,10)), coercibility(format(130,10));
354
select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
355
select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
356
select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
357
select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
358
select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
359
select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
360
select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
361
select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
362
select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
363
select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
364
select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
365
select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
366
select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
367
select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
368
select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
369
select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
370
select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
371
select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
372
select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
373
select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
374
select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
375
select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
376
select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
377
select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
378
379
create table t1 
380
select
381
  bin(130),
382
  oct(130),
383
  conv(130,16,10),
384
  hex(130),
385
  char(130),
386
  left(_latin2'a',1),
387
  right(_latin2'a',1), 
388
  lcase(_latin2'a'), 
389
  ucase(_latin2'a'),
390
  substring(_latin2'a',1,1),
391
  concat(_latin2'a',_latin2'b'),
392
  lpad(_latin2'a',4,_latin2'b'),
393
  rpad(_latin2'a',4,_latin2'b'),
394
  concat_ws(_latin2'a',_latin2'b'),
395
  make_set(255,_latin2'a',_latin2'b',_latin2'c'),
396
  export_set(255,_latin2'y',_latin2'n',_latin2' '),
397
  trim(_latin2' a '),
398
  ltrim(_latin2' a '),
399
  rtrim(_latin2' a '),
400
  trim(LEADING _latin2' ' FROM _latin2' a '),
401
  trim(TRAILING _latin2' ' FROM _latin2' a '),
402
  trim(BOTH _latin2' ' FROM _latin2' a '),
403
  repeat(_latin2'a',10),
404
  reverse(_latin2'ab'),
405
  quote(_latin2'ab'),
406
  substring(_latin2'ab',1),
407
  insert(_latin2'abcd',2,3,_latin2'ef'),
408
  replace(_latin2'abcd',_latin2'b',_latin2'B'),
409
  encode('abcd','ab')
410
;
411
show create table t1;
412
drop table t1;
413
414
#
415
# Bug#9129
416
#
417
create table t1 (a char character set latin2);
418
insert into t1 values (null);
419
select charset(a), collation(a), coercibility(a) from t1;
420
drop table t1;
421
select charset(null), collation(null), coercibility(null);
422
#
423
# Make sure OUTER JOIN is not replaced with a regular joun
424
#
425
CREATE TABLE t1 (a int, b int);
426
CREATE TABLE t2 (a int, b int);
427
INSERT INTO t1 VALUES (1,1),(2,2);
428
INSERT INTO t2 VALUES (2,2),(3,3);
429
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
430
where collation(t2.a) = _utf8'binary' order by t1.a,t2.a;
431
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
432
where charset(t2.a) = _utf8'binary' order by t1.a,t2.a;
433
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
434
where coercibility(t2.a) = 2 order by t1.a,t2.a;
435
DROP TABLE t1, t2;
436
437
#
438
# test for SUBSTR
439
#
440
select SUBSTR('abcdefg',3,2);
441
select SUBSTRING('abcdefg',3,2);
442
select SUBSTR('abcdefg',-3,2);
443
select SUBSTR('abcdefg',-1,5);
444
select SUBSTR('abcdefg',0,0);
445
select SUBSTR('abcdefg',-1,-1);
446
select SUBSTR('abcdefg',1,-1);
447
448
#
449
# Test that fix_fields doesn't follow to upper level (to comparison)
450
# when an error on a lower level (in concat) has accured:
451
#
452
create table t7 (s1 char);
453
--error 1267
454
select * from t7
455
where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
456
drop table t7;
457
458
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
459
460
explain extended select md5('hello');
461
explain extended select aes_decrypt(aes_encrypt('abc','1'),'1');
462
explain extended select concat('*',space(5),'*');
463
explain extended select reverse('abc');
464
explain extended select rpad('a',4,'1');
465
explain extended select lpad('a',4,'1');
466
explain extended select concat_ws(',','',NULL,'a');
467
explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c');
468
explain extended select elt(2,1);
469
explain extended select locate("a","b",2);
470
explain extended select char(0);
471
explain extended select conv(130,16,10);
472
explain extended select hex(130);
473
explain extended select binary 'HE';
474
explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' ');
475
explain extended select FIELD('b' COLLATE latin1_bin,'A','B');
476
explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d');
477
explain extended select collation(conv(130,16,10));
478
explain extended select coercibility(conv(130,16,10));
479
explain extended select length('\n\t\r\b\0\_\%\\');
480
explain extended select bit_length('\n\t\r\b\0\_\%\\');
481
explain extended select bit_length('\n\t\r\b\0\_\%\\');
482
explain extended select concat('monty',' was here ','again');
483
explain extended select length('hello');
484
explain extended select char(ascii('h'));
485
explain extended select ord('h');
486
explain extended select quote(1/0);
487
explain extended select crc32("123");
488
explain extended select replace('aaaa','a','b');
489
explain extended select insert('txs',2,1,'hi');
490
explain extended select left(_latin2'a',1);
491
explain extended select right(_latin2'a',1);
492
explain extended select lcase(_latin2'a');
493
explain extended select ucase(_latin2'a');
494
explain extended select SUBSTR('abcdefg',3,2);
495
explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);
496
explain extended select trim(_latin2' a ');
497
explain extended select ltrim(_latin2' a ');
498
explain extended select rtrim(_latin2' a ');
499
explain extended select decode(encode(repeat("a",100000),"monty"),"monty");
500
501
#
502
# lpad returns incorrect result (Bug #2182)
503
#
504
505
SELECT lpad(12345, 5, "#");
506
507
#
508
# Problem the the CONV() function (Bug #2972)
509
#
510
 
511
SELECT conv(71, 10, 36), conv('1Z', 36, 10);
512
SELECT conv(71, 10, 37), conv('1Z', 37, 10), conv(0,1,10),conv(0,0,10), conv(0,-1,10);
513
514
#
515
# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)
516
#
517
518
create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
519
insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb');
520
create table t2 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
521
insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');
522
select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2 
523
where t2.id=t1.id order by name;
524
drop table t1, t2;
525
526
#
527
# Test case for conversion of long string value to integer (Bug #3472)
528
#
529
530
create table t1 (c1 INT, c2 INT UNSIGNED);
531
insert into t1 values ('21474836461','21474836461');
532
insert into t1 values ('-21474836461','-21474836461');
533
show warnings;
534
select * from t1;
535
drop table t1;
536
537
#
538
# Bug #4878: LEFT() in integer/float context
539
#
540
541
select left(1234, 3) + 0;
542
543
#
544
# Bug #7101: bug with LEFT() when used as a field in GROUP BY aggregation
545
#
546
create table t1 (a int not null primary key, b varchar(40), c datetime);
547
insert into t1 (a,b,c) values (1,'Tom','2004-12-10 12:13:14'),(2,'ball games','2004-12-10 12:13:14'), (3,'Basil','2004-12-10 12:13:14'), (4,'Dean','2004-12-10 12:13:14'),(5,'Ellis','2004-12-10 12:13:14'), (6,'Serg','2004-12-10 12:13:14'), (7,'Sergei','2004-12-10 12:13:14'),(8,'Georg','2004-12-10 12:13:14'),(9,'Salle','2004-12-10 12:13:14'),(10,'Sinisa','2004-12-10 12:13:14'); 
548
select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
549
drop table t1;
550
551
#
552
# Bug#7455 unexpected result: TRIM(<NULL> FROM <whatever>) gives NOT NULL
553
# According to ANSI if one of the TRIM arguments is NULL, then the result
554
# must be NULL too.
555
#
556
select trim(null from 'kate') as "must_be_null";
557
select trim('xyz' from null) as "must_be_null";
558
select trim(leading NULL from 'kate') as "must_be_null";
559
select trim(trailing NULL from 'xyz') as "must_be_null";
560
561
#
562
# Bug #7751 - conversion for a bigint unsigned constant 
563
#
564
565
CREATE TABLE t1 (
566
  id int(11) NOT NULL auto_increment,
567
  a bigint(20) unsigned default NULL,
568
  PRIMARY KEY  (id)
569
) ENGINE=MyISAM;
570
571
INSERT INTO t1 VALUES
572
('0','16307858876001849059');
573
574
SELECT CONV('e251273eb74a8ee3', 16, 10);
575
576
EXPLAIN 
577
SELECT id
578
  FROM t1
579
  WHERE a = 16307858876001849059;
580
581
EXPLAIN 
582
  SELECT id
583
  FROM t1
584
  WHERE a = CONV('e251273eb74a8ee3', 16, 10);
585
586
DROP TABLE t1;
587
588
#
589
# Bug #6317: string function CHAR, parameter is NULL, wrong result
590
#
591
SELECT CHAR(NULL,121,83,81,'76') as my_column;
592
SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;
593
#
594
# Test case for bug #8669: null aes_decrypt result in order by query
595
#
596
597
CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL);
598
CREATE TABLE t2 (id int NOT NULL UNIQUE);
599
INSERT INTO t2 VALUES (1),(2);
600
INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar'));
601
INSERT INTO t1 VALUES (2, 'not valid');
602
603
SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id;
604
SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id
605
 ORDER BY t1.id;
606
607
DROP TABLE t1, t2;
608
609
#
610
# Bug #10944: Mishandling of NULL arguments in FIELD()
611
#
612
select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0);
613
select field(NULL,1,2,NULL), field(NULL,1,2,0);
614
615
#
616
# Bug #10124: access by integer index with a string key that is not a number  
617
#
618
619
CREATE TABLE t1 (str varchar(20) PRIMARY KEY);
620
CREATE TABLE t2 (num int primary key);
621
INSERT INTO t1 VALUES ('notnumber');
622
INSERT INTO t2 VALUES (0), (1); 
623
624
SELECT * FROM t1, t2 WHERE num=str;
625
SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
626
627
DROP TABLE t1,t2;
628
629
#
630
# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS  
631
#
632
633
CREATE TABLE t1(
634
  id int(11) NOT NULL auto_increment,
635
  pc int(11) NOT NULL default '0',
636
  title varchar(20) default NULL,
637
  PRIMARY KEY (id)
638
);
639
640
INSERT INTO t1 VALUES
641
  (1, 0, 'Main'),
642
  (2, 1, 'Toys'),
643
  (3, 1, 'Games');
644
645
SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
646
  FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
647
          LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
648
SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
649
  FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
650
          LEFT JOIN t1 AS t3 ON t2.pc=t3.id
651
    WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%';
652
653
DROP TABLE t1;
654
655
656
CREATE TABLE t1(
657
  trackid     int(10) unsigned NOT NULL auto_increment,
658
  trackname   varchar(100) NOT NULL default '',
659
  PRIMARY KEY (trackid)
660
);
661
662
CREATE TABLE t2(
663
  artistid    int(10) unsigned NOT NULL auto_increment,
664
  artistname  varchar(100) NOT NULL default '',
665
  PRIMARY KEY (artistid)
666
);
667
668
CREATE TABLE t3(
669
  trackid     int(10) unsigned NOT NULL,
670
  artistid    int(10) unsigned NOT NULL,
671
  PRIMARY KEY (trackid,artistid)
672
);
673
674
INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York');
675
INSERT INTO t2 VALUES (1, 'Vernon Duke');
676
INSERT INTO t3 VALUES (1,1);
677
678
SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname
679
  FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid
680
          LEFT JOIN t2 ON t2.artistid=t3.artistid
681
    WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%';
682
683
DROP TABLE t1,t2,t3;
684
685
#
686
# Correct length reporting from substring() (BUG#10269)
687
#
688
create table t1 (b varchar(5));
689
insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');
690
select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1;
691
select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t;
692
drop table t1;
693
694
#
695
# Bug #9854  hex() and out of range handling
696
#
697
select hex(29223372036854775809), hex(-29223372036854775809);
698
699
#
700
# Bug #11311: Incorrect length returned from LPAD() and RPAD()
701
#
702
create table t1 (i int);
703
insert into t1 values (1000000000),(1);
704
--enable_metadata
705
select lpad(i, 7, ' ') as t from t1;
706
select rpad(i, 7, ' ') as t from t1;
707
--disable_metadata
708
drop table t1;
709
710
#
711
# Bug #10418: LOAD_FILE does not behave like in manual if file does not exist
712
#
713
714
select load_file("lkjlkj");
715
select ifnull(load_file("lkjlkj"),"it's null");
716
717
#
718
# Bug#15351: Wrong collation used for comparison of md5() and sha()
719
# parameter can lead to a wrong result.
720
#
721
create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
722
insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
723
select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
724
select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
725
select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
726
select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
727
drop table t1;
728
729
#
730
# Bug#18243: REVERSE changes its argument
731
#
732
733
CREATE TABLE t1 (a varchar(10));
734
INSERT INTO t1 VALUES ('abc'), ('xyz');
735
736
SELECT a, CONCAT(a,' ',a) AS c FROM t1
737
  HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
738
739
SELECT a, CONCAT(a,' ',a) AS c FROM t1
740
  HAVING LEFT(CONCAT(a,' ',a),
741
              LENGTH(CONCAT(a,' ',a))-
742
                     INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
743
744
DROP TABLE t1;
745
746
#
747
# Bug#17526: WRONG PRINT for TRIM FUNCTION with two arguments
748
#
749
750
CREATE TABLE t1 (s varchar(10));
751
INSERT INTO t1 VALUES ('yadda'), ('yaddy');
752
753
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';
754
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
755
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';
756
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';
757
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
758
759
DROP TABLE t1;
760
 
761
#
762
# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly
763
#
764
create table t1(f1 varchar(4));
765
explain extended select encode(f1,'zxcv') as 'enc' from t1;
766
explain extended select decode(f1,'zxcv') as 'enc' from t1;
767
drop table t1;
768
769
#
770
# Bug #31758 inet_ntoa, oct, crashes server with null + filesort 
771
#
772
create table t1 (a bigint not null)engine=myisam;
773
insert into t1 set a = 1024*1024*1024*4;
774
delete from t1 order by (inet_ntoa(a)) desc limit 10;
775
drop table t1;
776
create table t1 (a char(36) not null)engine=myisam;
777
insert ignore into t1 set a = ' ';
778
insert ignore into t1 set a = ' ';
779
select * from t1 order by (oct(a));
780
drop table t1;
781
782
--echo End of 4.1 tests
783
784
#
785
# Bug #13361: SELECT FORMAT(<decimal field with null>, 2) crashes
786
#
787
create table t1 (d decimal default null);
788
insert into t1 values (null);
789
select format(d, 2) from t1;
790
drop table t1;
791
792
#
793
# Bug #14676: substring_index() returns incorrect results
794
#
795
create table t1 (c varchar(40));
796
insert into t1 values ('y,abc'),('y,abc');
797
select c, substring_index(lcase(c), @q:=',', -1) as res from t1;
798
drop table t1;
799
800
#
801
# Bug #17043: Casting trimmed string to decimal loses precision
802
#
803
select cast(rtrim('  20.06 ') as decimal(19,2));
804
select cast(ltrim('  20.06 ') as decimal(19,2));
805
select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
806
807
#
808
# Bug #13975: "same string" + 0 has 2 different results
809
#
810
select conv("18383815659218730760",10,10) + 0;
811
select "18383815659218730760" + 0;
812
813
#
814
# Bug #21698: substitution of a string field for a constant under a function 
815
#
816
817
CREATE TABLE t1 (code varchar(10));
818
INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
819
820
SELECT ASCII(code), code FROM t1 WHERE code='A12';
821
SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
822
823
INSERT INTO t1 VALUES ('a12 '), ('A12  ');
824
825
SELECT LENGTH(code), code FROM t1 WHERE code='A12';
826
SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
827
828
ALTER TABLE t1 ADD INDEX (code);
829
CREATE TABLE t2 (id varchar(10) PRIMARY KEY);
830
INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14');
831
832
SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id 
833
  WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00');
834
EXPLAIN EXTENDED 
835
SELECT * FROM t1 INNER JOIN t2 ON code=id 
836
  WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
837
838
DROP TABLE t1,t2;
839
840
#
841
# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions
842
#
843
844
select encode(NULL, NULL);
845
select encode("data", NULL);
846
select encode(NULL, "password");
847
848
select decode(NULL, NULL);
849
select decode("data", NULL);
850
select decode(NULL, "password");
851
852
select format(NULL, NULL);
853
select format(pi(), NULL);
854
select format(NULL, 2);
855
856
select benchmark(NULL, NULL);
857
select benchmark(0, NULL);
858
select benchmark(100, NULL);
859
select benchmark(NULL, 1+1);
860
861
#
862
# Bug #20752: BENCHMARK with many iterations returns too quickly
863
#
864
865
# not a string, but belongs with the above Bug#22684
866
select benchmark(-1, 1);
867
868
#
869
# Please note:
870
# 1) The collation of the password is irrelevant, the encryption uses
871
# the binary representation of the string without charset/collation.
872
# 2) These tests can not print the encoded text directly, because it's binary,
873
# and doing this would cause problems with source control.
874
# Instead, an md5() checksum is used, to verify the result indirectly.
875
# 3) Each md5() result must be identical.
876
# 4) The md5() result must never change, and must be stable across releases.
877
#
878
set @password="password";
879
set @my_data="clear text to encode";
880
select md5(encode(@my_data, "password"));
881
select md5(encode(@my_data, _utf8 "password"));
882
select md5(encode(@my_data, binary "password"));
883
select md5(encode(@my_data, _latin1 "password"));
884
select md5(encode(@my_data, _koi8r "password"));
885
select md5(encode(@my_data, (select "password" from dual)));
886
select md5(encode(@my_data, concat("pass", "word")));
887
select md5(encode(@my_data, @password));
888
889
set @my_data="binary encoded data";
890
select md5(decode(@my_data, "password"));
891
select md5(decode(@my_data, _utf8 "password"));
892
select md5(decode(@my_data, binary "password"));
893
select md5(decode(@my_data, _latin1 "password"));
894
select md5(decode(@my_data, _koi8r "password"));
895
select md5(decode(@my_data, (select "password" from dual)));
896
select md5(decode(@my_data, concat("pass", "word")));
897
select md5(decode(@my_data, @password));
898
899
set @dec=5;
900
select format(pi(), (1+1));
901
select format(pi(), (select 3 from dual));
902
select format(pi(), @dec);
903
904
set @bench_count=10;
905
select benchmark(10, pi());
906
select benchmark(5+5, pi());
907
select benchmark((select 10 from dual), pi());
908
select benchmark(@bench_count, pi());
909
910
911
#
912
# Bug #10963
913
# 4294967296 18446744073709551616
914
915
select locate('he','hello',-2);
916
select locate('lo','hello',-4294967295);
917
select locate('lo','hello',4294967295);
918
select locate('lo','hello',-4294967296);
919
select locate('lo','hello',4294967296);
920
select locate('lo','hello',-4294967297);
921
select locate('lo','hello',4294967297);
922
select locate('lo','hello',-18446744073709551615);
923
select locate('lo','hello',18446744073709551615);
924
select locate('lo','hello',-18446744073709551616);
925
select locate('lo','hello',18446744073709551616);
926
select locate('lo','hello',-18446744073709551617);
927
select locate('lo','hello',18446744073709551617);
928
929
select left('hello', 10);
930
select left('hello', 0);
931
select left('hello', -1);
932
select left('hello', -4294967295);
933
select left('hello', 4294967295);
934
select left('hello', -4294967296);
935
select left('hello', 4294967296);
936
select left('hello', -4294967297);
937
select left('hello', 4294967297);
938
select left('hello', -18446744073709551615);
939
select left('hello', 18446744073709551615);
940
select left('hello', -18446744073709551616);
941
select left('hello', 18446744073709551616);
942
select left('hello', -18446744073709551617);
943
select left('hello', 18446744073709551617);
944
945
select right('hello', 10);
946
select right('hello', 0);
947
select right('hello', -1);
948
select right('hello', -4294967295);
949
select right('hello', 4294967295);
950
select right('hello', -4294967296);
951
select right('hello', 4294967296);
952
select right('hello', -4294967297);
953
select right('hello', 4294967297);
954
select right('hello', -18446744073709551615);
955
select right('hello', 18446744073709551615);
956
select right('hello', -18446744073709551616);
957
select right('hello', 18446744073709551616);
958
select right('hello', -18446744073709551617);
959
select right('hello', 18446744073709551617);
960
961
select substring('hello', 2, -1);
962
963
select substring('hello', -1, 1);
964
select substring('hello', -2, 1);
965
select substring('hello', -4294967295, 1);
966
select substring('hello', 4294967295, 1);
967
select substring('hello', -4294967296, 1);
968
select substring('hello', 4294967296, 1);
969
select substring('hello', -4294967297, 1);
970
select substring('hello', 4294967297, 1);
971
select substring('hello', -18446744073709551615, 1);
972
select substring('hello', 18446744073709551615, 1);
973
select substring('hello', -18446744073709551616, 1);
974
select substring('hello', 18446744073709551616, 1);
975
select substring('hello', -18446744073709551617, 1);
976
select substring('hello', 18446744073709551617, 1);
977
select substring('hello', 1, -1);
978
select substring('hello', 1, -4294967295);
979
select substring('hello', 1, 4294967295);
980
select substring('hello', 1, -4294967296);
981
select substring('hello', 1, 4294967296);
982
select substring('hello', 1, -4294967297);
983
select substring('hello', 1, 4294967297);
984
select substring('hello', 1, -18446744073709551615);
985
select substring('hello', 1, 18446744073709551615);
986
select substring('hello', 1, -18446744073709551616);
987
select substring('hello', 1, 18446744073709551616);
988
select substring('hello', 1, -18446744073709551617);
989
select substring('hello', 1, 18446744073709551617);
990
select substring('hello', -1, -1);
991
select substring('hello', -4294967295, -4294967295);
992
select substring('hello', 4294967295, 4294967295);
993
select substring('hello', -4294967296, -4294967296);
994
select substring('hello', 4294967296, 4294967296);
995
select substring('hello', -4294967297, -4294967297);
996
select substring('hello', 4294967297, 4294967297);
997
select substring('hello', -18446744073709551615, -18446744073709551615);
998
select substring('hello', 18446744073709551615, 18446744073709551615);
999
select substring('hello', -18446744073709551616, -18446744073709551616);
1000
select substring('hello', 18446744073709551616, 18446744073709551616);
1001
select substring('hello', -18446744073709551617, -18446744073709551617);
1002
select substring('hello', 18446744073709551617, 18446744073709551617);
1003
1004
select insert('hello', -1, 1, 'hi');
1005
select insert('hello', -4294967295, 1, 'hi');
1006
select insert('hello', 4294967295, 1, 'hi');
1007
select insert('hello', -4294967296, 1, 'hi');
1008
select insert('hello', 4294967296, 1, 'hi');
1009
select insert('hello', -4294967297, 1, 'hi');
1010
select insert('hello', 4294967297, 1, 'hi');
1011
select insert('hello', -18446744073709551615, 1, 'hi');
1012
select insert('hello', 18446744073709551615, 1, 'hi');
1013
select insert('hello', -18446744073709551616, 1, 'hi');
1014
select insert('hello', 18446744073709551616, 1, 'hi');
1015
select insert('hello', -18446744073709551617, 1, 'hi');
1016
select insert('hello', 18446744073709551617, 1, 'hi');
1017
select insert('hello', 1, -1, 'hi');
1018
select insert('hello', 1, -4294967295, 'hi');
1019
select insert('hello', 1, 4294967295, 'hi');
1020
select insert('hello', 1, -4294967296, 'hi');
1021
select insert('hello', 1, 4294967296, 'hi');
1022
select insert('hello', 1, -4294967297, 'hi');
1023
select insert('hello', 1, 4294967297, 'hi');
1024
select insert('hello', 1, -18446744073709551615, 'hi');
1025
select insert('hello', 1, 18446744073709551615, 'hi');
1026
select insert('hello', 1, -18446744073709551616, 'hi');
1027
select insert('hello', 1, 18446744073709551616, 'hi');
1028
select insert('hello', 1, -18446744073709551617, 'hi');
1029
select insert('hello', 1, 18446744073709551617, 'hi');
1030
select insert('hello', -1, -1, 'hi');
1031
select insert('hello', -4294967295, -4294967295, 'hi');
1032
select insert('hello', 4294967295, 4294967295, 'hi');
1033
select insert('hello', -4294967296, -4294967296, 'hi');
1034
select insert('hello', 4294967296, 4294967296, 'hi');
1035
select insert('hello', -4294967297, -4294967297, 'hi');
1036
select insert('hello', 4294967297, 4294967297, 'hi');
1037
select insert('hello', -18446744073709551615, -18446744073709551615, 'hi');
1038
select insert('hello', 18446744073709551615, 18446744073709551615, 'hi');
1039
select insert('hello', -18446744073709551616, -18446744073709551616, 'hi');
1040
select insert('hello', 18446744073709551616, 18446744073709551616, 'hi');
1041
select insert('hello', -18446744073709551617, -18446744073709551617, 'hi');
1042
select insert('hello', 18446744073709551617, 18446744073709551617, 'hi');
1043
1044
select repeat('hello', -1);
1045
select repeat('hello', -4294967295);
1046
select repeat('hello', 4294967295);
1047
select repeat('hello', -4294967296);
1048
select repeat('hello', 4294967296);
1049
select repeat('hello', -4294967297);
1050
select repeat('hello', 4294967297);
1051
select repeat('hello', -18446744073709551615);
1052
select repeat('hello', 18446744073709551615);
1053
select repeat('hello', -18446744073709551616);
1054
select repeat('hello', 18446744073709551616);
1055
select repeat('hello', -18446744073709551617);
1056
select repeat('hello', 18446744073709551617);
1057
1058
select space(-1);
1059
select space(-4294967295);
1060
select space(4294967295);
1061
select space(-4294967296);
1062
select space(4294967296);
1063
select space(-4294967297);
1064
select space(4294967297);
1065
select space(-18446744073709551615);
1066
select space(18446744073709551615);
1067
select space(-18446744073709551616);
1068
select space(18446744073709551616);
1069
select space(-18446744073709551617);
1070
select space(18446744073709551617);
1071
1072
select rpad('hello', -1, '1');
1073
select rpad('hello', -4294967295, '1');
1074
select rpad('hello', 4294967295, '1');
1075
select rpad('hello', -4294967296, '1');
1076
select rpad('hello', 4294967296, '1');
1077
select rpad('hello', -4294967297, '1');
1078
select rpad('hello', 4294967297, '1');
1079
select rpad('hello', -18446744073709551615, '1');
1080
select rpad('hello', 18446744073709551615, '1');
1081
select rpad('hello', -18446744073709551616, '1');
1082
select rpad('hello', 18446744073709551616, '1');
1083
select rpad('hello', -18446744073709551617, '1');
1084
select rpad('hello', 18446744073709551617, '1');
1085
1086
select lpad('hello', -1, '1');
1087
select lpad('hello', -4294967295, '1');
1088
select lpad('hello', 4294967295, '1');
1089
select lpad('hello', -4294967296, '1');
1090
select lpad('hello', 4294967296, '1');
1091
select lpad('hello', -4294967297, '1');
1092
select lpad('hello', 4294967297, '1');
1093
select lpad('hello', -18446744073709551615, '1');
1094
select lpad('hello', 18446744073709551615, '1');
1095
select lpad('hello', -18446744073709551616, '1');
1096
select lpad('hello', 18446744073709551616, '1');
1097
select lpad('hello', -18446744073709551617, '1');
1098
select lpad('hello', 18446744073709551617, '1');
1099
1100
1101
#
1102
# BUG#17047: CHAR() and IN() can return NULL without signaling NULL
1103
# result
1104
#
1105
SET @orig_sql_mode = @@SQL_MODE;
1106
SET SQL_MODE=traditional;
1107
1108
SELECT CHAR(0xff,0x8f USING utf8);
1109
SELECT CHAR(0xff,0x8f USING utf8) IS NULL;
1110
1111
SET SQL_MODE=@orig_sql_mode;
1112
1113
#
1114
# Bug #24947: problem with some string function with unsigned int parameters
1115
#
1116
1117
select substring('abc', cast(2 as unsigned int));
1118
select repeat('a', cast(2 as unsigned int));
1119
select rpad('abc', cast(5 as unsigned integer), 'x');
1120
select lpad('abc', cast(5 as unsigned integer), 'x');
1121
1122
#
1123
# Bug#15757: Wrong SUBSTRING() result when a tmp table was employed.
1124
#
1125
create table t1(f1 longtext);
1126
insert into t1 values ("123"),("456");
1127
select substring(f1,1,1) from t1 group by 1;
1128
create table t2(f1 varchar(3));
1129
insert into t1 values ("123"),("456");
1130
select substring(f1,4,1), substring(f1,-4,1) from t2;
1131
drop table t1,t2;
1132
1133
#
1134
# Bug #25197 :repeat function returns null when using table field directly as count
1135
#
1136
1137
--disable_warnings
1138
DROP TABLE IF EXISTS t1;
1139
--enable_warnings
1140
1141
CREATE TABLE `t1` (
1142
  `id` varchar(20) NOT NULL,
1143
  `tire` tinyint(3) unsigned NOT NULL,
1144
  PRIMARY KEY (`id`)
1145
);
1146
1147
INSERT INTO `t1` (`id`, `tire`) VALUES ('A', 0), ('B', 1),('C', 2);
1148
1149
SELECT REPEAT( '#', tire ) AS A,
1150
       REPEAT( '#', tire % 999 ) AS B, tire FROM `t1`;
1151
1152
SELECT REPEAT('0', CAST(0 AS UNSIGNED));
1153
SELECT REPEAT('0', -2);
1154
SELECT REPEAT('0', 2);
1155
1156
DROP TABLE t1;
1157
1158
#
1159
# Bug #26537: UNHEX() IS NULL comparison fails
1160
#
1161
SELECT UNHEX('G');
1162
SELECT UNHEX('G') IS NULL;
1163
1164
#
1165
# Bug #26281: INSERT() function mishandles NUL on boundary condition
1166
#
1167
SELECT INSERT('abc', 3, 3, '1234');
1168
SELECT INSERT('abc', 4, 3, '1234');
1169
SELECT INSERT('abc', 5, 3, '1234');
1170
SELECT INSERT('abc', 6, 3, '1234');
1171
1172
#
1173
# Bug #27530: Grouping on crc32, or create table select crc32
1174
#
1175
CREATE TABLE t1 (a INT);
1176
CREATE VIEW v1 AS SELECT CRC32(a) AS C FROM t1;
1177
1178
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
1179
SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1;
1180
SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
1181
SELECT * FROM (SELECT CRC32(a) FROM t1) t2;
1182
CREATE TABLE t2 SELECT CRC32(a) FROM t1;
1183
desc t2;
1184
SELECT * FROM v1;
1185
SELECT * FROM (SELECT * FROM v1) x;
1186
1187
DROP TABLE t1, t2;
1188
DROP VIEW v1;
1189
1190
#
1191
# Bug #27932: LOCATE with argument evaluated to NULL
1192
#
1193
1194
SELECT LOCATE('foo', NULL);
1195
SELECT LOCATE(NULL, 'o');
1196
SELECT LOCATE(NULL, NULL);
1197
SELECT LOCATE('foo', NULL) IS NULL;
1198
SELECT LOCATE(NULL, 'o') IS NULL;
1199
SELECT LOCATE(NULL, NULL) IS NULL;
1200
SELECT ISNULL(LOCATE('foo', NULL));
1201
SELECT ISNULL(LOCATE(NULL, 'o'));
1202
SELECT ISNULL(LOCATE(NULL, NULL));
1203
SELECT LOCATE('foo', NULL) <=> NULL;
1204
SELECT LOCATE(NULL, 'o') <=> NULL;
1205
SELECT LOCATE(NULL, NULL) <=> NULL;
1206
1207
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10));
1208
1209
INSERT INTO t1 VALUES (1, 'foo', 'o');
1210
INSERT INTO t1 VALUES (2, 'foo', NULL);
1211
INSERT INTO t1 VALUES (3, NULL, 'o');
1212
INSERT INTO t1 VALUES (4, NULL, NULL);
1213
1214
SELECT id, LOCATE(a,p) FROM t1;
1215
SELECT id, LOCATE(a,p) IS NULL FROM t1;
1216
SELECT id, ISNULL(LOCATE(a,p)) FROM t1;
1217
SELECT id, LOCATE(a,p) <=> NULL FROM t1;
1218
SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL;
1219
SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL; 
1220
1221
DROP TABLE t1;
1222
1223
#
1224
# Bug #27130: SUBSTR with UNSIGNED 0 as the last argument
1225
#
1226
1227
SELECT SUBSTR('foo',1,0);
1228
SELECT SUBSTR('foo',1,CAST(0 AS SIGNED));
1229
SELECT SUBSTR('foo',1,CAST(0 AS UNSIGNED));
1230
1231
CREATE TABLE t1 (a varchar(10), len int unsigned);
1232
INSERT INTO t1 VALUES ('bar', 2), ('foo', 0);
1233
1234
SELECT SUBSTR(a,1,len) FROM t1;
1235
1236
DROP TABLE t1; 
1237
1238
#
1239
# Bug #28850: Potential bugs related to the return type of the CHAR function
1240
#
1241
1242
CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1;
1243
SELECT HEX(c1) from t1;
1244
DROP TABLE t1;
1245
1246
--echo End of 5.0 tests