Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 2437 insertions, 0 deletions

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("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
+DROP TABLE t1;
+
+#
+# test for Bug #2290 "output truncated with ELT when using DISTINCT"
+#
+
+CREATE TABLE t1 (i int, j int);
+INSERT INTO t1 VALUES (1,1),(2,2);
+SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
+DROP TABLE t1;
+
+#
+# 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(<NULL> FROM <whatever>) gives NOT NULL
+# According to ANSI if one of the TRIM arguments is NULL, then the result
+# must be NULL too.
+#
+select trim(null from 'kate') as "must_be_null";
+select trim('xyz' from null) as "must_be_null";
+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(<decimal field with null>, 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( '<a>new year</a>', '/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 #