func_str.test 26.4 KB
Newer Older
1 2 3 4
# Description
# -----------
# Testing string functions

5
--disable_warnings
6
drop table if exists t1;
7
--enable_warnings
8

9 10
set names latin1;

11 12 13
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
select 'hello' 'monty';
select length('\n\t\r\b\0\_\%\\');
unknown's avatar
unknown committed
14
select bit_length('\n\t\r\b\0\_\%\\');
15 16 17
select char_length('\n\t\r\b\0\_\%\\');
select length(_latin1'\n\t\n\b\0\\_\\%\\');
select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');
18
select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
19 20
select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE'); 
select position(binary 'll' in 'hello'),position('a' in binary 'hello');
21 22 23 24 25
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
unknown's avatar
unknown committed
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
select substring_index('aaaaaaaaa1','a',1);
select substring_index('aaaaaaaaa1','aa',1);
select substring_index('aaaaaaaaa1','aa',2);
select substring_index('aaaaaaaaa1','aa',3);
select substring_index('aaaaaaaaa1','aa',4);
select substring_index('aaaaaaaaa1','aa',5);
select substring_index('aaaaaaaaa1','aaa',1);
select substring_index('aaaaaaaaa1','aaa',2);
select substring_index('aaaaaaaaa1','aaa',3);
select substring_index('aaaaaaaaa1','aaa',4);
select substring_index('aaaaaaaaa1','aaaa',1);
select substring_index('aaaaaaaaa1','aaaa',2);
select substring_index('aaaaaaaaa1','1',1);
select substring_index('aaaaaaaaa1','a',-1);
select substring_index('aaaaaaaaa1','aa',-1);
select substring_index('aaaaaaaaa1','aa',-2);
select substring_index('aaaaaaaaa1','aa',-3);
select substring_index('aaaaaaaaa1','aa',-4);
select substring_index('aaaaaaaaa1','aa',-5);
select substring_index('aaaaaaaaa1','aaa',-1);
select substring_index('aaaaaaaaa1','aaa',-2);
select substring_index('aaaaaaaaa1','aaa',-3);
select substring_index('aaaaaaaaa1','aaa',-4);
select substring_index('the king of thethe hill','the',-2);
select substring_index('the king of the the hill','the',-2);
select substring_index('the king of the  the hill','the',-2);
select substring_index('the king of the  the hill',' the ',-1);
select substring_index('the king of the  the hill',' the ',-2);
select substring_index('the king of the  the hill',' ',-1);
select substring_index('the king of the  the hill',' ',-2);
select substring_index('the king of the  the hill',' ',-3);
select substring_index('the king of the  the hill',' ',-4);
select substring_index('the king of the  the hill',' ',-5);
select substring_index('the king of the.the hill','the',-2);
select substring_index('the king of thethethe.the hill','the',-3);
select substring_index('the king of thethethe.the hill','the',-1);
select substring_index('the king of the the hill','the',1);
select substring_index('the king of the the hill','the',2);
select substring_index('the king of the the hill','the',3);
65 66 67 68 69 70

select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');
select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
71
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
72

73 74 75 76 77
select concat_ws(', ','monty','was here','again');
select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
select concat_ws(',','',NULL,'a');
SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');

78 79 80
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
unknown's avatar
unknown committed
81
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
unknown's avatar
unknown committed
82 83 84 85 86
select 'mood' sounds like 'mud';
select 'Glazgo' sounds like 'Liverpool';
select null sounds like 'null';
select 'null' sounds like null;
select null sounds like null;
87
select md5('hello');
88
select crc32("123");
89 90 91 92 93 94 95 96 97 98
select sha('abc');
select sha1('abc');
select aes_decrypt(aes_encrypt('abc','1'),'1');
select aes_decrypt(aes_encrypt('abc','1'),1);
select aes_encrypt(NULL,"a");
select aes_encrypt("a",NULL);
select aes_decrypt(NULL,"a");
select aes_decrypt("a",NULL);
select aes_decrypt("a","a");
select aes_decrypt(aes_encrypt("","a"),"a");
99 100
select repeat('monty',5),concat('*',space(5),'*');
select reverse('abc'),reverse('abcd');
101 102
select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
103 104 105
select rpad(741653838,17,'0'),lpad(741653838,17,'0');
select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
106 107
select rpad('STRING', 20, CONCAT('p','a','d') );
select lpad('STRING', 20, CONCAT('p','a','d') );
108 109

select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
110 111 112 113 114
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");

select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
select decode(encode("abcdef","monty"),"monty")="abcdef";

