diff options
Diffstat (limited to 'mysql-test/t/func_str.test')
| -rw-r--r-- | mysql-test/t/func_str.test | 124 |
1 files changed, 77 insertions, 47 deletions
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 155ed459d1f..1c85f83baac 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1,5 +1,3 @@ -# Version: 3.23.29 -# # Description # ----------- # Testing string functions @@ -80,6 +78,9 @@ select quote(concat('abc\'', '\\cba')); select quote(1/0), quote('\0\Z'); select length(quote(concat(char(0),"test"))); select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235)))); +select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"); +select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456")); +select length(unhex(md5("abrakadabra"))); # # Wrong usage of functions @@ -141,6 +142,36 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); DROP TABLE t1; +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', + PRIMARY KEY(wid), +); + +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; + +# +# test for #739 + +CREATE TABLE t1 (title text) ENGINE=MyISAM; +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; + +# +# 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; +DROP TABLE t1; + # # Test collation and coercibility # @@ -149,22 +180,23 @@ select 1=_latin1'1'; select _latin1'1'=1; select _latin2'1'=1; select 1=_latin2'1'; ---error 1266 +--error 1267 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'); ---error 1266 +--error 1267 select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); ---error 1266 +--error 1267 select concat(_latin1'a',_latin2'a'); ---error 1269 -select concat(_latin1'a',_latin2'a',_latin5'a'); --error 1270 +select concat(_latin1'a',_latin2'a',_latin5'a'); +--error 1271 select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); - +--error 1267 +select concat_ws(_latin1'a',_latin2'a'); # # Test FIELD() and collations @@ -173,65 +205,64 @@ 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'); ---error 1269 +--error 1270 select FIELD(_latin2'b','A','B'); ---error 1269 +--error 1270 select FIELD('b',_latin2'A','B'); select FIELD('b',_latin2'A','B',1); - 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'); ---error 1266 +--error 1267 select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin); ---error 1266 +--error 1267 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'); ---error 1266 +--error 1267 select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); ---error 1266 +--error 1267 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); ---error 1266 +--error 1267 select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); ---error 1266 +--error 1267 select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2); 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; ---error 1269 +--error 1270 select _latin2'B' between _latin1'a' and _latin1'b'; ---error 1269 +--error 1270 select _latin1'B' between _latin2'a' and _latin1'b'; ---error 1269 +--error 1270 select _latin1'B' between _latin1'a' and _latin2'b'; ---error 1269 +--error 1270 select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b'; 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); ---error 1269 +--error 1270 select _latin2'B' in (_latin1'a',_latin1'b'); ---error 1269 +--error 1270 select _latin1'B' in (_latin2'a',_latin1'b'); ---error 1269 +--error 1270 select _latin1'B' in (_latin1'a',_latin2'b'); ---error 1269 +--error 1270 select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b'); ---error 1269 +--error 1270 select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin); select collation(bin(130)), coercibility(bin(130)); @@ -317,38 +348,37 @@ select SUBSTR('abcdefg',1,-1) FROM DUAL; # when an error on a lower level (in concat) has accured: # create table t7 (s1 char); ---error 1266 +--error 1267 select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; drop table t7; -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', - PRIMARY KEY(wid), -); +select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); -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; +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"); # -# test for #739 +# Bug #2182 +# -CREATE TABLE t1 (title text) ENGINE=MyISAM; -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; +SELECT lpad(12345, 5, "#"); + +# +# Bug #2972 +# + +SELECT conv(71, 10, 36), conv('1Z', 36, 10); -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"); # -# Bug #2182 +# Bug #3089 # -SELECT lpad(12345, 5, "#"); +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; |