func_str.test 18.6 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\_\%\\');
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') ;
serg@serg.mylan's avatar
serg@serg.mylan committed
42
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
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";

76 77
select quote('\'\"\\test');
select quote(concat('abc\'', '\\cba'));
78
select quote(1/0), quote('\0\Z');
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))));
81
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
serg@serg.mylan's avatar
serg@serg.mylan 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")));
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
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;
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
#

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

hf@deer.(none)'s avatar
hf@deer.(none) 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',
155
  PRIMARY KEY(wid)
hf@deer.(none)'s avatar
hf@deer.(none) 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;
hf@deer.(none)'s avatar
hf@deer.(none) committed
162 163 164 165

#
# test for #739

monty@mysql.com's avatar
monty@mysql.com committed
166
CREATE TABLE t1 (title text) ENGINE=MyISAM;
hf@deer.(none)'s avatar
hf@deer.(none) 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;
monty@mysql.com's avatar
monty@mysql.com 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');
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';
monty@mysql.com's avatar
monty@mysql.com 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');
monty@mysql.com's avatar
monty@mysql.com committed
211
--error 1267
212 213
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');

monty@mysql.com's avatar
monty@mysql.com committed
214
--error 1267
bar@bar.mysql.r18.ru's avatar
bar@bar.mysql.r18.ru committed
215
select concat(_latin1'a',_latin2'a');
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
216
--error 1270
monty@mysql.com's avatar
monty@mysql.com committed
217 218
select concat(_latin1'a',_latin2'a',_latin5'a');
--error 1271
bar@bar.mysql.r18.ru's avatar
bar@bar.mysql.r18.ru committed
219
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
220 221
--error 1267
select concat_ws(_latin1'a',_latin2'a');
bar@bar.mysql.r18.ru's avatar
bar@bar.mysql.r18.ru 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');
monty@mysql.com's avatar
monty@mysql.com committed
230
--error 1270
231
select FIELD(_latin2'b','A','B');
monty@mysql.com's avatar
monty@mysql.com 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');
monty@mysql.com's avatar
monty@mysql.com committed
239
--error 1267
240
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
monty@mysql.com's avatar
monty@mysql.com 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');
monty@mysql.com's avatar
monty@mysql.com committed
248
--error 1267
249
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
monty@mysql.com's avatar
monty@mysql.com 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);
monty@mysql.com's avatar
monty@mysql.com committed
257
--error 1267
258
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
monty@mysql.com's avatar
monty@mysql.com 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;
monty@mysql.com's avatar
monty@mysql.com committed
266
--error 1270
267
select _latin2'B' between _latin1'a' and _latin1'b';
monty@mysql.com's avatar
monty@mysql.com committed
268
--error 1270
269
select _latin1'B' between _latin2'a' and _latin1'b';
monty@mysql.com's avatar
monty@mysql.com committed
270
--error 1270
271
select _latin1'B' between _latin1'a' and _latin2'b';
monty@mysql.com's avatar
monty@mysql.com 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);
monty@mysql.com's avatar
monty@mysql.com committed
279
--error 1270
280
select _latin2'B' in (_latin1'a',_latin1'b');
monty@mysql.com's avatar
monty@mysql.com committed
281
--error 1270
282
select _latin1'B' in (_latin2'a',_latin1'b');
monty@mysql.com's avatar
monty@mysql.com committed
283
--error 1270
284
select _latin1'B' in (_latin1'a',_latin2'b');
monty@mysql.com's avatar
monty@mysql.com committed
285
--error 1270
286
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
monty@mysql.com's avatar
monty@mysql.com 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);
monty@mysql.com's avatar
monty@mysql.com 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;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
379

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)
#

monty@mysql.com's avatar
monty@mysql.com 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

#
# 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);
429
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'); 
430 431
select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
drop table t1;
432 433 434 435
# crashing bug with QUOTE() and LTRIM() or TRIM() fixed
# Bug #7495
#

436 437
select quote(ltrim(concat('    ', 'a')));
select quote(trim(concat('    ', 'a')));
timour@mysql.com's avatar
timour@mysql.com committed
438 439 440 441 442 443 444 445

#
# 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";