drop table if exists t1,t2;
create table t1 (a int, b int, v int as (a+1), index idx(b));
insert into t1(a, b) values
(4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10);
select * from t1 order by b;
a	b	v
1	10	2
2	20	3
3	30	4
4	40	5
5	50	6
7	70	8
8	80	9
delete from t1 where v > 6 order by b limit 1;
select * from t1 order by b;
a	b	v
1	10	2
2	20	3
3	30	4
4	40	5
5	50	6
8	80	9
update t1 set a=v order by b limit 1;
select * from t1 order by b;
a	b	v
2	10	3
2	20	3
3	30	4
4	40	5
5	50	6
8	80	9
drop table t1;
CREATE TABLE t1 (
a int NOT NULL DEFAULT '0',
v double AS ((1, a)) VIRTUAL
);
ERROR HY000: Expression for computed column cannot return a row
CREATE TABLE t1 (
a CHAR(255) BINARY NOT NULL DEFAULT 0,
b CHAR(255) BINARY NOT NULL DEFAULT 0,
v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL );
INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6');
SELECT 1 AS C FROM t1 ORDER BY v;
C
1
1
DROP TABLE t1;
CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT);
INSERT INTO t1(a) VALUES (1);
SELECT b, v FROM t1;
b	v
0	10
DROP TABLE t1;
CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT);
INSERT INTO t1 () VALUES ();
CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1));
INSERT INTO t2 () VALUES ();
SELECT a, v FROM t1;
a	v
100	101
SELECT a, v FROM t2;
a	v
100	101
DROP TABLE t1,t2;
CREATE TABLE t1 (
a datetime NOT NULL DEFAULT '2000-01-01',
v boolean AS (a < '2001-01-01')
);
INSERT INTO t1(a) VALUES ('2002-02-15');
INSERT INTO t1(a) VALUES ('2000-10-15');
SELECT a, v FROM t1;
a	v
2002-02-15 00:00:00	0
2000-10-15 00:00:00	1
SELECT a, v FROM t1;
a	v
2002-02-15 00:00:00	0
2000-10-15 00:00:00	1
CREATE TABLE t2 (
a datetime NOT NULL DEFAULT '2000-01-01',
v boolean AS (a < '2001-01-01') PERSISTENT
);
INSERT INTO t2(a) VALUES ('2002-02-15');
INSERT INTO t2(a) VALUES ('2000-10-15');
SELECT * FROM t2;
a	v
2002-02-15 00:00:00	0
2000-10-15 00:00:00	1
DROP TABLE t1, t2;
CREATE TABLE t1 (
a char(255), b char(255), c char(255), d char(255),
v char(255) AS (CONCAT(c,d) ) VIRTUAL
);
INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z');
SELECT v FROM t1 ORDER BY CONCAT(a,b);
v
yz
YZ
DROP TABLE t1;
CREATE TABLE t1 (f1 INTEGER, v1 INTEGER AS (f1) VIRTUAL);
CREATE TABLE t2 AS SELECT v1 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `v1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1,t2;
CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p)) VIRTUAL);
INSERT INTO t1 VALUES (0,1,0);
Warnings:
Warning	1906	The value specified for computed column 'v' in table 't1' ignored
INSERT INTO t1 VALUES (NULL,0,0);
Warnings:
Warning	1906	The value specified for computed column 'v' in table 't1' ignored
SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
a	p	v	ROUND(a,p)	ROUND(a,p+NULL)
1	0	1	1	NULL
0	NULL	NULL	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (p int, a double NOT NULL);
INSERT INTO t1(p,a) VALUES (0,1);
INSERT INTO t1(p,a) VALUES (NULL,0);
SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
a	p	ROUND(a,p)	ROUND(a,p+NULL)
1	0	1	NULL
0	NULL	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (a char(32), v char(32) CHARACTER SET ucs2 AS (a) VIRTUAL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(32) DEFAULT NULL,
  `v` char(32) CHARACTER SET ucs2 AS (a) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL);
SELECT table_schema, table_name, column_name, column_type, extra
FROM information_schema.columns WHERE table_name = 't1';
table_schema	table_name	column_name	column_type	extra
test	t1	a	int(11)	
test	t1	b	int(11)	
SELECT table_schema, table_name, column_name, column_type, extra 
FROM information_schema.columns WHERE table_name = 't2';
table_schema	table_name	column_name	column_type	extra
test	t2	a	int(11)	
test	t2	b	int(11)	VIRTUAL
DROP TABLE t1,t2;
create table t1 (
a int not null, b char(2) not null,
c enum('Y','N') as (case when b = 'aa' then 'Y' else 'N' end) persistent
);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` char(2) NOT NULL,
  `c` enum('Y','N') AS (case when b = 'aa' then 'Y' else 'N' end) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1(a,b) values (1,'bb'), (2,'aa'), (3,'cc');
