Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 286 insertions, 0 deletions

diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
new file mode 100644
index 00000000..c07e00a5
--- /dev/null
+++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
@@ -0,0 +1,286 @@
+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;