func_str.test 18 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 28 29 30 31
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  '),':');
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.*.*'),':');
32
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
33

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

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

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

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

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

91 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
#
# 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;
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132

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

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

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

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

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

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

#
# test for #739

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#
# 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);
insert into t1 (a,b,c) values (1,'Tom',now()),(2,'ball games',now()), (3,'Basil',now()), (4,'Dean',now()),(5,'Ellis',now()), (6,'Serg',now()), (7,'Sergei',now()),(8,'Georg',now()),(9,'Salle',now()),(10,'Sinisa',now()); 
select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
drop table t1;