ctype_binary.result

set names binary;
#
# Start of 5.5 tests
#
#
# Start of WL#2649 Number-to-string conversions
#
select hex(concat(1));
hex(concat(1))
31
create table t1 as select concat(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
31
drop table t1;
select hex(concat(18446744073709551615));
hex(concat(18446744073709551615))
3138343436373434303733373039353531363135
create table t1 as select concat(18446744073709551615) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
3138343436373434303733373039353531363135
drop table t1;
select hex(concat(1.1));
hex(concat(1.1))
312E31
create table t1 as select concat(1.1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
312E31
drop table t1;
select hex(concat('a', 1+2)), charset(concat(1+2));
hex(concat('a', 1+2))	charset(concat(1+2))
6133	binary
create table t1 as select concat(1+2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1-2));
hex(concat(1-2))
2D31
create table t1 as select concat(1-2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1*2));
hex(concat(1*2))
32
create table t1 as select concat(1*2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1/2));
hex(concat(1/2))
302E35303030
create table t1 as select concat(1/2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1 div 2));
hex(concat(1 div 2))
30
create table t1 as select concat(1 div 2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1 % 2));
hex(concat(1 % 2))
31
create table t1 as select concat(1 % 2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(-1));
hex(concat(-1))
2D31
create table t1 as select concat(-1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(-(1+2)));
hex(concat(-(1+2)))
2D33
create table t1 as select concat(-(1+2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1|2));
hex(concat(1|2))
33
create table t1 as select concat(1|2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1&2));
hex(concat(1&2))
30
create table t1 as select concat(1&2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(bit_count(12)));
hex(concat(bit_count(12)))
32
create table t1 as select concat(bit_count(12)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(2<<1));
hex(concat(2<<1))
34
create table t1 as select concat(2<<1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(2>>1));
hex(concat(2>>1))
31
create table t1 as select concat(2>>1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(~0));
hex(concat(~0))
3138343436373434303733373039353531363135
create table t1 as select concat(~0) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(3^2));
hex(concat(3^2))
31
create table t1 as select concat(3^2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(abs(-2)));
hex(concat(abs(-2)))
32
create table t1 as select concat(abs(-2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(exp(2)),1));
hex(left(concat(exp(2)),1))
37
create table t1 as select concat(exp(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log(2)),1));
hex(left(concat(log(2)),1))
30
create table t1 as select concat(log(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log2(2)),1));
hex(left(concat(log2(2)),1))
31
create table t1 as select concat(log2(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log10(2)),1));
hex(left(concat(log10(2)),1))
30
create table t1 as select concat(log10(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(sqrt(2)),1));
hex(left(concat(sqrt(2)),1))
31
create table t1 as select concat(sqrt(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(pow(2,2)),1));
hex(left(concat(pow(2,2)),1))
34
create table t1 as select concat(pow(2,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(acos(0.5)),1));
hex(left(concat(acos(0.5)),1))
31
create table t1 as select concat(acos(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(asin(0.5)),1));
hex(left(concat(asin(0.5)),1))
30
create table t1 as select concat(asin(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(atan(0.5)),1));
hex(left(concat(atan(0.5)),1))
30
create table t1 as select concat(atan(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(cos(0.5)),1));
hex(left(concat(cos(0.5)),1))
30
create table t1 as select concat(cos(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(sin(0.5)),1));
hex(left(concat(sin(0.5)),1))
30
create table t1 as select concat(sin(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(tan(0.5)),1));
hex(left(concat(tan(0.5)),1))
30
create table t1 as select concat(tan(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(degrees(0)));
hex(concat(degrees(0)))
30
create table t1 as select concat(degrees(0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(radians(0)));
hex(concat(radians(0)))
30
create table t1 as select concat(radians(0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ceiling(0.5)));
hex(concat(ceiling(0.5)))
31
create table t1 as select concat(ceiling(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(floor(0.5)));
hex(concat(floor(0.5)))
30
create table t1 as select concat(floor(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(round(0.5)));
hex(concat(round(0.5)))
31
create table t1 as select concat(round(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sign(0.5)));
hex(concat(sign(0.5)))
31
create table t1 as select concat(sign(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(rand()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(length('a')));
hex(concat(length('a')))
31
create table t1 as select concat(length('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(char_length('a')));
hex(concat(char_length('a')))
31
create table t1 as select concat(char_length('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(bit_length('a')));
hex(concat(bit_length('a')))
38
create table t1 as select concat(bit_length('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(coercibility('a')));
hex(concat(coercibility('a')))
34
create table t1 as select concat(coercibility('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(locate('a','a')));
hex(concat(locate('a','a')))
31
create table t1 as select concat(locate('a','a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(field('c','a','b','c')));
hex(concat(field('c','a','b','c')))
33
create table t1 as select concat(field('c','a','b','c')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ascii(61)));
hex(concat(ascii(61)))
3534
create table t1 as select concat(ascii(61)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ord(61)));
hex(concat(ord(61)))
3534
create table t1 as select concat(ord(61)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(find_in_set('b','a,b,c,d')));
hex(concat(find_in_set('b','a,b,c,d')))
32
create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select md5('a'), hex(md5('a'));
md5('a')	hex(md5('a'))
0cc175b9c0f1b6a831c399e269772661	3063633137356239633066316236613833316333393965323639373732363631
create table t1 as select md5('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select password('a'), hex(password('a'));
password('a')	hex(password('a'))
*667F407DE7C6AD07358FA38DAED7828A72014B4E	2A36363746343037444537433641443037333538464133384441454437383238413732303134423445
Warnings:
Warning	1681	'PASSWORD' is deprecated and will be removed in a future release.
Warning	1681	'PASSWORD' is deprecated and will be removed in a future release.
create table t1 as select password('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(41) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select sha('a'), hex(sha('a'));
sha('a')	hex(sha('a'))
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8	38366637653433376661613561376663653135643164646362396561656165613337373636376238
create table t1 as select sha('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select sha1('a'), hex(sha1('a'));
sha1('a')	hex(sha1('a'))
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8	38366637653433376661613561376663653135643164646362396561656165613337373636376238
create table t1 as select sha1('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('-1' as signed)));
hex(concat(cast('-1' as signed)))
2D31
create table t1 as select concat(cast('-1' as signed)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('1' as unsigned)));
hex(concat(cast('1' as unsigned)))
31
create table t1 as select concat(cast('1' as unsigned)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast(1/2 as decimal(5,5))));
hex(concat(cast(1/2 as decimal(5,5))))
302E3530303030
create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('2001-01-02 03:04:05' as date)));
hex(concat(cast('2001-01-02 03:04:05' as date)))
323030312D30312D3032
create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t1;
c1
2001-01-02
drop table t1;
select hex(concat(cast('2001-01-02 03:04:05' as time)));
hex(concat(cast('2001-01-02 03:04:05' as time)))
30333A30343A3035
create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t1;
c1
03:04:05
drop table t1;
select hex(concat(cast('2001-01-02' as datetime)));
hex(concat(cast('2001-01-02' as datetime)))
323030312D30312D30322030303A30303A3030
create table t1 as select concat(cast('2001-01-02' as datetime)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t1;
c1
2001-01-02 00:00:00
drop table t1;
select hex(concat(least(1,2)));
hex(concat(least(1,2)))
31
create table t1 as select concat(least(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(greatest(1,2)));
hex(concat(greatest(1,2)))
32
create table t1 as select concat(greatest(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(case when 11 then 22 else 33 end));
hex(concat(case when 11 then 22 else 33 end))
3232
create table t1 as select concat(case when 11 then 22 else 33 end) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(coalesce(1,2)));
hex(concat(coalesce(1,2)))
31
create table t1 as select concat(coalesce(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat_ws(1,2,3));
hex(concat_ws(1,2,3))
323133
create table t1 as select concat_ws(1,2,3) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(group_concat(1,2,3));
hex(group_concat(1,2,3))
313233
create table t1 as select group_concat(1,2,3) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` blob
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select 1 as c1 union select 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select hex(c1) from t1 order by c1;
hex(c1)
31
61
drop table t1;
create table t1 as select concat(last_insert_id()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(benchmark(0,0)));
hex(concat(benchmark(0,0)))
30
create table t1 as select concat(benchmark(0,0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sleep(0)));
hex(concat(sleep(0)))
30
create table t1 as select concat(sleep(0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(is_free_lock('xxxx')));
hex(concat(is_free_lock('xxxx')))
31
create table t1 as select concat(is_free_lock('xxxx')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(is_used_lock('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(release_lock('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(crc32('')));
hex(concat(crc32('')))
30
create table t1 as select concat(crc32('')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(uncompressed_length('')));
hex(concat(uncompressed_length('')))
30
create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(inet_aton('127.1.1.1')));
hex(concat(inet_aton('127.1.1.1')))
32313330373732323235
create table t1 as select concat(inet_aton('127.1.1.1')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(inet_ntoa(2130772225)));
hex(concat(inet_ntoa(2130772225)))
3132372E312E312E31
create table t1 as select concat(inet_ntoa(2130772225)) as c1;
select * from t1;
c1
127.1.1.1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(31) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select 1;
1
1
select hex(concat(row_count()));
hex(concat(row_count()))
2D31
create table t1 as select concat(row_count()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(found_rows()));
hex(concat(found_rows()))
30
create table t1 as select concat(found_rows()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(uuid_short()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(uuid()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(36) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select coercibility(uuid()), coercibility(cast('a' as char character set latin1));
coercibility(uuid())	coercibility(cast('a' as char character set latin1))
4	2
select charset(concat(uuid(), cast('a' as char character set latin1)));
charset(concat(uuid(), cast('a' as char character set latin1)))
latin1
create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(37) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=1));
hex(concat(@a1:=1))
31
create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2;
select hex(c1) from t1;
hex(c1)
32
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL,
  `c2` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
set @a2=1;
select hex(concat(@a2));
hex(concat(@a2))
31
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
31
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(20) DEFAULT NULL,
  `c2` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=sqrt(1)));
hex(concat(@a1:=sqrt(1)))
31
create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2;
select hex(c1) from t1;
hex(c1)
31
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL,
  `c2` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
set @a2=sqrt(1);
select hex(concat(@a2));
hex(concat(@a2))
31
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
31
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL,
  `c2` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=1.1));
hex(concat(@a1:=1.1))
312E31
create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2;
select hex(c1) from t1;
hex(c1)
312E31
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL,
  `c2` decimal(2,1) NOT NULL DEFAULT '0.0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
set @a2=1.1;
select hex(concat(@a2));
hex(concat(@a2))
312E31
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
312E31
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(83) DEFAULT NULL,
  `c2` decimal(65,30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@@ft_max_word_len));
hex(concat(@@ft_max_word_len))
3834
create table t1 as select concat(@@ft_max_word_len) as c1;
select hex(c1) from t1;
hex(c1)
3834
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a'='a' IS TRUE));
hex(concat('a'='a' IS TRUE))
31
create table t1 as select concat('a'='a' IS TRUE) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a'='a' IS NOT TRUE));
hex(concat('a'='a' IS NOT TRUE))
30
create table t1 as select concat('a'='a' IS NOT TRUE) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NOT 'a'='a'));
hex(concat(NOT 'a'='a'))
30
create table t1 as select concat(NOT 'a'='a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' IS NULL));
hex(concat('a' IS NULL))
30
create table t1 as select concat('a' IS NULL) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' IS NOT NULL));
hex(concat('a' IS NOT NULL))
31
create table t1 as select concat('a' IS NOT NULL) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' rlike 'a'));
hex(concat('a' rlike 'a'))
31
create table t1 as select concat('a' IS NOT NULL) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(strcmp('a','b')));
hex(concat(strcmp('a','b')))
2D31
create table t1 as select concat(strcmp('a','b')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' like 'a'));
hex(concat('a' like 'a'))
31
create table t1 as select concat('a' like 'b') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' between 'b' and 'c'));
hex(concat('a' between 'b' and 'c'))
30
create table t1 as select concat('a' between 'b' and 'c') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' in ('a','b')));
hex(concat('a' in ('a','b')))
31
create table t1 as select concat('a' in ('a','b')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(interval(23, 1, 15, 17, 30, 44, 200)));
hex(concat(interval(23, 1, 15, 17, 30, 44, 200)))
33
create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a varchar(10), fulltext key(a));
insert into t1 values ('a');
select hex(concat(match (a) against ('a'))) from t1;
hex(concat(match (a) against ('a')))
30
create table t2 as select concat(match (a) against ('a')) as a from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
select hex(ifnull(1,'a'));
hex(ifnull(1,'a'))
31
create table t1 as select ifnull(1,'a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ifnull(1,1)));
hex(concat(ifnull(1,1)))
31
create table t1 as select concat(ifnull(1,1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ifnull(1.1,1.1)));
hex(concat(ifnull(1.1,1.1)))
312E31
create table t1 as select concat(ifnull(1.1,1.1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(if(1,'b',1));
hex(if(1,'b',1))
62
create table t1 as select if(1,'b',1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(if(1,1,'b'));
hex(if(1,1,'b'))
31
create table t1 as select if(1,1,'b') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(if(1,1,1)));
hex(concat(if(1,1,1)))
31
create table t1 as select concat(if(1,1,1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(nullif(1,2)));
hex(concat(nullif(1,2)))
31
create table t1 as select concat(nullif(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_Dimension(ST_GeomFromText('LINESTRING(0 0,10 10)'))));
hex(concat(ST_Dimension(ST_GeomFromText('LINESTRING(0 0,10 10)'))))
31
create table t1 as select concat(ST_Dimension(ST_GeomFromText('LINSTRING(0 0,10 10)'))) as c1;
ERROR 22023: Invalid GIS data provided to function st_geometryfromtext.
select hex(concat(ST_NumGeometries(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
hex(concat(ST_NumGeometries(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
32
create table t1 as select concat(ST_NumGeometries(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_NumPoints(ST_MultiPointFromText('LINESTRING(0 0,10 10)'))));
hex(concat(ST_NumPoints(ST_MultiPointFromText('LINESTRING(0 0,10 10)'))))
32
create table t1 as select concat(ST_NumPoints(ST_MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_SRID(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
hex(concat(ST_SRID(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
30
create table t1 as select concat(ST_SRID(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_NumInteriorRings(ST_PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))));
hex(concat(ST_NumInteriorRings(ST_PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))))
31
create table t1 as select concat(ST_NumInteriorRings(ST_PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_IsEmpty(ST_GeomFromText('POINT(1 1)'))));
hex(concat(ST_IsEmpty(ST_GeomFromText('POINT(1 1)'))))
30
create table t1 as select concat(ST_IsEmpty(ST_GeomFromText('Point(1 1)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_IsSimple(ST_GeomFromText('POINT(1 1)'))));
hex(concat(ST_IsSimple(ST_GeomFromText('POINT(1 1)'))))
31
create table t1 as select concat(ST_IsSimple(ST_GeomFromText('Point(1 1)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_IsClosed(ST_GeomFromText('LineString(1 1,2 2)'))));
hex(concat(ST_IsClosed(ST_GeomFromText('LineString(1 1,2 2)'))))
30
create table t1 as select concat(ST_IsClosed(ST_GeomFromText('LineString(1 1,2 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(MBREquals(ST_GeomFromText('Point(1 1)'),ST_GeomFromText('Point(1 1)'))));
hex(concat(MBREquals(ST_GeomFromText('Point(1 1)'),ST_GeomFromText('Point(1 1)'))))
31
create table t1 as select concat(MBREquals(ST_GeomFromText('Point(1 1)'),ST_GeomFromText('Point(1 1)'))) as c1;
drop table t1;
select hex(concat(ST_X(ST_GeomFromText('Point(1 2)'))));
hex(concat(ST_X(ST_GeomFromText('Point(1 2)'))))
31
create table t1 as select concat(ST_X(ST_GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_Y(ST_GeomFromText('Point(1 2)'))));
hex(concat(ST_Y(ST_GeomFromText('Point(1 2)'))))
32
create table t1 as select concat(ST_X(ST_GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_Length(ST_GeomFromText('LineString(1 2,2 2)'))));
hex(concat(ST_Length(ST_GeomFromText('LineString(1 2,2 2)'))))
31
create table t1 as select concat(ST_Length(ST_GeomFromText('LineString(1 2, 2 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_Area(ST_GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))));
hex(concat(ST_Area(ST_GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))))
31
create table t1 as select concat(ST_Area(ST_GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_GeometryType(ST_GeomFromText('Point(1 2)'))));
hex(concat(ST_GeometryType(ST_GeomFromText('Point(1 2)'))))
504F494E54
create table t1 as select concat(ST_GeometryType(ST_GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ST_AsText(ST_GeomFromText('Point(1 2)'))));
hex(concat(ST_AsText(ST_GeomFromText('Point(1 2)'))))
504F494E542831203229
create table t1 as select concat(ST_AsText(ST_GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` longblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(period_add(200902, 2)));
hex(concat(period_add(200902, 2)))
323030393034
create table t1 as select concat(period_add(200902, 2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
SET sql_mode = '';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
select hex(concat(period_diff(200902, 200802)));
hex(concat(period_diff(200902, 200802)))
3132
create table t1 as select concat(period_add(200902, 200802)) as c1;
Warnings:
Warning	1265	Data truncated for column 'c1' at row 1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(6) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
SET sql_mode = default;
select hex(concat(to_days(20090224)));
hex(concat(to_days(20090224)))
373333383237
create table t1 as select concat(to_days(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofmonth(20090224)));
hex(concat(dayofmonth(20090224)))
3234
create table t1 as select concat(dayofmonth(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofyear(20090224)));
hex(concat(dayofyear(20090224)))
3535
create table t1 as select concat(dayofyear(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(hour('10:11:12')));
hex(concat(hour('10:11:12')))
3130
create table t1 as select concat(hour('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(minute('10:11:12')));
hex(concat(minute('10:11:12')))
3131
create table t1 as select concat(minute('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(second('10:11:12')));
hex(concat(second('10:11:12')))
3132
create table t1 as select concat(second('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(quarter(20090224)));
hex(concat(quarter(20090224)))
31
create table t1 as select concat(quarter(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(week(20090224)));
hex(concat(week(20090224)))
38
create table t1 as select concat(week(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(yearweek(20090224)));
hex(concat(yearweek(20090224)))
323030393038
create table t1 as select concat(yearweek(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(year(20090224)));
hex(concat(year(20090224)))
32303039
create table t1 as select concat(year(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(weekday(20090224)));
hex(concat(weekday(20090224)))
31
create table t1 as select concat(weekday(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofweek(20090224)));
hex(concat(dayofweek(20090224)))
33
create table t1 as select concat(dayofweek(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(unix_timestamp(20090224)));
hex(concat(unix_timestamp(20090224)))
31323335343232383030
create table t1 as select concat(unix_timestamp(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(time_to_sec('10:11:12')));
hex(concat(time_to_sec('10:11:12')))
3336363732
create table t1 as select concat(time_to_sec('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(extract(year from 20090702)));
hex(concat(extract(year from 20090702)))
32303039
create table t1 as select concat(extract(year from 20090702)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(microsecond('12:00:00.123456')));
hex(concat(microsecond('12:00:00.123456')))
313233343536
create table t1 as select concat(microsecond('12:00:00.123456')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(month(20090224)));
hex(concat(month(20090224)))
32
create table t1 as select concat(month(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(last_day('2003-02-05')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select c1, hex(c1) from t1;
c1	hex(c1)
2003-02-28	323030332D30322D3238
drop table t1;
create table t1 as select concat(from_days(730669)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select c1, hex(c1) from t1;
c1	hex(c1)
2000-07-03	323030302D30372D3033
drop table t1;
create table t1 as select concat(curdate()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(utc_date()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(curtime()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select repeat('a',20) as c1 limit 0;
set timestamp=1216359724;
insert into t1 values (current_date);
insert into t1 values (current_time);
select c1, hex(c1) from t1;
c1	hex(c1)
2008-07-18	323030382D30372D3138
08:42:04	30383A34323A3034
drop table t1;
create table t1 as select concat(utc_time()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sec_to_time(2378)));
hex(concat(sec_to_time(2378)))
30303A33393A3338
create table t1 as select concat(sec_to_time(2378)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')));
hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')))
32343A30303A3030
create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(maketime(10,11,12)));
hex(concat(maketime(10,11,12)))
31303A31313A3132
create table t1 as select concat(maketime(10,11,12)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(get_format(DATE,'USA'));
hex(get_format(DATE,'USA'))
256D2E25642E2559
create table t1 as select get_format(DATE,'USA') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(17) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(from_unixtime(1111885200)),4));
hex(left(concat(from_unixtime(1111885200)),4))
32303035
create table t1 as select concat(from_unixtime(1111885200)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')));
hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')))
323030332D31322D33312032303A30303A3030
create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)));
hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)))
323030342D30312D30322031323A30303A3030
create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t1;
c1
2004-01-02 12:00:00
drop table t1;
select hex(concat(makedate(2009,1)));
hex(concat(makedate(2009,1)))
323030392D30312D3031
create table t1 as select concat(makedate(2009,1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t1;
c1
2009-01-01
drop table t1;
create table t1 as select concat(now()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(utc_timestamp()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(sysdate()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(addtime('00:00:00','11:22:33')));
hex(concat(addtime('00:00:00','11:22:33')))
31313A32323A3333
create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(subtime('23:59:59','11:22:33')));
hex(concat(subtime('23:59:59','11:22:33')))
31323A33373A3236
create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(elt(1,2,3));
hex(elt(1,2,3))
32
create table t1 as select elt(1,2,3) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(export_set(1,2,3,4,2));
hex(export_set(1,2,3,4,2))
323433
create table t1 as select export_set(1,2,3,4,2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(127) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(insert(1133,3,0,22));
hex(insert(1133,3,0,22))
313132323333
create table t1 as select insert(1133,3,0,22) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(lcase(123));
hex(lcase(123))
313233
create table t1 as select lcase(123) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(left(123,1));
hex(left(123,1))
31
create table t1 as select left(123,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(lower(123));
hex(lower(123))
313233
create table t1 as select lower(123) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(lpad(1,2,0));
hex(lpad(1,2,0))
3031
create table t1 as select lpad(1,2,0) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(ltrim(1));
hex(ltrim(1))
31
create table t1 as select ltrim(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(mid(1,1,1));
hex(mid(1,1,1))
31
create table t1 as select mid(1,1,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(repeat(1,2));
hex(repeat(1,2))
3131
create table t1 as select repeat(1,2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(replace(1,1,2));
hex(replace(1,1,2))
32
create table t1 as select replace(1,1,2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(reverse(12));
hex(reverse(12))
3231
create table t1 as select reverse(12) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(right(123,1));
hex(right(123,1))
33
create table t1 as select right(123,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(rpad(1,2,0));
hex(rpad(1,2,0))
3130
create table t1 as select rpad(1,2,0) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(rtrim(1));
hex(rtrim(1))
31
create table t1 as select rtrim(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(soundex(1));
hex(soundex(1))

create table t1 as select soundex(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(substring(1,1,1));
hex(substring(1,1,1))
31
create table t1 as select substring(1,1,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(trim(1));
hex(trim(1))
31
create table t1 as select trim(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(ucase(1));
hex(ucase(1))
31
create table t1 as select ucase(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
select hex(upper(1));
hex(upper(1))
31
create table t1 as select upper(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select repeat(' ', 64) as a limit 0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varbinary(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into t1 values ("1.1"), ("2.1");
select a, hex(a) from t1;
a	hex(a)
1.1	312E31
2.1	322E31
update t1 set a= a + 0.1;
select a, hex(a) from t1;
a	hex(a)
1.2000000000000002	312E32303030303030303030303030303032
2.2	322E32
drop table t1;
create table t1 (a tinyint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a tinyint zerofill);
insert into t1 values (1), (10), (100);
select hex(concat(a)), a from t1;
hex(concat(a))	a
303031	001
303130	010
313030	100
drop table t1;
create table t1 (a tinyint(4) zerofill);
insert into t1 values (1), (10), (100);
select hex(concat(a)), a from t1;
hex(concat(a))	a
30303031	0001
30303130	0010
30313030	0100
drop table t1;
create table t1 (a decimal(10,2));
insert into t1 values (123.45);
select hex(concat(a)) from t1;
hex(concat(a))
3132332E3435
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a smallint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a smallint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
3030303031	00001
3030303130	00010
3030313030	00100
3031303030	01000
3130303030	10000
drop table t1;
create table t1 (a mediumint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a mediumint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
3030303030303031	00000001
3030303030303130	00000010
3030303030313030	00000100
3030303031303030	00001000
3030303130303030	00010000
drop table t1;
create table t1 (a int);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a int zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
30303030303030303031	0000000001
30303030303030303130	0000000010
30303030303030313030	0000000100
30303030303031303030	0000001000
30303030303130303030	0000010000
drop table t1;
create table t1 (a bigint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a bigint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
3030303030303030303030303030303030303031	00000000000000000001
3030303030303030303030303030303030303130	00000000000000000010
3030303030303030303030303030303030313030	00000000000000000100
3030303030303030303030303030303031303030	00000000000000001000
3030303030303030303030303030303130303030	00000000000000010000
drop table t1;
create table t1 (a float);
insert into t1 values (123.456);
select hex(concat(a)) from t1;
hex(concat(a))
3132332E343536
select concat(a) from t1;
concat(a)
123.456
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a float zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
select hex(concat(a)), a from t1;
hex(concat(a))	a
303030303030303030312E31	0000000001.1
303030303030303031302E31	0000000010.1
303030303030303130302E31	0000000100.1
303030303030313030302E31	0000001000.1
303030303031303030302E31	0000010000.1
drop table t1;
create table t1 (a double);
insert into t1 values (123.456);
select hex(concat(a)) from t1;
hex(concat(a))
3132332E343536
select concat(a) from t1;
concat(a)
123.456
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(22) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a double zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
select hex(concat(a)), a from t1;
hex(concat(a))	a
30303030303030303030303030303030303030312E31	00000000000000000001.1
30303030303030303030303030303030303031302E31	00000000000000000010.1
30303030303030303030303030303030303130302E31	00000000000000000100.1
30303030303030303030303030303030313030302E31	00000000000000001000.1
30303030303030303030303030303031303030302E31	00000000000000010000.1
drop table t1;
create table t1 (a year(4));
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
32303031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a year);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
32303031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a bit(64));
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0000000000000001
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert ignore into t1 values (0);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
insert into t1 values (20010203040506);
insert into t1 values (19800203040506);
insert into t1 values ('2001-02-03 04:05:06');
select hex(concat(a)) from t1;
hex(concat(a))
303030302D30302D30302030303A30303A3030
323030312D30322D30332030343A30353A3036
313938302D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
select concat(a) from t1;
concat(a)
0000-00-00 00:00:00
2001-02-03 04:05:06
1980-02-03 04:05:06
2001-02-03 04:05:06
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a date);
insert into t1 values ('2001-02-03');
insert into t1 values (20010203);
select hex(concat(a)) from t1;
hex(concat(a))
323030312D30322D3033
323030312D30322D3033
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a time);
insert into t1 values (1);
insert into t1 values ('01:02:03');
select hex(concat(a)) from t1;
hex(concat(a))
30303A30303A3031
30313A30323A3033
select concat(a) from t1;
concat(a)
00:00:01
01:02:03
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a datetime);
insert into t1 values ('2001-02-03 04:05:06');
insert into t1 values (20010203040506);
select hex(concat(a)) from t1;
hex(concat(a))
323030312D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(19) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a tinyint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(4)	YES		NULL	
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a tinyint zerofill);
insert into t1 values (1), (10), (100);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(3)	YES		NULL	
select hex(a) from v1;
hex(a)
303031
303130
313030
drop table t1;
drop view v1;
create table t1 (a tinyint(30) zerofill);
insert into t1 values (1), (10), (100);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(30)	YES		NULL	
select hex(a) from v1;
hex(a)
303030303030303030303030303030303030303030303030303030303031
303030303030303030303030303030303030303030303030303030303130
303030303030303030303030303030303030303030303030303030313030
drop table t1;
drop view v1;
create table t1 (a decimal(10,2));
insert into t1 values (123.45);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(12)	YES		NULL	
select hex(a) from v1;
hex(a)
3132332E3435
drop table t1;
drop view v1;
create table t1 (a smallint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(6)	YES		NULL	
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a smallint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(5)	YES		NULL	
select hex(a) from v1;
hex(a)
3030303031
3030303130
3030313030
3031303030
3130303030
drop table t1;
drop view v1;
create table t1 (a mediumint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(9)	YES		NULL	
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a mediumint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(8)	YES		NULL	
select hex(a) from v1;
hex(a)
3030303030303031
3030303030303130
3030303030313030
3030303031303030
3030303130303030
drop table t1;
drop view v1;
create table t1 (a int);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(11)	YES		NULL	
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a int zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(10)	YES		NULL	
select hex(a) from v1;
hex(a)
30303030303030303031
30303030303030303130
30303030303030313030
30303030303031303030
30303030303130303030
drop table t1;
drop view v1;
create table t1 (a bigint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(20)	YES		NULL	
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a bigint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(20)	YES		NULL	
select hex(a) from v1;
hex(a)
3030303030303030303030303030303030303031
3030303030303030303030303030303030303130
3030303030303030303030303030303030313030
3030303030303030303030303030303031303030
3030303030303030303030303030303130303030
drop table t1;
drop view v1;
create table t1 (a float);
insert into t1 values (123.456);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(12)	YES		NULL	
select hex(a) from v1;
hex(a)
3132332E343536
drop table t1;
drop view v1;
create table t1 (a float zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(12)	YES		NULL	
select hex(a) from v1;
hex(a)
303030303030303030312E31
303030303030303031302E31
303030303030303130302E31
303030303030313030302E31
303030303031303030302E31
drop table t1;
drop view v1;
create table t1 (a double);
insert into t1 values (123.456);
select concat(a) from t1;
concat(a)
123.456
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(22)	YES		NULL	
select hex(a) from v1;
hex(a)
3132332E343536
drop table t1;
drop view v1;
create table t1 (a double zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(22)	YES		NULL	
select hex(a) from v1;
hex(a)
30303030303030303030303030303030303030312E31
30303030303030303030303030303030303031302E31
30303030303030303030303030303030303130302E31
30303030303030303030303030303030313030302E31
30303030303030303030303030303031303030302E31
drop table t1;
drop view v1;
create table t1 (a year(4));
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(4)	YES		NULL	
select hex(a) from v1;
hex(a)
32303031
drop table t1;
drop view v1;
create table t1 (a year);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(4)	YES		NULL	
select hex(a) from v1;
hex(a)
32303031
drop table t1;
drop view v1;
create table t1 (a bit(64));
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(64)	YES		NULL	
select hex(a) from v1;
hex(a)
0000000000000001
drop table t1;
drop view v1;
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert ignore into t1 values (0);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
insert into t1 values (20010203040506);
insert into t1 values (19800203040506);
insert into t1 values ('2001-02-03 04:05:06');
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(19)	YES		NULL	
select hex(a) from v1;
hex(a)
303030302D30302D30302030303A30303A3030
323030312D30322D30332030343A30353A3036
313938302D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
drop table t1;
drop view v1;
create table t1 (a date);
insert into t1 values ('2001-02-03');
insert into t1 values (20010203);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(10)	YES		NULL	
select hex(a) from v1;
hex(a)
323030312D30322D3033
323030312D30322D3033
drop table t1;
drop view v1;
create table t1 (a time);
insert into t1 values (1);
insert into t1 values ('01:02:03');
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(10)	YES		NULL	
select hex(a) from v1;
hex(a)
30303A30303A3031
30313A30323A3033
drop table t1;
drop view v1;
create table t1 (a datetime);
insert into t1 values ('2001-02-03 04:05:06');
insert into t1 values (20010203040506);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(19)	YES		NULL	
select hex(a) from v1;
hex(a)
323030312D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
drop table t1;
drop view v1;
create function f1 (par1 int) returns int
begin
return concat(par1);
end|
set @a= f1(1);
select hex(@a);
hex(@a)
1
select hex(concat(f1(1)));
hex(concat(f1(1)))
31
create table t1 as select f1(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
create view v1 as select concat(f1(1)) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(11)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 decimal(18,2)) returns decimal(18,2)
begin
return concat(par1);
end|
set @a= f1(123.45);
select hex(@a);
hex(@a)
7B
select hex(concat(f1(123.45)));
hex(concat(f1(123.45)))
3132332E3435
create table t1 as select f1(123.45) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` decimal(18,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(123.45)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
create view v1 as select concat(f1(123.45)) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(20)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 float) returns float
begin
return concat(par1);
end|
set @a= f1(123.45);
select hex(@a);
hex(@a)
7B
select hex(concat(f1(123.45)));
hex(concat(f1(123.45)))
3132332E3435
create table t1 as select f1(123.45) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(123.45)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
create view v1 as select concat(f1(123.45)) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(12)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 date) returns date
begin
return concat(par1);
end|
set @a= f1(cast('2001-01-02' as date));
select hex(@a);
hex(@a)
323030312D30312D3032
select hex(concat(f1(cast('2001-01-02' as date))));
hex(concat(f1(cast('2001-01-02' as date))))
323030312D30312D3032
create table t1 as select f1(cast('2001-01-02' as date)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(10)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
#
# End of WL#2649 Number-to-string conversions
#
#
# Bug#54668 User variable assignments get wrong type
#
SET @ST_X=md5('a');
SELECT charset(@ST_X), collation(@ST_X);
charset(@ST_X)	collation(@ST_X)
binary	binary
SET @ST_X=password('a');
Warnings:
Warning	1681	'PASSWORD' is deprecated and will be removed in a future release.
SELECT charset(@ST_X), collation(@ST_X);
charset(@ST_X)	collation(@ST_X)
binary	binary
SET @ST_X=sha('a');
SELECT charset(@ST_X), collation(@ST_X);
charset(@ST_X)	collation(@ST_X)
binary	binary
SET @ST_X=sha1('a');
SELECT charset(@ST_X), collation(@ST_X);
charset(@ST_X)	collation(@ST_X)
binary	binary
SET @ST_X=ST_astext(point(1,2));
SELECT charset(@ST_X), collation(@ST_X);
charset(@ST_X)	collation(@ST_X)
binary	binary
SET @ST_X=ST_AsWKT(point(1,2));
SELECT charset(@ST_X), collation(@ST_X);
charset(@ST_X)	collation(@ST_X)
binary	binary
#
# Bug#54916 GROUP_CONCAT + IFNULL truncates output
#
SELECT @@collation_connection;
@@collation_connection
binary
CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM;
INSERT INTO t1 VALUES (1234567);
SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1;
GROUP_CONCAT(IFNULL(a,''))
1234567
SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1;
GROUP_CONCAT(IF(a,a,''))
1234567
SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;
GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END)
1234567
SELECT COALESCE(a,'') FROM t1 GROUP BY 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					COALESCE(a,'')	253	9	7	Y	128	31	63
COALESCE(a,'')
1234567
# All columns must be VARCHAR(9) with the same length:
CREATE TABLE t2 AS
SELECT
CONCAT(a),
IFNULL(a,''),
IF(a,a,''),
CASE WHEN a THEN a ELSE '' END,
COALESCE(a,'')
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `CONCAT(a)` varbinary(9) DEFAULT NULL,
  `IFNULL(a,'')` varbinary(9) NOT NULL DEFAULT '',
  `IF(a,a,'')` varbinary(9) DEFAULT NULL,
  `CASE WHEN a THEN a ELSE '' END` varbinary(9) DEFAULT NULL,
  `COALESCE(a,'')` varbinary(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `CONCAT_WS(1,2,3)` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `INSERT(1133,3,0,22)` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LCASE(a) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `LCASE(a)` varbinary(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT UCASE(a) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `UCASE(a)` varbinary(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `REPEAT(1,2)` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `LEFT(123,2)` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `RIGHT(123,2)` varbinary(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `LTRIM(123)` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `RTRIM(123)` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ELT(1,111,222,333)` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `REPLACE(111,2,3)` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `SUBSTRING_INDEX(111,111,1)` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `MAKE_SET(111,222,3)` varbinary(5) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `SOUNDEX(1)` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT EXPORT_SET(1,'ST_Y','N','',8);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `EXPORT_SET(1,'ST_Y','N','',8)` varbinary(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
#
# End of Bug#54916
#
#
# WL#5510 Functions to_base64 and from_base64
#
CREATE TABLE t1 AS SELECT TO_BASE64('test') AS to_base64;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `to_base64` varbinary(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64), HEX(to_base64) FROM t1;
to_base64	LENGTH(to_base64)	HEX(to_base64)
dGVzdA==	8	6447567A64413D3D
CREATE TABLE t2 AS SELECT FROM_BASE64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `from_base64` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SELECT CAST(from_base64 AS CHAR), LENGTH(from_base64), HEX(from_base64) FROM t2;
CAST(from_base64 AS CHAR)	LENGTH(from_base64)	HEX(from_base64)
test	4	74657374
DROP TABLE t2;
DROP TABLE t1;
#
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
#
SELECT @@collation_connection;
@@collation_connection
binary
CREATE TABLE t1 (
id INT(11) DEFAULT NULL,
date_column DATE DEFAULT NULL,
KEY(date_column));
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	date_column	date_column	4	NULL	2	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (`test`.`t1`.`date_column` between '2010-09-01' and '2010-10-01')
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	date_column	date_column	6	NULL	2	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (`test`.`t1`.`date_column` between '2010-09-01' and '2010-10-01')
DROP TABLE t1;
#
# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
#
SELECT @@collation_connection, @@character_set_results;
@@collation_connection	@@character_set_results
binary	binary
CREATE TABLE t1 AS
SELECT
DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `field_str1` varbinary(29) DEFAULT NULL,
  `field1_str2` varbinary(29) DEFAULT NULL,
  `field_date` date DEFAULT NULL,
  `field_datetime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t1;
SELECT
DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					field_str1	254	29	10	Y	128	31	63
def					field1_str2	254	29	19	Y	128	31	63
def					field_date	10	10	10	Y	128	0	63
def					field_datetime	12	19	19	Y	128	0	63
field_str1	field1_str2	field_date	field_datetime
2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
SELECT
HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2,
HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
field_str1	field1_str2	field_date	field_datetime
323030372D30382D30322032333A35393A3030	323030372D30382D30332031373A33323A3030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
#
# Bug#11926811 / Bug#60625 Illegal mix of collations
#
SELECT @@collation_connection;
@@collation_connection
binary
CREATE PROCEDURE p1()
BEGIN
DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
SELECT v_LastPaymentDate < NOW();
EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
SHOW WARNINGS;
EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
END//
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
CALL p1;
v_LastPaymentDate < NOW()
NULL
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
Level	Code	Message
Note	1003	/* select#1 */ select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
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:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select concat(v_LastPaymentDate@0,now()) AS `CONCAT(v_LastPaymentDate, NOW())`
DROP PROCEDURE p1;
#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
CREATE TABLE t1 (b INT);
INSERT INTO t1 VALUES (0);
SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a;
f1()
00:00:01
DROP FUNCTION f1;
DROP TABLE t1;
SET NAMES latin1;
SET sql_mode='';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a));
INSERT INTO t1 VALUES ();
SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1;
maketime(`a`,`a`,`a`)
00:00:00.000000
DROP TABLE t1;
SET sql_mode=default;
#
# End of 5.5 tests
#
#
# Start of 5.6 tests
#
SET NAMES binary;
SELECT is_ipv4(inet_ntoa('1'));
is_ipv4(inet_ntoa('1'))
1
SELECT is_ipv6(inet_ntoa('1'));
is_ipv6(inet_ntoa('1'))
0
SELECT inet6_aton(inet_ntoa('1'));
inet6_aton(inet_ntoa('1'))


SELECT inet6_ntoa(inet_ntoa('1'));
inet6_ntoa(inet_ntoa('1'))
NULL
Warnings:
Warning	1411	Incorrect string value: 'inet_ntoa('1')' for function inet6_ntoa
#
# Bug#14040277 UNINITIALIZED VALUE REFERENCED IN STR_TO_IPV6
#
SELECT inet6_aton(soundex('a'));
inet6_aton(soundex('a'))
NULL
Warnings:
Warning	1411	Incorrect string value: 'soundex('a')' for function inet6_aton
#
# Bug#19047425 UNINITIALISED VALUE IN STR_TO_IPV6
#
do is_ipv4_mapped(inet6_aton(convert(_ascii "a:" using binary)));
Warnings:
Warning	1411	Incorrect string value: 'convert(_ascii'a:' using binary)' for function inet6_aton
#
# End of 5.6 tests
#