SET @@session.storage_engine = 'MyISAM';
#
# NUMERIC FUNCTIONS
#
# ABS()
set sql_warnings = 1;
create table t1 (a int, b int as (abs(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (abs(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-1, default);
select * from t1;
a	b
-1	1
drop table t1;
set sql_warnings = 0;
# ACOS()
set sql_warnings = 1;
create table t1 (a double, b double as (format(acos(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(acos(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1, default);
insert into t1 values (1.0001,default);
insert into t1 values (0,default);
select * from t1;
a	b
1	0
1.0001	NULL
0	1.570796
drop table t1;
set sql_warnings = 0;
# ASIN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(asin(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(asin(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (0.2, default);
insert into t1 values (1.0001,default);
select * from t1;
a	b
0.2	0.201358
1.0001	NULL
drop table t1;
set sql_warnings = 0;
#ATAN
set sql_warnings = 1;
create table t1 (a double, b double, c double as (format(atan(a,b),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` double AS (format(atan(a,b),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-2,2,default);
insert into t1 values (format(PI(),6),0,default);
select * from t1;
a	b	c
-2	2	-0.785398
3.141593	0	1.570796
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a double, c double as (format(atan(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `c` double AS (format(atan(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-2,default);
insert into t1 values (format(PI(),6),default);
select * from t1;
a	c
-2	-1.107149
3.141593	1.262627
drop table t1;
set sql_warnings = 0;
# ATAN2
set sql_warnings = 1;
create table t1 (a double, b double, c double as (format(atan2(a,b),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` double AS (format(atan2(a,b),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-2,2,default);
insert into t1 values (format(PI(),6),0,default);
select * from t1;
a	b	c
-2	2	-0.785398
3.141593	0	1.570796
drop table t1;
set sql_warnings = 0;
# CEIL()
set sql_warnings = 1;
create table t1 (a double, b int as (ceil(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` int(11) AS (ceil(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1.23,default);
insert into t1 values (-1.23,default);
select * from t1;
a	b
1.23	2
-1.23	-1
drop table t1;
set sql_warnings = 0;
# CONV()
set sql_warnings = 1;
create table t1 (a varchar(10), b int, c int, d varchar(10) as (conv(a,b,c)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(10) AS (conv(a,b,c)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('a',16,2,default);
insert into t1 values ('6e',18,8,default);
insert into t1 values (-17,10,-18,default);
insert into t1 values (10+'10'+'10'+0xa,10,10,default);
select * from t1;
a	b	c	d
a	16	2	1010
6e	18	8	172
-17	10	-18	-H
40	10	10	40
drop table t1;
set sql_warnings = 0;
# COS()
set sql_warnings = 1;
create table t1 (a double, b double as (format(cos(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(cos(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (format(PI(),6),default);
select * from t1;
a	b
3.141593	-1
drop table t1;
set sql_warnings = 0;
# COT()
set sql_warnings = 1;
create table t1 (a double, b double as (format(cot(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(cot(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (12,default);
insert into t1 values (1,default);
select * from t1;
a	b
12	-1.572673
1	0.642093
drop table t1;
set sql_warnings = 0;
# CRC32()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (crc32(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext AS (crc32(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
insert into t1 values ('mysql',default);
select * from t1;
a	b
MySQL	3259397556
mysql	2501908538
drop table t1;
set sql_warnings = 0;
# DEGREES()
set sql_warnings = 1;
create table t1 (a double, b double as (format(degrees(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(degrees(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (format(PI(),6),default);
insert into t1 values (format(PI()/2,6),default);
select * from t1;
a	b
3.141593	180.00002
1.570796	89.999981
drop table t1;
set sql_warnings = 0;
# /
set sql_warnings = 1;
create table t1 (a double, b double as (a/2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (a/2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,default);
select * from t1;
a	b
2	1
drop table t1;
set sql_warnings = 0;
# EXP()
set sql_warnings = 1;
create table t1 (a double, b double as (format(exp(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(exp(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,default);
insert into t1 values (-2,default);
insert into t1 values (0,default);
select * from t1;
a	b
2	7.389056
-2	0.135335
0	1
drop table t1;
set sql_warnings = 0;
# FLOOR()
set sql_warnings = 1;
create table t1 (a double, b long as (floor(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` mediumtext AS (floor(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1.23,default);
insert into t1 values (-1.23,default);
select * from t1;
a	b
1.23	1
-1.23	-2
drop table t1;
set sql_warnings = 0;
# LN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(ln(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(ln(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,default);
insert into t1 values (-2,default);
select * from t1;
a	b
2	0.693147
-2	NULL
drop table t1;
set sql_warnings = 0;
# LOG()
set sql_warnings = 1;
create table t1 (a double, b double, c double as (format(log(a,b),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` double AS (format(log(a,b),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,65536,default);
insert into t1 values (10,100,default);
insert into t1 values (1,100,default);
select * from t1;
a	b	c
2	65536	16
10	100	2
1	100	NULL
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a double, b double as (format(log(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(log(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,default);
insert into t1 values (-2,default);
select * from t1;
a	b
2	0.693147
-2	NULL
drop table t1;
set sql_warnings = 0;
# LOG2()
set sql_warnings = 1;
create table t1 (a double, b double as (format(log2(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(log2(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (65536,default);
insert into t1 values (-100,default);
select * from t1;
a	b
65536	16
-100	NULL
drop table t1;
set sql_warnings = 0;
# LOG10()
set sql_warnings = 1;
create table t1 (a double, b double as (format(log10(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(log10(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,default);
insert into t1 values (100,default);
insert into t1 values (-100,default);
select * from t1;
a	b
2	0.30103
100	2
-100	NULL
drop table t1;
set sql_warnings = 0;
# -
set sql_warnings = 1;
create table t1 (a double, b double as (a-1));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (a-1) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2,default);
select * from t1;
a	b
2	1
drop table t1;
set sql_warnings = 0;
# MOD()
set sql_warnings = 1;
create table t1 (a int, b int as (mod(a,10)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (mod(a,10)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (11,default);
select * from t1;
a	b
1	1
11	1
drop table t1;
set sql_warnings = 0;
# %
set sql_warnings = 1;
create table t1 (a int, b int as (a % 10));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a % 10) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (11,default);
select * from t1;
a	b
1	1
11	1
drop table t1;
set sql_warnings = 0;
# OCT()
set sql_warnings = 1;
create table t1 (a double, b varchar(10) as (oct(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` varchar(10) AS (oct(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (12,default);
select * from t1;
a	b
12	14
drop table t1;
set sql_warnings = 0;
# PI()
set sql_warnings = 1;
create table t1 (a double, b double as (format(PI()*a*a,6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(PI()*a*a,6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
select * from t1;
a	b
1	3.141593
drop table t1;
set sql_warnings = 0;
# +
set sql_warnings = 1;
create table t1 (a int, b int as (a+1));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a+1) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
select * from t1;
a	b
1	2
drop table t1;
set sql_warnings = 0;
# POW, POWER
set sql_warnings = 1;
create table t1 (a int, b int as (pow(a,2)), c int as (power(a,2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (pow(a,2)) VIRTUAL,
  `c` int(11) AS (power(a,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default,default);
insert into t1 values (2,default,default);
select * from t1;
a	b	c
1	1	1
2	4	4
drop table t1;
set sql_warnings = 0;
# RADIANS()
set sql_warnings = 1;
create table t1 (a double, b double as (format(radians(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(radians(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (90,default);
select * from t1;
a	b
90	1.570796
drop table t1;
set sql_warnings = 0;
# ROUND()
set sql_warnings = 1;
create table t1 (a double, b int as (round(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` int(11) AS (round(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-1.23,default);
insert into t1 values (-1.58,default);
insert into t1 values (1.58,default);
select * from t1;
a	b
-1.23	-1
-1.58	-2
1.58	2
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a double, b double, c int as (round(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` int(11) AS (round(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1.298,1,default);
insert into t1 values (1.298,0,default);
insert into t1 values (23.298,-1,default);
select * from t1;
a	b	c
1.298	1	1
1.298	0	1
23.298	-1	20
drop table t1;
set sql_warnings = 0;
# SIGN()
set sql_warnings = 1;
create table t1 (a double, b int as (sign(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` int(11) AS (sign(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-32,default);
insert into t1 values (0,default);
insert into t1 values (234,default);
select * from t1;
a	b
-32	-1
0	0
234	1
drop table t1;
set sql_warnings = 0;
# SIN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(sin(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(sin(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (format(PI()/2,6),default);
select * from t1;
a	b
1.570796	1
drop table t1;
set sql_warnings = 0;
# SQRT()
set sql_warnings = 1;
create table t1 (a double, b double as (format(sqrt(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(sqrt(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (4,default);
insert into t1 values (20,default);
insert into t1 values (-16,default);
select * from t1;
a	b
4	2
20	4.472136
-16	NULL
drop table t1;
set sql_warnings = 0;
# TAN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(tan(a),6)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (format(tan(a),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (format(PI(),6),default);
insert into t1 values (format(PI()+1,6),default);
select * from t1;
a	b
3.141593	0
4.141593	1.557409
drop table t1;
set sql_warnings = 0;
# *
set sql_warnings = 1;
create table t1 (a double, b double as (a*3));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (a*3) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (0,default);
insert into t1 values (1,default);
insert into t1 values (2,default);
select * from t1;
a	b
0	0
1	3
2	6
drop table t1;
set sql_warnings = 0;
# TRUNCATE()
set sql_warnings = 1;
create table t1 (a double, b double as (truncate(a,4)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (truncate(a,4)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1.223,default);
insert into t1 values (1.999,default);
insert into t1 values (1.999,default);
insert into t1 values (122,default);
select * from t1;
a	b
1.223	1.223
1.999	1.999
1.999	1.999
122	122
drop table t1;
set sql_warnings = 0;
# Unary -
set sql_warnings = 1;
create table t1 (a double, b double as (-a));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double AS (-a) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (-1,default);
select * from t1;
a	b
1	-1
-1	1
drop table t1;
set sql_warnings = 0;
#
# STRING FUNCTIONS
#
# ASCII()
set sql_warnings = 1;
create table t1 (a char(2), b int as (ascii(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(2) DEFAULT NULL,
  `b` int(11) AS (ascii(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2',default);
insert into t1 values (2,default);
insert into t1 values ('dx',default);
select * from t1;
a	b
2	50
2	50
dx	100
drop table t1;
set sql_warnings = 0;
# BIN()
set sql_warnings = 1;
create table t1 (a int, b varchar(10) as (bin(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) AS (bin(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (12,default);
select * from t1;
a	b
12	1100
drop table t1;
set sql_warnings = 0;
# BIT_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (bit_length(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext AS (bit_length(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
a	b
text	32
drop table t1;
set sql_warnings = 0;
# CHAR_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (char_length(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext AS (char_length(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
a	b
text	4
drop table t1;
set sql_warnings = 0;
# CHAR()
set sql_warnings = 1;
create table t1 (a int, b int, c varbinary(10) as (char(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varbinary(10) AS (char(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (77,121,default);
select * from t1;
a	b	c
77	121	My
drop table t1;
set sql_warnings = 0;
# CHARACTER_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (character_length(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext AS (character_length(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
a	b
text	4
drop table t1;
set sql_warnings = 0;
# CONCAT_WS()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat_ws(',',a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(20) AS (concat_ws(',',a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('value1','value2',default);
select * from t1;
a	b	c
value1	value2	value1,value2
drop table t1;
set sql_warnings = 0;
# CONCAT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat(a,',',b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(20) AS (concat(a,',',b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('value1','value2',default);
select * from t1;
a	b	c
value1	value2	value1,value2
drop table t1;
set sql_warnings = 0;
# ELT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int, d varchar(10) as (elt(c,a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(10) AS (elt(c,a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('value1','value2',1,default);
insert into t1 values ('value1','value2',2,default);
select * from t1;
a	b	c	d
value1	value2	1	value1
value1	value2	2	value2
drop table t1;
set sql_warnings = 0;
# EXPORT_SET()
set sql_warnings = 1;
create table t1 (a int, b varchar(10) as (export_set(a,'1','0','',10)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) AS (export_set(a,'1','0','',10)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (6,default);
select * from t1;
a	b
6	0110000000
drop table t1;
set sql_warnings = 0;
# FIELD()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int as (field('aa',a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) AS (field('aa',a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('aa','bb',default);
insert into t1 values ('bb','aa',default);
select * from t1;
a	b	c
aa	bb	1
bb	aa	2
drop table t1;
set sql_warnings = 0;
# FIND_IN_SET()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int as (find_in_set(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) AS (find_in_set(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('aa','aa,bb,cc',default);
insert into t1 values ('aa','bb,aa,cc',default);
select * from t1;
a	b	c
aa	aa,bb,cc	1
aa	bb,aa,cc	2
drop table t1;
set sql_warnings = 0;
# FORMAT()
set sql_warnings = 1;
create table t1 (a double, b varchar(20) as (format(a,2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` varchar(20) AS (format(a,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (12332.123456,default);
select * from t1;
a	b
12332.123456	12,332.12
drop table t1;
set sql_warnings = 0;
# HEX()
set sql_warnings = 1;
create table t1 (a int, b varchar(10) as (hex(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) AS (hex(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (17,default);
select * from t1;
a	b
17	11
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (hex(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (hex(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('abc',default);
select * from t1;
a	b
abc	616263
drop table t1;
set sql_warnings = 0;
# INSERT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(20) as (insert(a,length(a),length(b),b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(20) AS (insert(a,length(a),length(b),b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('start,','end',default);
select * from t1;
a	b	c
start,	end	startend
drop table t1;
set sql_warnings = 0;
# INSTR()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int as (instr(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) AS (instr(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('foobarbar,','bar',default);
insert into t1 values ('xbar,','foobar',default);
select * from t1;
a	b	c
foobarbar,	bar	4
xbar,	foobar	0
drop table t1;
set sql_warnings = 0;
# LCASE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (lcase(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (lcase(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	mysql
drop table t1;
set sql_warnings = 0;
# LEFT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(5) as (left(a,5)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(5) AS (left(a,5)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('foobarbar',default);
select * from t1;
a	b
foobarbar	fooba
drop table t1;
set sql_warnings = 0;
# LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b int as (length(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` int(11) AS (length(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
a	b
text	4
drop table t1;
set sql_warnings = 0;
# LIKE
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a like 'H%!o' escape '!'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) AS (a like 'H%!o' escape '!') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
insert into t1 values ('MySQL',default);
select * from t1;
a	b
Hello	1
MySQL	0
drop table t1;
set sql_warnings = 0;
# LOCATE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (locate('bar',a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (locate('bar',a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('foobarbar',default);
select * from t1;
a	b
foobarbar	4
drop table t1;
set sql_warnings = 0;
# LOWER()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (lower(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (lower(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	mysql
drop table t1;
set sql_warnings = 0;
# LPAD()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (lpad(a,4,' ')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (lpad(a,4,' ')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
insert into t1 values ('M',default);
select * from t1;
a	b
MySQL	MySQ
M	   M
drop table t1;
set sql_warnings = 0;
# LTRIM()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (ltrim(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (ltrim(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('  MySQL',default);
insert into t1 values ('MySQL',default);
select * from t1;
a	b
  MySQL	MySQL
MySQL	MySQL
drop table t1;
set sql_warnings = 0;
# MAKE_SET()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int, d varchar(30) as (make_set(c,a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(30) AS (make_set(c,a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('a','b',1,default);
insert into t1 values ('a','b',3,default);
select * from t1;
a	b	c	d
a	b	1	a
a	b	3	a,b
drop table t1;
set sql_warnings = 0;
# MID()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (mid(a,1,2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (mid(a,1,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('foobarbar',default);
select * from t1;
a	b
foobarbar	fo
drop table t1;
set sql_warnings = 0;
# NOT LIKE
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a not like 'H%o'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) AS (a not like 'H%o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
insert into t1 values ('MySQL',default);
select * from t1;
a	b
Hello	0
MySQL	1
drop table t1;
set sql_warnings = 0;
# NOT REGEXP
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a not regexp 'H.+o'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) AS (a not regexp 'H.+o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
insert into t1 values ('hello',default);
select * from t1;
a	b
Hello	0
hello	0
drop table t1;
set sql_warnings = 0;
# OCTET_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b int as (octet_length(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` int(11) AS (octet_length(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
a	b
text	4
drop table t1;
set sql_warnings = 0;
# ORD()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (ord(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext AS (ord(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2',default);
select * from t1;
a	b
2	50
drop table t1;
set sql_warnings = 0;
# POSITION()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (position('bar' in a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (position('bar' in a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('foobarbar',default);
select * from t1;
a	b
foobarbar	4
drop table t1;
set sql_warnings = 0;
# QUOTE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (quote(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (quote(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Don\'t',default);
select * from t1;
a	b
Don't	'Don\'t'
drop table t1;
set sql_warnings = 0;
# REGEXP()
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a regexp 'H.+o'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) AS (a regexp 'H.+o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
insert into t1 values ('hello',default);
select * from t1;
a	b
Hello	1
hello	1
drop table t1;
set sql_warnings = 0;
# REPEAT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(30) as (repeat(a,3)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(30) AS (repeat(a,3)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	MySQLMySQLMySQL
drop table t1;
set sql_warnings = 0;
# REPLACE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(30) as (replace(a,'aa','bb')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(30) AS (replace(a,'aa','bb')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('maa',default);
select * from t1;
a	b
maa	mbb
drop table t1;
set sql_warnings = 0;
# REVERSE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(30) as (reverse(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(30) AS (reverse(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('maa',default);
select * from t1;
a	b
maa	aam
drop table t1;
set sql_warnings = 0;
# RIGHT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (right(a,4)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (right(a,4)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('foobarbar',default);
select * from t1;
a	b
foobarbar	rbar
drop table t1;
set sql_warnings = 0;
# RLIKE()
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a rlike 'H.+o'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) AS (a rlike 'H.+o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
insert into t1 values ('MySQL',default);
select * from t1;
a	b
Hello	1
MySQL	0
drop table t1;
set sql_warnings = 0;
# RPAD()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (rpad(a,4,'??')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (rpad(a,4,'??')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('He',default);
select * from t1;
a	b
He	He??
drop table t1;
set sql_warnings = 0;
# RTRIM();
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (rtrim(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (rtrim(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello  ',default);
select * from t1;
a	b
Hello  	Hello
drop table t1;
set sql_warnings = 0;
# SOUNDEX()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(20) as (soundex(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(20) AS (soundex(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
select * from t1;
a	b
Hello	H400
drop table t1;
set sql_warnings = 0;
# SOUNDS LIKE
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a sounds like b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a sounds like b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello','Hello',default);
insert into t1 values ('Hello','MySQL',default);
insert into t1 values ('Hello','hello',default);
select * from t1;
a	b	c
Hello	Hello	1
Hello	MySQL	0
Hello	hello	1
drop table t1;
set sql_warnings = 0;
# SPACE()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (concat(a,space(5))));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) AS (concat(a,space(5))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello', default);
select * from t1;
a	b
Hello	Hello     
drop table t1;
set sql_warnings = 0;
# STRCMP()
set sql_warnings = 1;
create table t1 (a varchar(9), b varchar(9), c tinyint(1) as (strcmp(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(9) DEFAULT NULL,
  `b` varchar(9) DEFAULT NULL,
  `c` tinyint(1) AS (strcmp(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello','Hello', default);
insert into t1 values ('Hello','Hello1', default);
select * from t1;
a	b	c
Hello	Hello	0
Hello	Hello1	-1
drop table t1;
set sql_warnings = 0;
# SUBSTR()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (substr(a,2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) AS (substr(a,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
select * from t1;
a	b
Hello	ello
drop table t1;
set sql_warnings = 0;
# SUBSTRING_INDEX()
set sql_warnings = 1;
create table t1 (a varchar(15), b varchar(10) as (substring_index(a,'.',2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(10) AS (substring_index(a,'.',2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('www.mysql.com',default);
select * from t1;
a	b
www.mysql.com	www.mysql
drop table t1;
set sql_warnings = 0;
# SUBSTRING()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (substring(a from 2 for 2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) AS (substring(a from 2 for 2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('Hello',default);
select * from t1;
a	b
Hello	el
drop table t1;
set sql_warnings = 0;
# TRIM()
set sql_warnings = 1;
create table t1 (a varchar(15), b varchar(10) as (trim(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(10) AS (trim(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (' aa ',default);
select * from t1;
a	b
 aa 	aa
drop table t1;
set sql_warnings = 0;
# UCASE()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (ucase(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) AS (ucase(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	MYSQL
drop table t1;
set sql_warnings = 0;
# UNHEX()
set sql_warnings = 1;
create table t1 (a varchar(15), b varchar(10) as (unhex(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(10) AS (unhex(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('4D7953514C',default);
select * from t1;
a	b
4D7953514C	MySQL
drop table t1;
set sql_warnings = 0;
# UPPER()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (upper(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) AS (upper(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	MYSQL
drop table t1;
set sql_warnings = 0;
#
# CONTROL FLOW FUNCTIONS
#
# CASE
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(16) as (case a when NULL then 'asd' when 'b' then 'B' else a end));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(16) AS (case a when NULL then 'asd' when 'b' then 'B' else a end) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL,default);
insert into t1 values ('b',default);
insert into t1 values ('c',default);
select * from t1;
a	b
NULL	NULL
b	B
c	c
drop table t1;
set sql_warnings = 0;
# IF
set sql_warnings = 1;
create table t1 (a int, b int, c int as (if(a=1,a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) AS (if(a=1,a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,2,default);
insert into t1 values (3,4,default);
select * from t1;
a	b	c
1	2	1
3	4	4
drop table t1;
set sql_warnings = 0;
# IFNULL
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(10) as (ifnull(a,'DEFAULT')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) AS (ifnull(a,'DEFAULT')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL,'adf',default);
insert into t1 values ('a','adf',default);
select * from t1;
a	b	c
NULL	adf	DEFAULT
a	adf	a
drop table t1;
set sql_warnings = 0;
# NULLIF
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (nullif(a,'DEFAULT')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) AS (nullif(a,'DEFAULT')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('DEFAULT',default);
insert into t1 values ('a',default);
select * from t1;
a	b
DEFAULT	NULL
a	a
drop table t1;
set sql_warnings = 0;
#
# OPERATORS
#
# AND, && 
set sql_warnings = 1;
create table t1 (a int, b bool as (a>0 && a<2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) AS (a>0 && a<2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-1,default);
insert into t1 values (1,default);
select * from t1;
a	b
-1	0
1	1
drop table t1;
set sql_warnings = 0;
# BETWEEN ... AND ... 
set sql_warnings = 1;
create table t1 (a int, b bool as (a between 0 and 2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) AS (a between 0 and 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-1,default);
insert into t1 values (1,default);
select * from t1;
a	b
-1	0
1	1
drop table t1;
set sql_warnings = 0;
# BINARY
set sql_warnings = 1;
create table t1 (a varchar(10), b varbinary(10) as (binary a));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varbinary(10) AS (binary a) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('11',default);
insert into t1 values (1,default);
select * from t1;
a	b
11	11
1	1
drop table t1;
set sql_warnings = 0;
# &
set sql_warnings = 1;
create table t1 (a int, b int as (a & 5));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a & 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (0,default);
select * from t1;
a	b
1	1
0	0
drop table t1;
set sql_warnings = 0;
# ~
set sql_warnings = 1;
create table t1 (a int, b int as (~a));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (~a) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
Warnings:
Warning	1264	Out of range value for column 'b' at row 1
select * from t1;
a	b
1	2147483647
drop table t1;
set sql_warnings = 0;
# |
set sql_warnings = 1;
create table t1 (a int, b int as (a | 5));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a | 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (0,default);
insert into t1 values (2,default);
select * from t1;
a	b
1	5
0	5
2	7
drop table t1;
set sql_warnings = 0;
# ^
set sql_warnings = 1;
create table t1 (a int, b int as (a ^ 5));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a ^ 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (0,default);
insert into t1 values (2,default);
select * from t1;
a	b
1	4
0	5
2	7
drop table t1;
set sql_warnings = 0;
# DIV
set sql_warnings = 1;
create table t1 (a int, b int as (a div 5));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a div 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (7,default);
select * from t1;
a	b
1	0
7	1
drop table t1;
set sql_warnings = 0;
# <=>
set sql_warnings = 1;
create table t1 (a int, b int, c bool as (a <=> b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` tinyint(1) AS (a <=> b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,1,default);
insert into t1 values (NULL,NULL,default);
insert into t1 values (1,NULL,default);
select * from t1;
a	b	c
1	1	1
NULL	NULL	1
1	NULL	0
drop table t1;
set sql_warnings = 0;
# =
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a=b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a=b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('a','b',default);
insert into t1 values ('a','a',default);
select * from t1;
a	b	c
a	b	0
a	a	1
drop table t1;
set sql_warnings = 0;
# >=
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a >= b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a >= b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('a','b',default);
insert into t1 values ('a','a',default);
select * from t1;
a	b	c
a	b	0
a	a	1
drop table t1;
set sql_warnings = 0;
# >
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a > b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a > b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('a','b',default);
insert into t1 values ('a','a',default);
select * from t1;
a	b	c
a	b	0
a	a	0
drop table t1;
set sql_warnings = 0;
# IS NOT NULL
set sql_warnings = 1;
create table t1 (a int, b bool as (a is not null));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) AS (a is not null) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (NULL,default);
select * from t1;
a	b
1	1
NULL	0
drop table t1;
set sql_warnings = 0;
# IS NULL
set sql_warnings = 1;
create table t1 (a int, b bool as (a is null));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) AS (a is null) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (NULL,default);
select * from t1;
a	b
1	0
NULL	1
drop table t1;
set sql_warnings = 0;
# <<
set sql_warnings = 1;
create table t1 (a int, b int as (a << 2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a << 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (3,default);
select * from t1;
a	b
1	4
3	12
drop table t1;
set sql_warnings = 0;
# <=
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a <= b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a <= b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('b','a',default);
insert into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a	b	c
b	a	0
b	b	1
b	c	1
drop table t1;
set sql_warnings = 0;
# <
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a < b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a < b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('b','a',default);
insert into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a	b	c
b	a	0
b	b	0
b	c	1
drop table t1;
set sql_warnings = 0;
# NOT BETWEEN ... AND ...
set sql_warnings = 1;
create table t1 (a int, b bool as (a not between 0 and 2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) AS (a not between 0 and 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (-1,default);
insert into t1 values (1,default);
select * from t1;
a	b
-1	1
1	0
drop table t1;
set sql_warnings = 0;
# <>
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a <> b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a <> b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('b','a',default);
insert into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a	b	c
b	a	1
b	b	0
b	c	1
drop table t1;
set sql_warnings = 0;
# !=
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a != b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) AS (a != b) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('b','a',default);
insert into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a	b	c
b	a	1
b	b	0
b	c	1
drop table t1;
set sql_warnings = 0;
# ||, OR
set sql_warnings = 1;
create table t1 (a int, b int as (a>5 || a<3));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a>5 || a<3) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (4,default);
select * from t1;
a	b
1	1
4	0
drop table t1;
set sql_warnings = 0;
# >>
set sql_warnings = 1;
create table t1 (a int, b int as (a >> 2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a >> 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (8,default);
insert into t1 values (3,default);
select * from t1;
a	b
8	2
3	0
drop table t1;
set sql_warnings = 0;
# XOR
set sql_warnings = 1;
create table t1 (a int, b int as (a xor 5));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a xor 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (0,default);
insert into t1 values (1,default);
insert into t1 values (2,default);
select * from t1;
a	b
0	1
1	0
2	0
drop table t1;
set sql_warnings = 0;
#
# DATE AND TIME FUNCTIONS
#
# ADDDATE()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (adddate(a,interval 1 month)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (adddate(a,interval 1 month)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-09-30 00:00:00
drop table t1;
set sql_warnings = 0;
# ADDTIME()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (addtime(a,'02:00:00')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (addtime(a,'02:00:00')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-08-31 02:00:00
drop table t1;
set sql_warnings = 0;
# CONVERT_TZ()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (convert_tz(a,'MET','UTC')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (convert_tz(a,'MET','UTC')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-08-30 22:00:00
drop table t1;
set sql_warnings = 0;
# DATE_ADD()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (date_add(a,interval 1 month)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (date_add(a,interval 1 month)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-09-30 00:00:00
drop table t1;
set sql_warnings = 0;
# DATE_FORMAT()
set sql_warnings = 1;
create table t1 (a datetime, b varchar(64) as (date_format(a,'%W %M %D')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(64) AS (date_format(a,'%W %M %D')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	Sunday August 31st
drop table t1;
set sql_warnings = 0;
# DATE_SUB()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (date_sub(a,interval 1 month)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (date_sub(a,interval 1 month)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-07-31 00:00:00
drop table t1;
set sql_warnings = 0;
# DATE()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (date(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (date(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31 02:00:00',default);
select * from t1;
a	b
2008-08-31 02:00:00	2008-08-31 00:00:00
drop table t1;
set sql_warnings = 0;
# DATEDIFF()
set sql_warnings = 1;
create table t1 (a datetime, b long as (datediff(a,'2000-01-01')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` mediumtext AS (datediff(a,'2000-01-01')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	3165
drop table t1;
set sql_warnings = 0;
# DAY()
set sql_warnings = 1;
create table t1 (a datetime, b int as (day(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (day(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	31
drop table t1;
set sql_warnings = 0;
# DAYNAME()
set sql_warnings = 1;
create table t1 (a datetime, b varchar(10) as (dayname(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(10) AS (dayname(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	Sunday
drop table t1;
set sql_warnings = 0;
# DAYOFMONTH()
set sql_warnings = 1;
create table t1 (a datetime, b int as (dayofmonth(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (dayofmonth(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	31
drop table t1;
set sql_warnings = 0;
# DAYOFWEEK()
set sql_warnings = 1;
create table t1 (a datetime, b int as (dayofweek(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (dayofweek(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	1
drop table t1;
set sql_warnings = 0;
# DAYOFYEAR()
set sql_warnings = 1;
create table t1 (a datetime, b int as (dayofyear(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (dayofyear(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	244
drop table t1;
set sql_warnings = 0;
# EXTRACT
set sql_warnings = 1;
create table t1 (a datetime, b int as (extract(year from a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (extract(year from a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008
drop table t1;
set sql_warnings = 0;
# FROM_DAYS()
set sql_warnings = 1;
create table t1 (a long, b datetime as (from_days(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` mediumtext,
  `b` datetime AS (from_days(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (730669,default);
select * from t1;
a	b
730669	2000-07-03 00:00:00
drop table t1;
set sql_warnings = 0;
# FROM_UNIXTIME()
set time_zone='UTC';
set sql_warnings = 1;
create table t1 (a long, b datetime as (from_unixtime(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` mediumtext,
  `b` datetime AS (from_unixtime(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1196440219,default);
select * from t1;
a	b
1196440219	2007-11-30 16:30:19
drop table t1;
set sql_warnings = 0;
# GET_FORMAT()
set sql_warnings = 1;
create table t1 (a datetime, b varchar(32) as (date_format(a,get_format(DATE,'EUR'))));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(32) AS (date_format(a,get_format(DATE,'EUR'))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	31.08.2008
drop table t1;
set sql_warnings = 0;
# HOUR()
set sql_warnings = 1;
create table t1 (a time, b long as (hour(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` time DEFAULT NULL,
  `b` mediumtext AS (hour(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('10:05:03',default);
select * from t1;
a	b
10:05:03	10
drop table t1;
set sql_warnings = 0;
# LAST_DAY()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (last_day(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (last_day(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2003-02-05',default);
insert into t1 values ('2003-02-32',default);
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
select * from t1;
a	b
2003-02-05 00:00:00	2003-02-28 00:00:00
0000-00-00 00:00:00	NULL
drop table t1;
set sql_warnings = 0;
# MAKEDATE()
set sql_warnings = 1;
create table t1 (a int, b datetime as (makedate(a,1)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` datetime AS (makedate(a,1)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2001,default);
select * from t1;
a	b
2001	2001-01-01 00:00:00
drop table t1;
set sql_warnings = 0;
# MAKETIME()
set sql_warnings = 1;
create table t1 (a int, b time as (maketime(a,1,3)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` time AS (maketime(a,1,3)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (12,default);
select * from t1;
a	b
12	12:01:03
drop table t1;
set sql_warnings = 0;
# MICROSECOND()
set sql_warnings = 1;
create table t1 (a datetime, b long as (microsecond(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` mediumtext AS (microsecond(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2009-12-31 12:00:00.123456',default);
insert into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a	b
2009-12-31 12:00:00	0
2009-12-31 23:59:59	0
drop table t1;
set sql_warnings = 0;
# MINUTE()
set sql_warnings = 1;
create table t1 (a datetime, b int as (minute(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (minute(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a	b
2009-12-31 23:59:59	59
drop table t1;
set sql_warnings = 0;
# MONTH()
set sql_warnings = 1;
create table t1 (a datetime, b int as (month(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (month(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a	b
2009-12-31 23:59:59	12
drop table t1;
set sql_warnings = 0;
# MONTHNAME()
set sql_warnings = 1;
create table t1 (a datetime, b varchar(16) as (monthname(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(16) AS (monthname(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a	b
2009-12-31 23:59:59	December
drop table t1;
set sql_warnings = 0;
# PERIOD_ADD()
set sql_warnings = 1;
create table t1 (a int, b int as (period_add(a,2)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (period_add(a,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (200801,default);
select * from t1;
a	b
200801	200803
drop table t1;
set sql_warnings = 0;
# PERIOD_DIFF()
set sql_warnings = 1;
create table t1 (a int, b int, c int as (period_diff(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) AS (period_diff(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (200802,200703,default);
select * from t1;
a	b	c
200802	200703	11
drop table t1;
set sql_warnings = 0;
# QUARTER()
set sql_warnings = 1;
create table t1 (a datetime, b int as (quarter(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (quarter(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	3
drop table t1;
set sql_warnings = 0;
# SEC_TO_TIME()
set sql_warnings = 1;
create table t1 (a long, b time as (sec_to_time(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` mediumtext,
  `b` time AS (sec_to_time(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (2378,default);
select * from t1;
a	b
2378	00:39:38
drop table t1;
set sql_warnings = 0;
# SECOND()
set sql_warnings = 1;
create table t1 (a datetime, b int as (second(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (second(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('10:05:03',default);
select * from t1;
a	b
2010-05-03 00:00:00	0
drop table t1;
set sql_warnings = 0;
# STR_TO_DATE()
set sql_warnings = 1;
create table t1 (a varchar(64), b datetime as (str_to_date(a,'%m/%d/%Y')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(64) DEFAULT NULL,
  `b` datetime AS (str_to_date(a,'%m/%d/%Y')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('04/30/2004',default);
select * from t1;
a	b
04/30/2004	2004-04-30 00:00:00
drop table t1;
set sql_warnings = 0;
# SUBDATE()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (subdate(a,interval 1 month)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (subdate(a,interval 1 month)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-07-31 00:00:00
drop table t1;
set sql_warnings = 0;
# SUBTIME()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (subtime(a,'02:00:00')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime AS (subtime(a,'02:00:00')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31',default);
select * from t1;
a	b
2008-08-31 00:00:00	2008-08-30 22:00:00
drop table t1;
set sql_warnings = 0;
# TIME_FORMAT()
set sql_warnings = 1;
create table t1 (a datetime, b varchar(32) as (time_format(a,'%r')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(32) AS (time_format(a,'%r')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31 02:03:04',default);
select * from t1;
a	b
2008-08-31 02:03:04	02:03:04 AM
drop table t1;
set sql_warnings = 0;
# TIME_TO_SEC()
set sql_warnings = 1;
create table t1 (a time, b long as (time_to_sec(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` time DEFAULT NULL,
  `b` mediumtext AS (time_to_sec(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('22:23:00',default);
select * from t1;
a	b
22:23:00	80580
drop table t1;
set sql_warnings = 0;
# TIME()
set sql_warnings = 1;
create table t1 (a datetime, b time as (time(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` time AS (time(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-08-31 02:03:04',default);
select * from t1;
a	b
2008-08-31 02:03:04	02:03:04
drop table t1;
set sql_warnings = 0;
# TIMEDIFF()
set sql_warnings = 1;
create table t1 (a datetime, b datetime, c long as (timediff(a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime DEFAULT NULL,
  `c` mediumtext AS (timediff(a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default);
select * from t1;
a	b	c
2008-12-31 23:59:59	2008-12-30 01:01:01	46:58:58
drop table t1;
set sql_warnings = 0;
# TIMESTAMP()
set sql_warnings = 1;
create table t1 (a datetime, b timestamp as (timestamp(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` timestamp AS (timestamp(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-12-31',default);
select * from t1;
a	b
2008-12-31 00:00:00	2008-12-31 00:00:00
drop table t1;
set sql_warnings = 0;
# TIMESTAMPADD()
set sql_warnings = 1;
create table t1 (a datetime, b timestamp as (timestampadd(minute,1,a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` timestamp AS (timestampadd(minute,1,a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2003-01-02',default);
select * from t1;
a	b
2003-01-02 00:00:00	2003-01-02 00:01:00
drop table t1;
set sql_warnings = 0;
# TIMESTAMPDIFF()
set sql_warnings = 1;
create table t1 (a timestamp, b timestamp, c long as (timestampdiff(MONTH, a,b)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c` mediumtext AS (timestampdiff(MONTH, a,b)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2003-02-01','2003-05-01',default);
select * from t1;
a	b	c
2003-02-01 00:00:00	2003-05-01 00:00:00	3
drop table t1;
set sql_warnings = 0;
# TO_DAYS()
set sql_warnings = 1;
create table t1 (a datetime, b long as (to_days(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` mediumtext AS (to_days(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2007-10-07',default);
select * from t1;
a	b
2007-10-07 00:00:00	733321
drop table t1;
set sql_warnings = 0;
# WEEK()
set sql_warnings = 1;
create table t1 (a datetime, b int as (week(a,0)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (week(a,0)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-09-01',default);
select * from t1;
a	b
2008-09-01 00:00:00	35
drop table t1;
set sql_warnings = 0;
# WEEKDAY()
set sql_warnings = 1;
create table t1 (a datetime, b int as (weekday(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (weekday(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-09-01',default);
select * from t1;
a	b
2008-09-01 00:00:00	0
drop table t1;
set sql_warnings = 0;
# WEEKOFYEAR()
set sql_warnings = 1;
create table t1 (a datetime, b int as (weekofyear(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (weekofyear(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-09-01',default);
select * from t1;
a	b
2008-09-01 00:00:00	36
drop table t1;
set sql_warnings = 0;
# YEAR()
set sql_warnings = 1;
create table t1 (a datetime, b int as (year(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (year(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-09-01',default);
select * from t1;
a	b
2008-09-01 00:00:00	2008
drop table t1;
set sql_warnings = 0;
# YEARWEEK()
set sql_warnings = 1;
create table t1 (a datetime, b int as (yearweek(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) AS (yearweek(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('2008-09-01',default);
select * from t1;
a	b
2008-09-01 00:00:00	200835
drop table t1;
set sql_warnings = 0;
#
# FULL TEXT SEARCH FUNCTIONS
#
# None.
#
# CAST FUNCTIONS AND OPERATORS
#
# CAST()
set sql_warnings = 1;
create table t1 (a int, b long as (cast(a as unsigned)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` mediumtext AS (cast(a as unsigned)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (-1,default);
Warnings:
Note	1105	Cast to unsigned converted negative integer to it's positive complement
select * from t1;
a	b
1	1
-1	18446744073709551615
Warnings:
Note	1105	Cast to unsigned converted negative integer to it's positive complement
Note	1105	Cast to unsigned converted negative integer to it's positive complement
drop table t1;
set sql_warnings = 0;
# Convert()
set sql_warnings = 1;
create table t1 (a int, b long as (convert(a,unsigned)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` mediumtext AS (convert(a,unsigned)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert into t1 values (-1,default);
Warnings:
Note	1105	Cast to unsigned converted negative integer to it's positive complement
select * from t1;
a	b
1	1
-1	18446744073709551615
Warnings:
Note	1105	Cast to unsigned converted negative integer to it's positive complement
Note	1105	Cast to unsigned converted negative integer to it's positive complement
drop table t1;
set sql_warnings = 0;
#
# XML FUNCTIONS
#
# None.
#
# OTHER FUNCTIONS
#
# AES_DECRYPT(), AES_ENCRYPT()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (aes_encrypt(aes_decrypt(a,'adf'),'adf')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (aes_encrypt(aes_decrypt(a,'adf'),'adf')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	NULL
drop table t1;
set sql_warnings = 0;
# BIT_COUNT()
set sql_warnings = 1;
create table t1 (a int, b int as (bit_count(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (bit_count(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (5,default);
select * from t1;
a	b
5	2
drop table t1;
set sql_warnings = 0;
# COMPRESS(), UNCOMPRESS()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (uncompress(compress(a))));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (uncompress(compress(a))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	MySQL
drop table t1;
set sql_warnings = 0;
# ENCODE(), DECODE()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (decode(encode(a,'abc'),'abc')));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (decode(encode(a,'abc'),'abc')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('MySQL',default);
select * from t1;
a	b
MySQL	MySQL
drop table t1;
set sql_warnings = 0;
# DEFAULT()
set sql_warnings = 1;
create table t1 (a varchar(1024) default 'aaa', b varchar(1024) as (ifnull(a,default(a))));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT 'aaa',
  `b` varchar(1024) AS (ifnull(a,default(a))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('any value',default);
select * from t1;
a	b
any value	any value
drop table t1;
set sql_warnings = 0;
# INET_ATON(), INET_NTOA()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (inet_ntoa(inet_aton(a))));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (inet_ntoa(inet_aton(a))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('127.0.0.1',default);
select * from t1;
a	b
127.0.0.1	127.0.0.1
drop table t1;
set sql_warnings = 0;
# MD5()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varbinary(32) as (md5(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varbinary(32) AS (md5(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('testing',default);
select * from t1;
a	b
testing	ae2b1fca515949e5d54fb22b8ed95575
drop table t1;
set sql_warnings = 0;
# OLD_PASSWORD()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (old_password(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (old_password(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('badpwd',default);
select * from t1;
a	b
badpwd	7f84554057dd964b
drop table t1;
set sql_warnings = 0;
# PASSWORD()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (password(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (password(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('badpwd',default);
select * from t1;
a	b
badpwd	*AAB3E285149C0135D51A520E1940DD3263DC008C
drop table t1;
set sql_warnings = 0;
# SHA1()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (sha1(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (sha1(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('abc',default);
select * from t1;
a	b
abc	a9993e364706816aba3e25717850c26c9cd0d89d
drop table t1;
set sql_warnings = 0;
# SHA()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (sha(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) AS (sha(a)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('abc',default);
select * from t1;
a	b
abc	a9993e364706816aba3e25717850c26c9cd0d89d
drop table t1;
set sql_warnings = 0;
# UNCOMPRESSED_LENGTH()
set sql_warnings = 1;
create table t1 (a char, b varchar(1024) as (uncompressed_length(compress(repeat(a,30)))));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(1) DEFAULT NULL,
  `b` varchar(1024) AS (uncompressed_length(compress(repeat(a,30)))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('a',default);
select * from t1;
a	b
a	30
drop table t1;
set sql_warnings = 0;