diff options
Diffstat (limited to 'mysql-test/main/func_str.result')
| -rw-r--r-- | mysql-test/main/func_str.result | 4926 |
1 files changed, 4926 insertions, 0 deletions
diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result new file mode 100644 index 00000000000..2b376373f51 --- /dev/null +++ b/mysql-test/main/func_str.result @@ -0,0 +1,4926 @@ +drop table if exists t1,t2; +set global max_allowed_packet=1048576; +connect conn1,localhost,root,,; +connection conn1; +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 rpad('abcd',7),lpad('abcd',7); +rpad('abcd',7) lpad('abcd',7) +abcd abcd +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' +Warnings: +Warning 1365 Division by 0 +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(lpad(_latin2'a',4)), coercibility(lpad(_latin2'a',4)); +collation(lpad(_latin2'a',4)) coercibility(lpad(_latin2'a',4)) +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(rpad(_latin2'a',4)), coercibility(rpad(_latin2'a',4)); +collation(rpad(_latin2'a',4)) coercibility(rpad(_latin2'a',4)) +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'), +lpad(_latin2'a',4), +rpad(_latin2'a',4), +concat_ws(_latin2'a',_latin2'b'), +make_set(255,_latin2'a',_latin2'b',_latin2'c'), +export_set(255,_latin2'y',_latin2'n',_latin2' '), +trim(_latin2' a '), +ltrim(_latin2' a '), +rtrim(_latin2' a '), +trim(LEADING _latin2' ' FROM _latin2' a '), +trim(TRAILING _latin2' ' FROM _latin2' a '), +trim(BOTH _latin2' ' FROM _latin2' a '), +repeat(_latin2'a',10), +reverse(_latin2'ab'), +quote(_latin2'ab'), +soundex(_latin2'ab'), +substring(_latin2'ab',1), +insert(_latin2'abcd',2,3,_latin2'ef'), +replace(_latin2'abcd',_latin2'b',_latin2'B'), +encode('abcd','ab') +; +show create table t1; +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) DEFAULT NULL, + `char(130)` varbinary(4) DEFAULT NULL, + `format(130,10)` varchar(45) DEFAULT NULL, + `left(_latin2'a',1)` varchar(1) CHARACTER SET latin2 DEFAULT NULL, + `right(_latin2'a',1)` varchar(1) CHARACTER SET latin2 DEFAULT NULL, + `lcase(_latin2'a')` varchar(1) CHARACTER SET latin2 DEFAULT NULL, + `ucase(_latin2'a')` varchar(1) CHARACTER SET latin2 DEFAULT NULL, + `substring(_latin2'a',1,1)` varchar(1) CHARACTER SET latin2 DEFAULT NULL, + `concat(_latin2'a',_latin2'b')` varchar(2) CHARACTER SET latin2 DEFAULT NULL, + `lpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 DEFAULT NULL, + `rpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 DEFAULT NULL, + `lpad(_latin2'a',4)` varchar(4) CHARACTER SET latin2 DEFAULT NULL, + `rpad(_latin2'a',4)` varchar(4) CHARACTER SET latin2 DEFAULT NULL, + `concat_ws(_latin2'a',_latin2'b')` varchar(1) CHARACTER SET latin2 DEFAULT NULL, + `make_set(255,_latin2'a',_latin2'b',_latin2'c')` varchar(5) CHARACTER SET latin2 DEFAULT NULL, + `export_set(255,_latin2'y',_latin2'n',_latin2' ')` varchar(127) CHARACTER SET latin2 DEFAULT NULL, + `trim(_latin2' a ')` varchar(3) CHARACTER SET latin2 DEFAULT NULL, + `ltrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 DEFAULT NULL, + `rtrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 DEFAULT NULL, + `trim(LEADING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 DEFAULT NULL, + `trim(TRAILING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 DEFAULT NULL, + `trim(BOTH _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 DEFAULT NULL, + `repeat(_latin2'a',10)` varchar(10) CHARACTER SET latin2 DEFAULT NULL, + `reverse(_latin2'ab')` varchar(2) CHARACTER SET latin2 DEFAULT NULL, + `quote(_latin2'ab')` varchar(6) CHARACTER SET latin2 DEFAULT NULL, + `soundex(_latin2'ab')` varchar(4) CHARACTER SET latin2 DEFAULT NULL, + `substring(_latin2'ab',1)` varchar(2) CHARACTER SET latin2 DEFAULT NULL, + `insert(_latin2'abcd',2,3,_latin2'ef')` varchar(6) CHARACTER SET latin2 DEFAULT NULL, + `replace(_latin2'abcd',_latin2'b',_latin2'B')` varchar(4) CHARACTER SET latin2 DEFAULT NULL, + `encode('abcd','ab')` varbinary(4) DEFAULT 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 rpad('a',4); +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) AS `rpad('a',4)` +explain extended select lpad('a',4); +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) AS `lpad('a',4)` +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 octet_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 octet_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 ignore 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 ignore 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 39 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 39 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 octet_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 rpad('hello', -1); +rpad('hello', -1) +NULL +select rpad('hello', -4294967295); +rpad('hello', -4294967295) +NULL +select rpad('hello', 4294967295); +rpad('hello', 4294967295) +NULL +Warnings: +Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated +select rpad('hello', -4294967296); +rpad('hello', -4294967296) +NULL +select rpad('hello', 4294967296); +rpad('hello', 4294967296) +NULL +Warnings: +Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated +select rpad('hello', -4294967297); +rpad('hello', -4294967297) +NULL +select rpad('hello', 4294967297); +rpad('hello', 4294967297) +NULL +Warnings: +Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated +select rpad('hello', -18446744073709551615); +rpad('hello', -18446744073709551615) +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); +rpad('hello', 18446744073709551615) +NULL +Warnings: +Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated +select rpad('hello', -18446744073709551616); +rpad('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 +select rpad('hello', 18446744073709551616); +rpad('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 rpad() was larger than max_allowed_packet (1048576) - truncated +select rpad('hello', -18446744073709551617); +rpad('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 +select rpad('hello', 18446744073709551617); +rpad('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 rpad() was larger than max_allowed_packet (1048576) - truncated +select rpad('hello', 0, 'x'); +rpad('hello', 0, 'x') + +select rpad('hello', 0); +rpad('hello', 0) + +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 +select lpad('hello', -1); +lpad('hello', -1) +NULL +select lpad('hello', -4294967295); +lpad('hello', -4294967295) +NULL +select lpad('hello', 4294967295); +lpad('hello', 4294967295) +NULL +Warnings: +Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated +select lpad('hello', -4294967296); +lpad('hello', -4294967296) +NULL +select lpad('hello', 4294967296); +lpad('hello', 4294967296) +NULL +Warnings: +Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated +select lpad('hello', -4294967297); +lpad('hello', -4294967297) +NULL +select lpad('hello', 4294967297); +lpad('hello', 4294967297) +NULL +Warnings: +Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated +select lpad('hello', -18446744073709551615); +lpad('hello', -18446744073709551615) +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); +lpad('hello', 18446744073709551615) +NULL +Warnings: +Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated +select lpad('hello', -18446744073709551616); +lpad('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 +select lpad('hello', 18446744073709551616); +lpad('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 lpad() was larger than max_allowed_packet (1048576) - truncated +select lpad('hello', -18446744073709551617); +lpad('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 +select lpad('hello', 18446744073709551617); +lpad('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 lpad() was larger than max_allowed_packet (1048576) - truncated +select lpad('hello', 0, 'x'); +lpad('hello', 0, 'x') + +select lpad('hello', 0); +lpad('hello', 0) + +Check parameters count rpad / lpad +select rpad('x'); +ERROR 42000: Incorrect parameter count in the call to native function 'rpad' +select rpad('x',2,'.','z'); +ERROR 42000: Incorrect parameter count in the call to native function 'rpad' +select lpad('x'); +ERROR 42000: Incorrect parameter count in the call to native function 'lpad' +select lpad('x',2,'.','z'); +ERROR 42000: Incorrect parameter count in the call to native function 'lpad' +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 +select rpad('abc', cast(5 as unsigned integer)); +rpad('abc', cast(5 as unsigned integer)) +abc +select lpad('abc', cast(5 as unsigned integer)); +lpad('abc', cast(5 as unsigned integer)) + abc +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 57 4 Y 0 39 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 +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(45) DEFAULT 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; +connect newconn, localhost, root,,; +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; +ERROR HY000: Illegal parameter data type varchar for operation 'st_geometryfromwkb' +connection conn1; +SET @@global.max_allowed_packet:= @tmp_max; +disconnect newconn; +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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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) DEFAULT 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 +disconnect conn1; +connection default; +set global max_allowed_packet=default; +# +# End of 5.6 tests +# +# +# Start of 10.0 tests +# +# +# MDEV-12681 Wrong VIEW results for CHAR(0xDF USING latin1) +# +EXPLAIN EXTENDED SELECT CHAR(0xDF USING latin1); +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(0xdf using latin1) AS `CHAR(0xDF USING latin1)` +EXPLAIN EXTENDED SELECT CHAR(0xDF USING `binary`); +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(0xdf) AS `CHAR(0xDF USING ``binary``)` +EXPLAIN EXTENDED SELECT CHAR(0xDF); +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(0xdf) AS `CHAR(0xDF)` +# +# 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; +# Start of func_str_ascii_checksum.inc +# +# MDEV-10850 Wrong result for WHERE .. (f2=TO_BASE64('test') OR f2=TO_BASE64('TEST')) +# +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(255), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('test',hex('test')), ('TEST', hex('TEST')); +SELECT * FROM t1 IGNORE INDEX(k1) WHERE f1='test' AND (f2= hex("test") OR f2= hex("TEST")); +f1 f2 +test 74657374 +TEST 54455354 +SELECT * FROM t1 WHERE f1='test' AND (f2= hex("test") OR f2= hex("TEST")); +f1 f2 +TEST 54455354 +test 74657374 +SELECT * FROM t1 WHERE f1='test' AND (f2= hex("TEST") OR f2= hex("test")); +f1 f2 +TEST 54455354 +test 74657374 +DROP TABLE t1; +# +# MDEV-10425 Assertion `collation.derivation == DERIVATION_IMPLICIT' failed in Item_func_conv_charset::fix_length_and_dec() +# +PREPARE stmt FROM "SELECT hex(CONVERT('foo' USING latin1))"; +EXECUTE stmt; +hex(CONVERT('foo' USING latin1)) +666F6F +DEALLOCATE PREPARE stmt; +# End of func_str_ascii_checksum.inc +# Start of func_str_ascii_checksum.inc +# +# MDEV-10850 Wrong result for WHERE .. (f2=TO_BASE64('test') OR f2=TO_BASE64('TEST')) +# +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(255), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('test',to_base64('test')), ('TEST', to_base64('TEST')); +SELECT * FROM t1 IGNORE INDEX(k1) WHERE f1='test' AND (f2= to_base64("test") OR f2= to_base64("TEST")); +f1 f2 +test dGVzdA== +TEST VEVTVA== +SELECT * FROM t1 WHERE f1='test' AND (f2= to_base64("test") OR f2= to_base64("TEST")); +f1 f2 +test dGVzdA== +TEST VEVTVA== +SELECT * FROM t1 WHERE f1='test' AND (f2= to_base64("TEST") OR f2= to_base64("test")); +f1 f2 +test dGVzdA== +TEST VEVTVA== +DROP TABLE t1; +# +# MDEV-10425 Assertion `collation.derivation == DERIVATION_IMPLICIT' failed in Item_func_conv_charset::fix_length_and_dec() +# +PREPARE stmt FROM "SELECT to_base64(CONVERT('foo' USING latin1))"; +EXECUTE stmt; +to_base64(CONVERT('foo' USING latin1)) +Zm9v +DEALLOCATE PREPARE stmt; +# End of func_str_ascii_checksum.inc +# +# MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +# +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(128), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('YQ==',from_base64('YQ==')), ('Yq==', from_base64('Yq==')); +SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +f1 HEX(f2) +YQ== 61 +Yq== 62 +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +f1 HEX(f2) +YQ== 61 +Yq== 62 +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ==")); +f1 HEX(f2) +YQ== 61 +Yq== 62 +DROP TABLE t1; +# +# End of 10.1 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-12685 Oracle-compatible function CHR() +# +select chr(65); +chr(65) +A +create database mysqltest1 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin'; +use mysqltest1; +select charset(chr(65)), length(chr(65)),char_length(chr(65)); +charset(chr(65)) length(chr(65)) char_length(chr(65)) +utf8 1 1 +select charset(chr(14844588)), length(chr(14844588)),char_length(chr(14844588)); +charset(chr(14844588)) length(chr(14844588)) char_length(chr(14844588)) +utf8 3 1 +drop database mysqltest1; +use test; +# +# MDEV-12592 Illegal mix of collations with the HEX function +# +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES (0x09),('a'); +SELECT IF(a<' ',HEX(a),a) FROM t1 ORDER BY a; +IF(a<' ',HEX(a),a) +09 +a +DROP TABLE t1; +# +# MDEV-12783 sql_mode=ORACLE: Functions LENGTH() and LENGTHB() +# +SELECT LENGTHB('a'), LENGTHB(_utf8 0xC39F), LENGTHB(123), LENGTH(null); +LENGTHB('a') LENGTHB(_utf8 0xC39F) LENGTHB(123) LENGTH(null) +1 2 3 NULL +EXPLAIN EXTENDED SELECT LENGTH('a'), LENGTHB('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 octet_length('a') AS `LENGTH('a')`,octet_length('a') AS `LENGTHB('a')` +# +# MDEV-13555 Assertion `!item->null_value' failed in Type_handler::Item_send_str +# +CREATE TABLE t1 (c VARCHAR(8)); +INSERT INTO t1 VALUES (NULL),('bar'); +SELECT LPAD( c, 0, '?' ) FROM t1; +LPAD( c, 0, '?' ) +NULL + +SELECT RPAD( c, 0, '?' ) FROM t1; +RPAD( c, 0, '?' ) +NULL + +DROP TABLE t1; +# +# End of 10.3 tests +# |