3
# Testing string functions
6
drop table if exists t1,t2;
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');
23
# Bug#11728 string function LEFT,
24
# strange undocumented behaviour, strict mode
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);
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");
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)), '"');
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') ;
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') );
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");
111
select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
112
select decode(encode("abcdef","monty"),"monty")="abcdef";
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")));
124
# Bug #6564: QUOTE(NULL
127
select concat('a', quote(NULL));
130
# Wrong usage of functions
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)));
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';
157
# Test bug in concat_ws
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,
167
created datetime default NULL,
168
modified timestamp NOT NULL,
169
bugstatus int(10) unsigned default NULL,
170
submitter int(10) unsigned default NULL
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;
181
# Test bug in AES_DECRYPT() when called with wrong argument
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");
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',
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;
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;
211
# test for Bug #2290 "output truncated with ELT when using DISTINCT"
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;
220
# bug #3756: quote and NULL
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;
229
# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str
232
select trim(trailing 'foo' from 'foo');
233
select trim(leading 'foo' from 'foo');
236
# crashing bug with QUOTE() and LTRIM() or TRIM() fixed
240
select quote(ltrim(concat(' ', 'a')));
241
select quote(trim(concat(' ', 'a')));
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;
248
# Test collation and coercibility
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');
262
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');
265
select concat(_latin1'a',_latin2'a');
267
select concat(_latin1'a',_latin2'a',_latin5'a');
269
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
271
select concat_ws(_latin1'a',_latin2'a');
274
# Test FIELD() and collations
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');
281
select FIELD(_latin2'b','A','B');
283
select FIELD('b',_latin2'A','B');
284
select FIELD('1',_latin2'3','2',1);
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');
290
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
292
select POSITION(_latin1'B' IN _latin2'abcd');
294
select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
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');
303
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
305
select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
307
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
311
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2);
312
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2);
316
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
318
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
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;
325
select _latin2'B' between _latin1'a' and _latin1'b';
327
select _latin1'B' between _latin2'a' and _latin1'b';
329
select _latin1'B' between _latin1'a' and _latin2'b';
331
select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
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);
338
select _latin2'B' in (_latin1'a',_latin1'b');
340
select _latin1'B' in (_latin2'a',_latin1'b');
342
select _latin1'B' in (_latin1'a',_latin2'b');
344
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
346
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
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'));
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' '),
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'),
406
substring(_latin2'ab',1),
407
insert(_latin2'abcd',2,3,_latin2'ef'),
408
replace(_latin2'abcd',_latin2'b',_latin2'B'),
411
show create table t1;
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;
421
select charset(null), collation(null), coercibility(null);
423
# Make sure OUTER JOIN is not replaced with a regular joun
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;
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);
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:
452
create table t7 (s1 char);
455
where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
458
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
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");
502
# lpad returns incorrect result (Bug #2182)
505
SELECT lpad(12345, 5, "#");
508
# Problem the the CONV() function (Bug #2972)
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);
515
# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)
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;
527
# Test case for conversion of long string value to integer (Bug #3472)
530
create table t1 (c1 INT, c2 INT UNSIGNED);
531
insert into t1 values ('21474836461','21474836461');
532
insert into t1 values ('-21474836461','-21474836461');
538
# Bug #4878: LEFT() in integer/float context
541
select left(1234, 3) + 0;
544
# Bug #7101: bug with LEFT() when used as a field in GROUP BY aggregation
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;
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
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";
562
# Bug #7751 - conversion for a bigint unsigned constant
566
id int(11) NOT NULL auto_increment,
567
a bigint(20) unsigned default NULL,
571
INSERT INTO t1 VALUES
572
('0','16307858876001849059');
574
SELECT CONV('e251273eb74a8ee3', 16, 10);
579
WHERE a = 16307858876001849059;
584
WHERE a = CONV('e251273eb74a8ee3', 16, 10);
589
# Bug #6317: string function CHAR, parameter is NULL, wrong result
591
SELECT CHAR(NULL,121,83,81,'76') as my_column;
592
SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;
594
# Test case for bug #8669: null aes_decrypt result in order by query
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');
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
610
# Bug #10944: Mishandling of NULL arguments in FIELD()
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);
616
# Bug #10124: access by integer index with a string key that is not a number
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);
624
SELECT * FROM t1, t2 WHERE num=str;
625
SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
630
# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS
634
id int(11) NOT NULL auto_increment,
635
pc int(11) NOT NULL default '0',
636
title varchar(20) default NULL,
640
INSERT INTO t1 VALUES
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%';
657
trackid int(10) unsigned NOT NULL auto_increment,
658
trackname varchar(100) NOT NULL default '',
659
PRIMARY KEY (trackid)
663
artistid int(10) unsigned NOT NULL auto_increment,
664
artistname varchar(100) NOT NULL default '',
665
PRIMARY KEY (artistid)
669
trackid int(10) unsigned NOT NULL,
670
artistid int(10) unsigned NOT NULL,
671
PRIMARY KEY (trackid,artistid)
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);
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%';
686
# Correct length reporting from substring() (BUG#10269)
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;
695
# Bug #9854 hex() and out of range handling
697
select hex(29223372036854775809), hex(-29223372036854775809);
700
# Bug #11311: Incorrect length returned from LPAD() and RPAD()
702
create table t1 (i int);
703
insert into t1 values (1000000000),(1);
705
select lpad(i, 7, ' ') as t from t1;
706
select rpad(i, 7, ' ') as t from t1;
711
# Bug #10418: LOAD_FILE does not behave like in manual if file does not exist
714
select load_file("lkjlkj");
715
select ifnull(load_file("lkjlkj"),"it's null");
718
# Bug#15351: Wrong collation used for comparison of md5() and sha()
719
# parameter can lead to a wrong result.
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"));
730
# Bug#18243: REVERSE changes its argument
733
CREATE TABLE t1 (a varchar(10));
734
INSERT INTO t1 VALUES ('abc'), ('xyz');
736
SELECT a, CONCAT(a,' ',a) AS c FROM t1
737
HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
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;
747
# Bug#17526: WRONG PRINT for TRIM FUNCTION with two arguments
750
CREATE TABLE t1 (s varchar(10));
751
INSERT INTO t1 VALUES ('yadda'), ('yaddy');
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';
762
# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly
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;
770
# Bug #31758 inet_ntoa, oct, crashes server with null + filesort
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;
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));
782
--echo End of 4.1 tests
785
# Bug #13361: SELECT FORMAT(<decimal field with null>, 2) crashes
787
create table t1 (d decimal default null);
788
insert into t1 values (null);
789
select format(d, 2) from t1;
793
# Bug #14676: substring_index() returns incorrect results
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;
801
# Bug #17043: Casting trimmed string to decimal loses precision
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));
808
# Bug #13975: "same string" + 0 has 2 different results
810
select conv("18383815659218730760",10,10) + 0;
811
select "18383815659218730760" + 0;
814
# Bug #21698: substitution of a string field for a constant under a function
817
CREATE TABLE t1 (code varchar(10));
818
INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
820
SELECT ASCII(code), code FROM t1 WHERE code='A12';
821
SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
823
INSERT INTO t1 VALUES ('a12 '), ('A12 ');
825
SELECT LENGTH(code), code FROM t1 WHERE code='A12';
826
SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
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');
832
SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id
833
WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00');
835
SELECT * FROM t1 INNER JOIN t2 ON code=id
836
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
841
# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions
844
select encode(NULL, NULL);
845
select encode("data", NULL);
846
select encode(NULL, "password");
848
select decode(NULL, NULL);
849
select decode("data", NULL);
850
select decode(NULL, "password");
852
select format(NULL, NULL);
853
select format(pi(), NULL);
854
select format(NULL, 2);
856
select benchmark(NULL, NULL);
857
select benchmark(0, NULL);
858
select benchmark(100, NULL);
859
select benchmark(NULL, 1+1);
862
# Bug #20752: BENCHMARK with many iterations returns too quickly
865
# not a string, but belongs with the above Bug#22684
866
select benchmark(-1, 1);
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.
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));
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));
900
select format(pi(), (1+1));
901
select format(pi(), (select 3 from dual));
902
select format(pi(), @dec);
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());
913
# 4294967296 18446744073709551616
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);
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);
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);
961
select substring('hello', 2, -1);
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);
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');
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);
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);
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');
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');
1102
# BUG#17047: CHAR() and IN() can return NULL without signaling NULL
1105
SET @orig_sql_mode = @@SQL_MODE;
1106
SET SQL_MODE=traditional;
1108
SELECT CHAR(0xff,0x8f USING utf8);
1109
SELECT CHAR(0xff,0x8f USING utf8) IS NULL;
1111
SET SQL_MODE=@orig_sql_mode;
1114
# Bug #24947: problem with some string function with unsigned int parameters
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');
1123
# Bug#15757: Wrong SUBSTRING() result when a tmp table was employed.
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;
1134
# Bug #25197 :repeat function returns null when using table field directly as count
1138
DROP TABLE IF EXISTS t1;
1142
`id` varchar(20) NOT NULL,
1143
`tire` tinyint(3) unsigned NOT NULL,
1147
INSERT INTO `t1` (`id`, `tire`) VALUES ('A', 0), ('B', 1),('C', 2);
1149
SELECT REPEAT( '#', tire ) AS A,
1150
REPEAT( '#', tire % 999 ) AS B, tire FROM `t1`;
1152
SELECT REPEAT('0', CAST(0 AS UNSIGNED));
1153
SELECT REPEAT('0', -2);
1154
SELECT REPEAT('0', 2);
1159
# Bug #26537: UNHEX() IS NULL comparison fails
1162
SELECT UNHEX('G') IS NULL;
1165
# Bug #26281: INSERT() function mishandles NUL on boundary condition
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');
1173
# Bug #27530: Grouping on crc32, or create table select crc32
1175
CREATE TABLE t1 (a INT);
1176
CREATE VIEW v1 AS SELECT CRC32(a) AS C FROM t1;
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;
1185
SELECT * FROM (SELECT * FROM v1) x;
1191
# Bug #27932: LOCATE with argument evaluated to NULL
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;
1207
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10));
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);
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;
1224
# Bug #27130: SUBSTR with UNSIGNED 0 as the last argument
1227
SELECT SUBSTR('foo',1,0);
1228
SELECT SUBSTR('foo',1,CAST(0 AS SIGNED));
1229
SELECT SUBSTR('foo',1,CAST(0 AS UNSIGNED));
1231
CREATE TABLE t1 (a varchar(10), len int unsigned);
1232
INSERT INTO t1 VALUES ('bar', 2), ('foo', 0);
1234
SELECT SUBSTR(a,1,len) FROM t1;
1239
# Bug #28850: Potential bugs related to the return type of the CHAR function
1242
CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1;
1243
SELECT HEX(c1) from t1;
1246
--echo End of 5.0 tests
added
removed
mysql-test/t/func_str.test
