From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/func_str.test | 2437 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 2437 insertions(+) create mode 100644 mysql-test/main/func_str.test (limited to 'mysql-test/main/func_str.test') diff --git a/mysql-test/main/func_str.test b/mysql-test/main/func_str.test new file mode 100644 index 00000000..bbd84fde --- /dev/null +++ b/mysql-test/main/func_str.test @@ -0,0 +1,2437 @@ +# Description +# ----------- +# Testing string functions +--source include/have_sequence.inc + +set @save_max_allowed_packet=@@global.max_allowed_packet; +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + +let $mysqld_datadir= `select @@datadir`; + +set names latin1; + +select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; +select 'hello' 'monty'; +select length('\n\t\r\b\0\_\%\\'); +select bit_length('\n\t\r\b\0\_\%\\'); +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'); +select hex(char(256)); +select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ; +select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE'); +select position(binary 'll' in 'hello'),position('a' in binary 'hello'); +# +# Bug#11728 string function LEFT, +# strange undocumented behaviour, strict mode +# +select left('hello',null), right('hello',null); +select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ; +#enable after fix MDEV-27871 +--disable_view_protocol +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); +--enable_view_protocol +select substring_index('aaaaaaaaa1','a',1); +select substring_index('aaaaaaaaa1','aa',1); +select substring_index('aaaaaaaaa1','aa',2); +select substring_index('aaaaaaaaa1','aa',3); +select substring_index('aaaaaaaaa1','aa',4); +select substring_index('aaaaaaaaa1','aa',5); +select substring_index('aaaaaaaaa1','aaa',1); +select substring_index('aaaaaaaaa1','aaa',2); +select substring_index('aaaaaaaaa1','aaa',3); +select substring_index('aaaaaaaaa1','aaa',4); +select substring_index('aaaaaaaaa1','aaaa',1); +select substring_index('aaaaaaaaa1','aaaa',2); +select substring_index('aaaaaaaaa1','1',1); +select substring_index('aaaaaaaaa1','a',-1); +#enable after fix MDEV-29601 +--disable_service_connection +select substring_index('aaaaaaaaa1','aa',-1); +select substring_index('aaaaaaaaa1','aa',-2); +select substring_index('aaaaaaaaa1','aa',-3); +select substring_index('aaaaaaaaa1','aa',-4); +select substring_index('aaaaaaaaa1','aa',-5); +--enable_service_connection +select substring_index('aaaaaaaaa1','aaa',-1); +select substring_index('aaaaaaaaa1','aaa',-2); +select substring_index('aaaaaaaaa1','aaa',-3); +select substring_index('aaaaaaaaa1','aaa',-4); +select substring_index('the king of thethe hill','the',-2); +select substring_index('the king of the the hill','the',-2); +select substring_index('the king of the the hill','the',-2); +select substring_index('the king of the the hill',' the ',-1); +select substring_index('the king of the the hill',' the ',-2); +select substring_index('the king of the the hill',' ',-1); +select substring_index('the king of the the hill',' ',-2); +select substring_index('the king of the the hill',' ',-3); +select substring_index('the king of the the hill',' ',-4); +select substring_index('the king of the the hill',' ',-5); +select substring_index('the king of the.the hill','the',-2); +select substring_index('the king of thethethe.the hill','the',-3); +select substring_index('the king of thethethe.the hill','the',-1); +select substring_index('the king of the the hill','the',1); +select substring_index('the king of the the hill','the',2); +select substring_index('the king of the the hill','the',3); + +select concat(':',ltrim(' left '),':',rtrim(' right '),':'); +#enable after fix MDEV-27871 +--disable_view_protocol +select concat(':',trim(leading from ' left '),':',trim(trailing from ' 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.*.*'),':'); +select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo"); +--enable_view_protocol + +select concat_ws(', ','monty','was here','again'); +select concat_ws(NULL,'a'),concat_ws(',',NULL,''); +select concat_ws(',','',NULL,'a'); +#enable after fix MDEV-27871 +--disable_view_protocol +SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"'); + +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'); +--enable_metadata +select replace('aaaa','a','bbbb'); +--disable_metadata +select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; +select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); +--enable_view_protocol +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; +select md5('hello'); +select crc32("123"); +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"); +select aes_decrypt("", "a"); +select repeat('monty',5),concat('*',space(5),'*'); +select reverse('abc'),reverse('abcd'); +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); +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'); +select rpad('STRING', 20, CONCAT('p','a','d') ); +select lpad('STRING', 20, CONCAT('p','a','d') ); +select rpad('abcd',7),lpad('abcd',7); + +select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'); +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"); + +#enable after fix MDEV-27871 +--disable_view_protocol +select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000); +--enable_view_protocol +select decode(encode("abcdef","monty"),"monty")="abcdef"; + +select quote('\'\"\\test'); +select quote(concat('abc\'', '\\cba')); +select quote(1/0), quote('\0\Z'); +select length(quote(concat(char(0),"test"))); +#enable after fix MDEV-27871 +--disable_view_protocol +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"), unhex(NULL); +--enable_view_protocol +select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456")); +select length(unhex(md5("abrakadabra"))); + +# +# Bug #6564: QUOTE(NULL +# + +#enable after fix MDEV-28651 +--disable_view_protocol +select concat('a', quote(NULL)); +--enable_view_protocol + +# +# 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))); +#enable after fix MDEV-28535 +--disable_view_protocol +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))); +--enable_view_protocol + +# +# 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; + +# +# 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 NOT NULL, + bugstatus int(10) unsigned default NULL, + submitter int(10) unsigned default NULL +) ENGINE=MyISAM; + +#enable after fix MDEV-27871 +--disable_view_protocol +INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4); +SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1; +SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1; +SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1; +SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc; +--enable_view_protocol +drop table t1; + +# +# Test bug in AES_DECRYPT() when called with wrong argument +# + +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM; +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("",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; + +# +# 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; +drop table t1; + +# +# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str +# + +select trim(trailing 'foo' from 'foo'); +select trim(leading 'foo' from 'foo'); + +# +# crashing bug with QUOTE() and LTRIM() or TRIM() fixed +# Bug #7495 +# + +select quote(ltrim(concat(' ', 'a'))); +select quote(trim(concat(' ', 'a'))); + +# Bad results from QUOTE(). Bug #8248 +CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3; +SELECT QUOTE('A') FROM t1; +DROP TABLE t1; + +# Test collation and coercibility +# + +select 1=_latin1'1'; +select _latin1'1'=1; +select _latin2'1'=1; +select 1=_latin2'1'; +--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 1267 +select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); + +--error 1267 +select concat(_latin1'a',_latin2'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 +# +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'); +#enable after fix MDEV-29601 +--disable_service_connection +--error 1270 +select FIELD(_latin2'b','A','B'); +--error 1270 +select FIELD('b',_latin2'A','B'); +--enable_service_connection +select FIELD('1',_latin2'3','2',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 1267 +select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin); +--error 1267 +select POSITION(_latin1'B' IN _latin2'abcd'); + +select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'); + +# fix this: +--disable_parsing +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'); +--enable_parsing + +--error 1267 +select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); +--error 1267 +select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d'); + +select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); + +# fix this: +--disable_parsing +select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2); +select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2); +--enable_parsing + +--error 1267 +select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); +--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 1270 +select _latin2'B' between _latin1'a' and _latin1'b'; +--error 1270 +select _latin1'B' between _latin2'a' and _latin1'b'; +--error 1270 +select _latin1'B' between _latin1'a' and _latin2'b'; +--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 1270 +select _latin2'B' in (_latin1'a',_latin1'b'); +--error 1270 +select _latin1'B' in (_latin2'a',_latin1'b'); +--error 1270 +select _latin1'B' in (_latin1'a',_latin2'b'); +--error 1270 +select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b'); +--error 1270 +select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin); + +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)); +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')); +select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b')); +select collation(lpad(_latin2'a',4)), coercibility(lpad(_latin2'a',4)); +select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b')); +select collation(rpad(_latin2'a',4)), coercibility(rpad(_latin2'a',4)); +select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b')); +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' ')); +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')); +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)); +select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef')); +select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); +select collation(encode('abcd','ab')), coercibility(encode('abcd','ab')); + +create table t1 +select + bin(130), + oct(130), + conv(130,16,10), + hex(130), + char(130), + format(130,10), + left(_latin2'a',1), + right(_latin2'a',1), + lcase(_latin2'a'), + ucase(_latin2'a'), + substring(_latin2'a',1,1), + concat(_latin2'a',_latin2'b'), + lpad(_latin2'a',4,_latin2'b'), + rpad(_latin2'a',4,_latin2'b'), + lpad(_latin2'a',4), + rpad(_latin2'a',4), + concat_ws(_latin2'a',_latin2'b'), + make_set(255,_latin2'a',_latin2'b',_latin2'c'), + export_set(255,_latin2'y',_latin2'n',_latin2' '), + trim(_latin2' a '), + ltrim(_latin2' a '), + rtrim(_latin2' a '), + trim(LEADING _latin2' ' FROM _latin2' a '), + trim(TRAILING _latin2' ' FROM _latin2' a '), + trim(BOTH _latin2' ' FROM _latin2' a '), + repeat(_latin2'a',10), + reverse(_latin2'ab'), + quote(_latin2'ab'), + soundex(_latin2'ab'), + substring(_latin2'ab',1), + insert(_latin2'abcd',2,3,_latin2'ef'), + replace(_latin2'abcd',_latin2'b',_latin2'B'), + encode('abcd','ab') +; +show create table t1; +drop table t1; + +# +# Bug#9129 +# +create table t1 (a char character set latin2); +insert into t1 values (null); +select charset(a), collation(a), coercibility(a) from t1; +drop table t1; +select charset(null), collation(null), coercibility(null); +# +# Make sure OUTER JOIN is not replaced with a regular joun +# +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (a int, b int); +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (2,2),(3,3); +select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b) +where collation(t2.a) = _utf8'binary' order by t1.a,t2.a; +select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b) +where charset(t2.a) = _utf8'binary' order by t1.a,t2.a; +select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b) +where coercibility(t2.a) = 5 order by t1.a,t2.a; +DROP TABLE t1, t2; + +# +# 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; + +# +# 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); +--error 1267 +select * from t7 +where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; +drop table t7; + +select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); + +explain extended select md5('hello'); +explain extended select sha('abc'); +explain extended select sha1('abc'); +explain extended select soundex(''); +explain extended select 'mood' sounds like 'mud'; +explain extended select aes_decrypt(aes_encrypt('abc','1'),'1'); +explain extended select concat('*',space(5),'*'); +explain extended select reverse('abc'); +explain extended select rpad('a',4,'1'); +explain extended select lpad('a',4,'1'); +explain extended select rpad('a',4); +explain extended select lpad('a',4); +explain extended select concat_ws(',','',NULL,'a'); +explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c'); +explain extended select elt(2,1); +explain extended select locate("a","b",2); +explain extended select format(130,10); +explain extended select char(0); +explain extended select conv(130,16,10); +explain extended select hex(130); +explain extended select binary 'HE'; +explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' '); +explain extended select FIELD('b' COLLATE latin1_bin,'A','B'); +explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d'); +explain extended select collation(conv(130,16,10)); +explain extended select coercibility(conv(130,16,10)); +explain extended select length('\n\t\r\b\0\_\%\\'); +explain extended select bit_length('\n\t\r\b\0\_\%\\'); +explain extended select bit_length('\n\t\r\b\0\_\%\\'); +explain extended select concat('monty',' was here ','again'); +explain extended select length('hello'); +explain extended select char(ascii('h')); +explain extended select ord('h'); +explain extended select quote(1/0); +explain extended select crc32("123"); +explain extended select replace('aaaa','a','b'); +explain extended select insert('txs',2,1,'hi'); +explain extended select left(_latin2'a',1); +explain extended select right(_latin2'a',1); +explain extended select lcase(_latin2'a'); +explain extended select ucase(_latin2'a'); +explain extended select SUBSTR('abcdefg',3,2); +explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2); +explain extended select trim(_latin2' a '); +explain extended select ltrim(_latin2' a '); +explain extended select rtrim(_latin2' a '); +explain extended select decode(encode(repeat("a",100000),"monty"),"monty"); + +# +# lpad returns incorrect result (Bug #2182) +# + +SELECT lpad(12345, 5, "#"); + +# +# Problem the the CONV() function (Bug #2972) +# + +SELECT conv(71, 10, 36), conv('1Z', 36, 10); +SELECT conv(71, 10, 37), conv('1Z', 37, 10), conv(0,1,10),conv(0,0,10), conv(0,-1,10); + +# +# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089) +# + +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; + +# +# Test case for conversion of long string value to integer (Bug #3472) +# + +create table t1 (c1 INT, c2 INT UNSIGNED); +insert ignore into t1 values ('21474836461','21474836461'); +insert ignore into t1 values ('-21474836461','-21474836461'); +show warnings; +select * from t1; +drop table t1; + +# +# Bug #4878: LEFT() in integer/float context +# + +select left(1234, 3) + 0; + +# +# 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','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'); +select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12; +drop table t1; + +# +# Bug#7455 unexpected result: TRIM( FROM ) 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"; +select trim(leading NULL from 'kate') as "must_be_null"; +select trim(trailing NULL from 'xyz') as "must_be_null"; + +# +# Bug #7751 - conversion for a bigint unsigned constant +# + +CREATE TABLE t1 ( + id int(11) NOT NULL auto_increment, + a bigint(20) unsigned default NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM; + +INSERT INTO t1 VALUES +('0','16307858876001849059'); + +SELECT CONV('e251273eb74a8ee3', 16, 10); + +EXPLAIN +SELECT id + FROM t1 + WHERE a = 16307858876001849059; + +EXPLAIN + SELECT id + FROM t1 + WHERE a = CONV('e251273eb74a8ee3', 16, 10); + +DROP TABLE t1; + +# +# Bug #6317: string function CHAR, parameter is NULL, wrong result +# +SELECT CHAR(NULL,121,83,81,'76') as my_column; +SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column; +# +# Test case for bug #8669: null aes_decrypt result in order by query +# + +CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL); +CREATE TABLE t2 (id int NOT NULL UNIQUE); +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar')); +INSERT INTO t1 VALUES (2, 'not valid'); + +SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id; +SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id + ORDER BY t1.id; + +DROP TABLE t1, t2; + +# +# Bug #10944: Mishandling of NULL arguments in FIELD() +# +select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0); +select field(NULL,1,2,NULL), field(NULL,1,2,0); + +# +# Bug #10124: access by integer index with a string key that is not a number +# + +CREATE TABLE t1 (str varchar(20) PRIMARY KEY); +CREATE TABLE t2 (num int primary key); +INSERT INTO t1 VALUES ('notnumber'); +INSERT INTO t2 VALUES (0), (1); + +SELECT * FROM t1, t2 WHERE num=str; +SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6); + +DROP TABLE t1,t2; + +# +# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS +# + +CREATE TABLE t1( + id int(11) NOT NULL auto_increment, + pc int(11) NOT NULL default '0', + title varchar(20) default NULL, + PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES + (1, 0, 'Main'), + (2, 1, 'Toys'), + (3, 1, 'Games'); + +SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 + FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id + LEFT JOIN t1 AS t3 ON t2.pc=t3.id; +SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 + FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id + LEFT JOIN t1 AS t3 ON t2.pc=t3.id + WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%'; + +DROP TABLE t1; + + +CREATE TABLE t1( + trackid int(10) unsigned NOT NULL auto_increment, + trackname varchar(100) NOT NULL default '', + PRIMARY KEY (trackid) +); + +CREATE TABLE t2( + artistid int(10) unsigned NOT NULL auto_increment, + artistname varchar(100) NOT NULL default '', + PRIMARY KEY (artistid) +); + +CREATE TABLE t3( + trackid int(10) unsigned NOT NULL, + artistid int(10) unsigned NOT NULL, + PRIMARY KEY (trackid,artistid) +); + +INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York'); +INSERT INTO t2 VALUES (1, 'Vernon Duke'); +INSERT INTO t3 VALUES (1,1); + +SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname + FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid + LEFT JOIN t2 ON t2.artistid=t3.artistid + WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%'; + +DROP TABLE t1,t2,t3; + +# +# Correct length reporting from substring() (BUG#10269) +# +create table t1 (b varchar(5)); +insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); +select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1; +select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t; +drop table t1; + +# +# Bug #9854 hex() and out of range handling +# +select hex(29223372036854775809), hex(-29223372036854775809); + +# +# Bug #11311: Incorrect length returned from LPAD() and RPAD() +# +create table t1 (i int); +insert into t1 values (1000000000),(1); +--enable_metadata +--disable_view_protocol +select lpad(i, 7, ' ') as t from t1; +select rpad(i, 7, ' ') as t from t1; +--enable_view_protocol +--disable_metadata +drop table t1; + +# +# Bug #10418: LOAD_FILE does not behave like in manual if file does not exist +# + +select load_file("lkjlkj"); +select ifnull(load_file("lkjlkj"),"it's null"); + +# +# Bug#15351: Wrong collation used for comparison of md5() and sha() +# parameter can lead to a wrong result. +# +create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2)); +insert into t1 values ( 'test',md5('test')), ('test', sha('test')); +select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST")); +select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test")); +select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST")); +select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test")); +drop table t1; + +# +# Bug#18243: REVERSE changes its argument +# + +CREATE TABLE t1 (a varchar(10)); +INSERT INTO t1 VALUES ('abc'), ('xyz'); + +SELECT a, CONCAT(a,' ',a) AS c FROM t1 + HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a; + +SELECT a, CONCAT(a,' ',a) AS c FROM t1 + HAVING LEFT(CONCAT(a,' ',a), + LENGTH(CONCAT(a,' ',a))- + INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a; + +DROP TABLE t1; + +# +# Bug#17526: WRONG PRINT for TRIM FUNCTION with two arguments +# + +CREATE TABLE t1 (s varchar(10)); +INSERT INTO t1 VALUES ('yadda'), ('yaddy'); + +EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab'; +EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab'; +EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab'; +EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab'; +EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab'; + +DROP TABLE t1; + +# +# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly +# +create table t1(f1 varchar(4)); +explain extended select encode(f1,'zxcv') as 'enc' from t1; +explain extended select decode(f1,'zxcv') as 'enc' from t1; +drop table t1; + +# +# Bug #31758 inet_ntoa, oct, crashes server with null + filesort +# +create table t1 (a bigint not null)engine=myisam; +insert into t1 set a = 1024*1024*1024*4; +delete from t1 order by (inet_ntoa(a)) desc limit 10; +drop table t1; +create table t1 (a char(36) not null)engine=myisam; +insert ignore into t1 set a = ' '; +insert ignore into t1 set a = ' '; +select * from t1 order by (oct(a)); +drop table t1; + +--echo End of 4.1 tests + +# +# Bug #13361: SELECT FORMAT(, 2) crashes +# +create table t1 (d decimal default null); +insert into t1 values (null); +select format(d, 2) from t1; +drop table t1; + +# +# Bug #14676: substring_index() returns incorrect results +# +create table t1 (c varchar(40)); +insert into t1 values ('y,abc'),('y,abc'); +select c, substring_index(lcase(c), @q:=',', -1) as res from t1; +drop table t1; + +# +# Bug #17043: Casting trimmed string to decimal loses precision +# +select cast(rtrim(' 20.06 ') as decimal(19,2)); +select cast(ltrim(' 20.06 ') as decimal(19,2)); +select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)); + +# +# Bug #13975: "same string" + 0 has 2 different results +# +select conv("18383815659218730760",10,10) + 0; +select "18383815659218730760" + 0; + +# +# Bug #21698: substitution of a string field for a constant under a function +# + +CREATE TABLE t1 (code varchar(10)); +INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13'); + +SELECT ASCII(code), code FROM t1 WHERE code='A12'; +SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65; + +INSERT INTO t1 VALUES ('a12 '), ('A12 '); + +SELECT LENGTH(code), code FROM t1 WHERE code='A12'; +SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5; + +ALTER TABLE t1 ADD INDEX (code); +CREATE TABLE t2 (id varchar(10) PRIMARY KEY); +INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14'); + +SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id + WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON code=id + WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00'); + +DROP TABLE t1,t2; + +# +# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions +# + +select encode(NULL, NULL); +select encode("data", NULL); +select encode(NULL, "password"); + +select decode(NULL, NULL); +select decode("data", NULL); +select decode(NULL, "password"); + +select format(NULL, NULL); +select format(pi(), NULL); +select format(NULL, 2); + +#enable after fix MDEV-28585 +--disable_view_protocol +select benchmark(NULL, NULL); +select benchmark(0, NULL); +select benchmark(100, NULL); +select benchmark(NULL, 1+1); +--enable_view_protocol + +# +# Bug #20752: BENCHMARK with many iterations returns too quickly +# + +# not a string, but belongs with the above Bug#22684 +#enable after fix MDEV-28585 +--disable_view_protocol +select benchmark(-1, 1); +--enable_view_protocol +# +# Please note: +# 1) The collation of the password is irrelevant, the encryption uses +# the binary representation of the string without charset/collation. +# 2) These tests can not print the encoded text directly, because it's binary, +# and doing this would cause problems with source control. +# Instead, an md5() checksum is used, to verify the result indirectly. +# 3) Each md5() result must be identical. +# 4) The md5() result must never change, and must be stable across releases. +# +set @password="password"; +set @my_data="clear text to encode"; +select md5(encode(@my_data, "password")); +select md5(encode(@my_data, _utf8 "password")); +select md5(encode(@my_data, binary "password")); +select md5(encode(@my_data, _latin1 "password")); +select md5(encode(@my_data, _koi8r "password")); +select md5(encode(@my_data, (select "password" from dual))); +select md5(encode(@my_data, concat("pass", "word"))); +select md5(encode(@my_data, @password)); + +set @my_data="binary encoded data"; +select md5(decode(@my_data, "password")); +select md5(decode(@my_data, _utf8 "password")); +select md5(decode(@my_data, binary "password")); +select md5(decode(@my_data, _latin1 "password")); +select md5(decode(@my_data, _koi8r "password")); +select md5(decode(@my_data, (select "password" from dual))); +select md5(decode(@my_data, concat("pass", "word"))); +select md5(decode(@my_data, @password)); + +set @dec=5; +select format(pi(), (1+1)); +select format(pi(), (select 3 from dual)); +select format(pi(), @dec); + +set @bench_count=10; +select benchmark(10, pi()); +select benchmark(5+5, pi()); +select benchmark((select 10 from dual), pi()); +select benchmark(@bench_count, pi()); + + +# +# Bug #10963 +# 4294967296 18446744073709551616 + +select locate('he','hello',-2); +select locate('lo','hello',-4294967295); +select locate('lo','hello',4294967295); +select locate('lo','hello',-4294967296); +select locate('lo','hello',4294967296); +select locate('lo','hello',-4294967297); +select locate('lo','hello',4294967297); +select locate('lo','hello',-18446744073709551615); +select locate('lo','hello',18446744073709551615); +select locate('lo','hello',-18446744073709551616); +select locate('lo','hello',18446744073709551616); +select locate('lo','hello',-18446744073709551617); +select locate('lo','hello',18446744073709551617); + +select left('hello', 10); +select left('hello', 0); +select left('hello', -1); +select left('hello', -4294967295); +#enable after fix MDEV-29552 +--disable_view_protocol +select left('hello', 4294967295); +--enable_view_protocol +select left('hello', -4294967296); +#enable after fix MDEV-29552 +--disable_view_protocol +select left('hello', 4294967296); +--enable_view_protocol +select left('hello', -4294967297); +#enable after fix MDEV-29552 +--disable_view_protocol +select left('hello', 4294967297); +--enable_view_protocol +#view protocol generates additional warning +--disable_view_protocol +select left('hello', -18446744073709551615); +select left('hello', 18446744073709551615); +select left('hello', -18446744073709551616); +select left('hello', 18446744073709551616); +select left('hello', -18446744073709551617); +select left('hello', 18446744073709551617); +--enable_view_protocol + +select right('hello', 10); +select right('hello', 0); +select right('hello', -1); +select right('hello', -4294967295); +#enable after fix MDEV-29552 +--disable_view_protocol +select right('hello', 4294967295); +--enable_view_protocol +select right('hello', -4294967296); +#enable after fix MDEV-29552 +--disable_view_protocol +select right('hello', 4294967296); +--enable_view_protocol +select right('hello', -4294967297); +#enable after fix MDEV-29552 +--disable_view_protocol +select right('hello', 4294967297); +--enable_view_protocol +#view protocol generates additional warning +--disable_view_protocol +select right('hello', -18446744073709551615); +select right('hello', 18446744073709551615); +select right('hello', -18446744073709551616); +select right('hello', 18446744073709551616); +select right('hello', -18446744073709551617); +select right('hello', 18446744073709551617); +--enable_view_protocol + +select substring('hello', 2, -1); + +select substring('hello', -1, 1); +select substring('hello', -2, 1); +select substring('hello', -4294967295, 1); +select substring('hello', 4294967295, 1); +select substring('hello', -4294967296, 1); +select substring('hello', 4294967296, 1); +select substring('hello', -4294967297, 1); +select substring('hello', 4294967297, 1); +#view protocol generates additional warning +--disable_view_protocol +select substring('hello', -18446744073709551615, 1); +select substring('hello', 18446744073709551615, 1); +select substring('hello', -18446744073709551616, 1); +select substring('hello', 18446744073709551616, 1); +select substring('hello', -18446744073709551617, 1); +select substring('hello', 18446744073709551617, 1); +--enable_view_protocol +#enable after fix MDEV-28652 +--disable_view_protocol +select substring('hello', 1, -1); +select substring('hello', 1, -4294967295); +select substring('hello', 1, 4294967295); +select substring('hello', 1, -4294967296); +select substring('hello', 1, 4294967296); +select substring('hello', 1, -4294967297); +select substring('hello', 1, 4294967297); +--enable_view_protocol +#view protocol generates additional warning +--disable_view_protocol +select substring('hello', 1, -18446744073709551615); +select substring('hello', 1, 18446744073709551615); +select substring('hello', 1, -18446744073709551616); +select substring('hello', 1, 18446744073709551616); +select substring('hello', 1, -18446744073709551617); +select substring('hello', 1, 18446744073709551617); +--enable_view_protocol +select substring('hello', -1, -1); +select substring('hello', -4294967295, -4294967295); +select substring('hello', 4294967295, 4294967295); +select substring('hello', -4294967296, -4294967296); +select substring('hello', 4294967296, 4294967296); +select substring('hello', -4294967297, -4294967297); +select substring('hello', 4294967297, 4294967297); +#view protocol generates additional warning +--disable_view_protocol +select substring('hello', -18446744073709551615, -18446744073709551615); +select substring('hello', 18446744073709551615, 18446744073709551615); +select substring('hello', -18446744073709551616, -18446744073709551616); +select substring('hello', 18446744073709551616, 18446744073709551616); +select substring('hello', -18446744073709551617, -18446744073709551617); +select substring('hello', 18446744073709551617, 18446744073709551617); +--enable_view_protocol + +select insert('hello', -1, 1, 'hi'); +select insert('hello', -4294967295, 1, 'hi'); +select insert('hello', 4294967295, 1, 'hi'); +select insert('hello', -4294967296, 1, 'hi'); +select insert('hello', 4294967296, 1, 'hi'); +select insert('hello', -4294967297, 1, 'hi'); +select insert('hello', 4294967297, 1, 'hi'); +select insert('hello', -18446744073709551615, 1, 'hi'); +select insert('hello', 18446744073709551615, 1, 'hi'); +select insert('hello', -18446744073709551616, 1, 'hi'); +select insert('hello', 18446744073709551616, 1, 'hi'); +select insert('hello', -18446744073709551617, 1, 'hi'); +select insert('hello', 18446744073709551617, 1, 'hi'); +select insert('hello', 1, -1, 'hi'); +select insert('hello', 1, -4294967295, 'hi'); +select insert('hello', 1, 4294967295, 'hi'); +select insert('hello', 1, -4294967296, 'hi'); +select insert('hello', 1, 4294967296, 'hi'); +select insert('hello', 1, -4294967297, 'hi'); +select insert('hello', 1, 4294967297, 'hi'); +select insert('hello', 1, -18446744073709551615, 'hi'); +select insert('hello', 1, 18446744073709551615, 'hi'); +select insert('hello', 1, -18446744073709551616, 'hi'); +select insert('hello', 1, 18446744073709551616, 'hi'); +select insert('hello', 1, -18446744073709551617, 'hi'); +select insert('hello', 1, 18446744073709551617, 'hi'); +select insert('hello', -1, -1, 'hi'); +select insert('hello', -4294967295, -4294967295, 'hi'); +select insert('hello', 4294967295, 4294967295, 'hi'); +select insert('hello', -4294967296, -4294967296, 'hi'); +select insert('hello', 4294967296, 4294967296, 'hi'); +select insert('hello', -4294967297, -4294967297, 'hi'); +select insert('hello', 4294967297, 4294967297, 'hi'); +#enable after fix MDEV-27871 +--disable_view_protocol +select insert('hello', -18446744073709551615, -18446744073709551615, 'hi'); +select insert('hello', 18446744073709551615, 18446744073709551615, 'hi'); +select insert('hello', -18446744073709551616, -18446744073709551616, 'hi'); +select insert('hello', 18446744073709551616, 18446744073709551616, 'hi'); +select insert('hello', -18446744073709551617, -18446744073709551617, 'hi'); +select insert('hello', 18446744073709551617, 18446744073709551617, 'hi'); +--enable_view_protocol + +select repeat('hello', -1); +select repeat('hello', -4294967295); +select repeat('hello', 4294967295); +select repeat('hello', -4294967296); +select repeat('hello', 4294967296); +select repeat('hello', -4294967297); +select repeat('hello', 4294967297); +#view protocol generates additional warning +--disable_view_protocol +select repeat('hello', -18446744073709551615); +select repeat('hello', 18446744073709551615); +select repeat('hello', -18446744073709551616); +select repeat('hello', 18446744073709551616); +select repeat('hello', -18446744073709551617); +select repeat('hello', 18446744073709551617); +--enable_view_protocol + +select space(-1); +select space(-4294967295); +select space(4294967295); +select space(-4294967296); +select space(4294967296); +select space(-4294967297); +select space(4294967297); +#view protocol generates additional warning +--disable_view_protocol +select space(-18446744073709551615); +select space(18446744073709551615); +select space(-18446744073709551616); +select space(18446744073709551616); +select space(-18446744073709551617); +select space(18446744073709551617); +--enable_view_protocol + +select rpad('hello', -1, '1'); +select rpad('hello', -4294967295, '1'); +select rpad('hello', 4294967295, '1'); +select rpad('hello', -4294967296, '1'); +select rpad('hello', 4294967296, '1'); +select rpad('hello', -4294967297, '1'); +select rpad('hello', 4294967297, '1'); +#view protocol generates additional warning +--disable_view_protocol +select rpad('hello', -18446744073709551615, '1'); +select rpad('hello', 18446744073709551615, '1'); +select rpad('hello', -18446744073709551616, '1'); +select rpad('hello', 18446744073709551616, '1'); +select rpad('hello', -18446744073709551617, '1'); +select rpad('hello', 18446744073709551617, '1'); +--enable_view_protocol +select rpad('hello', -1); +select rpad('hello', -4294967295); +select rpad('hello', 4294967295); +select rpad('hello', -4294967296); +select rpad('hello', 4294967296); +select rpad('hello', -4294967297); +select rpad('hello', 4294967297); +#view protocol generates additional warning +--disable_view_protocol +select rpad('hello', -18446744073709551615); +select rpad('hello', 18446744073709551615); +select rpad('hello', -18446744073709551616); +select rpad('hello', 18446744073709551616); +select rpad('hello', -18446744073709551617); +select rpad('hello', 18446744073709551617); +--enable_view_protocol +select rpad('hello', 0, 'x'); +select rpad('hello', 0); + +select lpad('hello', -1, '1'); +select lpad('hello', -4294967295, '1'); +select lpad('hello', 4294967295, '1'); +select lpad('hello', -4294967296, '1'); +select lpad('hello', 4294967296, '1'); +select lpad('hello', -4294967297, '1'); +select lpad('hello', 4294967297, '1'); +#view protocol generates additional warning +--disable_view_protocol +select lpad('hello', -18446744073709551615, '1'); +select lpad('hello', 18446744073709551615, '1'); +select lpad('hello', -18446744073709551616, '1'); +select lpad('hello', 18446744073709551616, '1'); +select lpad('hello', -18446744073709551617, '1'); +select lpad('hello', 18446744073709551617, '1'); +--enable_view_protocol +select lpad('hello', -1); +select lpad('hello', -4294967295); +select lpad('hello', 4294967295); +select lpad('hello', -4294967296); +select lpad('hello', 4294967296); +select lpad('hello', -4294967297); +select lpad('hello', 4294967297); +#view protocol generates additional warning +--disable_view_protocol +select lpad('hello', -18446744073709551615); +select lpad('hello', 18446744073709551615); +select lpad('hello', -18446744073709551616); +select lpad('hello', 18446744073709551616); +select lpad('hello', -18446744073709551617); +select lpad('hello', 18446744073709551617); +--enable_view_protocol +select lpad('hello', 0, 'x'); +select lpad('hello', 0); + +--echo Check parameters count rpad / lpad +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select rpad('x'); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select rpad('x',2,'.','z'); + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select lpad('x'); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select lpad('x',2,'.','z'); + +# +# BUG#17047: CHAR() and IN() can return NULL without signaling NULL +# result +# +SET @orig_sql_mode = @@SQL_MODE; +SET SQL_MODE=traditional; + +SELECT CHAR(0xff,0x8f USING utf8); +SELECT CHAR(0xff,0x8f USING utf8) IS NULL; + +SET SQL_MODE=@orig_sql_mode; + +# +# Bug #24947: problem with some string function with unsigned int parameters +# + +select substring('abc', cast(2 as unsigned int)); +select repeat('a', cast(2 as unsigned int)); +select rpad('abc', cast(5 as unsigned integer), 'x'); +select lpad('abc', cast(5 as unsigned integer), 'x'); +select rpad('abc', cast(5 as unsigned integer)); +select lpad('abc', cast(5 as unsigned integer)); + +# +# Bug#15757: Wrong SUBSTRING() result when a tmp table was employed. +# +create table t1(f1 longtext); +insert into t1 values ("123"),("456"); +select substring(f1,1,1) from t1 group by 1; +create table t2(f1 varchar(3)); +insert into t1 values ("123"),("456"); +select substring(f1,4,1), substring(f1,-4,1) from t2; +drop table t1,t2; + +# +# Bug #25197 :repeat function returns null when using table field directly as count +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE `t1` ( + `id` varchar(20) NOT NULL, + `tire` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`id`) +); + +INSERT INTO `t1` (`id`, `tire`) VALUES ('A', 0), ('B', 1),('C', 2); + +SELECT REPEAT( '#', tire ) AS A, + REPEAT( '#', tire % 999 ) AS B, tire FROM `t1`; + +SELECT REPEAT('0', CAST(0 AS UNSIGNED)); +SELECT REPEAT('0', -2); +SELECT REPEAT('0', 2); + +DROP TABLE t1; + +# +# Bug #26537: UNHEX() IS NULL comparison fails +# +SELECT UNHEX('G'); +SELECT UNHEX('G') IS NULL; + +# +# Bug #26281: INSERT() function mishandles NUL on boundary condition +# +SELECT INSERT('abc', 3, 3, '1234'); +SELECT INSERT('abc', 4, 3, '1234'); +SELECT INSERT('abc', 5, 3, '1234'); +SELECT INSERT('abc', 6, 3, '1234'); + +# +# Bug #27530: Grouping on crc32, or create table select crc32 +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT CRC32(a) AS C FROM t1; + +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1; +SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1; +SELECT * FROM (SELECT CRC32(a) FROM t1) t2; +CREATE TABLE t2 SELECT CRC32(a) FROM t1; +desc t2; +SELECT * FROM v1; +SELECT * FROM (SELECT * FROM v1) x; + +DROP TABLE t1, t2; +DROP VIEW v1; + +# +# Bug #27932: LOCATE with argument evaluated to NULL +# + +SELECT LOCATE('foo', NULL) FROM DUAL; +SELECT LOCATE(NULL, 'o') FROM DUAL; +SELECT LOCATE(NULL, NULL) FROM DUAL; +SELECT LOCATE('foo', NULL) IS NULL FROM DUAL; +SELECT LOCATE(NULL, 'o') IS NULL FROM DUAL; +SELECT LOCATE(NULL, NULL) IS NULL FROM DUAL; +SELECT ISNULL(LOCATE('foo', NULL)) FROM DUAL; +SELECT ISNULL(LOCATE(NULL, 'o')) FROM DUAL; +SELECT ISNULL(LOCATE(NULL, NULL)) FROM DUAL; +SELECT LOCATE('foo', NULL) <=> NULL FROM DUAL; +SELECT LOCATE(NULL, 'o') <=> NULL FROM DUAL; +SELECT LOCATE(NULL, NULL) <=> NULL FROM DUAL; + +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10)); + +INSERT INTO t1 VALUES (1, 'foo', 'o'); +INSERT INTO t1 VALUES (2, 'foo', NULL); +INSERT INTO t1 VALUES (3, NULL, 'o'); +INSERT INTO t1 VALUES (4, NULL, NULL); + +SELECT id, LOCATE(a,p) FROM t1; +SELECT id, LOCATE(a,p) IS NULL FROM t1; +SELECT id, ISNULL(LOCATE(a,p)) FROM t1; +SELECT id, LOCATE(a,p) <=> NULL FROM t1; +SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL; +SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL; + +DROP TABLE t1; + +# +# Bug #27130: SUBSTR with UNSIGNED 0 as the last argument +# + +SELECT SUBSTR('foo',1,0) FROM DUAL; +SELECT SUBSTR('foo',1,CAST(0 AS SIGNED)) FROM DUAL; +SELECT SUBSTR('foo',1,CAST(0 AS UNSIGNED)) FROM DUAL; + +CREATE TABLE t1 (a varchar(10), len int unsigned); +INSERT INTO t1 VALUES ('bar', 2), ('foo', 0); + +SELECT SUBSTR(a,1,len) FROM t1; + +DROP TABLE t1; + +# +# Bug #28850: Potential bugs related to the return type of the CHAR function +# + +CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from t1; +DROP TABLE t1; + +CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from v1; +DROP VIEW v1; + +# +# Bug #35558 Wrong server metadata blows up the client +# +create table t1(a float); +insert into t1 values (1.33); +--enable_metadata +--disable_view_protocol +select format(a, 2) from t1; +--enable_view_protocol +--disable_metadata +drop table t1; + +# +# Bug #41868: crash or memory overrun with concat + upper, date_format functions +# + +CREATE TABLE t1 (c DATE, aa VARCHAR(30)); +INSERT INTO t1 VALUES ('2008-12-31','aaaaaa'); +SELECT DATE_FORMAT(c, GET_FORMAT(DATE, 'eur')) h, CONCAT(UPPER(aa),', ', aa) i FROM t1; +DROP TABLE t1; + + +--echo # +--echo # BUG#44774: load_file function produces valgrind warnings +--echo # +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES ('aaaaaaaa'); +SELECT LOAD_FILE(a) FROM t1; +DROP TABLE t1; + +# +# Bug#46815 CONCAT_WS returning wrong data +# +CREATE TABLE t1 (f2 VARCHAR(20)); +CREATE TABLE t2 (f2 VARCHAR(20)); + +INSERT INTO t1 VALUES ('MIN'),('MAX'); +INSERT INTO t2 VALUES ('LOAD'); + +SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; + +DROP TABLE t1, t2; + + +--echo End of 5.0 tests + +# +# Bug#44358 valgrind errors with decode() function +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1(f1 tinyint default null)engine=myisam; +insert into t1 values (-1),(null); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a; +explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a; +set optimizer_switch=@tmp_optimizer_switch; +drop table t1; + + +--echo # +--echo # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (a VARCHAR(20), b INT); +CREATE TABLE t2 (a VARCHAR(20), b INT); + +INSERT INTO t1 VALUES ('ABC', 1); +INSERT INTO t2 VALUES ('ABC', 1); + +#enable after fix MDEV-27871 +--disable_view_protocol +SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a) + FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b; + +SELECT DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a) + FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b; + +SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC') + FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b; +--enable_view_protocol + +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; + +INSERT INTO t1 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1); +INSERT INTO t2 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1); + +#enable after fix MDEV-27871 +--disable_view_protocol +SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a) + FROM t2 WHERE t2.b = 1 GROUP BY t2.b; +--enable_view_protocol + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#52164 Assertion failed: param.sort_length, file .\filesort.cc, line 149 +--echo # +CREATE TABLE t1 (a LONGBLOB NOT NULL); +INSERT INTO t1 VALUES (''),(''); +SELECT 1 FROM t1, t1 t2 +ORDER BY QUOTE(t1.a); +DROP TABLE t1; + +--echo # +--echo # Bug#57913 large negative number to string conversion functions crash +--echo # Bug#57810 case/when/then : Assertion failed: length || !scale +--echo # +SELECT '1' IN ('1', SUBSTRING(-9223372036854775809, 1)); +#view protocol generates additional warning +--disable_view_protocol +SELECT CONVERT(('' IN (REVERSE(CAST(('') AS DECIMAL)), '')), CHAR(3)); +--enable_view_protocol + +--echo # +--echo # Bug#58165: "my_empty_string" gets modified and causes LOAD DATA to fail +--echo # and other crashes +--echo # +CREATE TABLE t1 ( a TEXT ); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--disable_ps2_protocol +--eval SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug58165.txt'; +--enable_ps2_protocol +SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' ); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +# +# MDEV-759 lp:998340 - Valgrind complains on simple selects containing expression DAY(FROM_UNIXTIME(-1)) +# +SELECT SUBSTRING('1', DAY(FROM_UNIXTIME(-1))); +SELECT LEFT('1', DAY(FROM_UNIXTIME(-1))); +SELECT RIGHT('1', DAY(FROM_UNIXTIME(-1))); +SELECT REPEAT('1', DAY(FROM_UNIXTIME(-1))); +SELECT RPAD('hi', DAY(FROM_UNIXTIME(-1)),'?'); +SELECT LPAD('hi', DAY(FROM_UNIXTIME(-1)),'?'); +SELECT RPAD('hi', DAY(FROM_UNIXTIME(-1))); +SELECT LPAD('hi', DAY(FROM_UNIXTIME(-1))); + +# +# MDEV-4289 Assertion `0' fails in make_sortkey with GROUP_CONCAT, MAKE_SET, GROUP BY +# +create table t1 (i int); +insert into t1 values (null),(8); +select group_concat( i ), make_set( i, 'a', 'b' ) field from t1 group by field; +drop table t1; + +--echo # +--echo # Bug#11766684 59851: UNINITIALISED VALUE IN ITEM_FUNC_LIKE::SELECT_OPTIMIZE WITH SUBQUERY AND +--echo # + +CREATE TABLE t2(a INT, KEY(a)); +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t1(b INT, PRIMARY KEY(b)); +INSERT INTO t1 VALUES (0),(254); +SELECT 1 FROM t2 WHERE a LIKE +(SELECT EXPORT_SET(1, b, b, b, b) FROM t1 LIMIT 1); +DROP TABLE t1, t2; + +--echo End of 5.1 tests + +--echo # +--echo # Start of 5.3 tests +--echo # + +--echo # +--echo # Bug#11829861: SUBSTRING_INDEX() RESULTS IN MISSING CHARACTERS WHEN USED +--echo # INSIDE LOWER() +--echo # +SET @user_at_host = 'root@mytinyhost-PC.local'; +SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1)); +--echo # End of test BUG#11829861 + +--echo # +--echo # Bug#42404: SUBSTRING_INDEX() RESULTS ARE INCONSISTENT +--echo # + +CREATE TABLE t (i INT NOT NULL, c CHAR(255) NOT NULL); +INSERT INTO t VALUES (0,'.www.mysql.com'),(1,'.wwwmysqlcom'); +SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t WHERE i = 1; +SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t; +DROP TABLE t; +--echo # End of test BUG#42404 + +--echo # +--echo # End of 5.3 tests +--echo # + + +--echo Start of 5.4 tests +# +# WL#4584 Internationalized number format +# +SELECT format(12345678901234567890.123, 3); +#view protocol generates additional warning +--disable_view_protocol +SELECT format(12345678901234567890.123, 3, NULL); +--disable_view_protocol +SELECT format(12345678901234567890.123, 3, 'ar_AE'); +SELECT format(12345678901234567890.123, 3, 'ar_SA'); +SELECT format(12345678901234567890.123, 3, 'be_BY'); +SELECT format(12345678901234567890.123, 3, 'de_DE'); +SELECT format(12345678901234567890.123, 3, 'en_IN'); +SELECT format(12345678901234567890.123, 3, 'en_US'); +SELECT format(12345678901234567890.123, 3, 'it_CH'); +SELECT format(12345678901234567890.123, 3, 'ru_RU'); +SELECT format(12345678901234567890.123, 3, 'ta_IN'); + +CREATE TABLE t1 (fmt CHAR(5) NOT NULL); +INSERT INTO t1 VALUES ('ar_AE'); +INSERT INTO t1 VALUES ('ar_SA'); +INSERT INTO t1 VALUES ('be_BY'); +INSERT INTO t1 VALUES ('de_DE'); +INSERT INTO t1 VALUES ('en_IN'); +INSERT INTO t1 VALUES ('en_US'); +INSERT INTO t1 VALUES ('it_CH'); +INSERT INTO t1 VALUES ('ru_RU'); +INSERT INTO t1 VALUES ('ta_IN'); +SELECT fmt, format(12345678901234567890.123, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(12345678901234567890.123, 0, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(12345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-12345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-02345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-00345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-00045678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +DROP TABLE t1; + +SELECT format(123, 1, 'Non-existent-locale'); + +--echo End of 5.4 tests + +--echo # +--echo # Start of 5.5 tests +--echo # + +--echo # +--echo # Bug#55912 FORMAT with locale set fails for numbers < 1000 +--echo # +SELECT FORMAT(123.33, 2, 'no_NO'), FORMAT(1123.33, 2, 'no_NO'); +SELECT FORMAT(12333e-2, 2, 'no_NO'), FORMAT(112333e-2, 2, 'no_NO'); +CREATE TABLE t1 AS SELECT format(123,2,'no_NO'); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug#11764310 conv function crashes, negative argument to memcpy +--echo # +SELECT CONV(1,-2147483648,-2147483648); + +--echo # +--echo # Bug#12985030 SIMPLE QUERY WITH DECIMAL NUMBERS LEAKS MEMORY +--echo # + +#enable after fix MDEV-27871 +--disable_view_protocol +SELECT (rpad(1.0,2048,1)) IS NOT FALSE; +SELECT ((+0) IN +((0b111111111111111111111111111111111111111111111111111),(rpad(1.0,2048,1)), +(32767.1))); +SELECT ((rpad(1.0,2048,1)) = ('4(') ^ (0.1)); +--enable_view_protocol + +--error 1690 +SELECT +pow((rpad(10.0,2048,1)),(b'1111111111111111111111111111111111111111111')); +SELECT ((rpad(1.0,2048,1)) + (0) ^ ('../')); +#enable after fix MDEV-28585 +--disable_view_protocol +SELECT stddev_samp(rpad(1.0,2048,1)); +--enable_view_protocol +SELECT ((127.1) not in ((rpad(1.0,2048,1)),(''),(-1.1))); +SELECT ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)); + +--echo # +--echo # Bug#13359121 LARGE NUMBERS, /STRINGS/DTOA.C:662: +--echo # BALLOC: ASSERTION `K <= 15' FAILED. +--echo # Bug#12985021 SIMPLE QUERY WITH DECIMAL NUMBERS TAKE AN +--echo # EXTRAORDINARY LONG TIME TO EXECUTE + +SELECT @tmp_max:= @@global.max_allowed_packet; +SET @@global.max_allowed_packet=1024*1024*1024; +# switching connection to allow the new max_allowed_packet take effect +--connect (newconn, localhost, root,,) + +SELECT @@global.max_allowed_packet; + +do +format(rpad('111111111.1', + 1111111, + '999999999999999999999999999999999999999999'),0,'be_BY') +; + +# +# Originally it was DO instead of SELECT here. +# But in mysqltest --disable_prepare_warnings affects SELECT queries only +# and can't suppress prepare time warnings for DO. +# + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT +round( + concat( ( + coalesce( ( + linefromwkb('2147483648', + -b'1111111111111111111111111111111111111111111')), + ( convert('[.DC2.]',decimal(30,30)) ), + bit_count('') + ) ), + ( lpad( ( elt('01','}:K5')), + sha1('P'), + ( ( select '-9223372036854775808.1' > all (select ''))) + ) + ) + ) +) AS r; + +--connection conn1 +SET @@global.max_allowed_packet:= @tmp_max; +--disconnect newconn +# +# Bug#11765562 58545: +# EXPORT_SET() CAN BE USED TO MAKE ENTIRE SERVER COMPLETELY UNRESPONSIVE +# +SELECT @tmp_max:= @@global.max_allowed_packet; +SET @@global.max_allowed_packet=1024*1024*1024; +SELECT @@global.max_allowed_packet; +SELECT CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000))); +SET @@global.max_allowed_packet:= @tmp_max; + +--echo # +--echo # MDEV-4272: DIV operator crashes in Item_func_int_div::val_int +--echo # (incorrect NULL value handling by convert) +--echo # +create table t1(a int) select null; +select 1 div convert(a using utf8) from t1; +drop table t1; + +# +# BUG#11757250: REPLACE(...) INSIDE A STORED PROCEDURE. +# +create table t1 (a int); +create table t2 (a int); +create procedure foo (var char(100)) + select replace(var, '00000000', table_name) + from information_schema.tables where table_schema='test'; +--sorted_result +call foo('(( 00000000 ++ 00000000 ))'); +drop procedure foo; +drop table t1,t2; + +# +# Bug#18786138 SHA/MD5 HASHING FUNCTIONS DIE WITH "FILENAME" CHARACTER SET +# +select md5(_filename "a"), sha(_filename "a"); + +--echo # +--echo # MDEV-18881 Assertion `0' failed in make_sortkey upon SELECT with GROUP BY after LOAD DATA +--echo # + +CREATE TABLE t1 (a BIT(22), b CHAR(8) NOT NULL, c CHAR(8)); +INSERT INTO t1 VALUES (0xA4B,'foo','qux'); +INSERT INTO t1 VALUES (0x8F5,'bar','foobar'); +INSERT INTO t1 VALUES (0x0, '', NULL); +INSERT INTO t1 VALUES (0x4B, 'foo','qux'); +INSERT INTO t1 VALUES (0x8F5, 'bar', 'foobar'); +SET SESSION SQL_MODE= ''; +SELECT GROUP_CONCAT(c) AS f FROM t1 GROUP BY LPAD('foo', 20, b); +DROP TABLE t1; +SET SESSION SQL_MODE=DEFAULT; + +CREATE TABLE t1 AS SELECT + LPAD('a',10,' '), + RPAD('a',10,' '), + LPAD('a',10,' '), + RPAD('a',10,' '), + LPAD('a',10,NULL), + RPAD('a',10,NULL), + LPAD('a',10,''), + RPAD('a',10,''), + LPAD('a',10,RAND()), + RPAD('a',10,RAND()); +SHOW CREATE TABLE t1; +DROP TABLE t1; +SET SESSION SQL_MODE= ''; +CREATE TABLE t1 AS SELECT + LPAD('a',10,' '), + RPAD('a',10,' '), + LPAD('a',10,' '), + RPAD('a',10,' '), + LPAD('a',10,NULL), + RPAD('a',10,NULL), + LPAD('a',10,''), + RPAD('a',10,''), + LPAD('a',10,RAND()), + RPAD('a',10,RAND()); +SHOW CREATE TABLE t1; +DROP TABLE t1; +SET SESSION SQL_MODE=DEFAULT; + + +--echo # +--echo # End of 5.5 tests +--echo # + + +--echo # +--echo # Start of 5.6 tests +--echo # + +--echo # +--echo # WL#5510 Functions to_base64 and from_base64 +--echo # +let $1=64; +while($1) +{ + dec $1; + eval CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',$1)) AS to_base64; + SHOW CREATE TABLE t1; + SELECT to_base64, LENGTH(to_base64) FROM t1; + CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1; + SHOW CREATE TABLE t2; + SELECT from_base64, LENGTH(from_base64) FROM t2; + DROP TABLE t2; + DROP TABLE t1; + --echo +} +CREATE TABLE t1 (a VARBINARY(64)); +INSERT INTO t1 VALUES (0x00), (0x0000), (0x000000), (0x00000000); +INSERT INTO t1 VALUES (0x00010203040506070809); +SELECT TO_BASE64(a), hex(a) FROM t1 ORDER BY a; +DROP TABLE t1; + +--echo # +--echo # Test NULL output for NULL input +--echo # +SELECT TO_BASE64(NULL); +SELECT FROM_BASE64(NULL); + +--echo # +--echo # RFC4648 test vectors +--echo # +SELECT @b:= TO_BASE64(''), FROM_BASE64(@b); # "" +SELECT @b:= TO_BASE64('f'), FROM_BASE64(@b); # "Zg==" +SELECT @b:= TO_BASE64('fo'), FROM_BASE64(@b); # "Zm8=" +SELECT @b:= TO_BASE64('foo'), FROM_BASE64(@b); # "Zm9v" +SELECT @b:= TO_BASE64('foob'), FROM_BASE64(@b); # "Zm9vYg==" +SELECT @b:= TO_BASE64('fooba'), FROM_BASE64(@b); # "Zm9vYmE=" +SELECT @b:= TO_BASE64('foobar'), FROM_BASE64(@b); # "Zm9vYmFy" + + +--echo # +--echo # Invalid characters - return NULL +--echo # +SELECT hex(FROM_BASE64('#')); +SELECT hex(FROM_BASE64('A#')); +SELECT hex(FROM_BASE64('AB#')); +SELECT hex(FROM_BASE64('ABC#')); +SELECT hex(FROM_BASE64('ABCD#')); + +--echo # +--echo # "=" is not valid on the first and second positions of a quadruple +--echo # +SELECT hex(FROM_BASE64('=')); +SELECT hex(FROM_BASE64('A=')); +SELECT hex(FROM_BASE64('ABCD=')); +SELECT hex(FROM_BASE64('ABCDE=')); + +--echo # +--echo # Incomplete sequences - return NULL +--echo # +SELECT hex(FROM_BASE64('A')); +SELECT hex(FROM_BASE64('AB')); +SELECT hex(FROM_BASE64('ABC')); + +--echo # +--echo # Unexpected input after pad characters - return NULL +--echo # +SELECT hex(FROM_BASE64('AAA=x')); +SELECT hex(FROM_BASE64('AA==x')); + + +--echo # +--echo # Delimiters are allowed at any position +--echo # +SELECT hex(FROM_BASE64(' A B C D ')); +SELECT hex(FROM_BASE64(' A A = = ')); +SELECT hex(FROM_BASE64(' A A A = ')); +SELECT hex(FROM_BASE64(' A \n B \r C \t D ')); + +--echo # +--echo # Testing that to_base64 respects max_allowed_packet +--echo # +SELECT LENGTH(TO_BASE64(REPEAT('a', @@max_allowed_packet-10))); + +--echo # +--echo # Testing base64 with various data types +--echo # +CREATE TABLE t1 ( + i1 INT, + f1 FLOAT, + dc1 DECIMAL(10,5), + e1 ENUM('enum11','enum12','enum13'), + s1 SET('set1','set2','set3'), + t1 TIME, + d1 DATE, + dt1 DATETIME +); +INSERT INTO t1 VALUES +(-12345, -456.789, 123.45, 'enum13', 'set1,set3', +'01:02:03', '2010-01-01', '2011-01-01 02:03:04'); +SELECT FROM_BASE64(TO_BASE64(i1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(f1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(dc1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(e1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(s1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(t1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(d1)) FROM t1; +SELECT FROM_BASE64(TO_BASE64(dt1)) FROM t1; +DROP TABLE t1; + + +# +# BUG #12735829: SPACE() FUNCTION WARNING REFERS TO REPEAT() IN ER_WARN_ALLOWED_PACKET_OVERFLOWED +# + +SELECT SPACE(@@global.max_allowed_packet*2); + + +# +# BUG #11746123-23637: CHARSET AND COLLATION OF THE FUNCTION SPACE() +# + +SET NAMES latin1; +PREPARE stmt FROM "SELECT COLLATION(space(2))"; +EXECUTE stmt; +SET NAMES latin2; +EXECUTE stmt; + +disconnect conn1; +connection default; +set global max_allowed_packet=@save_max_allowed_packet; + +--echo # +--echo # End of 5.6 tests +--echo # + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-12681 Wrong VIEW results for CHAR(0xDF USING latin1) +--echo # + +EXPLAIN EXTENDED SELECT CHAR(0xDF USING latin1); +EXPLAIN EXTENDED SELECT CHAR(0xDF USING `binary`); +EXPLAIN EXTENDED SELECT CHAR(0xDF); + +--echo # +--echo # MDEV-15619 using CONVERT() inside AES_ENCRYPT() in an UPDATE corrupts data +--echo # + +CREATE TABLE t1 ( + id int(11) NOT NULL, + session_id varchar(255) DEFAULT NULL, + directory mediumtext, + checksum int(10) DEFAULT NULL, + last_update datetime DEFAULT NULL, + PRIMARY KEY (id), + KEY lastupdate (last_update) +) DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,'',NULL,38391,'2017-06-24 07:35:28'); +UPDATE t1 SET directory = AES_ENCRYPT(CONVERT('test stringrererejrjerjehrjekhrjkehrjkehrkjehrjkerhkjehrjekrhkjehrkjerhjkehrkjehrkjehrjkehrjkehrjkehrjkerjkehrjkehrjkehrjke rekjhrejrejhrjehgrehjgrhjerjhegrjherejhgrjhegrjehgrjhegrejhrgjehgrjhegrjhegrjhergjhegrjhegrhjegrjerhthkjjkdhjkgdfjkgjkdgdjkfjkhgjkfdhjgjkfdghkjdfghkjfdghfjkdghkdjfghdkjfghfjkdghfkjdghkjfdghfkjdghfkdjghfkjdghfdjkghjkdfhgdfjkghfjkdghfjkdghfjdkghfjkdghkfjdghfkjdghfkjdghkjdfghfjdkghjkfdghkjdfhgjkdfhgjkfdhgkjfdghkfjdhgkjfdgdjkejktjherjthkjrethkjrethjkerthjkerhtjkerhtkjerhtjkerhtjkerhtjkrehtkjerhtkjrehtjkrehtkjrehtkjerhtkjerhtjkrehtkjrehtjkrehtkjrethjkrethkjrehtkjethjkerhtjkrehtjkretkjerhtkjrehtjkerhtjkrehtjrehtkjrekjtrfgdsfgdhjsghjgfdhjsfhjdfgdhjshjdshjfghjdsfgjhsfgjhsdfgjhdsfgjdhsfgsjhfgjhsdfgsdjhfgjdhsfdjshfgdsjhfgjsdhfdjshfgdjhsfgdjshfgjdhsfgjhsdfgjhsdgfjhsdgfjhdsgfjhsgfjhsdgfjhdsgfhjsdehkjthrkjethjkre' USING latin1), '95F5A1F52A554'), last_update= NOW(); +SELECT directory IS NULL FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + id int(11) NOT NULL PRIMARY KEY, + directory mediumtext +) DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,AES_ENCRYPT(CONVERT(REPEAT('a',800) USING latin1),'95F5A1F52A554')); +SELECT AES_DECRYPT(directory,'95F5A1F52A554') FROM t1; +DROP TABLE t1; + +SET @enc=AES_ENCRYPT(REPEAT(_latin1'a',800),'95F5A1F52A554'); +CREATE TABLE t1 ( + id int(11) NOT NULL PRIMARY KEY, + directory mediumtext +) DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,AES_DECRYPT(CONVERT(@enc USING binary),'95F5A1F52A554')); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field +--echo # +CREATE TABLE t1 (a BIGINT(20) ZEROFILL); +INSERT INTO t1 VALUES (18446744073709551615),(0); +SELECT * FROM t1 WHERE a=18446744073709551615; +SELECT * FROM t1 WHERE FORMAT(a,0)='18,446,744,073,709,551,615'; +SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615'; +# This should not propagate the equality into FORMAT() +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615'; +DROP TABLE t1; + +--echo # +--echo # Bug#58081 Duplicate entry error when doing GROUP BY +--echo # MDEV-9332 Bug after upgrade to 10.1.10 +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(0),(1),(0),(0); +SELECT COUNT(*) FROM t1, t1 t2 GROUP BY INSERT('', t2.a, t1.a, @@global.max_binlog_size); +DROP TABLE t1; + +--let func=hex +--source include/func_str_ascii_checksum.inc + +--let func=to_base64 +--source include/func_str_ascii_checksum.inc + +--echo # +--echo # MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +--echo # + +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(128), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('YQ==',from_base64('YQ==')), ('Yq==', from_base64('Yq==')); +SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ==")); +DROP TABLE t1; + + +--echo # +--echo # MDEV-18738 ASAN heap-use-after-free in copy_if_not_alloced / copy_fields +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT REPLACE( CAST( CURDATE() AS BINARY ), CURDATE(), REPEAT('a',32) ) AS f FROM t1 GROUP BY f; +DROP TABLE t1; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT REPLACE( LEFT( CURDATE(), 4), LEFT(CURDATE(),4), REPEAT('a',32) ) AS f FROM t1 GROUP BY f; +DROP TABLE t1; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT REPLACE(RIGHT(CURDATE(), 4), RIGHT(CURDATE(),4), REPEAT('a',32)) AS f FROM t1 GROUP BY f; +DROP TABLE t1; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT REPLACE(SUBSTR(CURDATE(),2,3), SUBSTR(CURDATE(),2,3), REPEAT('a',32)) AS f FROM t1 GROUP BY f; +DROP TABLE t1; + + +--echo # +--echo # MDEV-19359 ASAN heap-use-after-free in copy_if_not_alloced / make_sortkey +--echo # + +#check and enable after fix MDEV-28536 +--disable_view_protocol +CREATE TABLE t1 (a INT, b TIME, c TIME); +INSERT INTO t1 VALUES (NULL,'22:56:45','22:56:45'),(4,'12:51:42','12:51:42'); +SELECT REPLACE( BINARY c, a, b ) f FROM t1 GROUP BY f WITH ROLLUP; +DROP TABLE t1; +--enable_view_protocol + +--echo # +--echo # End of 10.1 tests +--echo # + + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-24742 Server crashes in Charset::numchars / String::numchars +--echo # + +#enable after fix MDEV-27871 +--disable_view_protocol +SELECT NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux'); +--enable_view_protocol + +--echo # +--echo # Bug#31374305 - FORMAT() NOT DISPLAYING WHOLE NUMBER SIDE CORRECTLY +--echo # FOR ES_MX AND ES_ES LOCALES +--echo # + +DELIMITER |; +CREATE PROCEDURE load_locale_format_table() +BEGIN + DECLARE locale_list VARCHAR(1000) DEFAULT ' + es_AR,es_BO,es_CL,es_CO,es_CR,es_DO,es_EC,es_ES,es_GT,es_HN, + es_MX,es_NI,es_PA,es_PE,es_PR,es_PY,es_SV,es_US,es_UY,es_VE'; + SET @fmt_stmt = 'INSERT INTO locale_format VALUES + (?, FORMAT(12131254123412541,2,?));'; + PREPARE stmt FROM @fmt_stmt; + WHILE locale_list != '' DO + /* get the first locale from the list */ + SET @locale = + TRIM(REPLACE((SUBSTRING_INDEX(locale_list, ',', 1)), '\n','')); + EXECUTE stmt USING @locale, @locale; + /* remove the first locale from the list */ + IF LOCATE(',', locale_list) > 0 THEN + SET locale_list = + SUBSTRING(locale_list, LOCATE(',', locale_list) + 1); + ELSE + SET locale_list = ''; + END IF; + END WHILE; + DEALLOCATE PREPARE stmt; +END| +DELIMITER ;| + +CREATE TABLE locale_format(locale VARCHAR(10), formatted_string VARCHAR(100)); +CALL load_locale_format_table(); +SELECT * FROM locale_format; +DROP PROCEDURE load_locale_format_table; +DROP TABLE locale_format; + +--echo # +--echo # End of 10.2 tests +--echo # + + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12685 Oracle-compatible function CHR() +--echo # +--disable_service_connection +select chr(65); +create database mysqltest1 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin'; +use mysqltest1; +select charset(chr(65)), length(chr(65)),char_length(chr(65)); +select charset(chr(14844588)), length(chr(14844588)),char_length(chr(14844588)); +drop database mysqltest1; +use test; +--enable_service_connection + +--echo # +--echo # MDEV-12592 Illegal mix of collations with the HEX function +--echo # + +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES (0x09),('a'); +SELECT IF(a<' ',HEX(a),a) FROM t1 ORDER BY a; +DROP TABLE t1; + +--echo # +--echo # MDEV-12783 sql_mode=ORACLE: Functions LENGTH() and LENGTHB() +--echo # +SELECT LENGTHB('a'), LENGTHB(_utf8 0xC39F), LENGTHB(123), LENGTH(null); +EXPLAIN EXTENDED SELECT LENGTH('a'), LENGTHB('a'); + + +--echo # +--echo # MDEV-13555 Assertion `!item->null_value' failed in Type_handler::Item_send_str +--echo # + +CREATE TABLE t1 (c VARCHAR(8)); +INSERT INTO t1 VALUES (NULL),('bar'); +SELECT LPAD( c, 0, '?' ) FROM t1; +SELECT RPAD( c, 0, '?' ) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20292 REPEAT(x,-1) returns a wrong data type +--echo # + +CREATE OR REPLACE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE OR REPLACE TABLE t2 AS SELECT + REPEAT(i,NULL) AS cn, + REPEAT(i,0) AS c0, + REPEAT(i,-1) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-20303 SPACE(-1) returns a wrong data type +--echo # + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + SPACE(NULL) AS cn, + SPACE(0) AS c0, + SPACE(-1) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + LPAD(i,NULL,'a') AS cn, + LPAD(i,0,'a') AS c0, + LPAD(i,-1,'a') AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + RPAD(i,NULL,'a') AS cn, + RPAD(i,0,'a') AS c0, + RPAD(i,-1,'a') AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + LEFT(i,NULL) AS cn, + LEFT(i,0) AS c0, + LEFT(i,18446744073709551615) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +SELECT c1 FROM t2; +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-25560 Creating table with certain generated column crashes server +--echo # + +CREATE TABLE t1 (i int, b int AS (RPAD(123,1)) stored); + +--echo # Original case from the reporter +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE crash_test_2 ( + DATA_VALUE CHAR(10) NULL, + HAS_DATA BIT NOT NULL, + TEST_COLUMN CHAR(10) AS (RPAD(CASE WHEN HAS_DATA = 1 + THEN DATA_VALUE ELSE NULL END, 10)) STORED); + +--echo # Cleanup +DROP TABLE t1; + +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-30351 crash in Item_func_left::val_str +--echo # + +SELECT WEIGHT_STRING('aa') IN (LEFT(WEIGHT_STRING('aaa'),4),'bbb') as expect_1; + +SELECT UNHEX('0032') in (LEFT(UNHEX('003200'), 2),'dog') as expect_1; + +--echo # +--echo # MDEV-21841 CONV() function truncates the result type to 21 symbol. +--echo # + +CREATE TABLE t1(i BIGINT); +INSERT INTO t1 VALUES (-1); +CREATE TABLE t2 AS SELECT conv(i,16,2) from t1; +SELECT * FROM t2; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-18205 Assertion `str_length < len' failed in Binary_string::realloc_raw +--echo # + +SELECT GROUP_CONCAT( UpdateXML( 'new year', '/a', '2019-01-01 00:00:00' ), ENCODE('text','pass') ) AS f; + + +--echo # +--echo # MDEV-31184 Remove parser tokens DECODE_MARIADB_SYM and DECODE_ORACLE_SYM +--echo # + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(NULL); +SELECT DECODE(NULL,NULL); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(NULL, NULL, NULL); + + +--echo # +--echo # End of 10.4 tests +--echo # + +--echo # +--echo # MDEV-25704 Function random_bytes +--echo # + +create table t1 as select random_bytes(100); +show create table t1; +drop table t1; + +--echo # The sequence starts at 17 so that the probability of test failure is small enough (about 2^(-136)) +select count(*) from seq_17_to_1024 where random_bytes(seq) <=> random_bytes(seq); + +select count(*) from seq_1_to_1024 where length(random_bytes(seq)) = seq; + +select random_bytes(`0`),`1` from (values (0,1),(null,2),(0,3)) t1; + +--echo # +--echo # Test NULL output for NULL input +--echo # + +SELECT random_bytes(NULL); + +--echo # +--echo # Test For values outside range from 1 to 1024 return NULL +--echo # + +SELECT random_bytes(0); +SELECT random_bytes(-1); +SELECT random_bytes(-100); +SELECT random_bytes(-26); +SELECT random_bytes(-132); +SELECT random_bytes(1025); +SELECT random_bytes(11111); +SELECT random_bytes(2056); + +#double warning for view protocol +--disable_view_protocol +SELECT length(random_bytes(10.0)); +SELECT length(random_bytes(10.1)); +SELECT length(random_bytes(+1e1)); +SELECT length(random_bytes(time("00:01"))); +SELECT length(random_bytes("10.0")); +SELECT length(random_bytes("10.1")); +SELECT length(random_bytes("+1e1")); +SELECT length(random_bytes("10.0bunnies")); +SELECT length(random_bytes("10.1chickens")); +SELECT length(random_bytes("+1e1rabbits")); +--enable_view_protocol + +--delimiter | +create procedure p1() +begin + declare r ROW (c1 INT); + set r.c1= 10; + select random_bytes(r); +end| +--delimiter ; +--error 1241 +call p1(); +drop procedure p1; + +--echo # +--echo # Test For invalid argument return NULL +--echo # +#double warning for view protocol +--disable_view_protocol +SELECT random_bytes('s'); +SELECT random_bytes('r'); +SELECT random_bytes('res'); +SELECT random_bytes('test'); +--enable_view_protocol + +--echo # +--echo # MDEV-29108 RANDOM_BYTES - assertion in Create_tmp_table::finalize +--echo # + +CREATE TABLE t (a INT); +INSERT INTO t VALUES (1),(2); +SELECT RANDOM_BYTES(-10) f1, IFNULL(a,1) f2 FROM t GROUP BY f1, f2; + +# Cleanup +DROP TABLE t; + +--echo # +--echo # MDEV-29154 Excessive warnings upon a call to RANDOM_BYTES +--echo # +#double warning for view protocol +--disable_view_protocol +select length(random_bytes(cast('x' as unsigned)+1)); +select repeat('.', cast('x' as unsigned)+1); +--enable_view_protocol + +--echo # +--echo # MDEV-23149 Server crashes in my_convert / ErrConvString::ptr / Item_char_typecast::check_truncation_with_warn +--echo # +#double warning for view protocol +--disable_view_protocol +--replace_regex /'.*'/'...random bytes...'/ +select "a" in ("abc", (convert(random_bytes(8) ,binary(2)))); +--enable_view_protocol +--echo # +--echo # End of 10.10 tests +--echo # -- cgit v1.2.3