3
# Testing string functions
6
drop table if exists t1,t2;
9
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
10
select 'hello' 'monty';
11
select length('\n\t\r\b\0\_\%\\');
12
select bit_length('\n\t\r\b\0\_\%\\');
13
select char_length('\n\t\r\b\0\_\%\\');
14
select length('\n\t\n\b\0\\_\\%\\');
15
select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');
16
select hex(char(256));
17
select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
18
select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE');
19
select position(binary 'll' in 'hello'),position('a' in binary 'hello');
21
# Bug#11728 string function LEFT,
22
# strange undocumented behaviour, strict mode
24
select left('hello',null), right('hello',null);
25
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
26
select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
27
select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
28
select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
29
select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
30
select substring_index('aaaaaaaaa1','a',1);
31
select substring_index('aaaaaaaaa1','aa',1);
32
select substring_index('aaaaaaaaa1','aa',2);
33
select substring_index('aaaaaaaaa1','aa',3);
34
select substring_index('aaaaaaaaa1','aa',4);
35
select substring_index('aaaaaaaaa1','aa',5);
36
select substring_index('aaaaaaaaa1','aaa',1);
37
select substring_index('aaaaaaaaa1','aaa',2);
38
select substring_index('aaaaaaaaa1','aaa',3);
39
select substring_index('aaaaaaaaa1','aaa',4);
40
select substring_index('aaaaaaaaa1','aaaa',1);
41
select substring_index('aaaaaaaaa1','aaaa',2);
42
select substring_index('aaaaaaaaa1','1',1);
43
select substring_index('aaaaaaaaa1','a',-1);
44
select substring_index('aaaaaaaaa1','aa',-1);
45
select substring_index('aaaaaaaaa1','aa',-2);
46
select substring_index('aaaaaaaaa1','aa',-3);
47
select substring_index('aaaaaaaaa1','aa',-4);
48
select substring_index('aaaaaaaaa1','aa',-5);
49
select substring_index('aaaaaaaaa1','aaa',-1);
50
select substring_index('aaaaaaaaa1','aaa',-2);
51
select substring_index('aaaaaaaaa1','aaa',-3);
52
select substring_index('aaaaaaaaa1','aaa',-4);
53
select substring_index('the king of thethe hill','the',-2);
54
select substring_index('the king of the the hill','the',-2);
55
select substring_index('the king of the the hill','the',-2);
56
select substring_index('the king of the the hill',' the ',-1);
57
select substring_index('the king of the the hill',' the ',-2);
58
select substring_index('the king of the the hill',' ',-1);
59
select substring_index('the king of the the hill',' ',-2);
60
select substring_index('the king of the the hill',' ',-3);
61
select substring_index('the king of the the hill',' ',-4);
62
select substring_index('the king of the the hill',' ',-5);
63
select substring_index('the king of the.the hill','the',-2);
64
select substring_index('the king of thethethe.the hill','the',-3);
65
select substring_index('the king of thethethe.the hill','the',-1);
66
select substring_index('the king of the the hill','the',1);
67
select substring_index('the king of the the hill','the',2);
68
select substring_index('the king of the the hill','the',3);
70
select concat(':',ltrim(' left '),':',rtrim(' right '),':');
71
select concat(':',trim(leading from ' left '),':',trim(trailing from ' right '),':');
72
select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
73
select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
74
select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
75
select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
76
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
78
select concat_ws(', ','monty','was here','again');
79
select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
80
select concat_ws(',','',NULL,'a');
81
SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
83
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
84
select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
85
select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
87
select repeat('monty',5),concat('*',space(5),'*');
88
select reverse('abc'),reverse('abcd');
89
select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
90
select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
91
select rpad(741653838,17,'0'),lpad(741653838,17,'0');
92
select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
93
select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
94
select rpad('STRING', 20, CONCAT('p','a','d') );
95
select lpad('STRING', 20, CONCAT('p','a','d') );
97
select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
99
select quote('\'\"\\test');
100
select quote(concat('abc\'', '\\cba'));
101
select quote(1/0), quote('\0\Z');
102
select length(quote(concat(char(0),"test")));
103
select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
104
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
105
select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
108
# Bug #6564: QUOTE(NULL
111
select concat('a', quote(NULL));
114
# Wrong usage of functions
118
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
119
select elt(2,1),field(NULL,"a","b","c"),reverse("");
120
select locate("a","b",2),locate("","a",1);
121
select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
122
select substring_index("www.tcx.se","",3);
123
select length(repeat("a",100000000)),length(repeat("a",1000*64));
124
select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
125
select position(("1" in (1,2,3)) in "01");
126
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)));
127
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)));
133
create table t1 ( domain char(50) );
134
insert into t1 VALUES ("hello.de" ), ("test.de" );
135
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';
136
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
140
# Test bug in concat_ws
145
title varchar(255) default NULL,
146
prio int default NULL,
147
category int default NULL,
148
program int default NULL,
150
created datetime default NULL,
151
modified timestamp NOT NULL,
152
bugstatus int default NULL,
153
submitter int default NULL
156
INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
157
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
158
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
159
SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
160
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
164
# Test bug in AES_DECRYPT() when called with wrong argument
167
CREATE TABLE t1 (id int NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;
168
INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
172
wid int NOT NULL auto_increment,
173
data_podp date default NULL,
174
status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',
178
INSERT INTO t1 VALUES (8,NULL,'real');
179
INSERT INTO t1 VALUES (9,NULL,'nowy');
180
SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;
186
CREATE TABLE t1 (title text) ENGINE=MyISAM;
187
INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
188
INSERT INTO t1 VALUES ('House passes the CAREERS bill');
189
SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
193
# test for Bug #2290 "output truncated with ELT when using DISTINCT"
196
CREATE TABLE t1 (i int, j int);
197
INSERT INTO t1 VALUES (1,1),(2,2);
198
SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
202
# bug #3756: quote and NULL
205
create table t1(a char(4));
206
insert into t1 values ('one'),(NULL),('two'),('four');
207
select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;
211
# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str
214
select trim(trailing 'foo' from 'foo');
215
select trim(leading 'foo' from 'foo');
218
# crashing bug with QUOTE() and LTRIM() or TRIM() fixed
222
select quote(ltrim(concat(' ', 'a')));
223
select quote(trim(concat(' ', 'a')));
225
# Bad results from QUOTE(). Bug #8248
226
CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
227
SELECT QUOTE('A') FROM t1;
233
select SUBSTR('abcdefg',3,2);
234
select SUBSTRING('abcdefg',3,2);
235
select SUBSTR('abcdefg',-3,2);
236
select SUBSTR('abcdefg',-1,5);
237
select SUBSTR('abcdefg',0,0);
238
select SUBSTR('abcdefg',-1,-1);
239
select SUBSTR('abcdefg',1,-1);
241
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
243
explain extended select concat('*',space(5),'*');
244
explain extended select reverse('abc');
245
explain extended select rpad('a',4,'1');
246
explain extended select lpad('a',4,'1');
247
explain extended select concat_ws(',','',NULL,'a');
248
explain extended select elt(2,1);
249
explain extended select locate("a","b",2);
250
explain extended select char(0);
251
explain extended select conv(130,16,10);
252
explain extended select hex(130);
253
explain extended select binary 'HE';
254
explain extended select collation(conv(130,16,10));
255
explain extended select coercibility(conv(130,16,10));
256
explain extended select length('\n\t\r\b\0\_\%\\');
257
explain extended select concat('monty',' was here ','again');
258
explain extended select length('hello');
259
explain extended select char(ascii('h'));
260
explain extended select ord('h');
261
explain extended select quote(1/0);
262
explain extended select crc32("123");
263
explain extended select replace('aaaa','a','b');
264
explain extended select insert('txs',2,1,'hi');
265
explain extended select SUBSTR('abcdefg',3,2);
266
explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);
269
# lpad returns incorrect result (Bug #2182)
272
SELECT lpad(12345, 5, "#");
275
# Problem the the CONV() function (Bug #2972)
278
SELECT conv(71, 10, 36), conv('1Z', 36, 10);
279
SELECT conv(71, 10, 37), conv('1Z', 37, 10), conv(0,1,10),conv(0,0,10), conv(0,-1,10);
282
# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)
285
create table t1 (id int, str varchar(10));
286
insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb');
287
create table t2 (id int, str varchar(10));
288
insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');
289
select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2
290
where t2.id=t1.id order by name;
294
# Test case for conversion of long string value to integer (Bug #3472)
297
create table t1 (c1 INT, c2 INT);
299
insert into t1 values ('21474836461','21474836461');
301
insert into t1 values ('-21474836461','-21474836461');
306
# Bug #4878: LEFT() in integer/float context
309
select left(1234, 3) + 0;
312
# Bug #7101: bug with LEFT() when used as a field in GROUP BY aggregation
314
create table t1 (a int not null primary key, b varchar(40), c datetime);
315
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');
316
select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
320
# Bug#7455 unexpected result: TRIM(<NULL> FROM <whatever>) gives NOT NULL
321
# According to ANSI if one of the TRIM arguments is NULL, then the result
324
select trim(null from 'kate') as "must_be_null";
325
select trim('xyz' from null) as "must_be_null";
326
select trim(leading NULL from 'kate') as "must_be_null";
327
select trim(trailing NULL from 'xyz') as "must_be_null";
330
# Bug #7751 - conversion for a bigint constant
334
id int NOT NULL auto_increment,
335
a bigint default NULL,
340
INSERT INTO t1 VALUES ('0','16307858876001849059');
342
SELECT CONV('e251273eb74a8ee3', 16, 10);
347
WHERE a = 16307858876001849059;
352
WHERE a = CONV('e251273eb74a8ee3', 16, 10);
357
# Bug #6317: string function CHAR, parameter is NULL, wrong result
359
SELECT CHAR(NULL,121,83,81,'76') as my_column;
360
SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;
362
# Test case for bug #8669: null aes_decrypt result in order by query
365
CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL);
366
CREATE TABLE t2 (id int NOT NULL UNIQUE);
367
INSERT INTO t2 VALUES (1),(2);
368
INSERT INTO t1 VALUES (2, 'not valid');
373
# Bug #10944: Mishandling of NULL arguments in FIELD()
375
select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0);
376
select field(NULL,1,2,NULL), field(NULL,1,2,0);
379
# Bug #10124: access by integer index with a string key that is not a number
382
CREATE TABLE t1 (str varchar(20) PRIMARY KEY);
383
CREATE TABLE t2 (num int primary key);
384
INSERT INTO t1 VALUES ('notnumber');
385
INSERT INTO t2 VALUES (0), (1);
387
SELECT * FROM t1, t2 WHERE num=str;
388
SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
393
# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS
397
id int NOT NULL auto_increment,
398
pc int NOT NULL default '0',
399
title varchar(20) default NULL,
403
INSERT INTO t1 VALUES
408
SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
409
FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
410
LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
411
SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
412
FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
413
LEFT JOIN t1 AS t3 ON t2.pc=t3.id
414
WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%';
420
trackid int NOT NULL auto_increment,
421
trackname varchar(100) NOT NULL default '',
422
PRIMARY KEY (trackid)
426
artistid int NOT NULL auto_increment,
427
artistname varchar(100) NOT NULL default '',
428
PRIMARY KEY (artistid)
432
trackid int NOT NULL,
433
artistid int NOT NULL,
434
PRIMARY KEY (trackid,artistid)
437
INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York');
438
INSERT INTO t2 VALUES (1, 'Vernon Duke');
439
INSERT INTO t3 VALUES (1,1);
441
SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname
442
FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid
443
LEFT JOIN t2 ON t2.artistid=t3.artistid
444
WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%';
449
# Correct length reporting from substring() (BUG#10269)
451
create table t1 (b varchar(5));
452
insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');
453
select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1;
454
select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t;
458
# Bug #9854 hex() and out of range handling
460
select hex(29223372036854775809), hex(-29223372036854775809);
463
# Bug #11311: Incorrect length returned from LPAD() and RPAD()
465
create table t1 (i int);
466
insert into t1 values (1000000000),(1);
468
select lpad(i, 7, ' ') as t from t1;
469
select rpad(i, 7, ' ') as t from t1;
474
# Bug #10418: LOAD_FILE does not behave like in manual if file does not exist
477
select load_file("lkjlkj");
478
select ifnull(load_file("lkjlkj"),"it's null");
481
# Bug#18243: REVERSE changes its argument
484
CREATE TABLE t1 (a varchar(10));
485
INSERT INTO t1 VALUES ('abc'), ('xyz');
487
SELECT a, CONCAT(a,' ',a) AS c FROM t1
488
HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
490
SELECT a, CONCAT(a,' ',a) AS c FROM t1
491
HAVING LEFT(CONCAT(a,' ',a),
492
LENGTH(CONCAT(a,' ',a))-
493
INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
498
# Bug#17526: WRONG PRINT for TRIM FUNCTION with two arguments
501
CREATE TABLE t1 (s varchar(10));
502
INSERT INTO t1 VALUES ('yadda'), ('yaddy');
504
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';
505
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
506
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';
507
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';
508
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
513
# Bug #31758 inet_ntoa, oct, crashes server with null + filesort
515
create table t1 (a bigint not null)engine=myisam;
516
insert into t1 set a = 1024*1024*1024*4;
518
create table t1 (a char(36) not null)engine=myisam;
519
insert ignore into t1 set a = ' ';
520
insert ignore into t1 set a = ' ';
521
select * from t1 order by (oct(a));
524
--echo End of 4.1 tests
527
# Bug #13361: SELECT FORMAT(<decimal field with null>, 2) crashes
529
create table t1 (d decimal default null);
530
insert into t1 values (null);
531
select format(d, 2) from t1;
535
# Bug #14676: substring_index() returns incorrect results
537
create table t1 (c varchar(40));
538
insert into t1 values ('y,abc'),('y,abc');
539
select c, substring_index(lcase(c), @q:=',', -1) as res from t1;
543
# Bug #17043: Casting trimmed string to decimal loses precision
545
select cast(rtrim(' 20.06 ') as decimal(19,2));
546
select cast(ltrim(' 20.06 ') as decimal(19,2));
547
select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2));
550
# Bug #13975: "same string" + 0 has 2 different results
552
select conv("18383815659218730760",10,10) + 0;
553
select "18383815659218730760" + 0;
556
# Bug #21698: substitution of a string field for a constant under a function
559
CREATE TABLE t1 (code varchar(10));
560
INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
562
SELECT ASCII(code), code FROM t1 WHERE code='A12';
563
SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
565
INSERT INTO t1 VALUES ('a12 '), ('A12 ');
567
SELECT LENGTH(code), code FROM t1 WHERE code='A12';
568
SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
570
ALTER TABLE t1 ADD INDEX (code);
571
CREATE TABLE t2 (id varchar(10) PRIMARY KEY);
572
INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14');
574
SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id
575
WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00');
577
SELECT * FROM t1 INNER JOIN t2 ON code=id
578
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
583
# Bug #20752: BENCHMARK with many iterations returns too quickly
586
# not a string, but belongs with the above Bug#22684
587
select benchmark(-1, 1);
591
# 1) The collation of the password is irrelevant, the encryption uses
592
# the binary representation of the string without charset/collation.
593
# 2) These tests can not print the encoded text directly, because it's binary,
594
# and doing this would cause problems with source control.
595
# Instead, an md5() checksum is used, to verify the result indirectly.
596
# 3) Each md5() result must be identical.
597
# 4) The md5() result must never change, and must be stable across releases.
601
select format(pi(), (1+1));
602
select format(pi(), (select 3));
603
select format(pi(), @dec);
606
select benchmark(10, pi());
607
select benchmark(5+5, pi());
608
select benchmark((select 10), pi());
609
select benchmark(@bench_count, pi());
614
# 4294967296 18446744073709551616
616
select locate('he','hello',-2);
617
select locate('lo','hello',-4294967295);
618
select locate('lo','hello',4294967295);
619
select locate('lo','hello',-4294967296);
620
select locate('lo','hello',4294967296);
621
select locate('lo','hello',-4294967297);
622
select locate('lo','hello',4294967297);
623
select locate('lo','hello',-18446744073709551615);
624
select locate('lo','hello',18446744073709551615);
625
select locate('lo','hello',-18446744073709551616);
626
select locate('lo','hello',18446744073709551616);
627
select locate('lo','hello',-18446744073709551617);
628
select locate('lo','hello',18446744073709551617);
630
select left('hello', 10);
631
select left('hello', 0);
632
select left('hello', -1);
633
select left('hello', -4294967295);
634
select left('hello', 4294967295);
635
select left('hello', -4294967296);
636
select left('hello', 4294967296);
637
select left('hello', -4294967297);
638
select left('hello', 4294967297);
639
select left('hello', -18446744073709551615);
640
select left('hello', 18446744073709551615);
641
select left('hello', -18446744073709551616);
642
select left('hello', 18446744073709551616);
643
select left('hello', -18446744073709551617);
644
select left('hello', 18446744073709551617);
646
select right('hello', 10);
647
select right('hello', 0);
648
select right('hello', -1);
649
select right('hello', -4294967295);
650
select right('hello', 4294967295);
651
select right('hello', -4294967296);
652
select right('hello', 4294967296);
653
select right('hello', -4294967297);
654
select right('hello', 4294967297);
655
select right('hello', -18446744073709551615);
656
select right('hello', 18446744073709551615);
657
select right('hello', -18446744073709551616);
658
select right('hello', 18446744073709551616);
659
select right('hello', -18446744073709551617);
660
select right('hello', 18446744073709551617);
662
select substring('hello', 2, -1);
664
select substring('hello', -1, 1);
665
select substring('hello', -2, 1);
666
select substring('hello', -4294967295, 1);
667
select substring('hello', 4294967295, 1);
668
select substring('hello', -4294967296, 1);
669
select substring('hello', 4294967296, 1);
670
select substring('hello', -4294967297, 1);
671
select substring('hello', 4294967297, 1);
672
select substring('hello', -18446744073709551615, 1);
673
select substring('hello', 18446744073709551615, 1);
674
select substring('hello', -18446744073709551616, 1);
675
select substring('hello', 18446744073709551616, 1);
676
select substring('hello', -18446744073709551617, 1);
677
select substring('hello', 18446744073709551617, 1);
678
select substring('hello', 1, -1);
679
select substring('hello', 1, -4294967295);
680
select substring('hello', 1, 4294967295);
681
select substring('hello', 1, -4294967296);
682
select substring('hello', 1, 4294967296);
683
select substring('hello', 1, -4294967297);
684
select substring('hello', 1, 4294967297);
685
select substring('hello', 1, -18446744073709551615);
686
select substring('hello', 1, 18446744073709551615);
687
select substring('hello', 1, -18446744073709551616);
688
select substring('hello', 1, 18446744073709551616);
689
select substring('hello', 1, -18446744073709551617);
690
select substring('hello', 1, 18446744073709551617);
691
select substring('hello', -1, -1);
692
select substring('hello', -4294967295, -4294967295);
693
select substring('hello', 4294967295, 4294967295);
694
select substring('hello', -4294967296, -4294967296);
695
select substring('hello', 4294967296, 4294967296);
696
select substring('hello', -4294967297, -4294967297);
697
select substring('hello', 4294967297, 4294967297);
698
select substring('hello', -18446744073709551615, -18446744073709551615);
699
select substring('hello', 18446744073709551615, 18446744073709551615);
700
select substring('hello', -18446744073709551616, -18446744073709551616);
701
select substring('hello', 18446744073709551616, 18446744073709551616);
702
select substring('hello', -18446744073709551617, -18446744073709551617);
703
select substring('hello', 18446744073709551617, 18446744073709551617);
705
select insert('hello', -1, 1, 'hi');
706
select insert('hello', -4294967295, 1, 'hi');
707
select insert('hello', 4294967295, 1, 'hi');
708
select insert('hello', -4294967296, 1, 'hi');
709
select insert('hello', 4294967296, 1, 'hi');
710
select insert('hello', -4294967297, 1, 'hi');
711
select insert('hello', 4294967297, 1, 'hi');
712
select insert('hello', -18446744073709551615, 1, 'hi');
713
select insert('hello', 18446744073709551615, 1, 'hi');
714
select insert('hello', -18446744073709551616, 1, 'hi');
715
select insert('hello', 18446744073709551616, 1, 'hi');
716
select insert('hello', -18446744073709551617, 1, 'hi');
717
select insert('hello', 18446744073709551617, 1, 'hi');
718
select insert('hello', 1, -1, 'hi');
719
select insert('hello', 1, -4294967295, 'hi');
720
select insert('hello', 1, 4294967295, 'hi');
721
select insert('hello', 1, -4294967296, 'hi');
722
select insert('hello', 1, 4294967296, 'hi');
723
select insert('hello', 1, -4294967297, 'hi');
724
select insert('hello', 1, 4294967297, 'hi');
725
select insert('hello', 1, -18446744073709551615, 'hi');
726
select insert('hello', 1, 18446744073709551615, 'hi');
727
select insert('hello', 1, -18446744073709551616, 'hi');
728
select insert('hello', 1, 18446744073709551616, 'hi');
729
select insert('hello', 1, -18446744073709551617, 'hi');
730
select insert('hello', 1, 18446744073709551617, 'hi');
731
select insert('hello', -1, -1, 'hi');
732
select insert('hello', -4294967295, -4294967295, 'hi');
733
select insert('hello', 4294967295, 4294967295, 'hi');
734
select insert('hello', -4294967296, -4294967296, 'hi');
735
select insert('hello', 4294967296, 4294967296, 'hi');
736
select insert('hello', -4294967297, -4294967297, 'hi');
737
select insert('hello', 4294967297, 4294967297, 'hi');
738
select insert('hello', -18446744073709551615, -18446744073709551615, 'hi');
739
select insert('hello', 18446744073709551615, 18446744073709551615, 'hi');
740
select insert('hello', -18446744073709551616, -18446744073709551616, 'hi');
741
select insert('hello', 18446744073709551616, 18446744073709551616, 'hi');
742
select insert('hello', -18446744073709551617, -18446744073709551617, 'hi');
743
select insert('hello', 18446744073709551617, 18446744073709551617, 'hi');
745
select repeat('hello', -1);
746
select repeat('hello', -4294967295);
747
select repeat('hello', 4294967295);
748
select repeat('hello', -4294967296);
749
select repeat('hello', 4294967296);
750
select repeat('hello', -4294967297);
751
select repeat('hello', 4294967297);
752
select repeat('hello', -18446744073709551615);
753
select repeat('hello', 18446744073709551615);
754
select repeat('hello', -18446744073709551616);
755
select repeat('hello', 18446744073709551616);
756
select repeat('hello', -18446744073709551617);
757
select repeat('hello', 18446744073709551617);
760
select space(-4294967295);
761
select space(4294967295);
762
select space(-4294967296);
763
select space(4294967296);
764
select space(-4294967297);
765
select space(4294967297);
766
select space(-18446744073709551615);
767
select space(18446744073709551615);
768
select space(-18446744073709551616);
769
select space(18446744073709551616);
770
select space(-18446744073709551617);
771
select space(18446744073709551617);
773
select rpad('hello', -1, '1');
774
select rpad('hello', -4294967295, '1');
775
select rpad('hello', 4294967295, '1');
776
select rpad('hello', -4294967296, '1');
777
select rpad('hello', 4294967296, '1');
778
select rpad('hello', -4294967297, '1');
779
select rpad('hello', 4294967297, '1');
780
select rpad('hello', -18446744073709551615, '1');
781
select rpad('hello', 18446744073709551615, '1');
782
select rpad('hello', -18446744073709551616, '1');
783
select rpad('hello', 18446744073709551616, '1');
784
select rpad('hello', -18446744073709551617, '1');
785
select rpad('hello', 18446744073709551617, '1');
787
select lpad('hello', -1, '1');
788
select lpad('hello', -4294967295, '1');
789
select lpad('hello', 4294967295, '1');
790
select lpad('hello', -4294967296, '1');
791
select lpad('hello', 4294967296, '1');
792
select lpad('hello', -4294967297, '1');
793
select lpad('hello', 4294967297, '1');
794
select lpad('hello', -18446744073709551615, '1');
795
select lpad('hello', 18446744073709551615, '1');
796
select lpad('hello', -18446744073709551616, '1');
797
select lpad('hello', 18446744073709551616, '1');
798
select lpad('hello', -18446744073709551617, '1');
799
select lpad('hello', 18446744073709551617, '1');
803
# BUG#17047: CHAR() and IN() can return NULL without signaling NULL
806
SELECT CHAR(0xff,0x8f);
807
SELECT CHAR(0xff,0x8f) IS NULL;
810
# Bug #24947: problem with some string function with int parameters
813
#select substring('abc', cast(2 as int));
814
#select repeat('a', cast(2 as int));
815
#select rpad('abc', cast(5 as integer), 'x');
816
#select lpad('abc', cast(5 as integer), 'x');
819
# Bug#15757: Wrong SUBSTRING() result when a tmp table was employed.
821
create table t1(f1 longtext);
822
insert into t1 values ("123"),("456");
823
select substring(f1,1,1) from t1 group by 1;
824
create table t2(f1 varchar(3));
825
insert into t1 values ("123"),("456");
826
select substring(f1,4,1), substring(f1,-4,1) from t2;
830
# Bug #25197 :repeat function returns null when using table field directly as count
834
DROP TABLE IF EXISTS t1;
838
`id` varchar(20) NOT NULL,
843
INSERT INTO `t1` (`id`, `tire`) VALUES ('A', 0), ('B', 1),('C', 2);
845
SELECT REPEAT( '#', tire ) AS A,
846
REPEAT( '#', tire % 999 ) AS B, tire FROM `t1`;
848
SELECT REPEAT('0', 0);
849
SELECT REPEAT('0', -2);
850
SELECT REPEAT('0', 2);
855
# Bug #26537: UNHEX() IS NULL comparison fails
858
SELECT UNHEX('G') IS NULL;
861
# Bug #26281: INSERT() function mishandles NUL on boundary condition
863
SELECT INSERT('abc', 3, 3, '1234');
864
SELECT INSERT('abc', 4, 3, '1234');
865
SELECT INSERT('abc', 5, 3, '1234');
866
SELECT INSERT('abc', 6, 3, '1234');
869
# Bug #27932: LOCATE with argument evaluated to NULL
872
SELECT LOCATE('foo', NULL);
873
SELECT LOCATE(NULL, 'o');
874
SELECT LOCATE(NULL, NULL);
875
SELECT LOCATE('foo', NULL) IS NULL;
876
SELECT LOCATE(NULL, 'o') IS NULL;
877
SELECT LOCATE(NULL, NULL) IS NULL;
878
SELECT ISNULL(LOCATE('foo', NULL));
879
SELECT ISNULL(LOCATE(NULL, 'o'));
880
SELECT ISNULL(LOCATE(NULL, NULL));
881
SELECT LOCATE('foo', NULL) <=> NULL;
882
SELECT LOCATE(NULL, 'o') <=> NULL;
883
SELECT LOCATE(NULL, NULL) <=> NULL;
885
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10));
887
INSERT INTO t1 VALUES (1, 'foo', 'o');
888
INSERT INTO t1 VALUES (2, 'foo', NULL);
889
INSERT INTO t1 VALUES (3, NULL, 'o');
890
INSERT INTO t1 VALUES (4, NULL, NULL);
892
SELECT id, LOCATE(a,p) FROM t1;
893
SELECT id, LOCATE(a,p) IS NULL FROM t1;
894
SELECT id, ISNULL(LOCATE(a,p)) FROM t1;
895
SELECT id, LOCATE(a,p) <=> NULL FROM t1;
896
SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL;
897
SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL;
902
# Bug #27130: SUBSTR with 0 as the last argument
905
SELECT SUBSTR('foo',1,0);
906
SELECT SUBSTR('foo',1,0);
907
SELECT SUBSTR('foo',1,0);
909
CREATE TABLE t1 (a varchar(10), len int);
910
INSERT INTO t1 VALUES ('bar', 2), ('foo', 0);
912
SELECT SUBSTR(a,1,len) FROM t1;
917
# Bug #28850: Potential bugs related to the return type of the CHAR function
920
CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1;
921
SELECT HEX(c1) from t1;
924
--echo End of 5.0 tests
added
removed
tests/t/func_str.test
