SET @@session.default_storage_engine = 'InnoDB';
#            - UNIQUE KEY
#            - INDEX
#            - FULLTEXT INDEX
#            - SPATIAL INDEX (not supported)
#            - FOREIGN INDEX (partially supported)
#            - CHECK (allowed but not used)
# UNIQUE
create table t1 (a int, b int as (a*2) unique);
drop table t1;
create table t1 (a int, b int as (a*2) persistent unique);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	UNI	NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int as (a*2), unique key (b));
drop table t1;
create table t1 (a int, b int as (a*2) persistent, unique (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	UNI	NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int as (a*2));
alter table t1 add unique key (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add unique key (b);
drop table t1;
# Testing data manipulation operations involving UNIQUE keys 
# on virtual columns can be found in:
#  - vcol_ins_upd.inc
#  - vcol_select.inc
# 
# INDEX
create table t1 (a int, b int as (a*2), index (b));
drop table t1;
create table t1 (a int, b int as (a*2), index (a,b));
drop table t1;
create table t1 (a int, b int as (a*2) persistent, index (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	int(11)	YES	MUL	NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int as (a*2) persistent, index (a,b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
  KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES	MUL	NULL	
b	int(11)	YES		NULL	STORED GENERATED
drop table t1;
create table t1 (a int, b int as (a*2));
alter table t1 add index (b);
alter table t1 add index (a,b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (a,b);
create table t2 like t1;
drop table t2;
drop table t1;
# Testing data manipulation operations involving INDEX
# on virtual columns can be found in:
#  - vcol_select.inc
#
# TODO: FULLTEXT INDEX
# SPATIAL INDEX
# Error "All parts of a SPATIAL index must be NOT NULL"
create table t1 (a int, b geometry as (a+1) persistent, spatial index (b));
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
create table t1 (a int, b int as (a+1) persistent);
alter table t1 add spatial index (b);
ERROR HY000: Incorrect arguments to SPATIAL INDEX
drop table t1;
# FOREIGN KEY
# Rejected FK options.
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update set null);
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update cascade);
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on delete set null);
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
create table t1 (a int, b int as (a+1) persistent);
alter table t1 add foreign key (b) references t2(a) on update set null;
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
alter table t1 add foreign key (b) references t2(a) on update cascade;
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
alter table t1 add foreign key (b) references t2(a) on delete set null;
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
drop table t1;
create table t1 (a int, b int as (a+1), foreign key (b) references t2(a));
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
create table t1 (a int, b int as (a+1));
alter table t1 add foreign key (b) references t2(a);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
drop table t1;
# Allowed FK options.
create table t2 (a int primary key, b char(5));
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update no action);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete cascade);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete no action);
drop table t1;

