#
# 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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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(c12,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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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;
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:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1831	Duplicate index 'vbidxcol_2' defined on the table 'test.ibstd_08'. 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);
Warnings:
Warning	124	InnoDB rebuilding table to add column FTS_DOC_ID
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 not null,
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 '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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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);
Warnings:
Warning	124	InnoDB rebuilding table to add column FTS_DOC_ID
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
Warnings:
Warning	1831	Duplicate index 'ftidx' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
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);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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;
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 ((col2 - col4)) VIRTUAL,
col6 int(11) GENERATED ALWAYS AS ((col5 + col1)) VIRTUAL,
col7 bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
col8 int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
col9 text,
KEY idx1 (col5 DESC),
KEY idx2 (col6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `col1` int NOT NULL,
  `col2` int DEFAULT NULL,
  `col3` int NOT NULL,
  `col4` int DEFAULT NULL,
  `col5` int GENERATED ALWAYS AS ((`col2` - `col4`)) VIRTUAL,
  `col6` int GENERATED ALWAYS AS ((`col5` + `col1`)) VIRTUAL,
  `col7` bigint GENERATED ALWAYS AS ((`col2` / `col1`)) VIRTUAL,
  `col8` int GENERATED ALWAYS AS ((`col5` % `col5`)) VIRTUAL,
  `col9` text,
  KEY `idx1` (`col5` DESC),
  KEY `idx2` (`col6`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (10, 50, 40, 300, "lope");
INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (100, 3240, 4000, 11, "dskf");
INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (5, 400, 20, 900, "hqal");
SELECT * FROM t1;
col1	col2	col3	col4	col5	col6	col7	col8	col9
10	50	40	300	-250	-240	5	0	lope
100	3240	4000	11	3229	3329	32	0	dskf
5	400	20	900	-500	-495	80	0	hqal
SELECT col5 FROM t1;
col5
-250
-500
3229
SELECT col6 FROM t1;
col6
-240
-495
3329
SELECT col7 FROM t1;
col7
32
5
80
ALTER TABLE t1 ADD INDEX idx3 (col7 DESC);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT col7 FROM t1;
col7
32
5
80
SELECT col7 FROM t1;
col7
32
5
80
ALTER TABLE t1 DROP INDEX idx3, ADD INDEX idx3 (col7 ASC);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT col7 FROM t1;
col7
32
5
80
DROP TABLE t1;
#
# Bug 25899959 - SERVER CRASH WHILE TRYING TO ADD A VIRTUAL COLUMN AND ADD INDEX ON IT
#
CREATE TABLE t1 (doc json DEFAULT NULL);
ALTER TABLE t1 ADD Name char(52) AS (doc->>"$.Name"), ADD INDEX n (Name);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `doc` json DEFAULT NULL,
  `Name` char(52) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.Name'))) VIRTUAL,
  KEY `n` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
#
# Bug #27968952  INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
#
SET sql_mode='';
CREATE TABLE t1(
a INT not null,
b CHAR(4) not null,
c TEXT GENERATED ALWAYS AS ((a <> b)) VIRTUAL,
UNIQUE KEY i1 (a),
UNIQUE KEY i2 (c(1))
) ENGINE=INNODB;
insert into t1 set a=1;
Warnings:
Warning	1364	Field 'b' doesn't have a default value
insert into t1 set a=0;
Warnings:
Warning	1364	Field 'b' doesn't have a default value
replace into t1 set b ='1';
Warnings:
Warning	1364	Field 'a' doesn't have a default value
DROP TABLE t1;
SET NAMES utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
CREATE TABLE t1 (
a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
b VARCHAR(1000) NOT NULL,
c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED NOT NULL,
KEY (b(1)),
KEY (a(1))
) ENGINE=INNODB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=1;
INSERT INTO t1(b) VALUES(REPEAT('a',947));
DELETE FROM t1;
DROP TABLE t1;
SET NAMES DEFAULT;
SET sql_mode = DEFAULT;
#
# Bug 23014521 - mysqld crash at row_upd_sec_index_entry_low
#
SET @org_mode=@@sql_mode;
SET @@sql_mode='';
CREATE TABLE t1 (
col1 blob,
col3 blob,
col4 blob,
col5 blob GENERATED ALWAYS AS ((col1 - col4)) VIRTUAL,
col5x blob GENERATED ALWAYS AS ((col1 / col3)) VIRTUAL,
col9 text,
col2b blob GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
KEY idx7 (col9(10), col5(5)),
KEY idx10 (col9(10), col2b(5))
) ENGINE=InnoDB;
INSERT INTO t1 (col1,col3,col4,col9) VALUES (REPEAT('a',20),REPEAT('a',20),REPEAT('a',20),REPEAT('a',20));
UPDATE t1 SET col1 = 50;
DELETE FROM t1;
DROP TABLE t1;
SET @@sql_mode=@org_mode;