1 files changed, 372 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_index.result b/mysql-test/suite/gcol/r/innodb_virtual_index.result
new file mode 100644
index 00000000..a26da4ae
--- /dev/null
+++ b/mysql-test/suite/gcol/r/innodb_virtual_index.result
@@ -0,0 +1,372 @@
+SET default_storage_engine= innodb;
+#
+# Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
+# THE NUMBER OF VIRTUAL COLUMNS
+#
+CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT
+GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4
+INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL,
+a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a)
+VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS
+(a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ;
+INSERT INTO t1(a) VALUES(10);
+SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND
+a6=10 AND a7=10 AND a8=10 AND a9=10;
+a	a1	a2	a3	a4	a5	a6	a7	a8	a9
+10	10	10	10	10	10	10	10	10	10
+DROP TABLE t1;
+#
+# Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS(
+# OFFSETS)
+#
+SET @@SESSION.sql_mode=0;
+CREATE TABLE t1(
+c1 int(1)AUTO_INCREMENT,
+c2 int(1),
+c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
+c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
+c5 date,
+c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
+c7 DATE,
+c8 time,
+c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
+c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
+c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
+c12 CHAR(1),
+c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
+c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
+PRIMARY KEY(c1),
+KEY c4_6(c4,c11)
+)ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE t2(
+c1 int(1)AUTO_INCREMENT,
+c2 int(1),
+c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
+c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
+c5 date,
+c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
+c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL,
+c7 DATE,
+c8 time,
+c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
+c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
+c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
+c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL,
+c12 CHAR(1),
+c13 CHAR(2)GENERATED ALWAYS AS (concat(RTRIM(c12),RTRIM(c12))) VIRTUAL,
+c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
+PRIMARY KEY(c1),
+KEY c13(c13),
+KEY c4_6(c4,c11)
+)ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v');
+CREATE TABLE t3(
+c1 int(1)AUTO_INCREMENT,
+c2 int(1),
+c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
+c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
+c5 date,
+c7 DATE,
+c8 time,
+c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
+c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
+c12 CHAR(1),
+PRIMARY KEY(c1),
+KEY c4_6(c4,c11)
+)ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES
+(0,0,0,0,0,'q'),(0,0,0,0,0,'g'),(0,0,0,0,0,'l'),(0,0,0,0,0,1),(0,0,0,0,0,'v'),
+(0,1,0,0,0,'c'),(0,0,0,0,0,'x');
+UPDATE
+t2 AS O1,t3 AS O2
+SET O1.c12=1
+WHERE O1.c14 NOT IN
+(
+SELECT
+DISTINCT I1.c14 AS y
+FROM t1 AS I1
+ORDER BY I1.c14);
+SET @@SESSION.sql_mode=default;
+InnoDB		0 transactions not purged
+DROP TABLE t1, t2, t3;
+#
+# Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
+#
+CREATE TABLE `ibstd_08` (
+`nc00577` tinyint(4) DEFAULT NULL,
+`nc07844` varchar(41) DEFAULT NULL,
+`gc01908` point NOT NULL,
+`nc04156` char(17) DEFAULT NULL,
+`nc09536` longblob NOT NULL,
+`nc09231` decimal(10,0) NOT NULL,
+`a` int(11) NOT NULL,
+`b` varchar(198) NOT NULL,
+`nc04560` mediumtext,
+`c` char(187) DEFAULT NULL,
+`vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
+`gc00881` polygon NOT NULL,
+`nc05121` int(11) NOT NULL DEFAULT '85941481',
+KEY `a` (`a`),
+KEY `b` (`b`(3),`a`),
+KEY `c` (`c`(99),`b`(25)),
+KEY `b_2` (`b`(5),`c`(10),`a`),
+KEY `vbidxcol` (`vbidxcol`),
+KEY `a_2` (`a`,`vbidxcol`),
+KEY `vbidxcol_2` (`vbidxcol`),
+FULLTEXT KEY `ftsic` (`c`,`b`)
+) ENGINE=InnoDB;
+Warnings:
+Note	1831	Duplicate index `vbidxcol_2`. This is deprecated and will be disallowed in a future release
+ALTER TABLE ibstd_08  ADD COLUMN nc07006 BIGINT  AUTO_INCREMENT NOT NULL  , ADD KEY auto_nc07006(nc07006);
+DROP TABLE ibstd_08;
+#
+# Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256)
+# AND SEGFAULT
+#
+set sql_mode="";
+create table t (a int) engine=innodb;
+create table s (
+b int generated always as (1) virtual,
+c int,
+d int generated always as (1) virtual,
+key (d)
+) engine=innodb;
+insert into t(a) values ((select d from s for update));
+insert into s(c) values ('');
+Warnings:
+Warning	1366	Incorrect integer value: '' for column `test`.`s`.`c` at row 1
+SET sql_mode = default;
+drop table if exists t,s;
+#
+# Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V
+#
+CREATE TABLE t1 (
+col1 int(11) NOT NULL,
+col2 int(11) DEFAULT NULL,
+col3 int(11) NOT NULL,
+col4 int(11) DEFAULT NULL,
+col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL,
+col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
+col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL,
+col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL,
+col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL,
+col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL,
+col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
+col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL,
+col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL,
+col9 text,
+col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
+col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL,
+col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
+col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL,
+extra int(11) DEFAULT NULL,
+col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL,
+col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL,
+col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL,
+col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL,
+col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
+col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
+col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL,
+col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL,
+UNIQUE KEY idx7 (col1,col3,col2),
+UNIQUE KEY uidx (col9(10)),
+KEY idx15 (col9(10) DESC,col2 DESC),
+KEY idx10 (col9(10) DESC,col1 DESC),
+KEY idx11 (col6x DESC),
+KEY idx6 (col9(10) DESC,col7 DESC),
+KEY idx14 (col6 DESC)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
+VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY
+CREATE FULLTEXT INDEX idx ON t1(col9);
+ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
+VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
+DROP TABLE t1;
+CREATE TABLE t1 (
+col1 int(11) NOT NULL,
+col2 int(11) DEFAULT NULL,
+col3 int(11) NOT NULL,
+col4 int(11) DEFAULT NULL) engine=innodb;
+ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
+VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY
+DROP TABLE t1;
+#
+# Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7
+#
+CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `col1` int(10) DEFAULT NULL,
+  `col2` char(21) GENERATED ALWAYS AS (`col1` * `col1`) VIRTUAL,
+  KEY `n` (`col2`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
+DROP TABLE t1;
+#
+# Bug #27968952  INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
+#
+CREATE TABLE t1(
+a INT NOT NULL UNIQUE,
+b INT NOT NULL,
+c TEXT GENERATED ALWAYS AS (a <> b) VIRTUAL,
+d TEXT NOT NULL,
+UNIQUE KEY (c(1)), KEY(d(1))
+) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
+SET @t = REPEAT('t',@@innodb_page_size);
+INSERT INTO t1 (a,b,d) VALUES (1,0,@t), (0,0,@t);
+UPDATE t1 SET b = a;
+ERROR 23000: Duplicate entry '0' for key 'c'
+REPLACE INTO t1 SET a = 0, b = 1, d = 'd';
+SELECT * FROM t1;
+a	b	c	d
+0	1	1	d
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;
+CREATE TABLE t1(
+a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
+b VARCHAR(1000) NOT NULL,
+c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED,
+KEY (b(1)),
+KEY (a(1))
+) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
+INSERT INTO t1(b) VALUES(REPEAT('b',1000));
+DELETE FROM t1;
+DROP TABLE t1;
+#
+# Bug #22990029 GCOLS: INCORRECT BEHAVIOR
+# AFTER DATA INSERTED WITH IGNORE KEYWORD
+#
+CREATE TABLE t1(a INT PRIMARY KEY, b INT, vb DATE AS(b) VIRTUAL, KEY(vb))
+ENGINE=InnoDB;
+INSERT IGNORE INTO t1 (a,b) VALUES(1,20190132);
+Warnings:
+Warning	1265	Data truncated for column 'vb' at row 1
+SELECT * FROM t1;
+a	b	vb
+1	20190132	0000-00-00
+BEGIN;
+DELETE FROM t1;
+INSERT INTO t1 (a,b) VALUES(1,20190123);
+SELECT * FROM t1;
+a	b	vb
+1	20190123	2019-01-23
+ROLLBACK;
+SELECT * FROM t1;
+a	b	vb
+1	20190132	0000-00-00
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;
+#
+# MDEV-25872 InnoDB: Assertion failure in row_merge_read_clustered_index
+#	upon ALTER on table with indexed virtual columns
+#
+CREATE TABLE t1 (
+id BIGINT AUTO_INCREMENT PRIMARY KEY,
+a INT,
+va INT ZEROFILL AS (a) VIRTUAL,
+b TIMESTAMP,
+c CHAR(204),
+vc CHAR(8),
+KEY(vc,c(64),b,va)
+) ENGINE=InnoDB CHARACTER SET utf32;
+INSERT INTO t1 (id) SELECT NULL FROM seq_1_to_75;
+INSERT IGNORE INTO t1 (id, a) VALUES (NULL, -1);
+Warnings:
+Warning	1264	Out of range value for column 'va' at row 1
+ALTER TABLE t1 FORCE;
+ERROR 22003: Out of range value for column 'va' at row 0
+DROP TABLE t1;
+#
+# MDEV-24713 Assertion `dict_table_is_comp(index->table)' failed
+#		in row_merge_buf_add()
+#
+CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(3),
+b CHAR(8) AS (a) VIRTUAL, KEY(b))
+ROW_FORMAT=REDUNDANT ENGINE=InnoDB
+CHARACTER SET utf8;
+INSERT INTO t1 (id,a) VALUES (1,'foo');
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+DROP TABLE t1;
+#
+# MDEV-20154 Assertion `len <= col->len || ((col->mtype) == 5
+# || (col->mtype) == 14)' failed in row_merge_buf_add
+#
+CREATE TABLE t1 (
+a VARCHAR(2500),
+b VARCHAR(2499) AS (a) VIRTUAL
+) ENGINE=InnoDB;
+INSERT INTO t1 (a) VALUES ('foo');
+ALTER TABLE t1 MODIFY a VARCHAR(2600), ALGORITHM=INPLACE;
+ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE;
+# Cleanup
+DROP TABLE t1;
+# End of 10.2 tests
+#
+# MDEV-29299 SELECT from table with vcol index reports warning
+#
+CREATE TABLE t(fld1 INT NOT NULL,
+fld2 INT AS (100/fld1) VIRTUAL,
+KEY(fld1), KEY(fld2));
+CREATE TABLE t_odd(id int);
+INSERT INTO t(fld1) VALUES(1), (2);
+connect stop_purge,localhost,root;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+INSERT INTO t_odd VALUES(10000);
+connection default;
+UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2;
+UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3;
+UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4;
+Warnings:
+Warning	1365	Division by 0
+SELECT fld2 FROM t FORCE INDEX(fld2);
+fld2
+NULL
+100
+SELECT fld2 FROM t FORCE INDEX(fld1);
+fld2
+100
+NULL
+Warnings:
+Warning	1365	Division by 0
+disconnect stop_purge;
+DROP TABLE t, t_odd;
+#
+# MDEV-29753 An error is wrongly reported during INSERT with vcol index
+# See also Bug #22990029
+#
+CREATE TABLE t(pk INT PRIMARY KEY,
+fld1 INT NOT NULL,
+fld2 INT AS (100/fld1) VIRTUAL,
+KEY(fld1), KEY(fld2));
+INSERT IGNORE t(pk, fld1) VALUES(1, 0);
+Warnings:
+Warning	1365	Division by 0
+SELECT * FROM t;
+pk	fld1	fld2
+1	0	NULL
+Warnings:
+Warning	1365	Division by 0
+BEGIN;
+DELETE FROM t;
+Warnings:
+Warning	1365	Division by 0
+Warning	1365	Division by 0
+Warning	1365	Division by 0
+INSERT INTO t (pk, fld1) VALUES(1,1);
+SELECT * FROM t;
+pk	fld1	fld2
+1	1	100
+# Cleanup
+ROLLBACK;
+DROP TABLE t;
+# End of 10.3 tests