select * from t1;
a	b	c
1	bb	N
2	aa	Y
3	cc	N
create table t2 (
a int, b int,
c set("y","n") 
as (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) persistent
);
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` set('y','n') AS (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t2(a,b) values (7,0), (2,3), (0,1);
select * from t2;
a	b	c
7	0	y,n
2	3	y
0	1	y,n
drop table t1,t2;
CREATE TABLE t1 (
ts TIMESTAMP,
tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
) ENGINE=MyISAM;
INSERT INTO t1 (tsv) VALUES (DEFAULT);
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00'
INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00'
FLUSH TABLES;
SELECT COUNT(*) FROM t1;
COUNT(*)
2
DROP TABLE t1;
#
# MDEV-4823 Server crashes in Item_func_not::fix_fields on
# creating a table with a virtual column using NOT
#
CREATE TABLE t1 ( f1 INT, v4 INT AS ( NOT f1 ) VIRTUAL );
drop table t1;
# end of 5.2 tests
create table t1 (a int, b int);
insert into t1 values (3, 30), (4, 20), (1, 20);
create table t2 (c int, d int, v int as (d+1), index idx(c));
insert into t2(c,d) values 
(20, 100), (20, 300), (30, 100), (30, 200), (40, 500),
(70, 100), (40, 300), (60, 100), (40, 100), (70, 100);
insert into t2(c,d) values 
(120, 100), (150, 300), (130, 100), (130, 200), (140, 500),
(170, 100), (180, 300), (160, 100), (40, 100), (170, 100);
set join_cache_level=6;
explain
select * from t1,t2 where t1.b=t2.c and d <= 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	2	Using where
select * from t1,t2 where t1.b=t2.c and d <= 100;
a	b	c	d	v
3	30	30	100	101
4	20	20	100	101
1	20	20	100	101
set join_cache_level=default;
drop table t1, t2;
create table t1 (a bigint, b bigint as (a > '2'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(20) DEFAULT NULL,
  `b` bigint(20) AS (a > '2') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 (a) values (1),(3);
select * from t1;
a	b
1	0
3	1
select * from t1;
a	b
1	0
3	1
drop table t1;
create table t1 (a bigint, b bigint as (a between 0 and 2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(20) DEFAULT NULL,
  `b` bigint(20) AS (a between 0 and 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 (a) values (1),(3);
select * from t1;
a	b
1	1
3	0
select * from t1;
a	b
1	1
3	0
drop table t1;
create table t1 (a char(10), b char(10) as (a between 0 and 2));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(10) DEFAULT NULL,
  `b` char(10) AS (a between 0 and 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 (a) values (1),(3);
select * from t1;
a	b
1	1
3	0
select * from t1;
a	b
1	1
3	0
drop table t1;
CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` varchar(32) DEFAULT NULL,
`c` int(11) AS (a MOD 10) VIRTUAL,
`d` varchar(5) AS (LEFT(b,5)) PERSISTENT
) ENGINE=MyISAM;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` int(11) AS (a MOD 10) VIRTUAL,
  `d` varchar(5) AS (LEFT(b,5)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO		NULL	
b	varchar(32)	YES		NULL	
c	int(11)	YES		NULL	VIRTUAL
d	varchar(5)	YES		NULL	PERSISTENT
show full columns from t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	NO		NULL		#	
b	varchar(32)	latin1_swedish_ci	YES		NULL		#	
c	int(11)	NULL	YES		NULL	VIRTUAL	#	
d	varchar(5)	latin1_swedish_ci	YES		NULL	PERSISTENT	#	
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL);
UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND  `d`='a';
Warnings:
Warning	1906	The value specified for computed column 'd' in table 't1' ignored
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
Warnings:
Warning	1906	The value specified for computed column 'd' in table 't1' ignored
set sql_mode='strict_all_tables';
UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND  `d`='a';
ERROR HY000: The value specified for computed column 'd' in table 't1' ignored
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
ERROR HY000: The value specified for computed column 'd' in table 't1' ignored
drop table t1;
# end of 5.3 tests