unknown's avatar
unknown committed
115 116
select quote('\'\"\\test');
select quote(concat('abc\'', '\\cba'));
117
select quote(1/0), quote('\0\Z');
unknown's avatar
unknown committed
118
select length(quote(concat(char(0),"test")));
119
select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
unknown's avatar
unknown committed
120
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
unknown's avatar
unknown committed
121 122
select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
select length(unhex(md5("abrakadabra")));
unknown's avatar
unknown committed
123

124 125 126 127 128 129
#
# Bug #6564: QUOTE(NULL
#

select concat('a', quote(NULL));

130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
#
# Wrong usage of functions
#

select reverse("");
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
select elt(2,1),field(NULL,"a","b","c"),reverse("");
select locate("a","b",2),locate("","a",1);
select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
select concat("1","2")|0,concat("1",".5")+0.0;
select substring_index("www.tcx.se","",3);
select length(repeat("a",100000000)),length(repeat("a",1000*64));
select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
select position(("1" in (1,2,3)) in "01");
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)));
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)));

#
# Problem med concat
#

create table t1 ( domain char(50) );
insert into t1 VALUES ("hello.de" ), ("test.de" );
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
drop table t1;
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171

#
# Test bug in concat_ws
#

CREATE TABLE t1 (
  id int(10) unsigned NOT NULL,
  title varchar(255) default NULL,
  prio int(10) unsigned default NULL,
  category int(10) unsigned default NULL,
  program int(10) unsigned default NULL,
  bugdesc text,
  created datetime default NULL,
  modified timestamp(14) NOT NULL,
  bugstatus int(10) unsigned default NULL,
  submitter int(10) unsigned default NULL
unknown's avatar
unknown committed
172
) ENGINE=MyISAM;
173 174

INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
175
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
176
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
177
SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
unknown's avatar
unknown committed
178
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
179
drop table t1;
180 181 182 183 184

#
# Test bug in AES_DECRYPT() when called with wrong argument
#

unknown's avatar
unknown committed
185
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;
186 187 188
INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
DROP TABLE t1;
189

unknown's avatar
unknown committed
190 191 192 193
CREATE TABLE t1 (
  wid int(10) unsigned NOT NULL auto_increment,
  data_podp date default NULL,
  status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',
unknown's avatar
unknown committed
194
  PRIMARY KEY(wid)
unknown's avatar
unknown committed
195 196 197 198 199 200
);

INSERT INTO t1 VALUES (8,NULL,'real');
INSERT INTO t1 VALUES (9,NULL,'nowy');
SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;
DROP TABLE t1;
unknown's avatar
unknown committed
201 202 203 204

#
# test for #739

unknown's avatar
unknown committed
205
CREATE TABLE t1 (title text) ENGINE=MyISAM;
unknown's avatar
unknown committed
206 207 208 209
INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
INSERT INTO t1 VALUES ('House passes the CAREERS bill');
SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
DROP TABLE t1;
210 211 212 213 214 215 216 217

#
# test for Bug #2290 "output truncated with ELT when using DISTINCT"
#

CREATE TABLE t1 (i int, j int);
INSERT INTO t1 VALUES (1,1),(2,2);
SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
218
DROP TABLE t1;
unknown's avatar
unknown committed
219

220 221 222 223 224 225 226
#
# bug #3756: quote and NULL
#

create table t1(a char(4));
insert into t1 values ('one'),(NULL),('two'),('four');
select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;
227
drop table t1;
228

229 230 231 232 233 234
#
# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str
#

select trim(trailing 'foo' from 'foo');
select trim(leading 'foo' from 'foo');
unknown's avatar
unknown committed
235

236
#
unknown's avatar
unknown committed
237 238 239 240
# crashing bug with QUOTE() and LTRIM() or TRIM() fixed
# Bug #7495
#

unknown's avatar
unknown committed
241 242
select quote(ltrim(concat('    ', 'a')));
select quote(trim(concat('    ', 'a')));
243 244 245 246 247

# Bad results from QUOTE(). Bug #8248
CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
SELECT QUOTE('A') FROM t1;
DROP TABLE t1;
248

249 250
# Test collation and coercibility
#
251 252 253 254 255

select 1=_latin1'1';
select _latin1'1'=1;
select _latin2'1'=1;
select 1=_latin2'1';
unknown's avatar
unknown committed
256
--error 1267
257 258 259 260 261
select _latin1'1'=_latin2'1';
select row('a','b','c') = row('a','b','c');
select row('A','b','c') = row('a','b','c');
select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');
select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');
unknown's avatar
unknown committed
262
--error 1267
263 264
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');