# Testing data manipulation operations involving FOREIGN KEY 
# on virtual columns can be found in:
#  - vcol_ins_upd.inc
#  - vcol_select.inc
create table t1 (a int, b timestamp as (now()), key (b));
ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
create table t1 (a int, b timestamp as (now()));
alter table t1 add index (b);
ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
drop table t1;
create table t1 (a int, b varchar(100) as (user()), key (b));
ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
create table t1 (a int, b varchar(100) as (user()));
alter table t1 add index (b);
ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
drop table t1;
create table t1 (a int, b double as (rand()), key (b));
ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
create table t1 (a int, b double as (rand()));
alter table t1 add index (b);
ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
drop table t1;
CREATE OR REPLACE TABLE t1 (
f2 DOUBLE NOT NULL DEFAULT '0',
f3 DOUBLE NOT NULL DEFAULT '0',
f4 DOUBLE,
f5 DOUBLE DEFAULT '0',
v4 DOUBLE AS (IF(f4,f3,f2)) VIRTUAL,
KEY (f5),
KEY (v4)
);
INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,4,1,0),(5,7,NULL,0);
INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f3, f5, f3 FROM t1;
INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,0,NULL,1);
INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f5, f5, f3 FROM t1;
DELETE FROM t1 WHERE f5 = 1 OR v4 = 4 ORDER BY f5,v4 LIMIT 9;
SELECT * from t1;
f2	f3	f4	f5	v4
5	7	NULL	0	5
5	4	0	4	5
5	7	0	7	5
5	0	0	4	5
5	0	0	7	5
5	7	7	7	7
5	1	1	0	1
DROP TABLE t1;
CREATE TABLE t1 (
d DECIMAL(63,0) NOT NULL DEFAULT 0,
c VARCHAR(64) NOT NULL DEFAULT '',
vd DECIMAL(63,0) AS (d) VIRTUAL,
vc VARCHAR(2048) AS (c) VIRTUAL,
pk BIGINT AUTO_INCREMENT,
PRIMARY KEY(pk));
INSERT INTO t1 (d,c) VALUES (0.5,'foo');
Warnings:
Note	1265	Data truncated for column 'd' at row 1
SELECT * FROM t1 WHERE vc != 'bar' ORDER BY vd;
d	c	vd	vc	pk
1	foo	1	foo	1
DROP TABLE t1;
CREATE TABLE t1 (
pk BIGINT,
c CHAR(64) NOT NULL DEFAULT '',
vc CHAR(64) AS (c) VIRTUAL,
PRIMARY KEY(pk),
INDEX(vc(32))
);
DELETE FROM t1 WHERE vc IS NULL ORDER BY pk;
DROP TABLE t1;
#
# MDEV-11737 Failing assertion: block->magic_n == MEM_BLOCK_MAGIC_N
# 
CREATE TABLE t1 (i INT PRIMARY KEY, vi INT AS (i*2) VIRTUAL UNIQUE)
ENGINE=InnoDB;
CREATE TABLE t2 (i INT) ENGINE=InnoDB;
ALTER TABLE t1 ADD COLUMN col INT;
SELECT * FROM t1 WHERE vi < 2;
i	vi	col
DROP TABLE t1, t2;
create table t1 (
pk int auto_increment,
col_varchar varchar(847) not null default '',
col_int bigint(15) unsigned zerofill,
col_datetime datetime(3) not null default '1900-01-01 00:00:00',
col_time time(5) not null default '00:00:00',
col_blob text,
col_bit bit(34),
col_year year,
col_char char(10),
col_dec decimal(18,9) not null default 0,
col_enum enum('','a','b','c','d','e','f','foo','bar') not null default '',
col_date date not null default '1900-01-01',
col_timestamp timestamp(3) not null default '1971-01-01 00:00:00',
vcol_datetime datetime as (truncate(col_datetime,0)) virtual,
vcol_dec decimal(18,9) zerofill as (col_dec) virtual,
vcol_bit bit(63) as (col_bit) virtual,
vcol_char binary(51) as (col_char) virtual,
vcol_timestamp timestamp(5) as (col_timestamp) virtual,
vcol_enum enum('','a','b','c','d','e','f','foo','bar') as (col_enum) virtual,
vcol_int tinyint(48) zerofill as (col_int) virtual,
vcol_time time(4) as (col_time) virtual,
vcol_varchar varbinary(3873) as (col_varchar) virtual,
vcol_year year as (col_year) virtual,
vcol_date date as (col_date) virtual,
vcol_blob longtext as (col_blob) virtual,
primary key(pk)
) engine=innodb;
insert into t1 (col_varchar,col_int,col_datetime,col_time,col_blob,col_bit,col_year,col_char,col_dec,col_enum,col_date,col_timestamp) values
('foo',1,'2010-05-08 13:08:12.034783','18:32:14','foo',b'0111110101001001',1992,'f',0.2,'','1994-12-26','2019-01-11 00:00:00'),
('bar',6,'1900-01-01 00:00:00','00:00:00','bar',b'10011000001101011000101',1985,'b',0.7,'','2028-04-06','1971-01-01 00:00:00');
alter table t1 add index(vcol_datetime);
drop table t1;
create table t1 (
pk int,
col_blob mediumtext not null default '',
vcol_blob tinyblob as (col_blob) virtual,
col_char char(22) null,
primary key(pk),
index(col_char,vcol_blob(64))
) engine=innodb;
insert ignore into t1 (pk) values (1),(2);
update t1 set col_char = 'foo' where pk = 1;
drop table t1;
create table t1 (
id int not null primary key,
a varchar(200),
b varchar(200),
c int,
va char(200)  generated always as (ucase(a)) virtual,
vb char(200) generated always as (ucase(b)) virtual,
key (c,va,vb)
) engine=innodb;
insert t1 (id,a,c) select seq,seq,seq from seq_1_to_330;
select IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE') from t1;
IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE')
GOOD
alter table t1 drop column va;
drop table t1;