drop table if exists t1,t2; set names latin1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo 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 repeat('monty',5),concat('*',space(5),'*'); repeat('monty',5) concat('*',space(5),'*') montymontymontymontymonty * * select reverse('abc'),reverse('abcd'); reverse('abc') reverse('abcd') cba dcba select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22); rpad('a',4,'1') rpad('a',4,'12') rpad('abcd',3,'12') rpad(11, 10 , 22) rpad("ab", 10, 22) a111 a121 abc 1122222222 ab22222222 select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22); lpad('a',4,'1') lpad('a',4,'12') lpad('abcd',3,'12') lpad(11, 10 , 22) 111a 121a abc 2222222211 select rpad(741653838,17,'0'),lpad(741653838,17,'0'); rpad(741653838,17,'0') lpad(741653838,17,'0') 74165383800000000 00000000741653838 select rpad('abcd',7,'ab'),lpad('abcd',7,'ab'); rpad('abcd',7,'ab') lpad('abcd',7,'ab') abcdaba abaabcd select rpad('abcd',1,'ab'),lpad('abcd',1,'ab'); rpad('abcd',1,'ab') lpad('abcd',1,'ab') a a select rpad('STRING', 20, CONCAT('p','a','d') ); rpad('STRING', 20, CONCAT('p','a','d') ) STRINGpadpadpadpadpa select lpad('STRING', 20, CONCAT('p','a','d') ); lpad('STRING', 20, CONCAT('p','a','d') ) padpadpadpadpaSTRING select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'); LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') NULL NULL select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0"); least(1,2,3) | greatest(16,32,8) least(5,4)*1 greatest(-1.0,1.0)*1 least(3,2,1)*1.0 greatest(1,1.1,1.0) least("10",9) greatest("A","B","0") 33 4 1.0 1.0 1.1 10 B 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 (67108864) - 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 (i int, j int); INSERT INTO t1 VALUES (1,1),(2,2); SELECT DISTINCT i, ELT(j, '345', '34') FROM t1; i ELT(j, '345', '34') 1 345 2 34 DROP TABLE t1; create table t1(a char(4)); insert into t1 values ('one'),(NULL),('two'),('four'); select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1; a quote(a) isnull(quote(a)) quote(a) is null ifnull(quote(a), 'n') one 'one' 0 0 'one' NULL NULL 0 0 NULL two 'two' 0 0 'two' four 'four' 0 0 'four' drop table t1; select trim(trailing 'foo' from 'foo'); trim(trailing 'foo' from 'foo') select trim(leading 'foo' from 'foo'); trim(leading 'foo' from 'foo') select quote(ltrim(concat(' ', 'a'))); quote(ltrim(concat(' ', 'a'))) 'a' select quote(trim(concat(' ', 'a'))); quote(trim(concat(' ', 'a'))) 'a' CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3; SELECT QUOTE('A') FROM t1; QUOTE('A') 'A' 'A' 'A' DROP TABLE t1; select 1=_latin1'1'; 1=_latin1'1' 1 select _latin1'1'=1; _latin1'1'=1 1 select _latin2'1'=1; _latin2'1'=1 1 select 1=_latin2'1'; 1=_latin2'1' 1 select _latin1'1'=_latin2'1'; ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' select row('a','b','c') = row('a','b','c'); row('a','b','c') = row('a','b','c') 1 select row('A','b','c') = row('a','b','c'); row('A','b','c') = row('a','b','c') 1 select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c'); row('A' COLLATE latin1_bin,'b','c') = row('a','b','c') 0 select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c'); row('A','b','c') = row('a' COLLATE latin1_bin,'b','c') 0 select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '=' select concat(_latin1'a',_latin2'a'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' select concat(_latin1'a',_latin2'a',_latin5'a'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat' select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); ERROR HY000: Illegal mix of collations for operation 'concat' select concat_ws(_latin1'a',_latin2'a'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat_ws' select FIELD('b','A','B'); FIELD('b','A','B') 2 select FIELD('B','A','B'); FIELD('B','A','B') 2 select FIELD('b' COLLATE latin1_bin,'A','B'); FIELD('b' COLLATE latin1_bin,'A','B') 0 select FIELD('b','A' COLLATE latin1_bin,'B'); FIELD('b','A' COLLATE latin1_bin,'B') 0 select FIELD(_latin2'b','A','B'); ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' select FIELD('b',_latin2'A','B'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' select FIELD('1',_latin2'3','2',1); FIELD('1',_latin2'3','2',1) 3 select POSITION(_latin1'B' IN _latin1'abcd'); POSITION(_latin1'B' IN _latin1'abcd') 2 select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin); POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin) 0 select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd'); POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd') 0 select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin); ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate' select POSITION(_latin1'B' IN _latin2'abcd'); ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'); FIND_IN_SET(_latin1'B',_latin1'a,b,c,d') 2 select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set' select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set' select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2) abcdabc select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substring_index' select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2); ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substring_index' select _latin1'B' between _latin1'a' and _latin1'c'; _latin1'B' between _latin1'a' and _latin1'c' 1 select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'; _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c' 0 select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'; _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c' 0 select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin; _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin 0 select _latin2'B' between _latin1'a' and _latin1'b'; ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between' select _latin1'B' between _latin2'a' and _latin1'b'; ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between' select _latin1'B' between _latin1'a' and _latin2'b'; ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between' select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b'; ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between' select _latin1'B' in (_latin1'a',_latin1'b'); _latin1'B' in (_latin1'a',_latin1'b') 1 select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b'); _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b') 0 select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b'); _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b') 0 select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin); _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin) 0 select _latin2'B' in (_latin1'a',_latin1'b'); ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' select _latin1'B' in (_latin2'a',_latin1'b'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' select _latin1'B' in (_latin1'a',_latin2'b'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN ' select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b'); ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin); ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN ' select collation(bin(130)), coercibility(bin(130)); collation(bin(130)) coercibility(bin(130)) latin1_swedish_ci 4 select collation(oct(130)), coercibility(oct(130)); collation(oct(130)) coercibility(oct(130)) latin1_swedish_ci 4 select collation(conv(130,16,10)), coercibility(conv(130,16,10)); collation(conv(130,16,10)) coercibility(conv(130,16,10)) latin1_swedish_ci 4 select collation(hex(130)), coercibility(hex(130)); collation(hex(130)) coercibility(hex(130)) latin1_swedish_ci 4 select collation(char(130)), coercibility(hex(130)); collation(char(130)) coercibility(hex(130)) binary 4 select collation(format(130,10)), coercibility(format(130,10)); collation(format(130,10)) coercibility(format(130,10)) latin1_swedish_ci 4 select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a')); collation(lcase(_latin2'a')) coercibility(lcase(_latin2'a')) latin2_general_ci 4 select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a')); collation(ucase(_latin2'a')) coercibility(ucase(_latin2'a')) latin2_general_ci 4 select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1)); collation(left(_latin2'a',1)) coercibility(left(_latin2'a',1)) latin2_general_ci 4 select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1)); collation(right(_latin2'a',1)) coercibility(right(_latin2'a',1)) latin2_general_ci 4 select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1)); collation(substring(_latin2'a',1,1)) coercibility(substring(_latin2'a',1,1)) latin2_general_ci 4 select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b')); collation(concat(_latin2'a',_latin2'b')) coercibility(concat(_latin2'a',_latin2'b')) latin2_general_ci 4 select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b')); collation(lpad(_latin2'a',4,_latin2'b')) coercibility(lpad(_latin2'a',4,_latin2'b')) latin2_general_ci 4 select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b')); collation(rpad(_latin2'a',4,_latin2'b')) coercibility(rpad(_latin2'a',4,_latin2'b')) latin2_general_ci 4 select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b')); collation(concat_ws(_latin2'a',_latin2'b')) coercibility(concat_ws(_latin2'a',_latin2'b')) latin2_general_ci 4 select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')); collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')) coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')) latin2_general_ci 4 select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')); collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')) coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')) latin2_general_ci 4 select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a ')); collation(trim(_latin2' a ')) coercibility(trim(_latin2' a ')) latin2_general_ci 4 select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a ')); collation(ltrim(_latin2' a ')) coercibility(ltrim(_latin2' a ')) latin2_general_ci 4 select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a ')); collation(rtrim(_latin2' a ')) coercibility(rtrim(_latin2' a ')) latin2_general_ci 4 select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a')); collation(trim(LEADING _latin2' ' FROM _latin2'a')) coercibility(trim(LEADING _latin2'a' FROM _latin2'a')) latin2_general_ci 4 select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')); collation(trim(TRAILING _latin2' ' FROM _latin2'a')) coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')) latin2_general_ci 4 select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a')); collation(trim(BOTH _latin2' ' FROM _latin2'a')) coercibility(trim(BOTH _latin2'a' FROM _latin2'a')) latin2_general_ci 4 select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10)); collation(repeat(_latin2'a',10)) coercibility(repeat(_latin2'a',10)) latin2_general_ci 4 select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab')); collation(reverse(_latin2'ab')) coercibility(reverse(_latin2'ab')) latin2_general_ci 4 select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab')); collation(quote(_latin2'ab')) coercibility(quote(_latin2'ab')) latin2_general_ci 4 select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab')); collation(soundex(_latin2'ab')) coercibility(soundex(_latin2'ab')) latin2_general_ci 4 select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1)); collation(substring(_latin2'ab',1)) coercibility(substring(_latin2'ab',1)) latin2_general_ci 4 select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef')); collation(insert(_latin2'abcd',2,3,_latin2'ef')) coercibility(insert(_latin2'abcd',2,3,_latin2'ef')) latin2_general_ci 4 select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); collation(replace(_latin2'abcd',_latin2'b',_latin2'B')) coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')) latin2_general_ci 4 create table t1 charset latin1 select bin(130), oct(130), conv(130,16,10), hex(130), char(130), format(130,10), left(_latin2'a',1), right(_latin2'a',1), lcase(_latin2'a'), ucase(_latin2'a'), substring(_latin2'a',1,1), concat(_latin2'a',_latin2'b'), lpad(_latin2'a',4,_latin2'b'), rpad(_latin2'a',4,_latin2'b'), concat_ws(_latin2'a',_latin2'b'), make_set(255,_latin2'a',_latin2'b',_latin2'c'), export_set(255,_latin2'y',_latin2'n',_latin2' '), trim(_latin2' a '), ltrim(_latin2' a '), rtrim(_latin2' a '), trim(LEADING _latin2' ' FROM _latin2' a '), trim(TRAILING _latin2' ' FROM _latin2' a '), trim(BOTH _latin2' ' FROM _latin2' a '), repeat(_latin2'a',10), reverse(_latin2'ab'), quote(_latin2'ab'), soundex(_latin2'ab'), substring(_latin2'ab',1), insert(_latin2'abcd',2,3,_latin2'ef'), replace(_latin2'abcd',_latin2'b',_latin2'B') ; 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(8) DEFAULT NULL, `char(130)` varbinary(4) DEFAULT NULL, `format(130,10)` varchar(38) 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, `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 NOT NULL DEFAULT '', `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 ) ENGINE=InnoDB 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 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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) charset latin1; 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 select md5('hello'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select md5('hello') AS `md5('hello')` explain select sha('abc'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select sha('abc') AS `sha('abc')` explain select sha1('abc'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select sha('abc') AS `sha1('abc')` explain select soundex(''); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select soundex('') AS `soundex('')` explain select 'mood' sounds like 'mud'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select (soundex('mood') = soundex('mud')) AS `'mood' sounds like 'mud'` explain select aes_decrypt(aes_encrypt('abc','1'),'1'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select aes_decrypt(aes_encrypt('abc','1'),'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')` explain select concat('*',space(5),'*'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select concat('*',space(5),'*') AS `concat('*',space(5),'*')` explain select reverse('abc'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select reverse('abc') AS `reverse('abc')` explain select rpad('a',4,'1'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select rpad('a',4,'1') AS `rpad('a',4,'1')` explain select lpad('a',4,'1'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select lpad('a',4,'1') AS `lpad('a',4,'1')` explain select concat_ws(',','',NULL,'a'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select concat_ws(',','',NULL,'a') AS `concat_ws(',','',NULL,'a')` explain select make_set(255,_latin2'a', _latin2'b', _latin2'c'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a', _latin2'b', _latin2'c')` explain select elt(2,1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select elt(2,1) AS `elt(2,1)` explain select locate("a","b",2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select locate('a','b',2) AS `locate("a","b",2)` explain select format(130,10); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select format(130,10) AS `format(130,10)` explain select char(0); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select char(0) AS `char(0)` explain select conv(130,16,10); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select conv(130,16,10) AS `conv(130,16,10)` explain select hex(130); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select hex(130) AS `hex(130)` explain select binary 'HE'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select cast('HE' as char charset binary) AS `binary 'HE'` explain select export_set(255,_latin2'y', _latin2'n', _latin2' '); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y', _latin2'n', _latin2' ')` explain select FIELD('b' COLLATE latin1_bin,'A','B'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select field(('b' collate latin1_bin),'A','B') AS `FIELD('b' COLLATE latin1_bin,'A','B')` explain select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B', _latin1'a,b,c,d')` explain select collation(conv(130,16,10)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select collation(conv(130,16,10)) AS `collation(conv(130,16,10))` explain select coercibility(conv(130,16,10)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))` explain select length('\n\t\r\b\0\_\%\\'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select length('\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')` explain select bit_length('\n\t\r\b\0\_\%\\'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select bit_length('\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')` explain select bit_length('\n\t\r\b\0\_\%\\'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select bit_length('\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')` explain select concat('monty',' was here ','again'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select concat('monty',' was here ','again') AS `concat('monty',' was here ','again')` explain select length('hello'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select length('hello') AS `length('hello')` explain select char(ascii('h')); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select char(ascii('h')) AS `char(ascii('h'))` explain select ord('h'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select ord('h') AS `ord('h')` explain select quote(1/0); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select quote((1 / 0)) AS `quote(1/0)` explain select crc32("123"); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select crc32('123') AS `crc32("123")` explain select replace('aaaa','a','b'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select replace('aaaa','a','b') AS `replace('aaaa','a','b')` explain select insert('txs',2,1,'hi'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select insert('txs',2,1,'hi') AS `insert('txs',2,1,'hi')` explain select left(_latin2'a',1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select left(_latin2'a',1) AS `left(_latin2'a',1)` explain select right(_latin2'a',1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select right(_latin2'a',1) AS `right(_latin2'a',1)` explain select lcase(_latin2'a'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select lower(_latin2'a') AS `lcase(_latin2'a')` explain select ucase(_latin2'a'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select upper(_latin2'a') AS `ucase(_latin2'a')` explain select SUBSTR('abcdefg',3,2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select substr('abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)` explain select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select substring_index('1abcd;2abcd;3abcd;4abcd',';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)` explain select trim(_latin2' a '); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select trim(_latin2' a ') AS `trim(_latin2' a ')` explain select ltrim(_latin2' a '); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select ltrim(_latin2' a ') AS `ltrim(_latin2' a ')` explain select rtrim(_latin2' a '); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select rtrim(_latin2' a ') AS `rtrim(_latin2' a ')` 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; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb'); create table t2 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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 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) charset latin1; 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 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) ); Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. 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) ); Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. CREATE TABLE t2( artistid int(10) unsigned NOT NULL auto_increment, artistname varchar(100) NOT NULL default '', PRIMARY KEY (artistid) ); Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. CREATE TABLE t3( trackid int(10) unsigned NOT NULL, artistid int(10) unsigned NOT NULL, PRIMARY KEY (trackid,artistid) ); Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. 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) as hex_signed, hex(cast(29223372036854775809 as unsigned)) as hex_unsigned; hex_signed hex_unsigned 7FFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF Warnings: Warning 1292 Truncated incorrect DECIMAL value: '29223372036854775809' Warning 1292 Truncated incorrect DECIMAL value: '29223372036854775809' select conv(29223372036854775809, -10, 16) as conv_signed, conv(29223372036854775809, 10, 16) as conv_unsigned; conv_signed conv_unsigned 7FFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF Warnings: Warning 1292 Truncated incorrect DECIMAL value: '29223372036854775809' Warning 1292 Truncated incorrect DECIMAL value: '29223372036854775809' select hex(-29223372036854775809) as hex_signed, hex(cast(-29223372036854775809 as unsigned)) as hex_unsigned; hex_signed hex_unsigned 8000000000000000 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-29223372036854775809' Warning 1292 Truncated incorrect DECIMAL value: '-29223372036854775809' select conv(-29223372036854775809, -10, 16) as conv_signed, conv(-29223372036854775809, 10, 16) as conv_unsigned; conv_signed conv_unsigned 8000000000000000 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-29223372036854775809' Warning 1292 Truncated incorrect DECIMAL value: '-29223372036854775809' create table t1 (i int); insert into t1 values (1000000000),(1); select lpad(i, 7, ' ') as t from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def t 253 7 7 Y 0 31 8 t 1000000 1 select rpad(i, 7, ' ') as t from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def t 253 7 7 Y 0 31 8 t 1000000 1 drop table t1; select load_file("lkjlkj"); load_file("lkjlkj") NULL select ifnull(load_file("lkjlkj"),"it is null"); ifnull(load_file("lkjlkj"),"it is null") it is 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 SELECT s FROM t1 WHERE TRIM(s) > 'ab'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > 'ab') EXPLAIN SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim('y' from `test`.`t1`.`s`) > 'ab') EXPLAIN SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading 'y' from `test`.`t1`.`s`) > 'ab') EXPLAIN SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing 'y' from `test`.`t1`.`s`) > 'ab') EXPLAIN SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab') DROP TABLE t1; create table t1 (d decimal default null); insert into t1 values (null); select format(d, 2) from t1; format(d, 2) NULL drop table t1; 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 Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. 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 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)) charset utf8mb4; 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 (_utf16 0x0061003100320007), (_utf16 0x00410031003200070007); 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) charset utf8mb4; 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 SELECT * FROM t1 INNER JOIN t2 ON code=id WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL const PRIMARY PRIMARY 42 const 1 100.00 Using index 1 SIMPLE t1 NULL ref code code 43 const 4 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and (length(`test`.`t1`.`code`) = 5)) DROP TABLE t1,t2; select 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 @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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select locate('lo','hello',18446744073709551615); locate('lo','hello',18446744073709551615) 0 select locate('lo','hello',-18446744073709551616); locate('lo','hello',-18446744073709551616) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select locate('lo','hello',18446744073709551616); locate('lo','hello',18446744073709551616) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select locate('lo','hello',-18446744073709551617); locate('lo','hello',-18446744073709551617) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select locate('lo','hello',18446744073709551617); locate('lo','hello',18446744073709551617) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select left('hello', 18446744073709551615); left('hello', 18446744073709551615) hello select left('hello', -18446744073709551616); left('hello', -18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select left('hello', 18446744073709551616); left('hello', 18446744073709551616) hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select left('hello', -18446744073709551617); left('hello', -18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select left('hello', 18446744073709551617); left('hello', 18446744073709551617) hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select right('hello', 18446744073709551615); right('hello', 18446744073709551615) hello select right('hello', -18446744073709551616); right('hello', -18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select right('hello', 18446744073709551616); right('hello', 18446744073709551616) hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select right('hello', -18446744073709551617); right('hello', -18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select right('hello', 18446744073709551617); right('hello', 18446744073709551617) hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select substring('hello', 18446744073709551615, 1); substring('hello', 18446744073709551615, 1) select substring('hello', -18446744073709551616, 1); substring('hello', -18446744073709551616, 1) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select substring('hello', 18446744073709551616, 1); substring('hello', 18446744073709551616, 1) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select substring('hello', -18446744073709551617, 1); substring('hello', -18446744073709551617, 1) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select substring('hello', 18446744073709551617, 1); substring('hello', 18446744073709551617, 1) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select substring('hello', 1, 18446744073709551615); substring('hello', 1, 18446744073709551615) hello select substring('hello', 1, -18446744073709551616); substring('hello', 1, -18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select substring('hello', 1, 18446744073709551616); substring('hello', 1, 18446744073709551616) hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select substring('hello', 1, -18446744073709551617); substring('hello', 1, -18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select substring('hello', 1, 18446744073709551617); substring('hello', 1, 18446744073709551617) hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select substring('hello', 18446744073709551615, 18446744073709551615); substring('hello', 18446744073709551615, 18446744073709551615) select substring('hello', -18446744073709551616, -18446744073709551616); substring('hello', -18446744073709551616, -18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select substring('hello', 18446744073709551616, 18446744073709551616); substring('hello', 18446744073709551616, 18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select substring('hello', -18446744073709551617, -18446744073709551617); substring('hello', -18446744073709551617, -18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select substring('hello', 18446744073709551617, 18446744073709551617); substring('hello', 18446744073709551617, 18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select insert('hello', 18446744073709551616, 1, 'hi'); insert('hello', 18446744073709551616, 1, 'hi') hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select insert('hello', -18446744073709551617, 1, 'hi'); insert('hello', -18446744073709551617, 1, 'hi') hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select insert('hello', 18446744073709551617, 1, 'hi'); insert('hello', 18446744073709551617, 1, 'hi') hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select insert('hello', 1, 18446744073709551616, 'hi'); insert('hello', 1, 18446744073709551616, 'hi') hi Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select insert('hello', 1, -18446744073709551617, 'hi'); insert('hello', 1, -18446744073709551617, 'hi') hi Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select insert('hello', 1, 18446744073709551617, 'hi'); insert('hello', 1, 18446744073709551617, 'hi') hi Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' 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 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select insert('hello', 18446744073709551616, 18446744073709551616, 'hi'); insert('hello', 18446744073709551616, 18446744073709551616, 'hi') hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' select insert('hello', -18446744073709551617, -18446744073709551617, 'hi'); insert('hello', -18446744073709551617, -18446744073709551617, 'hi') hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select insert('hello', 18446744073709551617, 18446744073709551617, 'hi'); insert('hello', 18446744073709551617, 18446744073709551617, 'hi') hello Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' 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 (67108864) - 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 (67108864) - 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 (67108864) - truncated select repeat('hello', -18446744073709551615); repeat('hello', -18446744073709551615) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select repeat('hello', 18446744073709551615); repeat('hello', 18446744073709551615) NULL Warnings: Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated select repeat('hello', -18446744073709551616); repeat('hello', -18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select repeat('hello', 18446744073709551616); repeat('hello', 18446744073709551616) NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated select repeat('hello', -18446744073709551617); repeat('hello', -18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select repeat('hello', 18446744073709551617); repeat('hello', 18446744073709551617) NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - 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 (67108864) - truncated select space(-4294967296); space(-4294967296) select space(4294967296); space(4294967296) NULL Warnings: Warning 1301 Result of space() was larger than max_allowed_packet (67108864) - truncated select space(-4294967297); space(-4294967297) select space(4294967297); space(4294967297) NULL Warnings: Warning 1301 Result of space() was larger than max_allowed_packet (67108864) - truncated select space(-18446744073709551615); space(-18446744073709551615) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select space(18446744073709551615); space(18446744073709551615) NULL Warnings: Warning 1301 Result of space() was larger than max_allowed_packet (67108864) - truncated select space(-18446744073709551616); space(-18446744073709551616) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select space(18446744073709551616); space(18446744073709551616) NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1301 Result of space() was larger than max_allowed_packet (67108864) - truncated select space(-18446744073709551617); space(-18446744073709551617) Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select space(18446744073709551617); space(18446744073709551617) NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1301 Result of space() was larger than max_allowed_packet (67108864) - 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 (67108864) - 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 (67108864) - 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 (67108864) - truncated select rpad('hello', -18446744073709551615, '1'); rpad('hello', -18446744073709551615, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select rpad('hello', 18446744073709551615, '1'); rpad('hello', 18446744073709551615, '1') NULL Warnings: Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated select rpad('hello', -18446744073709551616, '1'); rpad('hello', -18446744073709551616, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select rpad('hello', 18446744073709551616, '1'); rpad('hello', 18446744073709551616, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated select rpad('hello', -18446744073709551617, '1'); rpad('hello', -18446744073709551617, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select rpad('hello', 18446744073709551617, '1'); rpad('hello', 18446744073709551617, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated select lpad('hello', -1, '1'); lpad('hello', -1, '1') NULL select lpad('hello', -4294967295, '1'); lpad('hello', -4294967295, '1') NULL select lpad('hello', 4294967295, '1'); lpad('hello', 4294967295, '1') NULL Warnings: Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - 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 (67108864) - 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 (67108864) - truncated select lpad('hello', -18446744073709551615, '1'); lpad('hello', -18446744073709551615, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551615' select lpad('hello', 18446744073709551615, '1'); lpad('hello', 18446744073709551615, '1') NULL Warnings: Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated select lpad('hello', -18446744073709551616, '1'); lpad('hello', -18446744073709551616, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551616' select lpad('hello', 18446744073709551616, '1'); lpad('hello', 18446744073709551616, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551616' Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated select lpad('hello', -18446744073709551617, '1'); lpad('hello', -18446744073709551617, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '-18446744073709551617' select lpad('hello', 18446744073709551617, '1'); lpad('hello', 18446744073709551617, '1') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1292 Truncated incorrect DECIMAL value: '18446744073709551617' Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated SET @orig_sql_mode = @@SQL_MODE; SET SQL_MODE=traditional; SELECT CHAR(0xff,0x8f USING utf8); CHAR(0xff,0x8f USING utf8) NULL Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 1300 Invalid utf8 character string: 'FF8F' SET SQL_MODE=@orig_sql_mode; select substring('abc', cast(2 as unsigned int)); substring('abc', cast(2 as unsigned int)) bc select repeat('a', cast(2 as unsigned int)); repeat('a', cast(2 as unsigned int)) aa select rpad('abc', cast(5 as unsigned integer), 'x'); rpad('abc', cast(5 as unsigned integer), 'x') abcxx select lpad('abc', cast(5 as unsigned integer), 'x'); lpad('abc', cast(5 as unsigned integer), 'x') xxabc create table t1(f1 longtext); insert into t1 values ("123"),("456"); select substring(f1,1,1) from t1 group by 1; substring(f1,1,1) 1 4 create table t2(f1 varchar(3)); insert into t1 values ("123"),("456"); select substring(f1,4,1), substring(f1,-4,1) from t2; substring(f1,4,1) substring(f1,-4,1) drop table t1,t2; DROP TABLE IF EXISTS t1; CREATE TABLE `t1` ( `id` varchar(20) NOT NULL, `tire` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`) ); Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. 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 Warnings: Warning 1411 Incorrect string value: ''G'' for function unhex SELECT UNHEX('G') IS NULL; UNHEX('G') IS NULL 1 Warnings: Warning 1411 Incorrect string value: ''G'' for function unhex DO UNHEX(-1); Warnings: Warning 1411 Incorrect string value: '-(1)' for function unhex SHOW warnings; Level Code Message Warning 1411 Incorrect string value: '-(1)' for function unhex DO UNHEX(-182680438); Warnings: Warning 1411 Incorrect string value: '-(182680438)' for function unhex SHOW warnings; Level Code Message Warning 1411 Incorrect string value: '-(182680438)' for function unhex DO UNHEX(-2251799813685249); Warnings: Warning 1411 Incorrect string value: '-(2251799813685249)' for function unhex SHOW warnings; Level Code Message Warning 1411 Incorrect string value: '-(2251799813685249)' for function unhex select unhex('5078-04-25'); unhex('5078-04-25') NULL Warnings: Warning 1411 Incorrect string value: ''5078-04-25'' for function unhex 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(*) 2212294583 1 450215437 1 1842515611 1 4088798008 1 2226203566 1 498629140 1 1790921346 1 4194326291 1 2366072709 1 2707236321 1 SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1; CRC32(a) COUNT(*) 450215437 1 498629140 1 1790921346 1 1842515611 1 2212294583 1 2226203566 1 2366072709 1 2707236321 1 4088798008 1 4194326291 1 SELECT * FROM (SELECT CRC32(a) FROM t1) t2; CRC32(a) 2212294583 450215437 1842515611 4088798008 2226203566 498629140 1790921346 4194326291 2366072709 2707236321 CREATE TABLE t2 SELECT CRC32(a) FROM t1; desc t2; Field Type Null Key Default Extra CRC32(a) int(10) unsigned YES NULL SELECT * FROM v1; C 2212294583 450215437 1842515611 4088798008 2226203566 498629140 1790921346 4194326291 2366072709 2707236321 SELECT * FROM (SELECT * FROM v1) x; C 2212294583 450215437 1842515611 4088798008 2226203566 498629140 1790921346 4194326291 2366072709 2707236321 DROP TABLE t1, t2; DROP VIEW v1; SELECT LOCATE('foo', NULL) FROM DUAL; LOCATE('foo', NULL) NULL SELECT LOCATE(NULL, 'o') FROM DUAL; LOCATE(NULL, 'o') NULL SELECT LOCATE(NULL, NULL) FROM DUAL; LOCATE(NULL, NULL) NULL SELECT LOCATE('foo', NULL) IS NULL FROM DUAL; LOCATE('foo', NULL) IS NULL 1 SELECT LOCATE(NULL, 'o') IS NULL FROM DUAL; LOCATE(NULL, 'o') IS NULL 1 SELECT LOCATE(NULL, NULL) IS NULL FROM DUAL; LOCATE(NULL, NULL) IS NULL 1 SELECT ISNULL(LOCATE('foo', NULL)) FROM DUAL; ISNULL(LOCATE('foo', NULL)) 1 SELECT ISNULL(LOCATE(NULL, 'o')) FROM DUAL; ISNULL(LOCATE(NULL, 'o')) 1 SELECT ISNULL(LOCATE(NULL, NULL)) FROM DUAL; ISNULL(LOCATE(NULL, NULL)) 1 SELECT LOCATE('foo', NULL) <=> NULL FROM DUAL; LOCATE('foo', NULL) <=> NULL 1 SELECT LOCATE(NULL, 'o') <=> NULL FROM DUAL; LOCATE(NULL, 'o') <=> NULL 1 SELECT LOCATE(NULL, NULL) <=> NULL FROM DUAL; LOCATE(NULL, NULL) <=> NULL 1 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10)); INSERT INTO t1 VALUES (1, 'foo', 'o'); INSERT INTO t1 VALUES (2, 'foo', NULL); INSERT INTO t1 VALUES (3, NULL, 'o'); INSERT INTO t1 VALUES (4, NULL, NULL); SELECT id, LOCATE(a,p) FROM t1; id LOCATE(a,p) 1 0 2 NULL 3 NULL 4 NULL SELECT id, LOCATE(a,p) IS NULL FROM t1; id LOCATE(a,p) IS NULL 1 0 2 1 3 1 4 1 SELECT id, ISNULL(LOCATE(a,p)) FROM t1; id ISNULL(LOCATE(a,p)) 1 0 2 1 3 1 4 1 SELECT id, LOCATE(a,p) <=> NULL FROM t1; id LOCATE(a,p) <=> NULL 1 0 2 1 3 1 4 1 SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL; id 2 3 4 SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL; id 2 3 4 DROP TABLE t1; SELECT SUBSTR('foo',1,0) FROM DUAL; SUBSTR('foo',1,0) SELECT SUBSTR('foo',1,CAST(0 AS SIGNED)) FROM DUAL; SUBSTR('foo',1,CAST(0 AS SIGNED)) SELECT SUBSTR('foo',1,CAST(0 AS UNSIGNED)) FROM DUAL; SUBSTR('foo',1,CAST(0 AS UNSIGNED)) CREATE TABLE t1 (a varchar(10), len int unsigned); INSERT INTO t1 VALUES ('bar', 2), ('foo', 0); SELECT SUBSTR(a,1,len) FROM t1; SUBSTR(a,1,len) ba DROP TABLE t1; CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1; SELECT HEX(c1) from t1; HEX(c1) 414243 DROP TABLE t1; CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1; SELECT HEX(c1) from v1; HEX(c1) 414243 DROP VIEW v1; create table t1(a float); insert into t1 values (1.33); select format(a, 2) from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def format(a, 2) 253 49 4 Y 0 31 8 format(a, 2) 1.33 drop table t1; CREATE TABLE t1 (c DATE, aa VARCHAR(30)); INSERT INTO t1 VALUES ('2008-12-31','aaaaaa'); SELECT DATE_FORMAT(c, GET_FORMAT(DATE, 'eur')) h, CONCAT(UPPER(aa),', ', aa) i FROM t1; h i 31.12.2008 AAAAAA, aaaaaa DROP TABLE t1; # # BUG#44774: load_file function produces valgrind warnings # CREATE TABLE t1 (a TINYBLOB); INSERT INTO t1 VALUES ('aaaaaaaa'); SELECT LOAD_FILE(a) FROM t1; LOAD_FILE(a) NULL DROP TABLE t1; CREATE TABLE t1 (f2 VARCHAR(20)); CREATE TABLE t2 (f2 VARCHAR(20)); INSERT INTO t1 VALUES ('MIN'),('MAX'); INSERT INTO t2 VALUES ('LOAD'); SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; concat_name LOAD_MIN LOAD_MAX DROP TABLE t1, t2; End of 5.0 tests # # 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; # # Bug#12634989 VALGRIND WARNING ABOUT UNINITIALIZED VALUES CREATED IN ITEM_FUNC_DAYOFMONTH # SELECT SUBSTRING('1', DAY(FROM_UNIXTIME(-1))); SUBSTRING('1', DAY(FROM_UNIXTIME(-1))) NULL SELECT LEFT('1', DAY(FROM_UNIXTIME(-1))); LEFT('1', DAY(FROM_UNIXTIME(-1))) NULL SELECT RIGHT('1', DAY(FROM_UNIXTIME(-1))); RIGHT('1', DAY(FROM_UNIXTIME(-1))) NULL SELECT REPEAT('1', DAY(FROM_UNIXTIME(-1))); REPEAT('1', DAY(FROM_UNIXTIME(-1))) NULL SELECT RPAD('hi', DAY(FROM_UNIXTIME(-1)),'?'); RPAD('hi', DAY(FROM_UNIXTIME(-1)),'?') NULL SELECT LPAD('hi', DAY(FROM_UNIXTIME(-1)),'?'); LPAD('hi', DAY(FROM_UNIXTIME(-1)),'?') NULL CREATE TABLE t1 charset utf8mb4 SELECT SUBSTRING('1', DAY(FROM_UNIXTIME(-1))) AS f1, LEFT('1', DAY(FROM_UNIXTIME(-1))) AS f2, RIGHT('1', DAY(FROM_UNIXTIME(-1))) AS f3, REPEAT('1', DAY(FROM_UNIXTIME(-1))) AS f4, RPAD('hi', DAY(FROM_UNIXTIME(-1)),'?') AS f5, LPAD('hi', DAY(FROM_UNIXTIME(-1)),'?') AS f6; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` varchar(1) CHARACTER SET latin1 DEFAULT NULL, `f2` varchar(1) CHARACTER SET latin1 DEFAULT NULL, `f3` varchar(1) CHARACTER SET latin1 DEFAULT NULL, `f4` longtext CHARACTER SET latin1, `f5` longtext CHARACTER SET latin1, `f6` longtext CHARACTER SET latin1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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.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 charset utf8mb4 AS SELECT format(123,2,'no_NO'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `format(123,2,'no_NO')` varchar(38) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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 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 67108864 Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SET @@global.max_allowed_packet=1024*1024*1024; SELECT @@global.max_allowed_packet; @@global.max_allowed_packet 1073741824 do format(rpad('111111111.1', 1111111, '999999999999999999999999999999999999999999'),0,'be_BY') ; DO round( concat( ( coalesce( ( ST_linefromwkb('2147483648', -b'1111111111111111111111111111111111111111111')), ( convert('[.DC2.]',decimal(30,30)) ), bit_count('') ) ), ( lpad( ( elt('01','}:K5')), sha1('P'), ( ( select '-9223372036854775808.1' > all (select ''))) ) ) ) ); ERROR 22003: SRID value is out of range in 'st_linefromwkb' SET @@global.max_allowed_packet:= @tmp_max; # # End of 5.5 tests # # # Start of 5.6 tests # # # WL#5510 Functions to_base64 and from_base64 # CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh YWFhYWFh 85 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 63 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh YWFhYWE= 85 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 62 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh YWFhYQ== 85 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 61 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh YWFh 81 CREATE TABLE t2 charset utf8mb4 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(60) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 60 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh YWE= 81 CREATE TABLE t2 charset utf8mb4 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(60) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 59 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh YQ== 81 CREATE TABLE t2 charset utf8mb4 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(60) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 58 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 76 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 57 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 76 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 56 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 76 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 55 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 72 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 54 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 72 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 53 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 72 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 52 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 68 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 51 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 68 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 50 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 68 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 49 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 64 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 48 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 64 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 47 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 64 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 46 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 60 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 45 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 60 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 44 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 60 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 43 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 56 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 42 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 56 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 41 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 56 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 40 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 52 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 39 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 52 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 38 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 52 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 37 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 48 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 36 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 48 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 35 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 48 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 34 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 44 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 33 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 44 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 32 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 44 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 31 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 40 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 30 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 40 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaaa 29 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 40 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaaa 28 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 36 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaaa 27 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 36 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaaa 26 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 36 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaaa 25 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFh 32 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaaa 24 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYWE= 32 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaaa 23 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFhYQ== 32 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaaa 22 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWFh 28 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaaa 21 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYWE= 28 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaaa 20 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFhYQ== 28 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaaa 19 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWFh 24 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaaa 18 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYWE= 24 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaaa 17 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFhYQ== 24 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaaa 16 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWFh 20 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaaa 15 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYWE= 20 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaaa 14 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFhYQ== 20 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaaa 13 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWFh 16 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaaa 12 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYWE= 16 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaaa 11 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFhYQ== 16 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaaa 10 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWFh 12 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaaa 9 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYWE= 12 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaaa 8 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFhYQ== 12 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaaa 7 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWFh 8 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaaa 6 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYWE= 8 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaaa 5 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFhYQ== 8 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaaa 4 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWFh 4 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aaa 3 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YWE= 4 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) aa 2 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) YQ== 4 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT from_base64, LENGTH(from_base64) FROM t2; from_base64 LENGTH(from_base64) a 1 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 charset utf8mb4 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) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT to_base64, LENGTH(to_base64) FROM t1; to_base64 LENGTH(to_base64) 0 CREATE TABLE t2 charset utf8mb4 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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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) Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SELECT @b:= TO_BASE64('f'), FROM_BASE64(@b); @b:= TO_BASE64('f') FROM_BASE64(@b) Zg== f Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SELECT @b:= TO_BASE64('fo'), FROM_BASE64(@b); @b:= TO_BASE64('fo') FROM_BASE64(@b) Zm8= fo Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SELECT @b:= TO_BASE64('foo'), FROM_BASE64(@b); @b:= TO_BASE64('foo') FROM_BASE64(@b) Zm9v foo Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SELECT @b:= TO_BASE64('foob'), FROM_BASE64(@b); @b:= TO_BASE64('foob') FROM_BASE64(@b) Zm9vYg== foob Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SELECT @b:= TO_BASE64('fooba'), FROM_BASE64(@b); @b:= TO_BASE64('fooba') FROM_BASE64(@b) Zm9vYmE= fooba Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. SELECT @b:= TO_BASE64('foobar'), FROM_BASE64(@b); @b:= TO_BASE64('foobar') FROM_BASE64(@b) Zm9vYmFy foobar Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. # # Invalid characters - return NULL # SELECT hex(FROM_BASE64('#')); hex(FROM_BASE64('#')) NULL SELECT hex(FROM_BASE64('A#')); hex(FROM_BASE64('A#')) NULL SELECT hex(FROM_BASE64('AB#')); hex(FROM_BASE64('AB#')) NULL SELECT hex(FROM_BASE64('ABC#')); hex(FROM_BASE64('ABC#')) NULL SELECT hex(FROM_BASE64('ABCD#')); hex(FROM_BASE64('ABCD#')) NULL # # "=" is not valid on the first and second positions of a quadruple # SELECT hex(FROM_BASE64('=')); hex(FROM_BASE64('=')) NULL SELECT hex(FROM_BASE64('A=')); hex(FROM_BASE64('A=')) NULL SELECT hex(FROM_BASE64('ABCD=')); hex(FROM_BASE64('ABCD=')) NULL SELECT hex(FROM_BASE64('ABCDE=')); hex(FROM_BASE64('ABCDE=')) NULL # # Incomplete sequences - return NULL # SELECT hex(FROM_BASE64('A')); hex(FROM_BASE64('A')) NULL SELECT hex(FROM_BASE64('AB')); hex(FROM_BASE64('AB')) NULL SELECT hex(FROM_BASE64('ABC')); hex(FROM_BASE64('ABC')) NULL # # Unexpected input after pad characters - return NULL # SELECT hex(FROM_BASE64('AAA=x')); hex(FROM_BASE64('AAA=x')) NULL SELECT hex(FROM_BASE64('AA==x')); hex(FROM_BASE64('AA==x')) NULL # # 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 (67108864) - 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; # # Bug#11765562 58545: # EXPORT_SET() CAN BE USED TO MAKE ENTIRE SERVER COMPLETELY UNRESPONSIVE # SELECT @tmp_max:= @@global.max_allowed_packet; @tmp_max:= @@global.max_allowed_packet 67108864 Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. 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 export_set() was larger than max_allowed_packet (1073741824) - truncated SET @@global.max_allowed_packet:= @tmp_max; 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 (67108864) - 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 # # 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 # # Bug#13812875 ILLEGAL MIX OF COLLATIONS WITH FUNCTIONS THAT USED TO WORK # SET NAMES utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. CREATE TABLE t1 (a varchar(1)); SELECT * FROM t1 WHERE a=md5('a'); a SELECT * FROM t1 WHERE a=hex('a'); a SELECT * FROM t1 WHERE a=to_base64('a'); a DROP TABLE t1; SET NAMES latin1; # # Bug #11765149 DUPLICATE ENTRY ERROR WHEN DOING GROUP BY # create table t1(a varchar(20)); insert into t1 values(''), (' '), ('xxxxxxxxxxxxxxx'); create table t2(a int); insert into t2 values(0),(0),(1),(0),(10),(-2),(30),(NULL); select t1.a as str, length(t1.a) as str_length, t2.a as pos, t2.a + 10 as length, insert(t1.a, t2.a, t2.a + 10, '1234567') as 'insert' from t1, t2; str str_length pos length insert 0 -2 8 0 0 10 0 0 10 0 0 10 0 1 11 0 10 20 0 30 40 0 NULL NULL NULL 1 -2 8 1 0 10 1 0 10 1 0 10 1 1 11 1234567 1 10 20 1 30 40 1 NULL NULL NULL xxxxxxxxxxxxxxx 15 -2 8 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx 15 0 10 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx 15 0 10 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx 15 0 10 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx 15 1 11 1234567xxxx xxxxxxxxxxxxxxx 15 10 20 xxxxxxxxx1234567 xxxxxxxxxxxxxxx 15 30 40 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx 15 NULL NULL NULL drop table t1,t2; # # Bug #19174480: INSERT STRING FUNCTIONS RECENTLY CRASHING # CREATE TABLE t1(a TIMESTAMP(5)) ENGINE=INNODB; INSERT INTO t1 VALUES('1978-01-03 00:00:00'),('2000-001-02 00:00:00'); SELECT 1 FROM t1 GROUP BY insert(a,'1','11','1'); 1 1 SELECT insert(a,1,1,1) FROM t1; insert(a,1,1,1) 1978-01-03 00:00:00.00000 1000-01-02 00:00:00.00000 DROP TABLE t1; # # Bug#22888420 CONCAT_WS: ASSERTION FAILED: !S.USES_BUFFER_OWNED_BY(THIS) # do concat('a',concat_ws('a', 0x2859, 'a' , trim(period_add('a',1) from (1&'')) ) ); ERROR HY000: Incorrect arguments to period_add # # End of 5.6 tests # # # Bug#19646643 REPEAT() WASTES CPU TIME ON LARGE INPUT VALUES # THAT GAURANTEE EMPTY RESULT # SET @@global.max_allowed_packet=1024*1024; connect newconn, localhost, root,,; select length(repeat("",1)) as a; a 0 select length(repeat("",1024*1024*1024)) as a; a 0 select length(repeat("1",1024*1024)) as a; a 1048576 select length(repeat("1",1024*1024*1024)) as a; a NULL Warnings: Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated disconnect newconn; connection default; SET @@global.max_allowed_packet:= DEFAULT; # # Bug#20316028 RPAD + UTF16 ON X86 BUILD RUNNING ON WIN64 STACK # EXPLOITABLE do rpad(_utf16"33",1073741826,_latin1"44"); Warnings: Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated # # Bug#13740934 ASSERTION FAILED: ARGS[0]->FIXED ITEM_FUNC_CONV_CHARSET::ITEM_FUNC_CONV_CHARSET # CREATE TABLE t1(a TIME); INSERT INTO t1 VALUES (); SELECT (SELECT (SELECT 1 FROM t1 a WHERE t1.a <_ucs2'a')) AS d5 FROM t1 GROUP BY 1; d5 NULL DROP TABLE t1; # # Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 # SELECT length(rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2)) AS data; data 131072 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT length(data) AS len FROM ( SELECT rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2) AS data ) AS sub; len 131072 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT length(rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2)) AS data; data 131070 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT length(data) AS len FROM ( SELECT rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2) AS data ) AS sub; len 131070 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT length(repeat(_utf8mb4 0xE29883, 21844)) AS data; data 65532 SELECT length(data) AS len FROM ( SELECT repeat(_utf8mb4 0xE29883, 21844) AS data ) AS sub; len 65532 SELECT length(repeat(_utf8mb4 0xE29883, 21846)) AS data; data 65538 SELECT length(data) AS len FROM ( SELECT repeat(_utf8mb4 0xE29883, 21846) AS data ) AS sub; len 65538 # # Bug #22884187 RTRIM GETS EXPONENTIALLY SLOWER ON LARGE STRINGS # set names utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT TRIM(BOTH 'å' FROM 'aæaå'); TRIM(BOTH 'å' FROM 'aæaå') aæa set names default; SELECT HEX(TRIM(CONVERT(_gb18030 0x20202081408141208144202020 USING utf32))); HEX(TRIM(CONVERT(_gb18030 0x20202081408141208144202020 USING utf32))) 00004E0200004E040000002000004E0F # # Bug#21447969 FILTER USING TRIM RESULTS IN CORRECT # CREATE TABLE t1 (COUNTRY char(100)); INSERT INTO t1 VALUES ("Norway"), ("Australia"); SELECT COUNTRY FROM t1 WHERE trim(leading 'A' FROM COUNTRY) = 'ustralia'; COUNTRY Australia SELECT COUNTRY FROM t1 WHERE trim(trailing 'a' FROM COUNTRY)= 'Australi'; COUNTRY Australia SELECT COUNTRY FROM t1 WHERE trim(leading 'A' FROM COUNTRY) = 'ustralia' AND trim(trailing 'a' FROM COUNTRY) = 'Australi'; COUNTRY Australia DROP TABLE t1; # # Bug#22391186 TRUE WHERE CLAUSE DOESN'T RETURN EXPECTED ROW, # BUT EMPTY SET # CREATE DATABASE testt CHARACTER SET utf8mb4; USE testt; SET NAMES 'utf8mb4'; CREATE TABLE t1(c1 CHAR(20)); INSERT INTO t1 VALUES('11'); SELECT c1 <= REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) FROM t1; c1 <= REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) 1 SELECT * FROM t1 WHERE c1 <= REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8); c1 11 SELECT * FROM t1 WHERE c1 <= REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8); c1 11 DROP TABLE t1; DROP DATABASE testt; SET NAMES DEFAULT; USE test; # # Bug#22297983 HEX() FUNCTION CONVERTS DECIMAL TO DOUBLE AND # THUS RETURNS WRONG RESULT # select hex(cast(9007199254740992 as decimal(30,0))); hex(cast(9007199254740992 as decimal(30,0))) 20000000000000 select hex(cast(9007199254740993 as decimal(30,0))); hex(cast(9007199254740993 as decimal(30,0))) 20000000000001 select hex(cast(9007199254740994 as decimal(30,0))); hex(cast(9007199254740994 as decimal(30,0))) 20000000000002 select hex(cast(0x20000000000000 as unsigned) + 1); hex(cast(0x20000000000000 as unsigned) + 1) 20000000000001 select hex(cast(0x20000000000000 as decimal(30,0)) + 1); hex(cast(0x20000000000000 as decimal(30,0)) + 1) 20000000000001 select hex(cast(0x20000000000000 as decimal(30,0)) + 2); hex(cast(0x20000000000000 as decimal(30,0)) + 2) 20000000000002 # # Bug#22523836 FUNCTION REPLACE WITH BINARY OP DOESNT RETURN EXPECTED # NULL, WITH PARAM AS NULL # SELECT REPLACE( 'a', binary 'b', NULL ) as xxx; xxx NULL SELECT REPLACE( 'a', binary '', NULL ) as xxx; xxx NULL SELECT REPLACE( 'a', 'b', NULL ) as xxx; xxx NULL SELECT REPLACE( 'a', '', NULL ) as xxx; xxx NULL SELECT REPLACE( NULL, 'b', 'bravo' ) as xxx; xxx NULL SELECT REPLACE( NULL, '', 'bravo' ) as xxx; xxx NULL SELECT REPLACE( 'a', NULL, 'bravo' ) as xxx; xxx NULL SELECT REPLACE( 'a', binary NULL, 'bravo' ) as xxx; xxx NULL # # Bug#22565155 DATA ROWS LOST AFTER ADDING DISTINCT WITH # SUBSTR() IN SELECT LIST # CREATE TABLE t1(c1 CHAR(30)); INSERT INTO t1 VALUES('111'),('222'); SELECT DISTINCT substr(c1, 1, 2147483647) FROM t1; substr(c1, 1, 2147483647) 111 222 SELECT DISTINCT substr(c1, 1, 2147483648) FROM t1; substr(c1, 1, 2147483648) 111 222 SELECT DISTINCT substr(c1, -1, 2147483648) FROM t1; substr(c1, -1, 2147483648) 1 2 SELECT DISTINCT substr(c1, -2147483647, 2147483648) FROM t1; substr(c1, -2147483647, 2147483648) SELECT DISTINCT substr(c1, 9223372036854775807, 23) FROM t1; substr(c1, 9223372036854775807, 23) SELECT DISTINCT left(c1, 3) FROM t1; left(c1, 3) 111 222 SELECT DISTINCT left(c1, 2147483647) FROM t1; left(c1, 2147483647) 111 222 SELECT DISTINCT left(c1, 2147483648) FROM t1; left(c1, 2147483648) 111 222 SELECT DISTINCT right(c1, 3) FROM t1; right(c1, 3) 111 222 SELECT DISTINCT right(c1, 2147483647) FROM t1; right(c1, 2147483647) 111 222 SELECT DISTINCT right(c1, 2147483648) FROM t1; right(c1, 2147483648) 111 222 DROP TABLE t1; # # Bug#22545429 FUNC SUBSTRING_INDEX WORKS INCORRECTLY # WHEN THE COUNT PARAM IS A BIGINT UNSIGNED # SELECT SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ); SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ) xyz SELECT SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 ); SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 ) xyz SELECT SUBSTRING_INDEX( 'xyz', 'abc', 18446744073709551615 ); SUBSTRING_INDEX( 'xyz', 'abc', 18446744073709551615 ) xyz set names utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ); SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775807 ) xyz SELECT SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 ); SUBSTRING_INDEX( 'xyz', 'abc', 9223372036854775808 ) xyz SELECT SUBSTRING_INDEX( 'xyz', 'abc', 18446744073709551615 ); SUBSTRING_INDEX( 'xyz', 'abc', 18446744073709551615 ) xyz SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1); SUBSTRING_INDEX('www.mysql.com', '.', 1) www SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1); SUBSTRING_INDEX('www.mysql.com', '.', -1) com SELECT SUBSTRING_INDEX('www.mysql.com', '.', 100); SUBSTRING_INDEX('www.mysql.com', '.', 100) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', -100); SUBSTRING_INDEX('www.mysql.com', '.', -100) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2147483647); SUBSTRING_INDEX('www.mysql.com', '.', 2147483647) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2147483647); SUBSTRING_INDEX('www.mysql.com', '.', -2147483647) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2147483648); SUBSTRING_INDEX('www.mysql.com', '.', 2147483648) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2147483648); SUBSTRING_INDEX('www.mysql.com', '.', -2147483648) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2147483649); SUBSTRING_INDEX('www.mysql.com', '.', 2147483649) www.mysql.com SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2147483649); SUBSTRING_INDEX('www.mysql.com', '.', -2147483649) www.mysql.com set names default; # # Bug#22882717 "BIN", "OCT" FUNCTION LEADS TO MANY VALGRIND ERRORS # SELECT BIN(MID(REPEAT('b',64),21,229)); BIN(MID(REPEAT('b',64),21,229)) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' SELECT BIN(MID(1.051098E+308,9,99)); BIN(MID(1.051098E+308,9,99)) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'e308' SELECT BIN(RIGHT(REPEAT('b',64),30)); BIN(RIGHT(REPEAT('b',64),30)) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' SELECT BIN(SUBSTRING(REPEAT('b',64),9)); BIN(SUBSTRING(REPEAT('b',64),9)) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' SELECT OCT(LEFT(REPEAT('b',64),15)); OCT(LEFT(REPEAT('b',64),15)) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'bbbbbbbbbbbbbbb' # # Bug#22900560 CAST .. AS BINARY.. HEAP-USE-AFTER-FREE FROM ASAN # do from_base64(aes_encrypt(right(cast((0x5d44f4d736397d92c8267c12)as decimal),1), rand(to_days('2028-12-04 15:50:01.284969')))); Warnings: Warning 1292 Truncated incorrect BINARY value: 'x'5d44f4d736397d92c8267c12'' Warning 1264 Out of range value for column '(null)' at row 1 do from_base64(cast(right(11,1)as binary(24))); do convert(inet_aton(cast(left(-1,1)as binary(30))) using utf8); Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 1411 Incorrect string value: 'cast(left(-(1),1) as char(30) charset binary)' for function inet_aton do from_base64(cast((left(collation(4097),((repeat('1',32))or((-18772))))) as binary(40))); do from_base64(cast((mid(17653,row('-688:20:12.162697',(null)) >= row(('*.)$'),(0xc254b6)),1))as binary(34))); do from_base64(cast((mid(uuid(),20,64)) as binary(55))); set @e:=_latin1'77'; do inet_aton(aes_encrypt(left(@e,1),5)); Warnings: Warning 1411 Incorrect string value: 'aes_encrypt(left((@`e`),1),5)' for function inet_aton # # Bug#23194118 WEIGHT_STRING: ASSERTION FAILED: # SRC, FILE .\STRINGS\CTYPE-UTF8.CC, LINE 8686 # SET NAMES utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. CREATE TABLE t(a INT) engine=innodb; INSERT INTO t VALUES(1); SELECT 1 FROM t WHERE WEIGHT_STRING(CONCAT_WS('1','')); 1 SELECT 1 FROM t WHERE WEIGHT_STRING(CONCAT('','')); 1 SET NAMES DEFAULT; DROP TABLE t; # # Bug#24376444 USING COLLATE IN WHERE CLAUSE PREVENTS OPTIMIZER FROM # CONSIDERING INDEXES CREATE TABLE dd_table (name VARCHAR(64) COLLATE utf8_tolower_ci, UNIQUE KEY(name)); Warnings: Warning 3778 'utf8_tolower_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO dd_table VALUES('t1'), ('t2'); CREATE VIEW view1 AS SELECT name AS table_name FROM dd_table; CREATE VIEW view2 AS SELECT name COLLATE utf8_tolower_ci AS table_name FROM dd_table; CREATE PROCEDURE sub1(id CHAR(10) CHARACTER SET utf8) BEGIN SELECT * FROM view1 WHERE table_name=id COLLATE utf8_tolower_ci; EXPLAIN SELECT * FROM view1 WHERE table_name=id COLLATE utf8_tolower_ci; SELECT * FROM view2 WHERE table_name=id; EXPLAIN SELECT * FROM view2 WHERE table_name=id; SELECT * FROM dd_table WHERE name COLLATE utf8_tolower_ci = id; EXPLAIN SELECT * FROM dd_table WHERE name COLLATE utf8_tolower_ci = id; SELECT * FROM view1 WHERE id COLLATE utf8_tolower_ci=table_name; EXPLAIN SELECT * FROM view1 WHERE id COLLATE utf8_tolower_ci=table_name; SELECT * FROM view2 WHERE id=table_name; EXPLAIN SELECT * FROM view2 WHERE id=table_name; SELECT * FROM dd_table WHERE id = name COLLATE utf8_tolower_ci; EXPLAIN SELECT * FROM dd_table WHERE id = name COLLATE utf8_tolower_ci; SELECT * FROM view1 WHERE table_name COLLATE utf8_tolower_ci = id; EXPLAIN SELECT * FROM view1 WHERE table_name COLLATE utf8_tolower_ci = id; END | Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. CALL sub1('t1'); table_name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL const name name 195 const 1 100.00 Using index table_name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL const name name 195 const 1 100.00 Using index name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL const name name 195 const 1 100.00 Using index table_name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL const name name 195 const 1 100.00 Using index table_name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL const name name 195 const 1 100.00 Using index name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL const name name 195 const 1 100.00 Using index table_name t1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dd_table NULL index NULL name 195 NULL 2 100.00 Using where; Using index Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Note 1003 /* select#1 */ select `test`.`dd_table`.`name` AS `table_name` from `test`.`dd_table` where ((`test`.`dd_table`.`name` collate utf8_tolower_ci) = (id@0)) DROP PROCEDURE sub1; DROP VIEW view1; DROP VIEW view2; DROP TABLE dd_table; # # Bug#24911350 LOCATE('HE','HELLO',NULL) SHOULD RETURN NULL INSTEAD OF 0 # select locate('he','hello',null),locate('he',null,2),locate(null,'hello',2); locate('he','hello',null) locate('he',null,2) locate(null,'hello',2) NULL NULL NULL select locate(null,'hello',null),locate('he',null,null); locate(null,'hello',null) locate('he',null,null) NULL NULL select left('hello',null),left(null,1),left(null,null); left('hello',null) left(null,1) left(null,null) NULL NULL NULL select right('hello',null),right(null,1),right(null,null); right('hello',null) right(null,1) right(null,null) NULL NULL NULL select mid('hello',1,null),mid('hello',null,1),mid(null,1,1); mid('hello',1,null) mid('hello',null,1) mid(null,1,1) NULL NULL NULL select substr('hello',null,2),substr('hello',2,null),substr(null,1,2); substr('hello',null,2) substr('hello',2,null) substr(null,1,2) NULL NULL NULL select substr(null,null,null),mid(null,null,null); substr(null,null,null) mid(null,null,null) NULL NULL select substring_index('the king of the the null','the',null); substring_index('the king of the the null','the',null) NULL select substring_index('the king of the the null',null,3); substring_index('the king of the the null',null,3) NULL select substring_index(null,'the',3); substring_index(null,'the',3) NULL select substring_index(null,null,3); substring_index(null,null,3) NULL select substring_index(null,null,null); substring_index(null,null,null) NULL select insert(null,2,1,'hi'),insert('txs',null,1,'hi'); insert(null,2,1,'hi') insert('txs',null,1,'hi') NULL NULL select insert('txs',2,null,'hi'),insert('txs',2,1,null); insert('txs',2,null,'hi') insert('txs',2,1,null) NULL NULL select insert('txs',null,null,'hi'),insert(null,null,null,null); insert('txs',null,null,'hi') insert(null,null,null,null) NULL NULL # # Bug #24930038 BITWISE SHIFT: ASSERTION FAILED: NULL_VALUE OR LOST CONNECTION TO SERVER ERROR # SET @orig_sql_mode = @@SQL_MODE; SET SQL_MODE=''; SELECT REPEAT(0x1111, 40000000) >> 1; REPEAT(0x1111, 40000000) >> 1 NULL Warnings: Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated SELECT (~(REPEAT(0xb822, 0x5C9C380))); (~(REPEAT(0xb822, 0x5C9C380))) NULL Warnings: Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated SET SQL_MODE=@orig_sql_mode; # # Bug#25688192 ASSERTION FAILED: MAYBE_NULL IN ITEM_FUNC_CONCAT::VAL_STR # set @max_allowed_packet=@@global.max_allowed_packet; set global max_allowed_packet=2048; Warnings: Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length' connect con1,localhost,root,,; set sql_mode=""; do concat(export_set(1,1,repeat('a',31),' $',213)); Warnings: Warning 1301 Result of export_set() was larger than max_allowed_packet (2048) - truncated disconnect con1; connection default; set global max_allowed_packet=@max_allowed_packet; # # Bug#26524721 ASSERTION MAYBE_NULL FAILED IN ITEM_FUNC_CONCAT::VAL_STR # CREATE TABLE t(x VARCHAR(10) NOT NULL); Warnings: Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. SELECT COUNT(*), CONCAT(x, 'x') FROM (SELECT * FROM t) AS tt; COUNT(*) CONCAT(x, 'x') 0 NULL DROP TABLE t; # Bug#26049942 create table .. as select .. quote() give wrong length SET NAMES latin1; CREATE TABLE t1 AS SELECT quote('a'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `quote('a')` varchar(4) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET NAMES utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. CREATE TABLE t2 AS SELECT quote('a'); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `quote('a')` varchar(4) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET NAMES utf8mb4; CREATE TABLE t3 AS SELECT quote('a'); SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( `quote('a')` varchar(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET NAMES DEFAULT; DROP TABLE t1, t2, t3; # # Bug #27410088: LOWER(CHAR(51386)) NOT EVALUATED CONSISTENTLY IN # STORED PROCEDURE # CREATE PROCEDURE proc (ofs INT, count INT) BEGIN DECLARE i INT DEFAULT ofs; WHILE i < count DO SELECT i AS i; IF LOWER(CHAR(i USING utf8) COLLATE utf8_tolower_ci) != LOWER(CHAR(i USING utf8mb4) COLLATE utf8mb4_0900_as_ci) THEN SELECT i AS 'found funny character'; END IF; SET i = i + 1; END WHILE; END| Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. CALL proc(51390, 51394); i 51390 found funny character 51390 i 51391 i 51392 i 51393 CALL proc(51390, 51394); i 51390 found funny character 51390 i 51391 i 51392 i 51393 DROP PROCEDURE proc; # # Bug#29133127 ASSERTION `MAYBE_NULL' FAILED # |ITEM_FUNC_CONCAT::VAL_STR(STRING*) # SET GLOBAL max_allowed_packet=1024; Warnings: Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length' Warnings: Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. SET collation_connection=utf32_unicode_ci; SELECT CONCAT('"',CONCAT('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"'); CONCAT('"',CONCAT('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"') NULL Warnings: Warning 1301 Result of concat() was larger than max_allowed_packet (1024) - truncated 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)),'"') NULL Warnings: Warning 1301 Result of concat_ws() was larger than max_allowed_packet (1024) - truncated SELECT CONCAT_WS('"',CONCAT_WS('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"'); CONCAT_WS('"',CONCAT_WS('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"') " Warnings: Warning 1301 Result of concat_ws() was larger than max_allowed_packet (1024) - truncated SELECT CONCAT('"',CONCAT('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"'); CONCAT('"',CONCAT('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"') NULL Warnings: Warning 1301 Result of concat() was larger than max_allowed_packet (1024) - truncated 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)),'"') NULL Warnings: Warning 1301 Result of concat_ws() was larger than max_allowed_packet (1024) - truncated SELECT CONCAT_WS('"',CONCAT_WS('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"'); CONCAT_WS('"',CONCAT_WS('";"',repeat('a',60), repeat('b',60), repeat('c',60), repeat ('d',100)),'"') " Warnings: Warning 1301 Result of concat_ws() was larger than max_allowed_packet (1024) - truncated SET GLOBAL max_allowed_packet=default; # # Bug#29739778 ASSERTION FAILED: MAYBE_NULL, # IN ITEM_STR_FUNC::PUSH_PACKET_OVERFLOW_WARNING # set sql_mode=''; do repeat(row_count() ,((-9223372036854775808 )<<('{ }' )) ); Warnings: Warning 1292 Truncated incorrect INTEGER value: '{ }' Warning 1292 Truncated incorrect INTEGER value: '{ }' Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated do ((repeat(1,9223372036854775808))or(convert((0x6d5b5d8d)using dec8))); Warnings: Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated set sql_mode=default;