unknown's avatar
unknown committed
265
--error 1267
unknown's avatar
unknown committed
266
select concat(_latin1'a',_latin2'a');
unknown's avatar
unknown committed
267
--error 1270
unknown's avatar
unknown committed
268 269
select concat(_latin1'a',_latin2'a',_latin5'a');
--error 1271
unknown's avatar
unknown committed
270
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
271 272
--error 1267
select concat_ws(_latin1'a',_latin2'a');
unknown's avatar
unknown committed
273

274 275 276 277 278 279 280
#
# Test FIELD() and collations
#
select FIELD('b','A','B');
select FIELD('B','A','B');
select FIELD('b' COLLATE latin1_bin,'A','B');
select FIELD('b','A' COLLATE latin1_bin,'B');
unknown's avatar
unknown committed
281
--error 1270
282
select FIELD(_latin2'b','A','B');
unknown's avatar
unknown committed
283
--error 1270
284 285 286
select FIELD('b',_latin2'A','B');
select FIELD('b',_latin2'A','B',1);

287 288 289
select POSITION(_latin1'B' IN _latin1'abcd');
select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
unknown's avatar
unknown committed
290
--error 1267
291
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
unknown's avatar
unknown committed
292
--error 1267
293 294 295 296 297 298
select POSITION(_latin1'B' IN _latin2'abcd');

select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
--fix this:
--select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin);
--select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d');
unknown's avatar
unknown committed
299
--error 1267
300
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
unknown's avatar
unknown committed
301
--error 1267
302 303 304 305 306 307
select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');

select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
--fix this:
--select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2);
--select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2);
unknown's avatar
unknown committed
308
--error 1267
309
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
unknown's avatar
unknown committed
310
--error 1267
311 312
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);

313 314 315 316
select _latin1'B' between _latin1'a' and _latin1'c';
select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
unknown's avatar
unknown committed
317
--error 1270
318
select _latin2'B' between _latin1'a' and _latin1'b';
unknown's avatar
unknown committed
319
--error 1270
320
select _latin1'B' between _latin2'a' and _latin1'b';
unknown's avatar
unknown committed
321
--error 1270
322
select _latin1'B' between _latin1'a' and _latin2'b';
unknown's avatar
unknown committed
323
--error 1270
324
select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
325

326 327 328 329
select _latin1'B' in (_latin1'a',_latin1'b');
select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
unknown's avatar
unknown committed
330
--error 1270
331
select _latin2'B' in (_latin1'a',_latin1'b');
unknown's avatar
unknown committed
332
--error 1270
333
select _latin1'B' in (_latin2'a',_latin1'b');
unknown's avatar
unknown committed
334
--error 1270
335
select _latin1'B' in (_latin1'a',_latin2'b');
unknown's avatar
unknown committed
336
--error 1270
337
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
unknown's avatar
unknown committed
338
--error 1270
339 340
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);

341 342 343 344 345 346
select collation(bin(130)), coercibility(bin(130));
select collation(oct(130)), coercibility(oct(130));
select collation(conv(130,16,10)), coercibility(conv(130,16,10));
select collation(hex(130)), coercibility(hex(130));
select collation(char(130)), coercibility(hex(130));
select collation(format(130,10)), coercibility(format(130,10));
347 348 349 350 351 352
select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
353 354
select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
355
select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
356 357
select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
358 359 360 361 362 363
select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
364 365 366 367 368
select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab'));
select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
369
select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
370
select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
371
select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
372

373 374
create table t1 
select
375 376 377 378 379 380
  bin(130),
  oct(130),
  conv(130,16,10),
  hex(130),
  char(130),
  format(130,10),
381 382 383 384 385 386
  left(_latin2'a',1),
  right(_latin2'a',1), 
  lcase(_latin2'a'), 
  ucase(_latin2'a'),
  substring(_latin2'a',1,1),
  concat(_latin2'a',_latin2'b'),
387 388
  lpad(_latin2'a',4,_latin2'b'),
  rpad(_latin2'a',4,_latin2'b'),
389
  concat_ws(_latin2'a',_latin2'b'),
390 391
  make_set(255,_latin2'a',_latin2'b',_latin2'c'),
  export_set(255,_latin2'y',_latin2'n',_latin2' '),
392 393 394 395 396
  trim(_latin2' a '),
  ltrim(_latin2' a '),
  rtrim(_latin2' a '),
  trim(LEADING _latin2' ' FROM _latin2' a '),
  trim(TRAILING _latin2' ' FROM _latin2' a '),
