path: root/mysql-test/r/func_str.result

drop table if exists t1,t2;
set global max_allowed_packet=1048576;
set names latin1;
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
select 'hello' 'monty';
hello
hellomonty
select length('\n\t\r\b\0\_\%\\');
length('\n\t\r\b\0\_\%\\')
10
select bit_length('\n\t\r\b\0\_\%\\');
bit_length('\n\t\r\b\0\_\%\\')
80
select char_length('\n\t\r\b\0\_\%\\');
char_length('\n\t\r\b\0\_\%\\')
10
select length(_latin1'\n\t\n\b\0\\_\\%\\');
length(_latin1'\n\t\n\b\0\\_\\%\\')
10
select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');
concat('monty',' was here ','again')	length('hello')	char(ascii('h'))	ord('h')
monty was here again	5	h	104
select hex(char(256));
hex(char(256))
0100
select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
locate('he','hello')	locate('he','hello',2)	locate('lo','hello',2)
1	0	4
select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE');
instr('hello','HE')	instr('hello',binary 'HE')	instr(binary 'hello','HE')
1	0	0
select position(binary 'll' in 'hello'),position('a' in binary 'hello');
position(binary 'll' in 'hello')	position('a' in binary 'hello')
3	0
select left('hello',null), right('hello',null);
left('hello',null)	right('hello',null)
NULL	NULL
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
left('hello',2)	right('hello',2)	substring('hello',2,2)	mid('hello',1,5)
he	lo	el	hello
select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1))
happy
select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
substring_index('www.tcx.se','.',-2)	substring_index('www.tcx.se','.',1)
tcx.se	www
select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
substring_index('www.tcx.se','tcx',1)	substring_index('www.tcx.se','tcx',-1)
www.	.se
select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
substring_index('.tcx.se','.',-2)	substring_index('.tcx.se','.tcx',-1)
tcx.se	.se
select substring_index('aaaaaaaaa1','a',1);
substring_index('aaaaaaaaa1','a',1)

select substring_index('aaaaaaaaa1','aa',1);
substring_index('aaaaaaaaa1','aa',1)

select substring_index('aaaaaaaaa1','aa',2);
substring_index('aaaaaaaaa1','aa',2)
aa
select substring_index('aaaaaaaaa1','aa',3);
substring_index('aaaaaaaaa1','aa',3)
aaaa
select substring_index('aaaaaaaaa1','aa',4);
substring_index('aaaaaaaaa1','aa',4)
aaaaaa
select substring_index('aaaaaaaaa1','aa',5);
substring_index('aaaaaaaaa1','aa',5)
aaaaaaaaa1
select substring_index('aaaaaaaaa1','aaa',1);
substring_index('aaaaaaaaa1','aaa',1)

select substring_index('aaaaaaaaa1','aaa',2);
substring_index('aaaaaaaaa1','aaa',2)
aaa
select substring_index('aaaaaaaaa1','aaa',3);
substring_index('aaaaaaaaa1','aaa',3)
aaaaaa
select substring_index('aaaaaaaaa1','aaa',4);
substring_index('aaaaaaaaa1','aaa',4)
aaaaaaaaa1
select substring_index('aaaaaaaaa1','aaaa',1);
substring_index('aaaaaaaaa1','aaaa',1)

select substring_index('aaaaaaaaa1','aaaa',2);
substring_index('aaaaaaaaa1','aaaa',2)
aaaa
select substring_index('aaaaaaaaa1','1',1);
substring_index('aaaaaaaaa1','1',1)
aaaaaaaaa
select substring_index('aaaaaaaaa1','a',-1);
substring_index('aaaaaaaaa1','a',-1)
1
select substring_index('aaaaaaaaa1','aa',-1);
substring_index('aaaaaaaaa1','aa',-1)
1
select substring_index('aaaaaaaaa1','aa',-2);
substring_index('aaaaaaaaa1','aa',-2)
aa1
select substring_index('aaaaaaaaa1','aa',-3);
substring_index('aaaaaaaaa1','aa',-3)
aaaa1
select substring_index('aaaaaaaaa1','aa',-4);
substring_index('aaaaaaaaa1','aa',-4)
aaaaaa1
select substring_index('aaaaaaaaa1','aa',-5);
substring_index('aaaaaaaaa1','aa',-5)
aaaaaaaaa1
select substring_index('aaaaaaaaa1','aaa',-1);
substring_index('aaaaaaaaa1','aaa',-1)
1
select substring_index('aaaaaaaaa1','aaa',-2);
substring_index('aaaaaaaaa1','aaa',-2)
aaa1
select substring_index('aaaaaaaaa1','aaa',-3);
substring_index('aaaaaaaaa1','aaa',-3)
aaaaaa1
select substring_index('aaaaaaaaa1','aaa',-4);
substring_index('aaaaaaaaa1','aaa',-4)
aaaaaaaaa1
select substring_index('the king of thethe hill','the',-2);
substring_index('the king of thethe hill','the',-2)
the hill
select substring_index('the king of the the hill','the',-2);
substring_index('the king of the the hill','the',-2)
 the hill
select substring_index('the king of the  the hill','the',-2);
substring_index('the king of the  the hill','the',-2)
  the hill
select substring_index('the king of the  the hill',' the ',-1);
substring_index('the king of the  the hill',' the ',-1)
hill
select substring_index('the king of the  the hill',' the ',-2);
substring_index('the king of the  the hill',' the ',-2)
 the hill
select substring_index('the king of the  the hill',' ',-1);
substring_index('the king of the  the hill',' ',-1)
hill
select substring_index('the king of the  the hill',' ',-2);
substring_index('the king of the  the hill',' ',-2)
the hill
select substring_index('the king of the  the hill',' ',-3);
substring_index('the king of the  the hill',' ',-3)
 the hill
select substring_index('the king of the  the hill',' ',-4);
substring_index('the king of the  the hill',' ',-4)
the  the hill
select substring_index('the king of the  the hill',' ',-5);
substring_index('the king of the  the hill',' ',-5)
of the  the hill
select substring_index('the king of the.the hill','the',-2);
substring_index('the king of the.the hill','the',-2)
.the hill
select substring_index('the king of thethethe.the hill','the',-3);
substring_index('the king of thethethe.the hill','the',-3)
the.the hill
select substring_index('the king of thethethe.the hill','the',-1);
substring_index('the king of thethethe.the hill','the',-1)
 hill
select substring_index('the king of the the hill','the',1);
substring_index('the king of the the hill','the',1)

select substring_index('the king of the the hill','the',2);
substring_index('the king of the the hill','the',2)
the king of 
select substring_index('the king of the the hill','the',3);
substring_index('the king of the the hill','the',3)
the king of the 
select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');
concat(':',ltrim('  left  '),':',rtrim('  right  '),':')
:left  :  right:
select concat(':',trim(leading from '  left  '),':',trim(trailing from '  right  '),':');
concat(':',trim(leading from '  left  '),':',trim(trailing from '  right  '),':')
:left  :  right:
select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':')
:left: right:
select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':')
:m:y:s:
select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':')
:my:sql:
select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':')
:my:sql:
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
TRIM("foo" FROM "foo")	TRIM("foo" FROM "foook")	TRIM("foo" FROM "okfoo")
	ok	ok
select concat_ws(', ','monty','was here','again');
concat_ws(', ','monty','was here','again')
monty, was here, again
select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
concat_ws(NULL,'a')	concat_ws(',',NULL,'')
NULL	
select concat_ws(',','',NULL,'a');
concat_ws(',','',NULL,'a')
,a
SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"')
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
this	is 	test
select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
replace('aaaa','a','b')	replace('aaaa','aa','b')	replace('aaaa','a','bb')	replace('aaaa','','b')	replace('bbbb','a','c')
bbbb	bb	bbbbbbbb	aaaa	bbbb
select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL')
this is a REAL test
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
soundex('')	soundex('he')	soundex('hello all folks')	soundex('#3556 in bugdb')
	H000	H4142	I51231
select 'mood' sounds like 'mud';
'mood' sounds like 'mud'
1
select 'Glazgo' sounds like 'Liverpool';
'Glazgo' sounds like 'Liverpool'
0
select null sounds like 'null';
null sounds like 'null'
NULL
select 'null' sounds like null;
'null' sounds like null
NULL
select null sounds like null;
null sounds like null
NULL
select md5('hello');
md5('hello')
5d41402abc4b2a76b9719d911017c592
select crc32("123");
crc32("123")
2286445522
select sha('abc');
sha('abc')
a9993e364706816aba3e25717850c26c9cd0d89d
select sha1('abc');
sha1('abc')
a9993e364706816aba3e25717850c26c9cd0d89d
select aes_decrypt(aes_encrypt('abc','1'),'1');
aes_decrypt(aes_encrypt('abc','1'),'1')
abc
select aes_decrypt(aes_encrypt('abc','1'),1);
aes_decrypt(aes_encrypt('abc','1'),1)
abc
select aes_encrypt(NULL,"a");
aes_encrypt(NULL,"a")
NULL
select aes_encrypt("a",NULL);
aes_encrypt("a",NULL)
NULL
select aes_decrypt(NULL,"a");
aes_decrypt(NULL,"a")
NULL
select aes_decrypt("a",NULL);
aes_decrypt("a",NULL)
NULL
select aes_decrypt("a","a");
aes_decrypt("a","a")
NULL
select aes_decrypt(aes_encrypt("","a"),"a");
aes_decrypt(aes_encrypt("","a"),"a")

select aes_decrypt("", "a");
aes_decrypt("", "a")
NULL
select repeat('monty',5),concat('*',space(5),'*');
repeat('monty',5)	concat('*',space(5),'*')
montymontymontymontymonty	*     *
select reverse('abc'),reverse('abcd');
reverse('abc')	reverse('abcd')
cba	dcba
select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
rpad('a',4,'1')	rpad('a',4,'12')	rpad('abcd',3,'12')	rpad(11, 10 , 22)	rpad("ab", 10, 22)
a111	a121	abc	1122222222	ab22222222
select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
lpad('a',4,'1')	lpad('a',4,'12')	lpad('abcd',3,'12')	lpad(11, 10 , 22)
111a	121a	abc	2222222211
select rpad(741653838,17,'0'),lpad(741653838,17,'0');
rpad(741653838,17,'0')	lpad(741653838,17,'0')
74165383800000000	00000000741653838
select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
rpad('abcd',7,'ab')	lpad('abcd',7,'ab')
abcdaba	abaabcd
select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
rpad('abcd',1,'ab')	lpad('abcd',1,'ab')
a	a
select rpad('STRING', 20, CONCAT('p','a','d') );
rpad('STRING', 20, CONCAT('p','a','d') )
STRINGpadpadpadpadpa
select lpad('STRING', 20, CONCAT('p','a','d') );
lpad('STRING', 20, CONCAT('p','a','d') )
padpadpadpadpaSTRING
select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')	GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')
NULL	NULL
select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0");
least(1,2,3) | greatest(16,32,8)	least(5,4)*1	greatest(-1.0,1.0)*1	least(3,2,1)*1.0	greatest(1,1.1,1.0)	least("10",9)	greatest("A","B","0")
33	4	1.0	1.0	1.1	9	B
select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000)
1
select decode(encode("abcdef","monty"),"monty")="abcdef";
decode(encode("abcdef","monty"),"monty")="abcdef"
1
select quote('\'\"\\test');
quote('\'\"\\test')
'\'"\\test'
select quote(concat('abc\'', '\\cba'));
quote(concat('abc\'', '\\cba'))
'abc\'\\cba'
select quote(1/0), quote('\0\Z');
quote(1/0)	quote('\0\Z')
NULL	'\0\Z'
select length(quote(concat(char(0),"test")));
length(quote(concat(char(0),"test")))
8
select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))))
27E0E3E6E7E8EAEB27
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
unhex(hex("foobar"))	hex(unhex("1234567890ABCDEF"))	unhex("345678")	unhex(NULL)
foobar	1234567890ABCDEF	4Vx	NULL
select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
hex(unhex("1"))	hex(unhex("12"))	hex(unhex("123"))	hex(unhex("1234"))	hex(unhex("12345"))	hex(unhex("123456"))
01	12	0123	1234	012345	123456
select length(unhex(md5("abrakadabra")));
length(unhex(md5("abrakadabra")))
16
select concat('a', quote(NULL));
concat('a', quote(NULL))
aNULL
select reverse("");
reverse("")

