func_str.test 18.8 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 26 27
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);

select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');
28
select concat(':',trim(leading from '  left  '),':',trim(trailing from '  right  '),':');
29 30 31 32
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.*.*'),':');
33
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
34

35 36 37 38 39
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)), '"');

40 41 42
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
43
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
unknown's avatar
unknown committed
44 45 46 47 48
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;
49
select md5('hello');
50
select crc32("123");
51 52 53 54 55 56 57 58 59 60
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");
61 62
select repeat('monty',5),concat('*',space(5),'*');
select reverse('abc'),reverse('abcd');
63 64
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);
65 66 67
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');
68 69
select rpad('STRING', 20, CONCAT('p','a','d') );
select lpad('STRING', 20, CONCAT('p','a','d') );
70 71

select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
72 73 74 75 76
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
77 78
select quote('\'\"\\test');
select quote(concat('abc\'', '\\cba'));
79
select quote(1/0), quote('\0\Z');
unknown's avatar
unknown committed
80
select length(quote(concat(char(0),"test")));
81
select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
unknown's avatar
unknown committed
82
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
unknown's avatar
unknown committed
83 84
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
85

86 87 88 89 90 91
#
# Bug #6564: QUOTE(NULL
#

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

92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117
#
# 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;
118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133

#
# 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
134
) ENGINE=MyISAM;
135 136

INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
137
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
138
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
139
SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
unknown's avatar
unknown committed
140
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
141
drop table t1;
142 143 144 145 146

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

unknown's avatar
unknown committed
147
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;
148 149 150
INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
DROP TABLE t1;
151

unknown's avatar
unknown committed
152 153 154 155
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
156
  PRIMARY KEY(wid)
unknown's avatar
unknown committed
157 158 159 160 161 162
);

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
163 164 165 166

#
# test for #739

unknown's avatar
unknown committed
167
CREATE TABLE t1 (title text) ENGINE=MyISAM;
unknown's avatar
unknown committed
168 169 170 171
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;
172 173 174 175 176 177 178 179

#
# 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;
180
DROP TABLE t1;
unknown's avatar
unknown committed
181

182 183 184 185 186 187 188
#
# 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;
189
drop table t1;
190

191 192 193 194 195 196
#
# 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
197

198 199 200
#
# Test collation and coercibility
#
201 202 203 204 205

select 1=_latin1'1';
select _latin1'1'=1;
select _latin2'1'=1;
select 1=_latin2'1';
unknown's avatar
unknown committed
206
--error 1267
207 208 209 210 211
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
212
--error 1267
213 214
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');

unknown's avatar
unknown committed
215
--error 1267
unknown's avatar
unknown committed
216
select concat(_latin1'a',_latin2'a');
unknown's avatar
unknown committed
217
--error 1270
unknown's avatar
unknown committed
218 219
select concat(_latin1'a',_latin2'a',_latin5'a');
--error 1271
unknown's avatar
unknown committed
220
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
221 222
--error 1267
select concat_ws(_latin1'a',_latin2'a');
unknown's avatar
unknown committed
223

224 225 226 227 228 229 230
#
# 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
231
--error 1270
232
select FIELD(_latin2'b','A','B');
unknown's avatar
unknown committed
233
--error 1270
234 235 236
select FIELD('b',_latin2'A','B');
select FIELD('b',_latin2'A','B',1);

237 238 239
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
240
--error 1267
241
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
unknown's avatar
unknown committed
242
--error 1267
243 244 245 246 247 248
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
249
--error 1267
250
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
unknown's avatar
unknown committed
251
--error 1267
252 253 254 255 256 257
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
258
--error 1267
259
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
unknown's avatar
unknown committed
260
--error 1267
261 262
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);

263 264 265 266
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
267
--error 1270
268
select _latin2'B' between _latin1'a' and _latin1'b';
unknown's avatar
unknown committed
269
--error 1270
270
select _latin1'B' between _latin2'a' and _latin1'b';
unknown's avatar
unknown committed
271
--error 1270
272
select _latin1'B' between _latin1'a' and _latin2'b';
unknown's avatar
unknown committed
273
--error 1270
274
select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
275