397 398 399 400 401
  trim(BOTH _latin2' ' FROM _latin2' a '),
  repeat(_latin2'a',10),
  reverse(_latin2'ab'),
  quote(_latin2'ab'),
  soundex(_latin2'ab'),
402
  substring(_latin2'ab',1),
403
  insert(_latin2'abcd',2,3,_latin2'ef'),
404 405
  replace(_latin2'abcd',_latin2'b',_latin2'B'),
  encode('abcd','ab')
406
;
407 408
show create table t1;
drop table t1;
409

410 411 412 413 414 415 416 417
#
# Bug#9129
#
create table t1 (a char character set latin2);
insert into t1 values (null);
select charset(a), collation(a), coercibility(a) from t1;
drop table t1;
select charset(null), collation(null), coercibility(null);
418 419 420 421 422 423 424 425 426 427 428 429 430 431
#
# Make sure OUTER JOIN is not replaced with a regular joun
#
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);
INSERT INTO t1 VALUES (1,1),(2,2);
INSERT INTO t2 VALUES (2,2),(3,3);
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
where collation(t2.a) = _utf8'binary' order by t1.a,t2.a;
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
where charset(t2.a) = _utf8'binary' order by t1.a,t2.a;
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
where coercibility(t2.a) = 2 order by t1.a,t2.a;
DROP TABLE t1, t2;
432

433 434 435 436 437 438 439 440 441 442
#
# test for SUBSTR
#
select SUBSTR('abcdefg',3,2);
select SUBSTRING('abcdefg',3,2);
select SUBSTR('abcdefg',-3,2) FROM DUAL;
select SUBSTR('abcdefg',-1,5) FROM DUAL;
select SUBSTR('abcdefg',0,0) FROM DUAL;
select SUBSTR('abcdefg',-1,-1) FROM DUAL;
select SUBSTR('abcdefg',1,-1) FROM DUAL;
443 444 445 446 447 448

#
# Test that fix_fields doesn't follow to upper level (to comparison)
# when an error on a lower level (in concat) has accured:
#
create table t7 (s1 char);
unknown's avatar
unknown committed
449
--error 1267
450 451 452
select * from t7
where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
drop table t7;
unknown's avatar
unknown committed
453

unknown's avatar
unknown committed
454 455 456
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);

explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'),  concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty");
457 458

#
459
# lpad returns incorrect result (Bug #2182)
460 461 462
#

SELECT lpad(12345, 5, "#");
463

464
#
465
# Problem the the CONV() function (Bug #2972)
466 467 468 469
#
 
SELECT conv(71, 10, 36), conv('1Z', 36, 10);

470
#
471
# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)
472 473 474 475 476 477 478 479 480
#

create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb');
create table t2 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');
select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2 
where t2.id=t1.id order by name;
drop table t1, t2;
481 482 483 484 485

#
# Test case for conversion of long string value to integer (Bug #3472)
#

unknown's avatar
unknown committed
486 487 488
create table t1 (c1 INT, c2 INT UNSIGNED);
insert into t1 values ('21474836461','21474836461');
insert into t1 values ('-21474836461','-21474836461');
489 490 491
show warnings;
select * from t1;
drop table t1;
492 493 494 495 496 497

#
# Bug #4878: LEFT() in integer/float context
#

select left(1234, 3) + 0;
498 499 500 501 502

#
# Bug #7101: bug with LEFT() when used as a field in GROUP BY aggregation
#
create table t1 (a int not null primary key, b varchar(40), c datetime);
unknown's avatar
unknown committed
503
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'); 
504 505
select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
drop table t1;
unknown's avatar
unknown committed
506 507 508 509 510 511 512 513

#
# Bug#7455 unexpected result: TRIM(<NULL> FROM <whatever>) gives NOT NULL
# According to ANSI if one of the TRIM arguments is NULL, then the result
# must be NULL too.
#
select trim(null from 'kate') as "must_be_null";
select trim('xyz' from null) as "must_be_null";
unknown's avatar
unknown committed
514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540

#
# Bug #7751 - conversion for a bigint unsigned constant 
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  a bigint(20) unsigned default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES
('0','16307858876001849059');

SELECT CONV('e251273eb74a8ee3', 16, 10);

EXPLAIN 
SELECT id
  FROM t1
  WHERE a = 16307858876001849059;

EXPLAIN 
  SELECT id
  FROM t1
  WHERE a = CONV('e251273eb74a8ee3', 16, 10);

