diff options
Diffstat (limited to 'mysql-test/r/func_str.result')
| -rw-r--r-- | mysql-test/r/func_str.result | 109 |
1 files changed, 63 insertions, 46 deletions
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index ae6578795f6..3d7d693cdce 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; set names latin1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo @@ -165,6 +165,9 @@ the king of the select concat(':',ltrim(' left '),':',rtrim(' right '),':'); concat(':',ltrim(' left '),':',rtrim(' right '),':') :left : right: +select concat(':',trim(leading from ' left '),':',trim(trailing from ' right '),':'); +concat(':',trim(leading from ' left '),':',trim(trailing from ' right '),':') +:left : right: select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':'); concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':') :left: right: @@ -284,7 +287,7 @@ lpad('STRING', 20, CONCAT('p','a','d') ) padpadpadpadpaSTRING select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'); LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') -HAROLD HARRY +NULL NULL 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"); 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") 33 4 1.0 1.0 1.1 9 B @@ -350,6 +353,8 @@ Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - t select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql")); position("0" in "baaa" in (1)) position("0" in "1" in (1,2,3)) position("sql" in ("mysql")) 1 0 3 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'baaa' select position(("1" in (1,2,3)) in "01"); position(("1" in (1,2,3)) in "01") 2 @@ -376,7 +381,7 @@ category int(10) unsigned default NULL, program int(10) unsigned default NULL, bugdesc text, created datetime default NULL, -modified timestamp(14) NOT NULL, +modified timestamp NOT NULL, bugstatus int(10) unsigned default NULL, submitter int(10) unsigned default NULL ) ENGINE=MyISAM; @@ -507,9 +512,9 @@ select FIELD(_latin2'b','A','B'); ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' select FIELD('b',_latin2'A','B'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' -select FIELD('b',_latin2'A','B',1); -FIELD('b',_latin2'A','B',1) -1 +select FIELD('1',_latin2'3','2',1); +FIELD('1',_latin2'3','2',1) +3 select POSITION(_latin1'B' IN _latin1'abcd'); POSITION(_latin1'B' IN _latin1'abcd') 2 @@ -534,9 +539,9 @@ select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2) abcdabc select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index' +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substring_index' select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2); -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index' +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substring_index' select _latin1'B' between _latin1'a' and _latin1'c'; _latin1'B' between _latin1'a' and _latin1'c' 1 @@ -711,37 +716,37 @@ Warning 1265 Data truncated for column 'format(130,10)' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `bin(130)` char(64) NOT NULL default '', - `oct(130)` char(64) NOT NULL default '', - `conv(130,16,10)` char(64) NOT NULL default '', - `hex(130)` char(6) NOT NULL default '', - `char(130)` char(1) NOT NULL default '', - `format(130,10)` char(4) NOT NULL default '', - `left(_latin2'a',1)` char(1) character set latin2 NOT NULL default '', - `right(_latin2'a',1)` char(1) character set latin2 NOT NULL default '', - `lcase(_latin2'a')` char(1) character set latin2 NOT NULL default '', - `ucase(_latin2'a')` char(1) character set latin2 NOT NULL default '', - `substring(_latin2'a',1,1)` char(1) character set latin2 NOT NULL default '', - `concat(_latin2'a',_latin2'b')` char(2) character set latin2 NOT NULL default '', - `lpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '', - `rpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '', - `concat_ws(_latin2'a',_latin2'b')` char(1) character set latin2 NOT NULL default '', - `make_set(255,_latin2'a',_latin2'b',_latin2'c')` char(5) character set latin2 NOT NULL default '', - `export_set(255,_latin2'y',_latin2'n',_latin2' ')` char(127) character set latin2 NOT NULL default '', - `trim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', - `ltrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', - `rtrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', - `trim(LEADING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', - `trim(TRAILING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', - `trim(BOTH _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', - `repeat(_latin2'a',10)` char(10) character set latin2 NOT NULL default '', - `reverse(_latin2'ab')` char(2) character set latin2 NOT NULL default '', - `quote(_latin2'ab')` char(6) character set latin2 NOT NULL default '', - `soundex(_latin2'ab')` char(4) character set latin2 NOT NULL default '', - `substring(_latin2'ab',1)` char(2) character set latin2 NOT NULL default '', - `insert(_latin2'abcd',2,3,_latin2'ef')` char(6) character set latin2 NOT NULL default '', - `replace(_latin2'abcd',_latin2'b',_latin2'B')` char(4) character set latin2 NOT NULL default '', - `encode('abcd','ab')` binary(4) NOT NULL default '' + `bin(130)` varchar(64) NOT NULL default '', + `oct(130)` varchar(64) NOT NULL default '', + `conv(130,16,10)` varchar(64) NOT NULL default '', + `hex(130)` varchar(6) NOT NULL default '', + `char(130)` varchar(1) NOT NULL default '', + `format(130,10)` varchar(4) NOT NULL default '', + `left(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '', + `right(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '', + `lcase(_latin2'a')` varchar(1) character set latin2 NOT NULL default '', + `ucase(_latin2'a')` varchar(1) character set latin2 NOT NULL default '', + `substring(_latin2'a',1,1)` varchar(1) character set latin2 NOT NULL default '', + `concat(_latin2'a',_latin2'b')` varchar(2) character set latin2 NOT NULL default '', + `lpad(_latin2'a',4,_latin2'b')` varchar(4) character set latin2 NOT NULL default '', + `rpad(_latin2'a',4,_latin2'b')` varchar(4) character set latin2 NOT NULL default '', + `concat_ws(_latin2'a',_latin2'b')` varchar(1) character set latin2 NOT NULL default '', + `make_set(255,_latin2'a',_latin2'b',_latin2'c')` varchar(5) character set latin2 NOT NULL default '', + `export_set(255,_latin2'y',_latin2'n',_latin2' ')` varchar(127) character set latin2 NOT NULL default '', + `trim(_latin2' a ')` varchar(3) character set latin2 NOT NULL default '', + `ltrim(_latin2' a ')` varchar(3) character set latin2 NOT NULL default '', + `rtrim(_latin2' a ')` varchar(3) character set latin2 NOT NULL default '', + `trim(LEADING _latin2' ' FROM _latin2' a ')` varchar(3) character set latin2 NOT NULL default '', + `trim(TRAILING _latin2' ' FROM _latin2' a ')` varchar(3) character set latin2 NOT NULL default '', + `trim(BOTH _latin2' ' FROM _latin2' a ')` varchar(3) character set latin2 NOT NULL default '', + `repeat(_latin2'a',10)` varchar(10) character set latin2 NOT NULL default '', + `reverse(_latin2'ab')` varchar(2) character set latin2 NOT NULL default '', + `quote(_latin2'ab')` varchar(6) character set latin2 NOT NULL default '', + `soundex(_latin2'ab')` varchar(4) character set latin2 NOT NULL default '', + `substring(_latin2'ab',1)` varchar(2) character set latin2 NOT NULL default '', + `insert(_latin2'abcd',2,3,_latin2'ef')` varchar(6) character set latin2 NOT NULL default '', + `replace(_latin2'abcd',_latin2'b',_latin2'B')` varchar(4) character set latin2 NOT NULL default '', + `encode('abcd','ab')` varbinary(4) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a char character set latin2); @@ -806,7 +811,7 @@ explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'moo id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` +Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` SELECT lpad(12345, 5, "#"); lpad(12345, 5, "#") 12345 @@ -826,16 +831,16 @@ drop table t1, t2; create table t1 (c1 INT, c2 INT UNSIGNED); insert into t1 values ('21474836461','21474836461'); Warnings: -Warning 1265 Data truncated for column 'c1' at row 1 -Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1264 Out of range value adjusted for column 'c1' at row 1 +Warning 1264 Out of range value adjusted for column 'c2' at row 1 insert into t1 values ('-21474836461','-21474836461'); Warnings: -Warning 1265 Data truncated for column 'c1' at row 1 -Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1264 Out of range value adjusted for column 'c1' at row 1 +Warning 1264 Out of range value adjusted for column 'c2' at row 1 show warnings; Level Code Message -Warning 1265 Data truncated for column 'c1' at row 1 -Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1264 Out of range value adjusted for column 'c1' at row 1 +Warning 1264 Out of range value adjusted for column 'c2' at row 1 select * from t1; c1 c2 2147483647 4294967295 @@ -856,6 +861,12 @@ NULL select trim('xyz' from null) as "must_be_null"; must_be_null NULL +select trim(leading NULL from 'kate') as "must_be_null"; +must_be_null +NULL +select trim(trailing NULL from 'xyz') as "must_be_null"; +must_be_null +NULL CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, a bigint(20) unsigned default NULL, @@ -1000,3 +1011,9 @@ t 1000000 1 drop table t1; +create table t1 (d decimal default null); +insert into t1 values (null); +select format(d, 2) from t1; +format(d, 2) +NULL +drop table t1; |