select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
insert("aa",100,1,"b")	insert("aa",1,3,"b")	left("aa",-1)	substring("a",1,2)
aa	b		a
select elt(2,1),field(NULL,"a","b","c"),reverse("");
elt(2,1)	field(NULL,"a","b","c")	reverse("")
NULL	0	
select locate("a","b",2),locate("","a",1);
locate("a","b",2)	locate("","a",1)
0	1
select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
ltrim("a")	rtrim("a")	trim(BOTH "" from "a")	trim(BOTH " " from "a")
a	a	a	a
select concat("1","2")|0,concat("1",".5")+0.0;
concat("1","2")|0	concat("1",".5")+0.0
12	1.5
select substring_index("www.tcx.se","",3);
substring_index("www.tcx.se","",3)

select length(repeat("a",100000000)),length(repeat("a",1000*64));
length(repeat("a",100000000))	length(repeat("a",1000*64))
NULL	64000
Warnings:
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
position("0" in "baaa" in (1))	position("0" in "1" in (1,2,3))	position("sql" in ("mysql"))
1	0	3
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'baaa'
select position(("1" in (1,2,3)) in "01");
position(("1" in (1,2,3)) in "01")
2
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)));
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)))
65500	64000	50000	60000
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)));
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)))
1000000	96000	160000	95000
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';
domain
hello.de
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
domain
test.de
drop table t1;
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;
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;
CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"')
"Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"')
"Link";"1";"1";"1";"0";"4"
SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter)
Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
bugdesc	REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb')
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
drop table t1;
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");
1
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;
elt(status_wnio,data_podp)
NULL
NULL
DROP TABLE t1;
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;
CONCAT("</a>",RPAD("",(55 - LENGTH(title)),"."))
NULL
</a>..........................
DROP TABLE t1;
CREATE TABLE t1 (i int, j int);
INSERT INTO t1 VALUES (1,1),(2,2);
SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
i	ELT(j, '345', '34')
1	345
2	34
DROP TABLE t1;
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;
a	quote(a)	isnull(quote(a))	quote(a) is null	ifnull(quote(a), 'n')
one	'one'	0	0	'one'
NULL	NULL	0	0	NULL
two	'two'	0	0	'two'
four	'four'	0	0	'four'
drop table t1;
select trim(trailing 'foo' from 'foo');
trim(trailing 'foo' from 'foo')

select trim(leading 'foo' from 'foo');
trim(leading 'foo' from 'foo')