DROP TABLE t1;
541 542 543 544 545 546

#
# Bug #6317: string function CHAR, parameter is NULL, wrong result
#
SELECT CHAR(NULL,121,83,81,'76') as my_column;
SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;
unknown's avatar
unknown committed
547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562
#
# Test case for bug #8669: null aes_decrypt result in order by query
#

CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL);
CREATE TABLE t2 (id int NOT NULL UNIQUE);
INSERT INTO t2 VALUES (1),(2);
INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar'));
INSERT INTO t1 VALUES (2, 'not valid');

SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id;
SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id
 ORDER BY t1.id;

DROP TABLE t1, t2;

563 564 565 566 567 568

#
# Bug #10944: Mishandling of NULL arguments in FIELD()
#
select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0);
select field(NULL,1,2,NULL), field(NULL,1,2,0);
unknown's avatar
unknown committed
569 570 571 572 573 574 575 576 577 578 579 580 581 582

#
# Bug #10124: access by integer index with a string key that is not a number  
#

CREATE TABLE t1 (str varchar(20) PRIMARY KEY);
CREATE TABLE t2 (num int primary key);
INSERT INTO t1 VALUES ('notnumber');
INSERT INTO t2 VALUES (0), (1); 

SELECT * FROM t1, t2 WHERE num=str;
SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);

DROP TABLE t1,t2;
unknown's avatar
unknown committed
583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638

#
# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS  
#

CREATE TABLE t1(
  id int(11) NOT NULL auto_increment,
  pc int(11) NOT NULL default '0',
  title varchar(20) default NULL,
  PRIMARY KEY (id)
);

INSERT INTO t1 VALUES
  (1, 0, 'Main'),
  (2, 1, 'Toys'),
  (3, 1, 'Games');

SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
  FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
          LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
  FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
          LEFT JOIN t1 AS t3 ON t2.pc=t3.id
    WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%';

DROP TABLE t1;


CREATE TABLE t1(
  trackid     int(10) unsigned NOT NULL auto_increment,
  trackname   varchar(100) NOT NULL default '',
  PRIMARY KEY (trackid)
);

CREATE TABLE t2(
  artistid    int(10) unsigned NOT NULL auto_increment,
  artistname  varchar(100) NOT NULL default '',
  PRIMARY KEY (artistid)
);

CREATE TABLE t3(
  trackid     int(10) unsigned NOT NULL,
  artistid    int(10) unsigned NOT NULL,
  PRIMARY KEY (trackid,artistid)
);

INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York');
INSERT INTO t2 VALUES (1, 'Vernon Duke');
INSERT INTO t3 VALUES (1,1);

SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname
  FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid
          LEFT JOIN t2 ON t2.artistid=t3.artistid
    WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%';

DROP TABLE t1,t2,t3;
unknown's avatar
Merge  
unknown committed
639

640 641 642 643 644 645 646 647
#
# Correct length reporting from substring() (BUG#10269)
#
create table t1 (b varchar(5));
insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');
select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1;
select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t;
drop table t1;
648 649 650 651 652

#
# Bug #9854  hex() and out of range handling
#
select hex(29223372036854775809), hex(-29223372036854775809);
653 654 655 656 657 658 659 660 661 662 663

#
# Bug #11311: Incorrect length returned from LPAD() and RPAD()
#
create table t1 (i int);
insert into t1 values (1000000000),(1);
--enable_metadata
select lpad(i, 7, ' ') as t from t1;
select rpad(i, 7, ' ') as t from t1;
--disable_metadata
drop table t1;
664

665 666 667 668 669 670 671
#
# Bug #10418: LOAD_FILE does not behave like in manual if file does not exist
#

select load_file("lkjlkj");
select ifnull(load_file("lkjlkj"),"it's null");

672 673 674 675 676 677 678 679 680 681 682 683
#
# Bug#15351: Wrong collation used for comparison of md5() and sha()
# parameter can lead to a wrong result.
#
create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
drop table t1;

unknown's avatar
unknown committed
684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700
#
# Bug#18243: REVERSE changes its argument
#

CREATE TABLE t1 (a varchar(10));
INSERT INTO t1 VALUES ('abc'), ('xyz');

SELECT a, CONCAT(a,' ',a) AS c FROM t1
  HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;

SELECT a, CONCAT(a,' ',a) AS c FROM t1
  HAVING LEFT(CONCAT(a,' ',a),
              LENGTH(CONCAT(a,' ',a))-
                     INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;

DROP TABLE t1;
 
701
--echo End of 4.1 tests