276 277 278 279
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
280
--error 1270
281
select _latin2'B' in (_latin1'a',_latin1'b');
unknown's avatar
unknown committed
282
--error 1270
283
select _latin1'B' in (_latin2'a',_latin1'b');
unknown's avatar
unknown committed
284
--error 1270
285
select _latin1'B' in (_latin1'a',_latin2'b');
unknown's avatar
unknown committed
286
--error 1270
287
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
unknown's avatar
unknown committed
288
--error 1270
289 290
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);

291 292 293 294 295 296
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));
297 298 299 300 301 302
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'));
303 304
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'));
305
select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
306 307
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' '));
308 309 310 311 312 313
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'));
314 315 316 317 318
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));
319
select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
320
select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
321
select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
322

323 324
create table t1 
select
325 326 327 328 329 330
  bin(130),
  oct(130),
  conv(130,16,10),
  hex(130),
  char(130),
  format(130,10),
331 332 333 334 335 336
  left(_latin2'a',1),
  right(_latin2'a',1), 
  lcase(_latin2'a'), 
  ucase(_latin2'a'),
  substring(_latin2'a',1,1),
  concat(_latin2'a',_latin2'b'),
337 338
  lpad(_latin2'a',4,_latin2'b'),
  rpad(_latin2'a',4,_latin2'b'),
339
  concat_ws(_latin2'a',_latin2'b'),
340 341
  make_set(255,_latin2'a',_latin2'b',_latin2'c'),
  export_set(255,_latin2'y',_latin2'n',_latin2' '),
342 343 344 345 346
  trim(_latin2' a '),
  ltrim(_latin2' a '),
  rtrim(_latin2' a '),
  trim(LEADING _latin2' ' FROM _latin2' a '),
  trim(TRAILING _latin2' ' FROM _latin2' a '),
347 348 349 350 351
  trim(BOTH _latin2' ' FROM _latin2' a '),
  repeat(_latin2'a',10),
  reverse(_latin2'ab'),
  quote(_latin2'ab'),
  soundex(_latin2'ab'),
352
  substring(_latin2'ab',1),
353
  insert(_latin2'abcd',2,3,_latin2'ef'),
354 355
  replace(_latin2'abcd',_latin2'b',_latin2'B'),
  encode('abcd','ab')
356
;
357 358
show create table t1;
drop table t1;
359 360 361 362 363 364 365 366 367 368 369

#
# 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;
370 371 372 373 374 375

#
# 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
376
--error 1267
377 378 379
select * from t7
where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
drop table t7;
unknown's avatar
unknown committed
380

unknown's avatar
unknown committed
381 382 383
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");
384 385

#
386
# lpad returns incorrect result (Bug #2182)
387 388 389
#

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

391
#
392
# Problem the the CONV() function (Bug #2972)
393 394 395 396
#
 
SELECT conv(71, 10, 36), conv('1Z', 36, 10);

397
#
398
# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)
399 400 401 402 403 404 405 406 407
#

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;
408 409 410 411 412

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

unknown's avatar
unknown committed
413 414 415
create table t1 (c1 INT, c2 INT UNSIGNED);
insert into t1 values ('21474836461','21474836461');
insert into t1 values ('-21474836461','-21474836461');
416 417 418
show warnings;
select * from t1;
drop table t1;
419 420 421 422 423 424

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

select left(1234, 3) + 0;
425 426 427 428 429

#
# 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
430
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'); 
431 432
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
433 434 435 436
# crashing bug with QUOTE() and LTRIM() or TRIM() fixed
# Bug #7495
#

unknown's avatar
unknown committed
437 438
select quote(ltrim(concat('    ', 'a')));
select quote(trim(concat('    ', 'a')));
unknown's avatar
unknown committed
439 440 441 442 443 444 445 446

#
# 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";
447 448
select trim(leading NULL from 'kate') as "must_be_null";
select trim(trailing NULL from 'xyz') as "must_be_null";