select quote(ltrim(concat('    ', 'a')));
quote(ltrim(concat('    ', 'a')))
'a'
select quote(trim(concat('    ', 'a')));
quote(trim(concat('    ', 'a')))
'a'
CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
SELECT QUOTE('A') FROM t1;
QUOTE('A')
'A'
'A'
'A'
DROP TABLE t1;
select 1=_latin1'1';
1=_latin1'1'
1
select _latin1'1'=1;
_latin1'1'=1
1
select _latin2'1'=1;
_latin2'1'=1
1
select 1=_latin2'1';
1=_latin2'1'
1
select _latin1'1'=_latin2'1';
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
select row('a','b','c') = row('a','b','c');
row('a','b','c') = row('a','b','c')
1
select row('A','b','c') = row('a','b','c');
row('A','b','c') = row('a','b','c')
1
select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');
row('A' COLLATE latin1_bin,'b','c') = row('a','b','c')
0
select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');
row('A','b','c') = row('a' COLLATE latin1_bin,'b','c')
0
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '='
select concat(_latin1'a',_latin2'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
select concat(_latin1'a',_latin2'a',_latin5'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat'
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
ERROR HY000: Illegal mix of collations for operation 'concat'
select concat_ws(_latin1'a',_latin2'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat_ws'
select FIELD('b','A','B');
FIELD('b','A','B')
2
select FIELD('B','A','B');
FIELD('B','A','B')
2
select FIELD('b' COLLATE latin1_bin,'A','B');
FIELD('b' COLLATE latin1_bin,'A','B')
0
select FIELD('b','A' COLLATE latin1_bin,'B');
FIELD('b','A' COLLATE latin1_bin,'B')
0
select FIELD(_latin2'b','A','B');
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
select FIELD('b',_latin2'A','B');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
select FIELD('1',_latin2'3','2',1);
FIELD('1',_latin2'3','2',1)
3
select POSITION(_latin1'B' IN _latin1'abcd');
POSITION(_latin1'B' IN _latin1'abcd')
2
select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin)
0
select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd')
0
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate'
select POSITION(_latin1'B' IN _latin2'abcd');
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate'
select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')
2
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set'
select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set'
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2)
abcdabc
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substring_index'
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substring_index'
select _latin1'B' between _latin1'a' and _latin1'c';
_latin1'B' between _latin1'a' and _latin1'c'
1
select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'
0
select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'
0
select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin
0
select _latin2'B' between _latin1'a' and _latin1'b';
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
select _latin1'B' between _latin2'a' and _latin1'b';
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
select _latin1'B' between _latin1'a' and _latin2'b';
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'
select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'
select _latin1'B' in (_latin1'a',_latin1'b');
_latin1'B' in (_latin1'a',_latin1'b')
1
select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
_latin1'B' collate latin1_bin in (_latin1'a',_latin1'b')
0
select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
_latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b')
0
select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
_latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin)
0
select _latin2'B' in (_latin1'a',_latin1'b');
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
select _latin1'B' in (_latin2'a',_latin1'b');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
select _latin1'B' in (_latin1'a',_latin2'b');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN '
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN '
select collation(bin(130)), coercibility(bin(130));
collation(bin(130))	coercibility(bin(130))
latin1_swedish_ci	4
select collation(oct(130)), coercibility(oct(130));
collation(oct(130))	coercibility(oct(130))
latin1_swedish_ci	4
select collation(conv(130,16,10)), coercibility(conv(130,16,10));
collation(conv(130,16,10))	coercibility(conv(130,16,10))
latin1_swedish_ci	4
select collation(hex(130)), coercibility(hex(130));
collation(hex(130))	coercibility(hex(130))
latin1_swedish_ci	4
select collation(char(130)), coercibility(hex(130));
collation(char(130))	coercibility(hex(130))
binary	4
select collation(format(130,10)), coercibility(format(130,10));
collation(format(130,10))	coercibility(format(130,10))
latin1_swedish_ci	4
select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
collation(lcase(_latin2'a'))	coercibility(lcase(_latin2'a'))
latin2_general_ci	4
select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
collation(ucase(_latin2'a'))	coercibility(ucase(_latin2'a'))
latin2_general_ci	4
select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
collation(left(_latin2'a',1))	coercibility(left(_latin2'a',1))
latin2_general_ci	4
select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
collation(right(_latin2'a',1))	coercibility(right(_latin2'a',1))
latin2_general_ci	4
select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
collation(substring(_latin2'a',1,1))	coercibility(substring(_latin2'a',1,1))
latin2_general_ci	4
select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
collation(concat(_latin2'a',_latin2'b'))	coercibility(concat(_latin2'a',_latin2'b'))
latin2_general_ci	4
select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
collation(lpad(_latin2'a',4,_latin2'b'))	coercibility(lpad(_latin2'a',4,_latin2'b'))
latin2_general_ci	4
select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
collation(rpad(_latin2'a',4,_latin2'b'))	coercibility(rpad(_latin2'a',4,_latin2'b'))
latin2_general_ci	4
select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
collation(concat_ws(_latin2'a',_latin2'b'))	coercibility(concat_ws(_latin2'a',_latin2'b'))
latin2_general_ci	4
select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
collation(make_set(255,_latin2'a',_latin2'b',_latin2'c'))	coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'))
latin2_general_ci	4
select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
collation(export_set(255,_latin2'y',_latin2'n',_latin2' '))	coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '))
latin2_general_ci	4
select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
collation(trim(_latin2' a '))	coercibility(trim(_latin2' a '))
latin2_general_ci	4
select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
collation(ltrim(_latin2' a '))	coercibility(ltrim(_latin2' a '))
latin2_general_ci	4
select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
collation(rtrim(_latin2' a '))	coercibility(rtrim(_latin2' a '))
latin2_general_ci	4
select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
collation(trim(LEADING _latin2' ' FROM _latin2'a'))	coercibility(trim(LEADING _latin2'a' FROM _latin2'a'))
latin2_general_ci	4
select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
collation(trim(TRAILING _latin2' ' FROM _latin2'a'))	coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'))
latin2_general_ci	4
select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
collation(trim(BOTH _latin2' ' FROM _latin2'a'))	coercibility(trim(BOTH _latin2'a' FROM _latin2'a'))
latin2_general_ci	4
select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
collation(repeat(_latin2'a',10))	coercibility(repeat(_latin2'a',10))
latin2_general_ci	4
select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
collation(reverse(_latin2'ab'))	coercibility(reverse(_latin2'ab'))
latin2_general_ci	4
select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
collation(quote(_latin2'ab'))	coercibility(quote(_latin2'ab'))
latin2_general_ci	4
select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab'));
collation(soundex(_latin2'ab'))	coercibility(soundex(_latin2'ab'))
latin2_general_ci	4
select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
collation(substring(_latin2'ab',1))	coercibility(substring(_latin2'ab',1))
latin2_general_ci	4
select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
collation(insert(_latin2'abcd',2,3,_latin2'ef'))	coercibility(insert(_latin2'abcd',2,3,_latin2'ef'))
latin2_general_ci	4
select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
collation(replace(_latin2'abcd',_latin2'b',_latin2'B'))	coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'))
latin2_general_ci	4
select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
collation(encode('abcd','ab'))	coercibility(encode('abcd','ab'))
binary	4
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'),
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;
Table	Create Table
t1	CREATE TABLE `t1` (
  `bin(130)` varchar(64) DEFAULT NULL,
  `oct(130)` varchar(64) DEFAULT NULL,
  `conv(130,16,10)` varchar(64) DEFAULT NULL,
  `hex(130)` varchar(6) NOT NULL,
  `char(130)` varbinary(4) NOT NULL,
  `format(130,10)` varchar(37) NOT NULL,
  `left(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL,
  `right(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL,
  `lcase(_latin2'a')` varchar(1) CHARACTER SET latin2 NOT NULL,
  `ucase(_latin2'a')` varchar(1) CHARACTER SET latin2 NOT NULL,
  `substring(_latin2'a',1,1)` varchar(1) CHARACTER SET latin2 NOT NULL,
  `concat(_latin2'a',_latin2'b')` varchar(2) CHARACTER SET latin2 NOT NULL,
  `lpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 NOT NULL,
  `rpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 NOT NULL,
  `concat_ws(_latin2'a',_latin2'b')` varchar(1) CHARACTER SET latin2 NOT NULL,
  `make_set(255,_latin2'a',_latin2'b',_latin2'c')` varchar(5) CHARACTER SET latin2 NOT NULL,
  `export_set(255,_latin2'y',_latin2'n',_latin2' ')` varchar(127) CHARACTER SET latin2 NOT NULL,
  `trim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL,
  `ltrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL,
  `rtrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL,
  `trim(LEADING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL,
  `trim(TRAILING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL,
  `trim(BOTH _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL,
  `repeat(_latin2'a',10)` varchar(10) CHARACTER SET latin2 NOT NULL,
  `reverse(_latin2'ab')` varchar(2) CHARACTER SET latin2 NOT NULL,
  `quote(_latin2'ab')` varchar(6) CHARACTER SET latin2 NOT NULL,
  `soundex(_latin2'ab')` varchar(4) CHARACTER SET latin2 NOT NULL,
  `substring(_latin2'ab',1)` varchar(2) CHARACTER SET latin2 NOT NULL,
  `insert(_latin2'abcd',2,3,_latin2'ef')` varchar(6) CHARACTER SET latin2 NOT NULL,
  `replace(_latin2'abcd',_latin2'b',_latin2'B')` varchar(4) CHARACTER SET latin2 NOT NULL,
  `encode('abcd','ab')` varbinary(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a char character set latin2);
insert into t1 values (null);
select charset(a), collation(a), coercibility(a) from t1;
charset(a)	collation(a)	coercibility(a)
latin2	latin2_general_ci	2
drop table t1;
select charset(null), collation(null), coercibility(null);
charset(null)	collation(null)	coercibility(null)
binary	binary	6
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;
a	b	a	b
1	1	NULL	NULL
2	2	2	2
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;
a	b	a	b
1	1	NULL	NULL
2	2	2	2
select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
where coercibility(t2.a) = 5 order by t1.a,t2.a;
a	b	a	b
1	1	NULL	NULL
2	2	2	2
DROP TABLE t1, t2;
select SUBSTR('abcdefg',3,2);
SUBSTR('abcdefg',3,2)
cd
select SUBSTRING('abcdefg',3,2);
SUBSTRING('abcdefg',3,2)
cd
select SUBSTR('abcdefg',-3,2) FROM DUAL;
SUBSTR('abcdefg',-3,2)
ef
select SUBSTR('abcdefg',-1,5) FROM DUAL;
SUBSTR('abcdefg',-1,5)
g
select SUBSTR('abcdefg',0,0) FROM DUAL;
SUBSTR('abcdefg',0,0)

select SUBSTR('abcdefg',-1,-1) FROM DUAL;
SUBSTR('abcdefg',-1,-1)

select SUBSTR('abcdefg',1,-1) FROM DUAL;
SUBSTR('abcdefg',1,-1)

create table t7 (s1 char);
select * from t7
where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat'
drop table t7;
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)	substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2)
1abcd;2abcd	3abcd;4abcd
explain extended select md5('hello');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select md5('hello') AS `md5('hello')`
explain extended select sha('abc');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select sha('abc') AS `sha('abc')`
explain extended select sha1('abc');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select sha('abc') AS `sha1('abc')`
explain extended select soundex('');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select soundex('') AS `soundex('')`
explain extended select 'mood' sounds like 'mud';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select (soundex('mood') = soundex('mud')) AS `'mood' sounds like 'mud'`
explain extended select aes_decrypt(aes_encrypt('abc','1'),'1');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select aes_decrypt(aes_encrypt('abc','1'),'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`
explain extended select concat('*',space(5),'*');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select concat('*',space(5),'*') AS `concat('*',space(5),'*')`
explain extended select reverse('abc');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select reverse('abc') AS `reverse('abc')`
explain extended select rpad('a',4,'1');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select rpad('a',4,'1') AS `rpad('a',4,'1')`
explain extended select lpad('a',4,'1');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select lpad('a',4,'1') AS `lpad('a',4,'1')`
explain extended select concat_ws(',','',NULL,'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select concat_ws(',','',NULL,'a') AS `concat_ws(',','',NULL,'a')`
explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a', _latin2'b', _latin2'c')`
explain extended select elt(2,1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select elt(2,1) AS `elt(2,1)`
explain extended select locate("a","b",2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select locate('a','b',2) AS `locate("a","b",2)`
explain extended select format(130,10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select format(130,10) AS `format(130,10)`
explain extended select char(0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select char(0) AS `char(0)`
explain extended select conv(130,16,10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select conv(130,16,10) AS `conv(130,16,10)`
explain extended select hex(130);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select hex(130) AS `hex(130)`
explain extended select binary 'HE';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select cast('HE' as char charset binary) AS `binary 'HE'`
explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' ');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y', _latin2'n', _latin2' ')`
explain extended select FIELD('b' COLLATE latin1_bin,'A','B');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select field(('b' collate latin1_bin),'A','B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`
explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B', _latin1'a,b,c,d')`
explain extended select collation(conv(130,16,10));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select collation(conv(130,16,10)) AS `collation(conv(130,16,10))`
explain extended select coercibility(conv(130,16,10));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`
explain extended select length('\n\t\r\b\0\_\%\\');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select length('\n	\r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`
explain extended select bit_length('\n\t\r\b\0\_\%\\');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select bit_length('\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`
explain extended select bit_length('\n\t\r\b\0\_\%\\');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select bit_length('\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`
explain extended select concat('monty',' was here ','again');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select concat('monty',' was here ','again') AS `concat('monty',' was here ','again')`
explain extended select length('hello');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select length('hello') AS `length('hello')`
explain extended select char(ascii('h'));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select char(ascii('h')) AS `char(ascii('h'))`
explain extended select ord('h');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select ord('h') AS `ord('h')`
explain extended select quote(1/0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select quote((1 / 0)) AS `quote(1/0)`
explain extended select crc32("123");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select crc32('123') AS `crc32("123")`
explain extended select replace('aaaa','a','b');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select replace('aaaa','a','b') AS `replace('aaaa','a','b')`
explain extended select insert('txs',2,1,'hi');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select insert('txs',2,1,'hi') AS `insert('txs',2,1,'hi')`
explain extended select left(_latin2'a',1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select left(_latin2'a',1) AS `left(_latin2'a',1)`
explain extended select right(_latin2'a',1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select right(_latin2'a',1) AS `right(_latin2'a',1)`
explain extended select lcase(_latin2'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select lcase(_latin2'a') AS `lcase(_latin2'a')`
explain extended select ucase(_latin2'a');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select ucase(_latin2'a') AS `ucase(_latin2'a')`
explain extended select SUBSTR('abcdefg',3,2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select substr('abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`
explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select substring_index('1abcd;2abcd;3abcd;4abcd',';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`
explain extended select trim(_latin2' a ');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select trim(_latin2' a ') AS `trim(_latin2' a ')`
explain extended select ltrim(_latin2' a ');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`
explain extended select rtrim(_latin2' a ');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`
explain extended select decode(encode(repeat("a",100000),"monty"),"monty");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select decode(encode(repeat('a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")`
SELECT lpad(12345, 5, "#");
lpad(12345, 5, "#")
12345
SELECT conv(71, 10, 36), conv('1Z', 36, 10);
conv(71, 10, 36)	conv('1Z', 36, 10)
1Z	71
SELECT conv(71, 10, 37), conv('1Z', 37, 10), conv(0,1,10),conv(0,0,10), conv(0,-1,10);
conv(71, 10, 37)	conv('1Z', 37, 10)	conv(0,1,10)	conv(0,0,10)	conv(0,-1,10)
NULL	NULL	NULL	NULL	NULL
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;
name
aaaaaaaaaaccccc
bbbbbbbbbbddddd
drop table t1, t2;
create table t1 (c1 INT, c2 INT UNSIGNED);
insert into t1 values ('21474836461','21474836461');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
Warning	1264	Out of range value for column 'c2' at row 1
insert into t1 values ('-21474836461','-21474836461');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
Warning	1264	Out of range value for column 'c2' at row 1
show warnings;
Level	Code	Message
Warning	1264	Out of range value for column 'c1' at row 1
Warning	1264	Out of range value for column 'c2' at row 1
select * from t1;
c1	c2
2147483647	4294967295
-2147483648	0
drop table t1;
select left(1234, 3) + 0;
left(1234, 3) + 0
123
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;
total	reg
10	2004-12-10
drop table t1;
select trim(null from 'kate') as "must_be_null";
must_be_null
NULL
select trim('xyz' from null) as "must_be_null";
must_be_null
NULL
select trim(leading NULL from 'kate') as "must_be_null";
must_be_null
NULL
select trim(trailing NULL from 'xyz') as "must_be_null";
must_be_null
NULL
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
a bigint(20) unsigned default NULL,
PRIMARY KEY  (id)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('0','16307858876001849059');
SELECT CONV('e251273eb74a8ee3', 16, 10);
CONV('e251273eb74a8ee3', 16, 10)
16307858876001849059
EXPLAIN 
SELECT id
FROM t1
WHERE a = 16307858876001849059;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
EXPLAIN 
SELECT id
FROM t1
WHERE a = CONV('e251273eb74a8ee3', 16, 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
DROP TABLE t1;
SELECT CHAR(NULL,121,83,81,'76') as my_column;
my_column
ySQL
SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;
my_column
4
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;
id	aes_decrypt(str, 'bar')
1	foo
2	NULL
SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id
ORDER BY t1.id;
id	aes_decrypt(str, 'bar')
1	foo
2	NULL
DROP TABLE t1, t2;
select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0);
field(0,NULL,1,0)	field("",NULL,"bar","")	field(0.0,NULL,1.0,0.0)
3	3	3
select field(NULL,1,2,NULL), field(NULL,1,2,0);
field(NULL,1,2,NULL)	field(NULL,1,2,0)
0	0
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;
str	num
notnumber	0
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'notnumber'
SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
str	num
notnumber	0
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'notnum'
DROP TABLE t1,t2;
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;
id	col1
1	Main
2	Main->Toys
3	Main->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
WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%';
id	col1
2	Main->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%';
trackname	artistname
April In Paris Vernon Duke	Vernon Duke
Autumn In New York	NULL
DROP TABLE t1,t2,t3;
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;
b	substring(b,1)	substring(b,-1)	substring(b,-2)	substring(b,-3)	substring(b,-4)	substring(b,-5)
ab	ab	b	ab			
abc	abc	c	bc	abc		
abcd	abcd	d	cd	bcd	abcd	
abcde	abcde	e	de	cde	bcde	abcde
select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t;
b	substring(b,1)	substring(b,-1)	substring(b,-2)	substring(b,-3)	substring(b,-4)	substring(b,-5)
ab	ab	b	ab			
abc	abc	c	bc	abc		
abcd	abcd	d	cd	bcd	abcd	
abcde	abcde	e	de	cde	bcde	abcde
drop table t1;
select hex(29223372036854775809), hex(-29223372036854775809);
hex(29223372036854775809)	hex(-29223372036854775809)
FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF
create table t1 (i int);
insert into t1 values (1000000000),(1);
select lpad(i, 7, ' ') as t from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					t	253	7	7	Y	0	31	8
t
1000000
      1
select rpad(i, 7, ' ') as t from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					t	253	7	7	Y	0	31	8
t
1000000
1      
drop table t1;
select load_file("lkjlkj");
load_file("lkjlkj")
NULL
select ifnull(load_file("lkjlkj"),"it's null");
ifnull(load_file("lkjlkj"),"it's null")
it's null
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"));
f1	f2
test	098f6bcd4621d373cade4e832627b4f6
select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
f1	f2
test	098f6bcd4621d373cade4e832627b4f6
select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
f1	f2
test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
f1	f2
test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
drop table t1;
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;
a	c
abc	abc abc
xyz	xyz xyz
SELECT a, CONCAT(a,' ',a) AS c FROM t1
HAVING LEFT(CONCAT(a,' ',a),
LENGTH(CONCAT(a,' ',a))-
INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
a	c
abc	abc abc
xyz	xyz xyz
DROP TABLE t1;
CREATE TABLE t1 (s varchar(10));
INSERT INTO t1 VALUES ('yadda'), ('yaddy');
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > 'ab')
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading 'y' from `test`.`t1`.`s`) > 'ab')
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing 'y' from `test`.`t1`.`s`) > 'ab')
EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')
DROP TABLE t1;
create table t1(f1 varchar(4));
explain extended select encode(f1,'zxcv') as 'enc' from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
Warnings:
Note	1003	select encode(NULL,'zxcv') AS `enc` from `test`.`t1`
explain extended select decode(f1,'zxcv') as 'enc' from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
Warnings:
Note	1003	select decode(NULL,'zxcv') AS `enc` from `test`.`t1`
drop table t1;
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));
a


drop table t1;
End of 4.1 tests
create table t1 (d decimal default null);
insert into t1 values (null);
select format(d, 2) from t1;
format(d, 2)
NULL
drop table t1;
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;
c	res
y,abc	abc
y,abc	abc
drop table t1;
select cast(rtrim('  20.06 ') as decimal(19,2));
cast(rtrim('  20.06 ') as decimal(19,2))
20.06
select cast(ltrim('  20.06 ') as decimal(19,2));
cast(ltrim('  20.06 ') as decimal(19,2))
20.06
Warnings:
Note	1292	Truncated incorrect DECIMAL value: '20.06 '
select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
cast(rtrim(ltrim('  20.06 ')) as decimal(19,2))
20.06
select conv("18383815659218730760",10,10) + 0;
conv("18383815659218730760",10,10) + 0
1.838381565921873e19
select "18383815659218730760" + 0;
"18383815659218730760" + 0
1.838381565921873e19
CREATE TABLE t1 (code varchar(10));
INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
SELECT ASCII(code), code FROM t1 WHERE code='A12';
ASCII(code)	code
97	a12
65	A12
SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
ASCII(code)	code
65	A12
INSERT INTO t1 VALUES ('a12 '), ('A12  ');
SELECT LENGTH(code), code FROM t1 WHERE code='A12';
LENGTH(code)	code
3	a12
3	A12
4	a12 
5	A12  
SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
LENGTH(code)	code
5	A12  
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');
code	id
A12  	a12
EXPLAIN EXTENDED 
SELECT * FROM t1 INNER JOIN t2 ON code=id 
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	const	PRIMARY	PRIMARY	12	const	1	100.00	Using index
1	SIMPLE	t1	ref	code	code	13	const	3	100.00	Using where; Using index
Warnings:
Note	1003	select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and (length(`test`.`t1`.`code`) = 5))
DROP TABLE t1,t2;
select encode(NULL, NULL);
encode(NULL, NULL)
NULL
select encode("data", NULL);
encode("data", NULL)
NULL
select encode(NULL, "password");
encode(NULL, "password")
NULL
select decode(NULL, NULL);
decode(NULL, NULL)
NULL
select decode("data", NULL);
decode("data", NULL)
NULL
select decode(NULL, "password");
decode(NULL, "password")
NULL
select format(NULL, NULL);
format(NULL, NULL)
NULL
select format(pi(), NULL);
format(pi(), NULL)
NULL
select format(NULL, 2);
format(NULL, 2)
NULL
select benchmark(NULL, NULL);
benchmark(NULL, NULL)
NULL
select benchmark(0, NULL);
benchmark(0, NULL)
0
select benchmark(100, NULL);
benchmark(100, NULL)
0
select benchmark(NULL, 1+1);
benchmark(NULL, 1+1)
NULL
select benchmark(-1, 1);
benchmark(-1, 1)
NULL
Warnings:
Warning	1411	Incorrect count value: '-1' for function benchmark
set @password="password";
set @my_data="clear text to encode";
select md5(encode(@my_data, "password"));
md5(encode(@my_data, "password"))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, _utf8 "password"));
md5(encode(@my_data, _utf8 "password"))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, binary "password"));
md5(encode(@my_data, binary "password"))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, _latin1 "password"));
md5(encode(@my_data, _latin1 "password"))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, _koi8r "password"));
md5(encode(@my_data, _koi8r "password"))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, (select "password" from dual)));
md5(encode(@my_data, (select "password" from dual)))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, concat("pass", "word")));
md5(encode(@my_data, concat("pass", "word")))
44320fd2b4a0ec92faa2da2122def917
select md5(encode(@my_data, @password));
md5(encode(@my_data, @password))
44320fd2b4a0ec92faa2da2122def917
set @my_data="binary encoded data";
select md5(decode(@my_data, "password"));
md5(decode(@my_data, "password"))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, _utf8 "password"));
md5(decode(@my_data, _utf8 "password"))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, binary "password"));
md5(decode(@my_data, binary "password"))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, _latin1 "password"));
md5(decode(@my_data, _latin1 "password"))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, _koi8r "password"));
md5(decode(@my_data, _koi8r "password"))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, (select "password" from dual)));
md5(decode(@my_data, (select "password" from dual)))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, concat("pass", "word")));
md5(decode(@my_data, concat("pass", "word")))
5bea8c394368dbc03b76684483b7756b
select md5(decode(@my_data, @password));
md5(decode(@my_data, @password))
5bea8c394368dbc03b76684483b7756b
set @dec=5;
select format(pi(), (1+1));
format(pi(), (1+1))
3.14
select format(pi(), (select 3 from dual));
format(pi(), (select 3 from dual))
3.142
select format(pi(), @dec);
format(pi(), @dec)
3.14159
set @bench_count=10;
select benchmark(10, pi());
benchmark(10, pi())
0
select benchmark(5+5, pi());
benchmark(5+5, pi())
0
select benchmark((select 10 from dual), pi());
benchmark((select 10 from dual), pi())
0
select benchmark(@bench_count, pi());
benchmark(@bench_count, pi())
0
select locate('he','hello',-2);
locate('he','hello',-2)
0
select locate('lo','hello',-4294967295);
locate('lo','hello',-4294967295)
0
select locate('lo','hello',4294967295);
locate('lo','hello',4294967295)
0
select locate('lo','hello',-4294967296);
locate('lo','hello',-4294967296)
0
select locate('lo','hello',4294967296);
locate('lo','hello',4294967296)
0
select locate('lo','hello',-4294967297);
locate('lo','hello',-4294967297)
0
select locate('lo','hello',4294967297);
locate('lo','hello',4294967297)
0
select locate('lo','hello',-18446744073709551615);
locate('lo','hello',-18446744073709551615)
0
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select locate('lo','hello',18446744073709551615);
locate('lo','hello',18446744073709551615)
0
select locate('lo','hello',-18446744073709551616);
locate('lo','hello',-18446744073709551616)
0
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select locate('lo','hello',18446744073709551616);
locate('lo','hello',18446744073709551616)
0
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select locate('lo','hello',-18446744073709551617);
locate('lo','hello',-18446744073709551617)
0
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select locate('lo','hello',18446744073709551617);
locate('lo','hello',18446744073709551617)
0
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select left('hello', 10);
left('hello', 10)
hello
select left('hello', 0);
left('hello', 0)

select left('hello', -1);
left('hello', -1)

select left('hello', -4294967295);
left('hello', -4294967295)

select left('hello', 4294967295);
left('hello', 4294967295)
hello
select left('hello', -4294967296);
left('hello', -4294967296)

select left('hello', 4294967296);
left('hello', 4294967296)
hello
select left('hello', -4294967297);
left('hello', -4294967297)

select left('hello', 4294967297);
left('hello', 4294967297)
hello
select left('hello', -18446744073709551615);
left('hello', -18446744073709551615)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select left('hello', 18446744073709551615);
left('hello', 18446744073709551615)
hello
select left('hello', -18446744073709551616);
left('hello', -18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select left('hello', 18446744073709551616);
left('hello', 18446744073709551616)
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select left('hello', -18446744073709551617);
left('hello', -18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select left('hello', 18446744073709551617);
left('hello', 18446744073709551617)
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select right('hello', 10);
right('hello', 10)
hello
select right('hello', 0);
right('hello', 0)

select right('hello', -1);
right('hello', -1)

select right('hello', -4294967295);
right('hello', -4294967295)

select right('hello', 4294967295);
right('hello', 4294967295)
hello
select right('hello', -4294967296);
right('hello', -4294967296)

select right('hello', 4294967296);
right('hello', 4294967296)
hello
select right('hello', -4294967297);
right('hello', -4294967297)

select right('hello', 4294967297);
right('hello', 4294967297)
hello
select right('hello', -18446744073709551615);
right('hello', -18446744073709551615)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select right('hello', 18446744073709551615);
right('hello', 18446744073709551615)
hello
select right('hello', -18446744073709551616);
right('hello', -18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select right('hello', 18446744073709551616);
right('hello', 18446744073709551616)
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select right('hello', -18446744073709551617);
right('hello', -18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select right('hello', 18446744073709551617);
right('hello', 18446744073709551617)
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select substring('hello', 2, -1);
substring('hello', 2, -1)

select substring('hello', -1, 1);
substring('hello', -1, 1)
o
select substring('hello', -2, 1);
substring('hello', -2, 1)
l
select substring('hello', -4294967295, 1);
substring('hello', -4294967295, 1)

select substring('hello', 4294967295, 1);
substring('hello', 4294967295, 1)

select substring('hello', -4294967296, 1);
substring('hello', -4294967296, 1)

select substring('hello', 4294967296, 1);
substring('hello', 4294967296, 1)

select substring('hello', -4294967297, 1);
substring('hello', -4294967297, 1)

select substring('hello', 4294967297, 1);
substring('hello', 4294967297, 1)

select substring('hello', -18446744073709551615, 1);
substring('hello', -18446744073709551615, 1)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select substring('hello', 18446744073709551615, 1);
substring('hello', 18446744073709551615, 1)

select substring('hello', -18446744073709551616, 1);
substring('hello', -18446744073709551616, 1)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select substring('hello', 18446744073709551616, 1);
substring('hello', 18446744073709551616, 1)

Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select substring('hello', -18446744073709551617, 1);
substring('hello', -18446744073709551617, 1)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select substring('hello', 18446744073709551617, 1);
substring('hello', 18446744073709551617, 1)

Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select substring('hello', 1, -1);
substring('hello', 1, -1)

select substring('hello', 1, -4294967295);
substring('hello', 1, -4294967295)

select substring('hello', 1, 4294967295);
substring('hello', 1, 4294967295)
hello
select substring('hello', 1, -4294967296);
substring('hello', 1, -4294967296)

select substring('hello', 1, 4294967296);
substring('hello', 1, 4294967296)
hello
select substring('hello', 1, -4294967297);
substring('hello', 1, -4294967297)

select substring('hello', 1, 4294967297);
substring('hello', 1, 4294967297)
hello
select substring('hello', 1, -18446744073709551615);
substring('hello', 1, -18446744073709551615)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select substring('hello', 1, 18446744073709551615);
substring('hello', 1, 18446744073709551615)
hello
select substring('hello', 1, -18446744073709551616);
substring('hello', 1, -18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select substring('hello', 1, 18446744073709551616);
substring('hello', 1, 18446744073709551616)
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select substring('hello', 1, -18446744073709551617);
substring('hello', 1, -18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select substring('hello', 1, 18446744073709551617);
substring('hello', 1, 18446744073709551617)
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select substring('hello', -1, -1);
substring('hello', -1, -1)

select substring('hello', -4294967295, -4294967295);
substring('hello', -4294967295, -4294967295)

select substring('hello', 4294967295, 4294967295);
substring('hello', 4294967295, 4294967295)

select substring('hello', -4294967296, -4294967296);
substring('hello', -4294967296, -4294967296)

select substring('hello', 4294967296, 4294967296);
substring('hello', 4294967296, 4294967296)

select substring('hello', -4294967297, -4294967297);
substring('hello', -4294967297, -4294967297)

select substring('hello', 4294967297, 4294967297);
substring('hello', 4294967297, 4294967297)

select substring('hello', -18446744073709551615, -18446744073709551615);
substring('hello', -18446744073709551615, -18446744073709551615)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select substring('hello', 18446744073709551615, 18446744073709551615);
substring('hello', 18446744073709551615, 18446744073709551615)

select substring('hello', -18446744073709551616, -18446744073709551616);
substring('hello', -18446744073709551616, -18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select substring('hello', 18446744073709551616, 18446744073709551616);
substring('hello', 18446744073709551616, 18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select substring('hello', -18446744073709551617, -18446744073709551617);
substring('hello', -18446744073709551617, -18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select substring('hello', 18446744073709551617, 18446744073709551617);
substring('hello', 18446744073709551617, 18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select insert('hello', -1, 1, 'hi');
insert('hello', -1, 1, 'hi')
hello
select insert('hello', -4294967295, 1, 'hi');
insert('hello', -4294967295, 1, 'hi')
hello
select insert('hello', 4294967295, 1, 'hi');
insert('hello', 4294967295, 1, 'hi')
hello
select insert('hello', -4294967296, 1, 'hi');
insert('hello', -4294967296, 1, 'hi')
hello
select insert('hello', 4294967296, 1, 'hi');
insert('hello', 4294967296, 1, 'hi')
hello
select insert('hello', -4294967297, 1, 'hi');
insert('hello', -4294967297, 1, 'hi')
hello
select insert('hello', 4294967297, 1, 'hi');
insert('hello', 4294967297, 1, 'hi')
hello
select insert('hello', -18446744073709551615, 1, 'hi');
insert('hello', -18446744073709551615, 1, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select insert('hello', 18446744073709551615, 1, 'hi');
insert('hello', 18446744073709551615, 1, 'hi')
hello
select insert('hello', -18446744073709551616, 1, 'hi');
insert('hello', -18446744073709551616, 1, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select insert('hello', 18446744073709551616, 1, 'hi');
insert('hello', 18446744073709551616, 1, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select insert('hello', -18446744073709551617, 1, 'hi');
insert('hello', -18446744073709551617, 1, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select insert('hello', 18446744073709551617, 1, 'hi');
insert('hello', 18446744073709551617, 1, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select insert('hello', 1, -1, 'hi');
insert('hello', 1, -1, 'hi')
hi
select insert('hello', 1, -4294967295, 'hi');
insert('hello', 1, -4294967295, 'hi')
hi
select insert('hello', 1, 4294967295, 'hi');
insert('hello', 1, 4294967295, 'hi')
hi
select insert('hello', 1, -4294967296, 'hi');
insert('hello', 1, -4294967296, 'hi')
hi
select insert('hello', 1, 4294967296, 'hi');
insert('hello', 1, 4294967296, 'hi')
hi
select insert('hello', 1, -4294967297, 'hi');
insert('hello', 1, -4294967297, 'hi')
hi
select insert('hello', 1, 4294967297, 'hi');
insert('hello', 1, 4294967297, 'hi')
hi
select insert('hello', 1, -18446744073709551615, 'hi');
insert('hello', 1, -18446744073709551615, 'hi')
hi
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select insert('hello', 1, 18446744073709551615, 'hi');
insert('hello', 1, 18446744073709551615, 'hi')
hi
select insert('hello', 1, -18446744073709551616, 'hi');
insert('hello', 1, -18446744073709551616, 'hi')
hi
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select insert('hello', 1, 18446744073709551616, 'hi');
insert('hello', 1, 18446744073709551616, 'hi')
hi
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select insert('hello', 1, -18446744073709551617, 'hi');
insert('hello', 1, -18446744073709551617, 'hi')
hi
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select insert('hello', 1, 18446744073709551617, 'hi');
insert('hello', 1, 18446744073709551617, 'hi')
hi
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select insert('hello', -1, -1, 'hi');
insert('hello', -1, -1, 'hi')
hello
select insert('hello', -4294967295, -4294967295, 'hi');
insert('hello', -4294967295, -4294967295, 'hi')
hello
select insert('hello', 4294967295, 4294967295, 'hi');
insert('hello', 4294967295, 4294967295, 'hi')
hello
select insert('hello', -4294967296, -4294967296, 'hi');
insert('hello', -4294967296, -4294967296, 'hi')
hello
select insert('hello', 4294967296, 4294967296, 'hi');
insert('hello', 4294967296, 4294967296, 'hi')
hello
select insert('hello', -4294967297, -4294967297, 'hi');
insert('hello', -4294967297, -4294967297, 'hi')
hello
select insert('hello', 4294967297, 4294967297, 'hi');
insert('hello', 4294967297, 4294967297, 'hi')
hello
select insert('hello', -18446744073709551615, -18446744073709551615, 'hi');
insert('hello', -18446744073709551615, -18446744073709551615, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select insert('hello', 18446744073709551615, 18446744073709551615, 'hi');
insert('hello', 18446744073709551615, 18446744073709551615, 'hi')
hello
select insert('hello', -18446744073709551616, -18446744073709551616, 'hi');
insert('hello', -18446744073709551616, -18446744073709551616, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select insert('hello', 18446744073709551616, 18446744073709551616, 'hi');
insert('hello', 18446744073709551616, 18446744073709551616, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
select insert('hello', -18446744073709551617, -18446744073709551617, 'hi');
insert('hello', -18446744073709551617, -18446744073709551617, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select insert('hello', 18446744073709551617, 18446744073709551617, 'hi');
insert('hello', 18446744073709551617, 18446744073709551617, 'hi')
hello
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
select repeat('hello', -1);
repeat('hello', -1)

select repeat('hello', -4294967295);
repeat('hello', -4294967295)

select repeat('hello', 4294967295);
repeat('hello', 4294967295)
NULL
Warnings:
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select repeat('hello', -4294967296);
repeat('hello', -4294967296)

select repeat('hello', 4294967296);
repeat('hello', 4294967296)
NULL
Warnings:
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select repeat('hello', -4294967297);
repeat('hello', -4294967297)

select repeat('hello', 4294967297);
repeat('hello', 4294967297)
NULL
Warnings:
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select repeat('hello', -18446744073709551615);
repeat('hello', -18446744073709551615)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select repeat('hello', 18446744073709551615);
repeat('hello', 18446744073709551615)
NULL
Warnings:
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select repeat('hello', -18446744073709551616);
repeat('hello', -18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select repeat('hello', 18446744073709551616);
repeat('hello', 18446744073709551616)
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select repeat('hello', -18446744073709551617);
repeat('hello', -18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select repeat('hello', 18446744073709551617);
repeat('hello', 18446744073709551617)
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
select space(-1);
space(-1)

select space(-4294967295);
space(-4294967295)

select space(4294967295);
space(4294967295)
NULL
Warnings:
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
select space(-4294967296);
space(-4294967296)

select space(4294967296);
space(4294967296)
NULL
Warnings:
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
select space(-4294967297);
space(-4294967297)

select space(4294967297);
space(4294967297)
NULL
Warnings:
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
select space(-18446744073709551615);
space(-18446744073709551615)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select space(18446744073709551615);
space(18446744073709551615)
NULL
Warnings:
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
select space(-18446744073709551616);
space(-18446744073709551616)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select space(18446744073709551616);
space(18446744073709551616)
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
select space(-18446744073709551617);
space(-18446744073709551617)

Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select space(18446744073709551617);
space(18446744073709551617)
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
select rpad('hello', -1, '1');
rpad('hello', -1, '1')
NULL
select rpad('hello', -4294967295, '1');
rpad('hello', -4294967295, '1')
NULL
select rpad('hello', 4294967295, '1');
rpad('hello', 4294967295, '1')
NULL
Warnings:
Warning	1301	Result of rpad() was larger than max_allowed_packet (1048576) - truncated
select rpad('hello', -4294967296, '1');
rpad('hello', -4294967296, '1')
NULL
select rpad('hello', 4294967296, '1');
rpad('hello', 4294967296, '1')
NULL
Warnings:
Warning	1301	Result of rpad() was larger than max_allowed_packet (1048576) - truncated
select rpad('hello', -4294967297, '1');
rpad('hello', -4294967297, '1')
NULL
select rpad('hello', 4294967297, '1');
rpad('hello', 4294967297, '1')
NULL
Warnings:
Warning	1301	Result of rpad() was larger than max_allowed_packet (1048576) - truncated
select rpad('hello', -18446744073709551615, '1');
rpad('hello', -18446744073709551615, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select rpad('hello', 18446744073709551615, '1');
rpad('hello', 18446744073709551615, '1')
NULL
Warnings:
Warning	1301	Result of rpad() was larger than max_allowed_packet (1048576) - truncated
select rpad('hello', -18446744073709551616, '1');
rpad('hello', -18446744073709551616, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select rpad('hello', 18446744073709551616, '1');
rpad('hello', 18446744073709551616, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1301	Result of rpad() was larger than max_allowed_packet (1048576) - truncated
select rpad('hello', -18446744073709551617, '1');
rpad('hello', -18446744073709551617, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select rpad('hello', 18446744073709551617, '1');
rpad('hello', 18446744073709551617, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1301	Result of rpad() was larger than max_allowed_packet (1048576) - truncated
select lpad('hello', -1, '1');
lpad('hello', -1, '1')
NULL
select lpad('hello', -4294967295, '1');
lpad('hello', -4294967295, '1')
NULL
select lpad('hello', 4294967295, '1');
lpad('hello', 4294967295, '1')
NULL
Warnings:
Warning	1301	Result of lpad() was larger than max_allowed_packet (1048576) - truncated
select lpad('hello', -4294967296, '1');
lpad('hello', -4294967296, '1')
NULL
select lpad('hello', 4294967296, '1');
lpad('hello', 4294967296, '1')
NULL
Warnings:
Warning	1301	Result of lpad() was larger than max_allowed_packet (1048576) - truncated
select lpad('hello', -4294967297, '1');
lpad('hello', -4294967297, '1')
NULL
select lpad('hello', 4294967297, '1');
lpad('hello', 4294967297, '1')
NULL
Warnings:
Warning	1301	Result of lpad() was larger than max_allowed_packet (1048576) - truncated
select lpad('hello', -18446744073709551615, '1');
lpad('hello', -18446744073709551615, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551615' to INT. Value truncated.
select lpad('hello', 18446744073709551615, '1');
lpad('hello', 18446744073709551615, '1')
NULL
Warnings:
Warning	1301	Result of lpad() was larger than max_allowed_packet (1048576) - truncated
select lpad('hello', -18446744073709551616, '1');
lpad('hello', -18446744073709551616, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551616' to INT. Value truncated.
select lpad('hello', 18446744073709551616, '1');
lpad('hello', 18446744073709551616, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551616' to INT. Value truncated.
Warning	1301	Result of lpad() was larger than max_allowed_packet (1048576) - truncated
select lpad('hello', -18446744073709551617, '1');
lpad('hello', -18446744073709551617, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '-18446744073709551617' to INT. Value truncated.
select lpad('hello', 18446744073709551617, '1');
lpad('hello', 18446744073709551617, '1')
NULL
Warnings:
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1916	Got overflow when converting '18446744073709551617' to INT. Value truncated.
Warning	1301	Result of lpad() was larger than max_allowed_packet (1048576) - truncated
SET @orig_sql_mode = @@SQL_MODE;
SET SQL_MODE=traditional;
SELECT CHAR(0xff,0x8f USING utf8);
CHAR(0xff,0x8f USING utf8)
NULL
Warnings:
Warning	1300	Invalid utf8 character string: 'FF8F'
SELECT CHAR(0xff,0x8f USING utf8) IS NULL;
CHAR(0xff,0x8f USING utf8) IS NULL
1
Warnings:
Warning	1300	Invalid utf8 character string: 'FF8F'
SET SQL_MODE=@orig_sql_mode;
select substring('abc', cast(2 as unsigned int));
substring('abc', cast(2 as unsigned int))
bc
select repeat('a', cast(2 as unsigned int));
repeat('a', cast(2 as unsigned int))
aa
select rpad('abc', cast(5 as unsigned integer), 'x');
rpad('abc', cast(5 as unsigned integer), 'x')
abcxx
select lpad('abc', cast(5 as unsigned integer), 'x');
lpad('abc', cast(5 as unsigned integer), 'x')
xxabc
create table t1(f1 longtext);
insert into t1 values ("123"),("456");
select substring(f1,1,1) from t1 group by 1;
substring(f1,1,1)
1
4
create table t2(f1 varchar(3));
insert into t1 values ("123"),("456");
select substring(f1,4,1), substring(f1,-4,1) from t2;
substring(f1,4,1)	substring(f1,-4,1)
drop table t1,t2;
DROP TABLE IF EXISTS t1;
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`;
A	B	tire
		0
#	#	1
##	##	2
SELECT REPEAT('0', CAST(0 AS UNSIGNED));
REPEAT('0', CAST(0 AS UNSIGNED))

SELECT REPEAT('0', -2);
REPEAT('0', -2)

SELECT REPEAT('0', 2);
REPEAT('0', 2)
00
DROP TABLE t1;
SELECT UNHEX('G');
UNHEX('G')
NULL
SELECT UNHEX('G') IS NULL;
UNHEX('G') IS NULL
1
SELECT INSERT('abc', 3, 3, '1234');
INSERT('abc', 3, 3, '1234')
ab1234
SELECT INSERT('abc', 4, 3, '1234');
INSERT('abc', 4, 3, '1234')
abc
SELECT INSERT('abc', 5, 3, '1234');
INSERT('abc', 5, 3, '1234')
abc
SELECT INSERT('abc', 6, 3, '1234');
INSERT('abc', 6, 3, '1234')
abc
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;
CRC32(a)	COUNT(*)
450215437	1
498629140	1
1790921346	1
1842515611	1
2212294583	1
2226203566	1
2366072709	1
2707236321	1
4088798008	1
4194326291	1
SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
CRC32(a)	COUNT(*)
450215437	1
498629140	1
1790921346	1
1842515611	1
2212294583	1
2226203566	1
2366072709	1
2707236321	1
4088798008	1
4194326291	1
SELECT * FROM (SELECT CRC32(a) FROM t1) t2;
CRC32(a)
2212294583
450215437
1842515611
4088798008
2226203566
498629140
1790921346
4194326291
2366072709
2707236321
CREATE TABLE t2 SELECT CRC32(a) FROM t1;
desc t2;
Field	Type	Null	Key	Default	Extra
CRC32(a)	int(10) unsigned	YES		NULL	
SELECT * FROM v1;
C
2212294583
450215437
1842515611
4088798008
2226203566
498629140
1790921346
4194326291
2366072709
2707236321
SELECT * FROM (SELECT * FROM v1) x;
C
2212294583
450215437
1842515611
4088798008
2226203566
498629140
1790921346
4194326291
2366072709
2707236321
DROP TABLE t1, t2;
DROP VIEW v1;
SELECT LOCATE('foo', NULL) FROM DUAL;
LOCATE('foo', NULL)
NULL
SELECT LOCATE(NULL, 'o') FROM DUAL;
LOCATE(NULL, 'o')
NULL
SELECT LOCATE(NULL, NULL) FROM DUAL;
LOCATE(NULL, NULL)
NULL
SELECT LOCATE('foo', NULL) IS NULL FROM DUAL;
LOCATE('foo', NULL) IS NULL
1
SELECT LOCATE(NULL, 'o') IS NULL FROM DUAL;
LOCATE(NULL, 'o') IS NULL
1
SELECT LOCATE(NULL, NULL) IS NULL FROM DUAL;
LOCATE(NULL, NULL) IS NULL
1
SELECT ISNULL(LOCATE('foo', NULL)) FROM DUAL;
ISNULL(LOCATE('foo', NULL))
1
SELECT ISNULL(LOCATE(NULL, 'o')) FROM DUAL;
ISNULL(LOCATE(NULL, 'o'))
1
SELECT ISNULL(LOCATE(NULL, NULL)) FROM DUAL;
ISNULL(LOCATE(NULL, NULL))
1
SELECT LOCATE('foo', NULL) <=> NULL FROM DUAL;
LOCATE('foo', NULL) <=> NULL
1
SELECT LOCATE(NULL, 'o') <=> NULL FROM DUAL;
LOCATE(NULL, 'o') <=> NULL
1
SELECT LOCATE(NULL, NULL) <=> NULL FROM DUAL;
LOCATE(NULL, NULL) <=> NULL
1
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;
id	LOCATE(a,p)
1	0
2	NULL
3	NULL
4	NULL
SELECT id, LOCATE(a,p) IS NULL FROM t1;
id	LOCATE(a,p) IS NULL
1	0
2	1
3	1
4	1
SELECT id, ISNULL(LOCATE(a,p)) FROM t1;
id	ISNULL(LOCATE(a,p))
1	0
2	1
3	1
4	1
SELECT id, LOCATE(a,p) <=> NULL FROM t1;
id	LOCATE(a,p) <=> NULL
1	0
2	1
3	1
4	1
SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL;
id
2
3
4
SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL;
id
2
3
4
DROP TABLE t1;
SELECT SUBSTR('foo',1,0) FROM DUAL;
SUBSTR('foo',1,0)

SELECT SUBSTR('foo',1,CAST(0 AS SIGNED)) FROM DUAL;
SUBSTR('foo',1,CAST(0 AS SIGNED))

SELECT SUBSTR('foo',1,CAST(0 AS UNSIGNED)) FROM DUAL;
SUBSTR('foo',1,CAST(0 AS UNSIGNED))

CREATE TABLE t1 (a varchar(10), len int unsigned);
INSERT INTO t1 VALUES ('bar', 2), ('foo', 0);
SELECT SUBSTR(a,1,len) FROM t1;
SUBSTR(a,1,len)
ba

DROP TABLE t1;
CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1;
SELECT HEX(c1) from t1;
HEX(c1)
414243
DROP TABLE t1;
CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1;
SELECT HEX(c1) from v1;
HEX(c1)
414243
DROP VIEW v1;
create table t1(a float);
insert into t1 values (1.33);
select format(a, 2) from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					format(a, 2)	253	49	4	Y	0	31	8
format(a, 2)
1.33
drop table t1;
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;
h	i
31.12.2008	AAAAAA, aaaaaa
DROP TABLE t1;
#
# BUG#44774: load_file function produces valgrind warnings
#
CREATE TABLE t1 (a TINYBLOB);
INSERT INTO t1 VALUES ('aaaaaaaa');
SELECT LOAD_FILE(a) FROM t1;
LOAD_FILE(a)
NULL
DROP TABLE t1;
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;
concat_name
LOAD_MIN
LOAD_MAX
DROP TABLE t1, t2;
End of 5.0 tests
drop table if exists t1;
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
set optimizer_switch=@tmp_optimizer_switch;
drop table t1;
#
# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
#
DROP TABLE IF EXISTS t1, t2;
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);
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;
DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
secret
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;
DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
secret
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;
DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC')
secret
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);
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;
DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a)
secret
DROP TABLE t1, t2;
#
# Bug#52164 Assertion failed: param.sort_length, file .\filesort.cc, line 149
#
CREATE TABLE t1 (a LONGBLOB NOT NULL);
INSERT INTO t1 VALUES (''),('');
SELECT 1 FROM t1, t1 t2
ORDER BY QUOTE(t1.a);
1
1
1
1
1
DROP TABLE t1;
#
# Bug#57913 large negative number to string conversion functions crash
# Bug#57810 case/when/then : Assertion failed: length || !scale
#
SELECT '1' IN ('1', SUBSTRING(-9223372036854775809, 1));
'1' IN ('1', SUBSTRING(-9223372036854775809, 1))
1
SELECT CONVERT(('' IN (REVERSE(CAST(('') AS DECIMAL)), '')), CHAR(3));
CONVERT(('' IN (REVERSE(CAST(('') AS DECIMAL)), '')), CHAR(3))
1
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: ''
# 
# Bug#58165: "my_empty_string" gets modified and causes LOAD DATA to fail
# and other crashes
#
CREATE TABLE t1 ( a TEXT );
SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt';;
SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' );
insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' )

Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'b'
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1;;
SELECT * FROM t1;
a
aaaaaaaaaaaaaa
DROP TABLE t1;
SELECT SUBSTRING('1', DAY(FROM_UNIXTIME(-1)));
SUBSTRING('1', DAY(FROM_UNIXTIME(-1)))
NULL
SELECT LEFT('1', DAY(FROM_UNIXTIME(-1)));
LEFT('1', DAY(FROM_UNIXTIME(-1)))
NULL
SELECT RIGHT('1', DAY(FROM_UNIXTIME(-1)));
RIGHT('1', DAY(FROM_UNIXTIME(-1)))
NULL
SELECT REPEAT('1', DAY(FROM_UNIXTIME(-1)));
REPEAT('1', DAY(FROM_UNIXTIME(-1)))
NULL
SELECT RPAD('hi', DAY(FROM_UNIXTIME(-1)),'?');
RPAD('hi', DAY(FROM_UNIXTIME(-1)),'?')
NULL
SELECT LPAD('hi', DAY(FROM_UNIXTIME(-1)),'?');
LPAD('hi', DAY(FROM_UNIXTIME(-1)),'?')
NULL
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;
group_concat( i )	field
NULL	NULL
8	
drop table t1;
#
# Bug#11766684 59851: UNINITIALISED VALUE IN ITEM_FUNC_LIKE::SELECT_OPTIMIZE WITH SUBQUERY AND
#
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);
1
DROP TABLE t1, t2;
End of 5.1 tests
#
# Start of 5.3 tests
#
#
# Bug#11829861: SUBSTRING_INDEX() RESULTS IN MISSING CHARACTERS WHEN USED
# INSIDE LOWER()
#
SET @user_at_host = 'root@mytinyhost-PC.local';
SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1))
mytinyhost-pc.local
# End of test  BUG#11829861
#
# Bug#42404: SUBSTRING_INDEX() RESULTS ARE INCONSISTENT
#
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;
i	SUBSTRING_INDEX(c, '.', -2)
1	.wwwmysqlcom
SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t;
i	SUBSTRING_INDEX(c, '.', -2)
0	mysql.com
1	.wwwmysqlcom
DROP TABLE t;
# End of test  BUG#42404
#
# End of 5.3 tests
#
Start of 5.4 tests
SELECT format(12345678901234567890.123, 3);
format(12345678901234567890.123, 3)
12,345,678,901,234,567,890.123
SELECT format(12345678901234567890.123, 3, NULL);
format(12345678901234567890.123, 3, NULL)
12,345,678,901,234,567,890.123
Warnings:
Warning	1649	Unknown locale: 'NULL'
SELECT format(12345678901234567890.123, 3, 'ar_AE');
format(12345678901234567890.123, 3, 'ar_AE')
12,345,678,901,234,567,890.123
SELECT format(12345678901234567890.123, 3, 'ar_SA');
format(12345678901234567890.123, 3, 'ar_SA')
12345678901234567890.123
SELECT format(12345678901234567890.123, 3, 'be_BY');
format(12345678901234567890.123, 3, 'be_BY')
12.345.678.901.234.567.890,123
SELECT format(12345678901234567890.123, 3, 'de_DE');
format(12345678901234567890.123, 3, 'de_DE')
12.345.678.901.234.567.890,123
SELECT format(12345678901234567890.123, 3, 'en_IN');
format(12345678901234567890.123, 3, 'en_IN')
1,23,45,67,89,01,23,45,67,890.123
SELECT format(12345678901234567890.123, 3, 'en_US');
format(12345678901234567890.123, 3, 'en_US')
12,345,678,901,234,567,890.123
SELECT format(12345678901234567890.123, 3, 'it_CH');
format(12345678901234567890.123, 3, 'it_CH')
12'345'678'901'234'567'890,123
SELECT format(12345678901234567890.123, 3, 'ru_RU');
format(12345678901234567890.123, 3, 'ru_RU')
12 345 678 901 234 567 890,123
SELECT format(12345678901234567890.123, 3, 'ta_IN');
format(12345678901234567890.123, 3, 'ta_IN')
1,23,45,67,89,01,23,45,67,890.123
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;
fmt	format(12345678901234567890.123, 3, fmt)
ar_AE	12,345,678,901,234,567,890.123
ar_SA	12345678901234567890.123
be_BY	12.345.678.901.234.567.890,123
de_DE	12.345.678.901.234.567.890,123
en_IN	1,23,45,67,89,01,23,45,67,890.123
en_US	12,345,678,901,234,567,890.123
it_CH	12'345'678'901'234'567'890,123
ru_RU	12 345 678 901 234 567 890,123
ta_IN	1,23,45,67,89,01,23,45,67,890.123
SELECT fmt, format(12345678901234567890.123, 0, fmt) FROM t1 ORDER BY fmt;
fmt	format(12345678901234567890.123, 0, fmt)
ar_AE	12,345,678,901,234,567,890
ar_SA	12345678901234567890
be_BY	12.345.678.901.234.567.890
de_DE	12.345.678.901.234.567.890
en_IN	1,23,45,67,89,01,23,45,67,890
en_US	12,345,678,901,234,567,890
it_CH	12'345'678'901'234'567'890
ru_RU	12 345 678 901 234 567 890
ta_IN	1,23,45,67,89,01,23,45,67,890
SELECT fmt, format(12345678901234567890,     3, fmt) FROM t1 ORDER BY fmt;
fmt	format(12345678901234567890,     3, fmt)
ar_AE	12,345,678,901,234,567,890.000
ar_SA	12345678901234567890.000
be_BY	12.345.678.901.234.567.890,000
de_DE	12.345.678.901.234.567.890,000
en_IN	1,23,45,67,89,01,23,45,67,890.000
en_US	12,345,678,901,234,567,890.000
it_CH	12'345'678'901'234'567'890,000
ru_RU	12 345 678 901 234 567 890,000
ta_IN	1,23,45,67,89,01,23,45,67,890.000
SELECT fmt, format(-12345678901234567890,    3, fmt) FROM t1 ORDER BY fmt;
fmt	format(-12345678901234567890,    3, fmt)
ar_AE	-12,345,678,901,234,567,890.000
ar_SA	-12345678901234567890.000
be_BY	-12.345.678.901.234.567.890,000
de_DE	-12.345.678.901.234.567.890,000
en_IN	-1,23,45,67,89,01,23,45,67,890.000
en_US	-12,345,678,901,234,567,890.000
it_CH	-12'345'678'901'234'567'890,000
ru_RU	-12 345 678 901 234 567 890,000
ta_IN	-1,23,45,67,89,01,23,45,67,890.000
SELECT fmt, format(-02345678901234567890,    3, fmt) FROM t1 ORDER BY fmt;
fmt	format(-02345678901234567890,    3, fmt)
ar_AE	-2,345,678,901,234,567,890.000
ar_SA	-2345678901234567890.000
be_BY	-2.345.678.901.234.567.890,000
de_DE	-2.345.678.901.234.567.890,000
en_IN	-23,45,67,89,01,23,45,67,890.000
en_US	-2,345,678,901,234,567,890.000
it_CH	-2'345'678'901'234'567'890,000
ru_RU	-2 345 678 901 234 567 890,000
ta_IN	-23,45,67,89,01,23,45,67,890.000
SELECT fmt, format(-00345678901234567890,    3, fmt) FROM t1 ORDER BY fmt;
fmt	format(-00345678901234567890,    3, fmt)
ar_AE	-345,678,901,234,567,890.000
ar_SA	-345678901234567890.000
be_BY	-345.678.901.234.567.890,000
de_DE	-345.678.901.234.567.890,000
en_IN	-3,45,67,89,01,23,45,67,890.000
en_US	-345,678,901,234,567,890.000
it_CH	-345'678'901'234'567'890,000
ru_RU	-345 678 901 234 567 890,000
ta_IN	-3,45,67,89,01,23,45,67,890.000
SELECT fmt, format(-00045678901234567890,    3, fmt) FROM t1 ORDER BY fmt;
fmt	format(-00045678901234567890,    3, fmt)
ar_AE	-45,678,901,234,567,890.000
ar_SA	-45678901234567890.000
be_BY	-45.678.901.234.567.890,000
de_DE	-45.678.901.234.567.890,000
en_IN	-45,67,89,01,23,45,67,890.000
en_US	-45,678,901,234,567,890.000
it_CH	-45'678'901'234'567'890,000
ru_RU	-45 678 901 234 567 890,000
ta_IN	-45,67,89,01,23,45,67,890.000
DROP TABLE t1;
SELECT format(123, 1, 'Non-existent-locale');
format(123, 1, 'Non-existent-locale')
123.0
Warnings:
Warning	1649	Unknown locale: 'Non-existent-locale'
End of 5.4 tests
#
# Start of 5.5 tests
#
#
# Bug#55912 FORMAT with locale set fails for numbers < 1000
#
SELECT FORMAT(123.33, 2, 'no_NO'), FORMAT(1123.33, 2, 'no_NO');
FORMAT(123.33, 2, 'no_NO')	FORMAT(1123.33, 2, 'no_NO')
123,33	1.123,33
SELECT FORMAT(12333e-2, 2, 'no_NO'), FORMAT(112333e-2, 2, 'no_NO');
FORMAT(12333e-2, 2, 'no_NO')	FORMAT(112333e-2, 2, 'no_NO')
123,33	1.123,33
CREATE TABLE t1 AS SELECT format(123,2,'no_NO');
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `format(123,2,'no_NO')` varchar(37) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t1;
format(123,2,'no_NO')
123,00
DROP TABLE t1;
#
# Bug#11764310 conv function crashes, negative argument to memcpy
#
SELECT CONV(1,-2147483648,-2147483648);
CONV(1,-2147483648,-2147483648)
NULL
#
# Bug#12985030 SIMPLE QUERY WITH DECIMAL NUMBERS LEAKS MEMORY
#
SELECT (rpad(1.0,2048,1)) IS NOT FALSE;
(rpad(1.0,2048,1)) IS NOT FALSE
1
SELECT ((+0) IN
((0b111111111111111111111111111111111111111111111111111),(rpad(1.0,2048,1)),
(32767.1)));
((+0) IN
((0b111111111111111111111111111111111111111111111111111),(rpad(1.0,2048,1)),
(32767.1)))
0
SELECT ((rpad(1.0,2048,1)) = ('4(') ^ (0.1));
((rpad(1.0,2048,1)) = ('4(') ^ (0.1))
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '4('
SELECT
pow((rpad(10.0,2048,1)),(b'1111111111111111111111111111111111111111111'));
ERROR 22003: DOUBLE value is out of range in 'pow(rpad(10.0,2048,1),0x07ffffffffff)'
SELECT ((rpad(1.0,2048,1)) + (0) ^ ('../'));
((rpad(1.0,2048,1)) + (0) ^ ('../'))
1.011111111111111
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '../'
SELECT stddev_samp(rpad(1.0,2048,1));
stddev_samp(rpad(1.0,2048,1))
NULL
SELECT ((127.1) not in ((rpad(1.0,2048,1)),(''),(-1.1)));
((127.1) not in ((rpad(1.0,2048,1)),(''),(-1.1)))
1
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: ''
SELECT ((0xf3) * (rpad(1.0,2048,1)) << (0xcc));
((0xf3) * (rpad(1.0,2048,1)) << (0xcc))
0
#
# Bug#13359121 LARGE NUMBERS, /STRINGS/DTOA.C:662:
#              BALLOC: ASSERTION `K <= 15' FAILED.
# Bug#12985021 SIMPLE QUERY WITH DECIMAL NUMBERS TAKE AN 
#              EXTRAORDINARY LONG TIME TO EXECUTE
SELECT @tmp_max:= @@global.max_allowed_packet;
@tmp_max:= @@global.max_allowed_packet
1048576
SET @@global.max_allowed_packet=1024*1024*1024;
SELECT @@global.max_allowed_packet;
@@global.max_allowed_packet
1073741824
do
format(rpad('111111111.1',
1111111,
'999999999999999999999999999999999999999999'),0,'be_BY')
;
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;
r
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9'
Warning	1292	Truncated incorrect DECIMAL value: '[.DC2.]'
Warning	1292	Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9'
Warning	1292	Truncated incorrect DOUBLE value: '0.000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'
SET @@global.max_allowed_packet:= @tmp_max;
SELECT @tmp_max:= @@global.max_allowed_packet;
@tmp_max:= @@global.max_allowed_packet
1048576
SET @@global.max_allowed_packet=1024*1024*1024;
SELECT @@global.max_allowed_packet;
@@global.max_allowed_packet
1073741824
SELECT CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000)));
CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000)))
NULL
Warnings:
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
SET @@global.max_allowed_packet:= @tmp_max;
#
# MDEV-4272: DIV operator crashes in Item_func_int_div::val_int
# (incorrect NULL value handling by convert)
#
create table t1(a int) select null;
select 1 div convert(a using utf8) from t1;
1 div convert(a using utf8)
NULL
drop table t1;
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';
call foo('(( 00000000 ++ 00000000 ))');
replace(var, '00000000', table_name)
(( t1 ++ t1 ))
(( t2 ++ t2 ))
drop procedure foo;
drop table t1,t2;
select md5(_filename "a"), sha(_filename "a");
md5(_filename "a")	sha(_filename "a")
0cc175b9c0f1b6a831c399e269772661	86f7e437faa5a7fce15d1ddcb9eaeaea377667b8
#
# End of 5.5 tests
#
#
# Start of 5.6 tests
#
#
# WL#5510 Functions to_base64 and from_base64
#
CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',63)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(85) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh
YWFhYWFh	85
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(66) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	63
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',62)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(85) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh
YWFhYWE=	85
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(66) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	62
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',61)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(85) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh
YWFhYQ==	85
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(66) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	61
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',60)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(81) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh
YWFh	81
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(63) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	60
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',59)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(81) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh
YWE=	81
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(63) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	59
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',58)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(81) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh
YQ==	81
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(63) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	58
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',57)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(76) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	76
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(57) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	57
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',56)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(76) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	76
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(57) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	56
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',55)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(76) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	76
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(57) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	55
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',54)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(72) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	72
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(54) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	54
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',53)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(72) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	72
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(54) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	53
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',52)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(72) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	72
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(54) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	52
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',51)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(68) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	68
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(51) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	51
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',50)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(68) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	68
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(51) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	50
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',49)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(68) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	68
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(51) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	49
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',48)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	64
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(48) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	48
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',47)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	64
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(48) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	47
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',46)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	64
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(48) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	46
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',45)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(60) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	60
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(45) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	45
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',44)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(60) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	60
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(45) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	44
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',43)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(60) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	60
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(45) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	43
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',42)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(56) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	56
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(42) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	42
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',41)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(56) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	56
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(42) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	41
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',40)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(56) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	56
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(42) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	40
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',39)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(52) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	52
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(39) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	39
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',38)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(52) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	52
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(39) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	38
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',37)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(52) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	52
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(39) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	37
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',36)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(48) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	48
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(36) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	36
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',35)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(48) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	48
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(36) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	35
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',34)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(48) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	48
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(36) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	34
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',33)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(44) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	44
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(33) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	33
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',32)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(44) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	44
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(33) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	32
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',31)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(44) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	44
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(33) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	31
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',30)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(40) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	40
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	30
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',29)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(40) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	40
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaa	29
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',28)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(40) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	40
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaaa	28
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',27)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(36) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	36
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(27) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaaa	27
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',26)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(36) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	36
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(27) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaaa	26
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',25)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(36) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	36
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(27) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaaa	25
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',24)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh	32
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaaa	24
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',23)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE=	32
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaaa	23
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',22)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ==	32
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaaa	22
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',21)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(28) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWFh	28
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaaa	21
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',20)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(28) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYWE=	28
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaaa	20
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',19)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(28) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFhYQ==	28
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaaa	19
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',18)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(24) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWFh	24
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(18) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaaa	18
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',17)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(24) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYWE=	24
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(18) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaaa	17
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',16)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(24) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFhYQ==	24
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(18) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaaa	16
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',15)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWFh	20
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaaa	15
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',14)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYWE=	20
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaaa	14
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',13)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFhYQ==	20
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaaa	13
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',12)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWFh	16
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaaa	12
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',11)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYWE=	16
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaaa	11
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',10)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFhYQ==	16
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaaa	10
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',9)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWFh	12
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaaa	9
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',8)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYWE=	12
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaaa	8
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',7)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFhYQ==	12
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaaa	7
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',6)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(8) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWFh	8
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaaa	6
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',5)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(8) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYWE=	8
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaaa	5
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',4)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(8) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFhYQ==	8
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaaa	4
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',3)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWFh	4
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aaa	3
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',2)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YWE=	4
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
aa	2
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',1)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varchar(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
YQ==	4
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
a	1
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT TO_BASE64(REPEAT('a',0)) AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` char(0) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64) FROM t1;
to_base64	LENGTH(to_base64)
	0
CREATE TABLE t2 AS SELECT from_base64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` binary(0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT from_base64, LENGTH(from_base64) FROM t2;
from_base64	LENGTH(from_base64)
	0
DROP TABLE t2;
DROP TABLE t1;

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;
TO_BASE64(a)	hex(a)
AA==	00
AAA=	0000
AAAA	000000
AAAAAA==	00000000
AAECAwQFBgcICQ==	00010203040506070809
DROP TABLE t1;
#
# Test NULL output for NULL input
#
SELECT TO_BASE64(NULL);
TO_BASE64(NULL)
NULL
SELECT FROM_BASE64(NULL);
FROM_BASE64(NULL)
NULL
#
# RFC4648 test vectors
#
SELECT @b:= TO_BASE64(''), FROM_BASE64(@b);
@b:= TO_BASE64('')	FROM_BASE64(@b)
	
SELECT @b:= TO_BASE64('f'), FROM_BASE64(@b);
@b:= TO_BASE64('f')	FROM_BASE64(@b)
Zg==	f
SELECT @b:= TO_BASE64('fo'), FROM_BASE64(@b);
@b:= TO_BASE64('fo')	FROM_BASE64(@b)
Zm8=	fo
SELECT @b:= TO_BASE64('foo'), FROM_BASE64(@b);
@b:= TO_BASE64('foo')	FROM_BASE64(@b)
Zm9v	foo
SELECT @b:= TO_BASE64('foob'), FROM_BASE64(@b);
@b:= TO_BASE64('foob')	FROM_BASE64(@b)
Zm9vYg==	foob
SELECT @b:= TO_BASE64('fooba'), FROM_BASE64(@b);
@b:= TO_BASE64('fooba')	FROM_BASE64(@b)
Zm9vYmE=	fooba
SELECT @b:= TO_BASE64('foobar'), FROM_BASE64(@b);
@b:= TO_BASE64('foobar')	FROM_BASE64(@b)
Zm9vYmFy	foobar
#
# Invalid characters - return NULL
#
SELECT hex(FROM_BASE64('#'));
hex(FROM_BASE64('#'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 0
SELECT hex(FROM_BASE64('A#'));
hex(FROM_BASE64('A#'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 1
SELECT hex(FROM_BASE64('AB#'));
hex(FROM_BASE64('AB#'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 2
SELECT hex(FROM_BASE64('ABC#'));
hex(FROM_BASE64('ABC#'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 3
SELECT hex(FROM_BASE64('ABCD#'));
hex(FROM_BASE64('ABCD#'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 4
#
# "=" is not valid on the first and second positions of a quadruple
#
SELECT hex(FROM_BASE64('='));
hex(FROM_BASE64('='))
NULL
Warnings:
Warning	1958	Bad base64 data as position 0
SELECT hex(FROM_BASE64('A='));
hex(FROM_BASE64('A='))
NULL
Warnings:
Warning	1958	Bad base64 data as position 1
SELECT hex(FROM_BASE64('ABCD='));
hex(FROM_BASE64('ABCD='))
NULL
Warnings:
Warning	1958	Bad base64 data as position 4
SELECT hex(FROM_BASE64('ABCDE='));
hex(FROM_BASE64('ABCDE='))
NULL
Warnings:
Warning	1958	Bad base64 data as position 5
#
# Incomplete sequences - return NULL
#
SELECT hex(FROM_BASE64('A'));
hex(FROM_BASE64('A'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 1
SELECT hex(FROM_BASE64('AB'));
hex(FROM_BASE64('AB'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 2
SELECT hex(FROM_BASE64('ABC'));
hex(FROM_BASE64('ABC'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 3
#
# Unexpected input after pad characters - return NULL
#
SELECT hex(FROM_BASE64('AAA=x'));
hex(FROM_BASE64('AAA=x'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 4
SELECT hex(FROM_BASE64('AA==x'));
hex(FROM_BASE64('AA==x'))
NULL
Warnings:
Warning	1958	Bad base64 data as position 4
#
# Delimiters are allowed at any position
#
SELECT hex(FROM_BASE64('  A B C D  '));
hex(FROM_BASE64('  A B C D  '))
001083
SELECT hex(FROM_BASE64('  A A = = '));
hex(FROM_BASE64('  A A = = '))
00
SELECT hex(FROM_BASE64('  A A A = '));
hex(FROM_BASE64('  A A A = '))
0000
SELECT hex(FROM_BASE64('  A  \n  B  \r  C  \t D  '));
hex(FROM_BASE64('  A  \n  B  \r  C  \t D  '))
001083
#
# Testing that to_base64 respects max_allowed_packet
#
SELECT LENGTH(TO_BASE64(REPEAT('a', @@max_allowed_packet-10)));
LENGTH(TO_BASE64(REPEAT('a', @@max_allowed_packet-10)))
NULL
Warnings:
Warning	1301	Result of to_base64() was larger than max_allowed_packet (1048576) - truncated
#
# Testing base64 with various data types
#
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;
FROM_BASE64(TO_BASE64(i1))
-12345
SELECT FROM_BASE64(TO_BASE64(f1)) FROM t1;
FROM_BASE64(TO_BASE64(f1))
-456.789
SELECT FROM_BASE64(TO_BASE64(dc1)) FROM t1;
FROM_BASE64(TO_BASE64(dc1))
123.45000
SELECT FROM_BASE64(TO_BASE64(e1)) FROM t1;
FROM_BASE64(TO_BASE64(e1))
enum13
SELECT FROM_BASE64(TO_BASE64(s1)) FROM t1;
FROM_BASE64(TO_BASE64(s1))
set1,set3
SELECT FROM_BASE64(TO_BASE64(t1)) FROM t1;
FROM_BASE64(TO_BASE64(t1))
01:02:03
SELECT FROM_BASE64(TO_BASE64(d1)) FROM t1;
FROM_BASE64(TO_BASE64(d1))
2010-01-01
SELECT FROM_BASE64(TO_BASE64(dt1)) FROM t1;
FROM_BASE64(TO_BASE64(dt1))
2011-01-01 02:03:04
DROP TABLE t1;
SELECT SPACE(@@global.max_allowed_packet*2);
SPACE(@@global.max_allowed_packet*2)
NULL
Warnings:
Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
SET NAMES latin1;
PREPARE stmt FROM "SELECT COLLATION(space(2))";
EXECUTE stmt;
COLLATION(space(2))
latin1_swedish_ci
SET NAMES latin2;
EXECUTE stmt;
COLLATION(space(2))
latin2_general_ci
set global max_allowed_packet=default;
#
# End of 5.6 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
#
CREATE TABLE t1 (a BIGINT(20) ZEROFILL);
INSERT INTO t1 VALUES (18446744073709551615),(0);
SELECT * FROM t1 WHERE a=18446744073709551615;
a
18446744073709551615
SELECT * FROM t1 WHERE FORMAT(a,0)='18,446,744,073,709,551,615';
a
18446744073709551615
SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';
a
18446744073709551615
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 18446744073709551615) and (format(`test`.`t1`.`a`,0) = '18,446,744,073,709,551,615'))
DROP TABLE t1;
#
# Bug#58081 Duplicate entry error when doing GROUP BY
# MDEV-9332 Bug after upgrade to 10.1.10
#
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);
COUNT(*)
25
DROP TABLE t1;
#
# End of 10.1 tests
#