diff options
author | Sergei Golubchik <serg@mariadb.org> | 2016-11-07 22:35:02 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2016-12-12 20:27:42 +0100 |
commit | 1cae1af6f9286ca6695206ce20c63bb639c60310 (patch) | |
tree | 3877547f579596add5d757059d1e2f10dd8ef429 /mysql-test/suite/gcol/t/innodb_virtual_basic.test | |
parent | 7fca91f2b454db6e33d964a7484237793699f77d (diff) | |
download | mariadb-git-1cae1af6f9286ca6695206ce20c63bb639c60310.tar.gz |
MDEV-5800 InnoDB support for indexed vcols
* remove old 5.2+ InnoDB support for virtual columns
* enable corresponding parts of the innodb-5.7 sources
* copy corresponding test cases from 5.7
* copy detailed Alter_inplace_info::HA_ALTER_FLAGS flags from 5.7
- and more detailed detection of changes in fill_alter_inplace_info()
* more "innodb compatibility hooks" in sql_class.cc to
- create/destroy/reset a THD (used by background purge threads)
- find a prelocked table by name
- open a table (from a background purge thread)
* different from 5.7:
- new service thread "thd_destructor_proxy" to make sure all THDs are
destroyed at the correct point in time during the server shutdown
- proper opening/closing of tables for vcol evaluations in
+ FK checks (use already opened prelocked tables)
+ purge threads (open the table, MDLock it, add it to tdc, close
when not needed)
- cache open tables in vc_templ
- avoid unnecessary allocations, reuse table->record[0] and table->s->default_values
- not needed in 5.7, because it overcalculates:
+ tell the server to calculate vcols for an on-going inline ADD INDEX
+ calculate vcols for correct error messages
* update other engines (mroonga/tokudb) accordingly
Diffstat (limited to 'mysql-test/suite/gcol/t/innodb_virtual_basic.test')
-rw-r--r-- | mysql-test/suite/gcol/t/innodb_virtual_basic.test | 1474 |
1 files changed, 1474 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test new file mode 100644 index 00000000000..342d9e42976 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test @@ -0,0 +1,1474 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +call mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual"); + +set default_storage_engine=innodb; + +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d CHAR(20), e CHAR(10) GENERATED ALWAYS AS (c), g INT); +INSERT INTO t VALUES(10, DEFAULT, "aa", "bb", DEFAULT, 20); +INSERT INTO t VALUES(11, DEFAULT, "jj", "kk", DEFAULT, 21); + +CREATE INDEX idx ON t(e) algorithm=inplace; +INSERT INTO t VALUES(12, DEFAULT, 'mm', "nn", DEFAULT, 22); + +SELECT e FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +CREATE INDEX idx ON t(c); +SELECT c FROM t; + +UPDATE t SET a = 10 WHERE a = 11; +SELECT c FROM t; + +SELECT * FROM t; + +DELETE FROM t WHERE a = 18; + +SELECT c FROM t; + +START TRANSACTION; + +INSERT INTO t VALUES (128, 22, DEFAULT, "xx"); +INSERT INTO t VALUES (1290, 212, DEFAULT, "xmx"); +ROLLBACK; + +SELECT c FROM t; +SELECT * FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); +INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); +INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); + +SELECT c FROM t; +SELECT m FROM t; +SELECT p FROM t; +SELECT * FROM t; + +update t set a = 13 where a =11; + +delete from t where a =13; + +DROP INDEX idx1 ON t; +DROP INDEX idx2 ON t; +DROP TABLE t; + +/* Test large BLOB data */ +CREATE TABLE `t` ( + `a` BLOB, + `b` BLOB, + `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `h` VARCHAR(10) DEFAULT NULL +) ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk"); + +CREATE INDEX idx ON t(c(100)); + +SELECT length(c) FROM t; + +START TRANSACTION; + +INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); + +ROLLBACK; + +INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); + +START TRANSACTION; + +UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%"; + +ROLLBACK; + +# This SELECT did not give correct answer, even though InnoDB return +# all qualified rows +SELECT COUNT(*) FROM t WHERE c like "aaa%"; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +CREATE INDEX idx ON t(c); + +START TRANSACTION; + +UPDATE t SET a = 100 WHERE a = 11; + +UPDATE t SET a =22 WHERE a = 18; + +UPDATE t SET a = 33 WHERE a = 22; + +SELECT c FROM t; + +ROLLBACK; + +SELECT c FROM t; +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +CREATE INDEX idx ON t(c); +SELECT c FROM t; + +connect(con1,localhost,root,,test); +START TRANSACTION; +SELECT c FROM t; + +connection default; +UPDATE t SET a = 19 WHERE a = 11; + +# this should report the same value as previous one (14, 19, 29). +connection con1; +SELECT c FROM t; + +ROLLBACK; + +SELECT c FROM t; + +connection default; +disconnect con1; + +DROP TABLE t; + +# CREATE a more complex TABLE +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + x), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, y)), x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(1, 2, DEFAULT, "hhh", 3, DEFAULT, "nnn", DEFAULT, 4, "yyy", 5); + +INSERT INTO t VALUES(2, 3, DEFAULT, "hhha", 4, DEFAULT, "nnna", DEFAULT, 5, "yyya", 6); + +INSERT INTO t VALUES(12, 13, DEFAULT, "hhhb", 14, DEFAULT, "nnnb", DEFAULT, 15, "yyyb", 16); + +# CREATE an INDEX ON multiple virtual COLUMN +CREATE INDEX idx6 ON t(p, c); + +SELECT p, c FROM t; + +START TRANSACTION; +INSERT INTO t VALUES(32, 33, DEFAULT, "hhhb", 34, DEFAULT, "nnnb", DEFAULT, 35, "yyyb", 36); +ROLLBACK; + +UPDATE t SET a = 100 WHERE a = 1; + +START TRANSACTION; +UPDATE t SET a = 1 WHERE a = 100; +ROLLBACK; + +DROP TABLE t; + +CREATE TABLE t1(a INT); +ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual); +ALTER TABLE t1 add COLUMN (d INT generated always as (a+1) virtual, e INT as(5) virtual); + +SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual; + +#--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN +ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT as(5) virtual), DROP COLUMN e; + +SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual; + +DROP TABLE t1; + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1); + +ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT ); + +# Inplace ADD/DROP virtual COLUMNs can only go with their own for +# inplace algorithm, not to combine with other operations, except create index +ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace; + +--enable_info +ALTER TABLE t1 add COLUMN (h1 INT generated always as (a+1) virtual), add INDEX idx1 (h1); +--disable_info + +ALTER TABLE t1 DROP COLUMN h1, DROP INDEX idx; + +DROP TABLE t1; + +# Virtual COLUMN cannot be INDEXed +CREATE TABLE t1(a INT); +CREATE INDEX idx ON t1(a); +CREATE TABLE t3(a INT, b INT , INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a)); +--error ER_CANT_CREATE_TABLE +CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a)); +CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b)); +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1(a INT); +ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual); + +ALTER TABLE t1 change b x INT generated always as (a+1) virtual; + +SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual; + +DROP TABLE t1; + +# We do not support Fulltext or Spatial INDEX ON Virtual Columns +--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN +CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a), fulltext INDEX idx (b)); +CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a)); +--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN +ALTER TABLE t ADD FULLTEXT INDEX (b); +DROP TABLE t; + +--error ER_SPATIAL_CANT_HAVE_NULL +CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a), spatial INDEX idx (b)); +CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a)); +--error ER_SPATIAL_CANT_HAVE_NULL +ALTER TABLE t ADD SPATIAL INDEX (b); +DROP TABLE t; + +#test DEFAULT value +CREATE TABLE t (a INT DEFAULT 1, b INT DEFAULT 2, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); +CREATE INDEX idx ON t(c); +INSERT INTO t(h)VALUES ('mm'); +SELECT c FROM t; + +CREATE unique INDEX idx1 ON t(c); + +--error ER_DUP_ENTRY +INSERT INTO t(h)VALUES ('mm'); + +DROP TABLE t; + +CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, `x` INT(11) NOT NULL, `h` VARCHAR(10) DEFAULT NULL, PRIMARY KEY (`x`), KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm'); +INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm'); + +connect(con1,localhost,root,,test); +UPDATE t1 SET x = 4 WHERE x =3; +DROP TABLE t1; + +CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, `x` INT(11) NOT NULL, `h` VARCHAR(10) DEFAULT NULL, KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm'); +INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm'); + +START TRANSACTION; +SELECT * FROM t1; + +connection con1; +START TRANSACTION; +UPDATE t1 SET x = 15 WHERE x = 3; + +UPDATE t1 SET b = 10 WHERE b=2; +ROLLBACK; + +connection default; +SELECT c FROM t1; + +disconnect con1; + +DROP TABLE t1; + +CREATE TABLE `t` ( + `a` INT(11) DEFAULT NULL, + `b` INT(11) DEFAULT NULL, + `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, + `d` INT(11) GENERATED ALWAYS AS (a) VIRTUAL, + `h` INT(11) NOT NULL, + PRIMARY KEY (`h`), + KEY `idx` (`c`) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, 1); +INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, 2); +INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, 3); +INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, 4); + +delimiter |; +CREATE PROCEDURE UPDATE_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 2000) DO + UPDATE t SET a = 100 + i WHERE h = 1; + SET i = i + 1; + END WHILE; +END| + +CREATE PROCEDURE DELETE_insert_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 2000) DO + UPDATE t SET a = 100 + i WHERE h = 1; + SET i = i + 1; + END WHILE; +END| +delimiter ;| + +CALL UPDATE_t(); +SELECT c FROM t; + +CALL DELETE_insert_t(); +SELECT c FROM t; + +DROP INDEX idx ON t; +CALL UPDATE_t(); +SELECT c FROM t; + +DROP PROCEDURE DELETE_insert_t; +DROP PROCEDURE UPDATE_t; + +DROP TABLE t; + +--echo # Bug#20767937: WL8149:ASSERTION FAILED IN ROW_UPD_SEC_INDEX_ENTRY +CREATE TABLE b ( +col_INT_nokey INTEGER NOT NULL, +col_INT_key INTEGER GENERATED ALWAYS AS (col_INT_nokey) VIRTUAL, +col_date_nokey DATE, +col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey, + INTerval 30 day)) VIRTUAL, + +col_datetime_nokey DATETIME NOT NULL, +col_time_nokey TIME NOT NULL, + +col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, + col_time_nokey)), +col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, + col_time_nokey)), + +col_VARCHAR_nokey VARCHAR(1) NOT NULL, +col_VARCHAR_key VARCHAR(2) GENERATED ALWAYS AS(CONCAT(col_VARCHAR_nokey, + col_VARCHAR_nokey)), + +KEY (col_INT_key), +KEY (col_VARCHAR_key), +KEY (col_date_key), +KEY (col_time_key), +KEY (col_datetime_key), +KEY (col_INT_key, col_VARCHAR_key), +KEY (col_INT_key, col_VARCHAR_key, col_date_key, +col_time_key, col_datetime_key) +); +INSERT INTO b ( + col_INT_nokey, + col_date_nokey, + col_time_nokey, + col_datetime_nokey, + col_VARCHAR_nokey +) VALUES +(0, NULL, '21:22:34.025509', '2002-02-13 17:30:06.013935', 'j'), +(8, '2004-09-18', '10:50:38.059966', '2008-09-27 +00:34:58.026613', 'v'); + +EXPLAIN SELECT col_INT_key FROM b; +SELECT col_INT_key FROM b; +SELECT col_INT_nokey, col_INT_key FROM b; +DELETE FROM b; + +DROP TABLE b; + +let $row_format=COMPACT; +--source inc/innodb_v_large_col.inc + +CREATE TABLE `t` ( + `a` BLOB, + `b` BLOB, + `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `d` BLOB GENERATED ALWAYS AS (b) VIRTUAL, + `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL, + `h` INT(11) NOT NULL, + PRIMARY KEY (`h`) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, DEFAULT, DEFAULT, 1); +INSERT INTO t VALUES (REPEAT('a', 32000), REPEAT('b', 11000), DEFAULT, DEFAULT, DEFAULT, 2); +INSERT INTO t VALUES (REPEAT('m', 18000), REPEAT('n', 46000), DEFAULT, DEFAULT, DEFAULT, 3); + +CREATE INDEX idx ON t(c(100), d(20)); + +UPDATE t SET a = NULL WHERE h=1; + +UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1; + +UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 1000) WHERE h = 1; + +delimiter |; + +CREATE PROCEDURE UPDATE_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 200) DO + UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1; + SET i = i + 1; + END WHILE; +END| + +CREATE PROCEDURE DELETE_insert_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 200) DO + DELETE FROM t WHERE h = 1; + INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1); + SET i = i + 1; + END WHILE; +END| +delimiter ;| + +CALL UPDATE_t(); +CALL DELETE_insert_t(); + +UPDATE t SET a = NULL WHERE h=1; + +DROP PROCEDURE DELETE_insert_t; +DROP PROCEDURE UPDATE_t; +DROP TABLE t; + +CREATE TABLE `t` ( + `m1` INT(11) DEFAULT NULL, + `m2` INT(11) DEFAULT NULL, + `m3` INT(11) GENERATED ALWAYS AS (m1 + m2) VIRTUAL, + `m4` INT(11) DEFAULT NULL, + `m5` CHAR(10) DEFAULT NULL, + `m6` CHAR(12) GENERATED ALWAYS AS (m5) VIRTUAL, + `a` VARCHAR(10000) DEFAULT NULL, + `b` VARCHAR(3000) DEFAULT NULL, + `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL, + `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL, + `h` INT(11) NOT NULL, + PRIMARY KEY (`h`), + KEY `m3` (`m3`), + KEY `c` (`c`(100)), + KEY `e` (`e`,`d`(20)) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (1, 2, DEFAULT, 3, "aaa", DEFAULT, REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1); + +INSERT INTO t VALUES (11, 21, DEFAULT, 31, "bbb", DEFAULT, REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2); + +INSERT INTO t VALUES (21, 31, DEFAULT, 41, "zzz", DEFAULT, REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3); + +ALTER TABLE t DROP COLUMN c; + +DELETE FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +CREATE INDEX idx ON t(a, c); +SELECT a, c FROM t; + +START TRANSACTION; + +UPDATE t SET a = 13 where a = 11; + +ROLLBACK; +DELETE FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), m int); + +INSERT INTO t VALUES (11, 3, DEFAULT, "a", 1); +INSERT INTO t VALUES (18, 1, DEFAULT, "b", 2); +INSERT INTO t VALUES (28, 1, DEFAULT, "c", 3 ); +INSERT INTO t VALUES (null, null, DEFAULT, "d", 4); + +CREATE INDEX idx ON t(a, c, h); +SELECT a, c FROM t; + +START TRANSACTION; +UPDATE t SET m =10 WHERE m = 1; +UPDATE t SET h = "e" WHERE h="a"; +ROLLBACK; +SELECT a, c, h FROM t; + +DROP TABLE t; + +# bug#21065137 - WL8149:FAILING ASSERTION: NAME_OFS < FULL_LEN +CREATE TABLE `t1` ( + `col1` int(11) NOT NULL, + `col2` int(11) NOT NULL, + `col3` int(11) NOT NULL, + `col4` int(11) DEFAULT NULL, + `col5` int(11) GENERATED ALWAYS AS (col2 % col3) VIRTUAL, + `col7` int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL, + `col8` int(11) GENERATED ALWAYS AS (col5 % col5) VIRTUAL, + `col9` text, + `extra` int(11) DEFAULT NULL, + UNIQUE KEY `uidx` (`col5`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE t1 CHANGE COLUMN extra col6 INT; + +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# No spatial and FTS index on virtual columns +--error ER_SPATIAL_CANT_HAVE_NULL +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c point, d point GENERATED ALWAYS AS (c), spatial index idx (d)); + +--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d char(20) GENERATED ALWAYS AS (c), fulltext index idx (d)); + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); +INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); +INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); + +alter table t add x int, add xx int generated ALWAYS AS(x); + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); +INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); +INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); + +ALTER TABLE t DROP COLUMN c, algorithm=inplace; +ALTER TABLE t DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace; + +# This should fail +#ALTER TABLE t ADD x INT, DROP COLUMN m, algorithm=inplace; +SELECT s FROM t; + +ALTER TABLE t ADD x VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), ADD INDEX idx (x), algorithm=inplace; +DROP TABLE t; + +CREATE TABLE `t1` ( + `col1` int(11) DEFAULT NULL, + `col2` int(11) DEFAULT NULL, + `col3` int(11) DEFAULT NULL, + `col4` int(11) DEFAULT NULL, + `col5` int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL, + `col6` int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, + `col7` int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL, + `col8` int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, + `col9` text, + `extra` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE t1 DROP COLUMN col7; + +DROP TABLE t1; + +CREATE TABLE t1 ( + col1 INTEGER NOT NULL, + gv_col INTEGER GENERATED ALWAYS AS (col1) VIRTUAL, + txt1 TEXT, + FULLTEXT INDEX fi(txt1) +); + +select * from t1; + +DROP TABLE t1; + +CREATE TABLE t1 ( + col1 INTEGER NOT NULL, + col2 INTEGER NOT NULL, + col3 INTEGER DEFAULT NULL, + col4 INTEGER DEFAULT NULL, + col5 INTEGER DEFAULT NULL, + col6 INTEGER DEFAULT NULL, + col7 INTEGER DEFAULT NULL, + col8 INTEGER DEFAULT NULL, + col9 INTEGER DEFAULT NULL, + col10 INTEGER DEFAULT NULL, + col11 INTEGER DEFAULT NULL, + col12 INTEGER DEFAULT NULL, + col13 INTEGER DEFAULT NULL, + col14 INTEGER DEFAULT NULL, + col15 INTEGER DEFAULT NULL, + col16 INTEGER DEFAULT NULL, + col17 INTEGER DEFAULT NULL, + col18 INTEGER DEFAULT NULL, + col19 INTEGER DEFAULT NULL, + col20 INTEGER DEFAULT NULL, + col21 INTEGER DEFAULT NULL, + col22 INTEGER DEFAULT NULL, + col23 INTEGER DEFAULT NULL, + col24 INTEGER DEFAULT NULL, + col25 INTEGER DEFAULT NULL, + col26 INTEGER DEFAULT NULL, + col27 INTEGER DEFAULT NULL, + col28 INTEGER DEFAULT NULL, + col29 INTEGER DEFAULT NULL, + col30 INTEGER DEFAULT NULL, + col31 INTEGER DEFAULT NULL, + col32 INTEGER DEFAULT NULL, + col33 INTEGER DEFAULT NULL, + gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL, + KEY idx1 (gcol1) +); + +INSERT INTO t1 (col1, col2) + VALUES (0,1), (1,2), (2,3), (3,4), (4,5); + +SELECT gcol1 FROM t1 FORCE INDEX(idx1); + +ALTER TABLE t1 ADD COLUMN extra INTEGER; + +SELECT gcol1 FROM t1 FORCE INDEX(idx1); + +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT NOT NULL, + store_id INT NOT NULL, + x INT GENERATED ALWAYS AS (id + store_id) +) +PARTITION BY RANGE (store_id) ( + PARTITION p0 VALUES LESS THAN (6), + PARTITION p1 VALUES LESS THAN (11), + PARTITION p2 VALUES LESS THAN (16), + PARTITION p3 VALUES LESS THAN (21) +); + +INSERT INTO t1 VALUES(1, 2, default); +INSERT INTO t1 VALUES(3, 4, default); + +INSERT INTO t1 VALUES(3, 12, default); +INSERT INTO t1 VALUES(4, 18, default); + +CREATE INDEX idx ON t1(x); + +SELECT x FROM t1; + +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT NOT NULL, + store_id INT NOT NULL, + x INT GENERATED ALWAYS AS (id + store_id) +) +PARTITION BY RANGE (x) ( + PARTITION p0 VALUES LESS THAN (6), + PARTITION p1 VALUES LESS THAN (11), + PARTITION p2 VALUES LESS THAN (16), + PARTITION p3 VALUES LESS THAN (21) +); + +insert into t1 values(1, 2, default); +insert into t1 values(3, 4, default); + +insert into t1 values(3, 12, default); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values(4, 18, default); + +CREATE INDEX idx ON t1(x); +SELECT x FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a+1) ,c int) PARTITION BY RANGE (b) ( +PARTITION p0 VALUES LESS THAN (6), +PARTITION p1 VALUES LESS THAN (11), +PARTITION p2 VALUES LESS THAN (16), +PARTITION p3 VALUES LESS THAN (21) ); + +INSERT INTO t1 VALUES (10,DEFAULT,2); +INSERT INTO t1 VALUES (19,DEFAULT,8); + +CREATE INDEX idx ON t1 (b); + +INSERT INTO t1 VALUES (5,DEFAULT,9); + +SELECT * FROM t1; + +ALTER TABLE t1 REMOVE PARTITIONING; + +DROP TABLE t1; + +CREATE TABLE `t#P#1` (a INT, bt INT GENERATED ALWAYS AS (a+1) ,c int) +PARTITION BY RANGE (bt) +subpartition by hash (bt) + ( + PARTITION p0 VALUES LESS THAN (6) ( + SUBPARTITION s0, + SUBPARTITION s1), + PARTITION p1 VALUES LESS THAN (11) ( + SUBPARTITION s2, + SUBPARTITION s3), + PARTITION p2 VALUES LESS THAN (16) ( + SUBPARTITION s4, + SUBPARTITION s5), + PARTITION p3 VALUES LESS THAN (21) ( + SUBPARTITION s6, + SUBPARTITION s7) + ); +insert into `t#P#1` values (10,DEFAULT,2); +insert into `t#P#1` values (19,DEFAULT,8); +create index idx on `t#P#1` (bt); +insert into `t#P#1` values (5,DEFAULT,9); +select * from `t#P#1`; +alter table `t#P#1` remove partitioning; +drop table `t#P#1`; + +let $row_format=DYNAMIC; +--source inc/innodb_v_large_col.inc + +let $row_format=REDUNDANT; +--source inc/innodb_v_large_col.inc + +let $row_format=COMPRESSED; +--source inc/innodb_v_large_col.inc + +# Make sure FTS_DOC_ID for FULLTEXT index set with correct column id with +# virtual columns +CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB; +ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ; +ALTER TABLE t ADD FULLTEXT INDEX (a) ; +ALTER TABLE t ADD INDEX (b(1)) ; + +DROP TABLE t; + +CREATE TABLE t(a TEXT CHARSET UTF8, FULLTEXT INDEX(a))ENGINE=INNODB; +ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ; +ALTER TABLE t ADD INDEX (b(1)) ; +DROP TABLE t; + +# Virtual column cannot be used as DOC ID column for FULLTEXT index +CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB; +ALTER TABLE t ADD COLUMN FTS_DOC_ID BLOB GENERATED ALWAYS AS (a) VIRTUAL ; +--error ER_INNODB_FT_WRONG_DOCID_COLUMN +ALTER TABLE t ADD FULLTEXT INDEX (a) ; +DROP TABLE t; + +# Test uses ICP on column h and d +create table t (a int,b int,c int,d int,e int, +f int generated always as (a+b) virtual, +g int,h blob,i int,unique key (d,h(25))) engine=innodb; + +select h from t where d is null; +drop table t; + +# Test Add virtual column of MySQL long true type +create table t(a blob not null) engine=innodb; +alter table t add column b int; +alter table t add column c varbinary (1000) generated always as (a) virtual; +alter table t add unique index (c(39)); +replace into t set a = 'a',b =1; +replace into t set a = 'a',b =1; +drop table t; + +CREATE TABLE t (a INT, b INT, h VARCHAR(10)); +INSERT INTO t VALUES (12, 3, "ss"); +INSERT INTO t VALUES (13, 4, "ss"); +INSERT INTO t VALUES (14, 0, "ss"); +alter table t add c INT GENERATED ALWAYS AS(a/b); +create index idx on t(c); +DROP TABLE t; + +CREATE TABLE t ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER /*! NULL */, + col_int INT GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) STORED, + col_int_key INTEGER GENERATED ALWAYS AS (col_int + col_int_nokey) VIRTUAL, + col_date_nokey DATE /*! NULL */, + col_date DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) STORED, + col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date,interval 30 day)) VIRTUAL, + col_datetime_nokey DATETIME /*! NULL */, + col_time_nokey TIME /*! NULL */, + col_datetime DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED, + col_time TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED, + col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime, col_time_nokey)) VIRTUAL, + col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time)) VIRTUAL, + col_varchar_nokey VARCHAR(1) /*! NULL */, + col_varchar VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey,col_varchar_nokey)) STORED, + col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar, 'x')) VIRTUAL, + unique KEY (pk,col_int_key), + KEY(col_int), + KEY(col_date), + KEY(col_datetime), + KEY(col_time), + KEY(col_varchar), + UNIQUE KEY (col_int_key), + KEY (col_time_key), + KEY (col_datetime_key), + UNIQUE KEY (col_int_key, col_varchar_key), + KEY (col_int_key, col_int_nokey), + KEY(col_int_key,col_date_key), + KEY(col_int_key, col_time_key), + KEY(col_int_key, col_datetime_key), + KEY(col_date_key,col_time_key,col_datetime_key), + KEY (col_varchar_key, col_varchar_nokey), + UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key) +) AUTO_INCREMENT=10 ENGINE=INNODB PARTITION BY KEY(col_int_key) PARTITIONS 3; + +ALTER TABLE t DROP COLUMN `pk`; + +SHOW CREATE TABLE t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t ADD COLUMN xs INT GENERATED ALWAYS AS(a+b), ADD COLUMN mm INT +GENERATED ALWAYS AS(a+b) STORED, ALGORITHM = INPLACE; + +ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ALGORITHM = INPLACE; + +ALTER TABLE t DROP COLUMN x, ALGORITHM = INPLACE; + +ALTER TABLE t ADD COLUMN x1 INT GENERATED ALWAYS AS(a+b), DROP COLUMN c, +ALGORITHM = INPLACE; + +DROP TABLE t; + +if (0) { +# Some test on virtual/stored column numbering for spatial indexing +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` POINT GENERATED ALWAYS AS (1) STORED +) ENGINE=INNODB; +ALTER TABLE t ADD SPATIAL INDEX (`e`); +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` POINT GENERATED ALWAYS AS (1) STORED, + SPATIAL INDEX (`e`) +) ENGINE=INNODB; +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e2` POINT GENERATED ALWAYS AS (1) STORED, + `e` POINT GENERATED ALWAYS AS (1) STORED +) ENGINE=INNODB; +ALTER TABLE t ADD SPATIAL INDEX (`e`); +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e2` POINT GENERATED ALWAYS AS (1) STORED, + `d2` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` POINT GENERATED ALWAYS AS (1) STORED +) ENGINE=INNODB; +ALTER TABLE t ADD SPATIAL INDEX (`e`); +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e2` POINT GENERATED ALWAYS AS (1) STORED, + `d2` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` int +) ENGINE=INNODB; +ALTER TABLE t ADD INDEX (`e`); +DROP TABLE t; +} + +CREATE TABLE t (a INT GENERATED ALWAYS AS(1) VIRTUAL,KEY(a)) ENGINE=INNODB; +INSERT INTO t VALUES(default); +SELECT a FROM t FOR UPDATE; +DROP TABLE t; + +# Test add virtual column and add index at the same time +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); + +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); + +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); + +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +--enable_info +ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x); +--disable_info + +SELECT x FROM t; + +DROP TABLE t; + +CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t1 VALUES (11, 3, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm'); + +ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE; + +DROP TABLE t1 ; + +# Check ALTER TABLE CHANGE VIRTUAL COLUMN TYPE and ORDER +CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t1 VALUES (11, 3, DEFAULT, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (18, 1, DEFAULT, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (28, 1, DEFAULT, DEFAULT, 'mm'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 CHANGE d d INT GENERATED ALWAYS AS(a+b) FIRST, ALGORITHM = INPLACE; + +# Change column type is not allow for inplace also +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 CHANGE d d VARCHAR(10) GENERATED ALWAYS AS(h), ALGORITHM = INPLACE; + +ALTER TABLE t1 CHANGE d d INT GENERATED ALWAYS AS(a+b) FIRST; + +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +# Test foreign key which could be a base column of indexed virtual column +CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=INNODB; + +CREATE TABLE child ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + FOREIGN KEY (parent_id) + REFERENCES parent(id) + ON DELETE CASCADE +) ENGINE=INNODB; + +ALTER TABLE child ADD INDEX `i1` (x); + +# If foreign constrain does not have cascade clause, or with "no action" clause +# the index can still be created +CREATE TABLE child_1 ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + FOREIGN KEY (parent_id) + REFERENCES parent(id) +) ENGINE=INNODB; + +# This should be successful +ALTER TABLE child_1 ADD INDEX `i1` (x); + +DROP TABLE child_1; + +DROP TABLE child; + +CREATE TABLE child ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + INDEX i1 (x), + + FOREIGN KEY (parent_id) + REFERENCES parent(id) + ON DELETE CASCADE +) ENGINE=INNODB; + +DROP TABLE child; + +CREATE TABLE child ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + INDEX `i1` (x) +) ENGINE=INNODB; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON DELETE CASCADE; + +# Check inplace option +SET foreign_key_checks = 0; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON DELETE CASCADE; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON DELETE SET NULL; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON UPDATE CASCADE; + +# this should be successful +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id); + +SET foreign_key_checks = 1; + +DROP TABLE child; + +DROP TABLE parent; + +# Test for Bug 21890816 - ASSERT UPDATE->OLD_VROW, VIRTUAL COLUMNS +CREATE TABLE `ibstd_16` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`) +) ENGINE=INNODB; + +# Block when FK constraint on base column of stored column. +#--error ER_CANNOT_ADD_FOREIGN +CREATE TABLE `ibstd_16_fk` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`), + CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL +) ENGINE=InnoDB; +DROP TABLE ibstd_16_fk; + +# Take out "KEY `a_2` (`a`,`vbidxcol`)", this should then be successful +CREATE TABLE `ibstd_16_fk` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`), + CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL +) ENGINE=InnoDB; + +ALTER TABLE ibstd_16_fk ADD INDEX `a_2` (`a`,`vbidxcol`); + +# Now try to add a table with virtual index, and then add constraint +DROP TABLE ibstd_16_fk; + +# Create a table without constraint +CREATE TABLE `ibstd_16_fk` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`) +) ENGINE=InnoDB; + +ALTER TABLE `ibstd_16_fk` ADD CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL; + +# DROP the index +DROP INDEX a_2 ON ibstd_16_fk; + +INSERT INTO ibstd_16 VALUES (1, 2, "aaa", "bbb", default, default, default); +INSERT INTO ibstd_16_fk VALUES(1, 3, "mmm", "SSS", default, default); + +# Cascading delete/update on column non-related to virtual column or virtual +# index will be fine +DELETE FROM ibstd_16 WHERE a = 1; + +DROP TABLE ibstd_16_fk; +DROP TABLE ibstd_16; + +# Bug 21941320 - GCOLS: FAILING ASSERTION: N_IDX > 0 +create table t(a int) engine=innodb; +insert into t set a=1; +alter table t add column c int generated always as (1) virtual; +insert into t set a=2; + +# Following will cause create index fail, we need to make sure the column +# ord_part is reset +--error ER_DUP_ENTRY +alter table t add unique index(c); +insert into t set a=1; +drop table t; + +# Bug 21875974 - VCOL : READ OF FREED MEMORY IN DTUPLE_GET_N_FIELDS +# CAUSE CRASH + +create table t ( + x int, + a int generated always as (x) virtual, + b int generated always as (1) stored, + c int not null, + unique (b), + unique (a,b) +) engine=innodb; + +insert into t(x, c) values(1, 3); + +# This will exercise row_vers_impl_x_locked_low() for virtual columns +replace into t(x, c) values(1, 0); + +drop table t; + +# Bug22123674 VCOL:INNODB: FAILING ASSERTION: !UT_STRCMP(NAME, +# FIELD->FIELD_NAME) + +CREATE TABLE t( +c7c CHAR(1)GENERATED ALWAYS AS (c3) VIRTUAL, +c1 int(1), +c2 int(1), +c3 int(1), +c4 int(1), +c5 int(1)GENERATED ALWAYS AS ((c2 - c4)) VIRTUAL, +UNIQUE KEY c5_9(c5) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t CHANGE COLUMN c5 c5 INT(1) GENERATED ALWAYS AS(c2 - +c4)VIRTUAL AFTER c3,ALGORITHM=INPLACE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t CHANGE COLUMN c7c c7c INT(1) GENERATED ALWAYS AS(c3) +VIRTUAL AFTER c5,ALGORITHM=INPLACE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t DROP COLUMN c7c,ADD COLUMN c5c INT GENERATED ALWAYS AS(c4/ +c3)VIRTUAL AFTER c3,ALGORITHM=INPLACE; + +DROP TABLE t; + +# Bug22111464 VCOL:INNODB: FAILING ASSERTION: I < TABLE->N_DEF + +CREATE TABLE `t` ( + `col1` int(11) DEFAULT NULL, + `col2` int(11) DEFAULT NULL, + `col4` int(11) DEFAULT NULL, + `col5` int(11) GENERATED ALWAYS AS ((`col2` % `col4`)) VIRTUAL, + `col6` int(11) GENERATED ALWAYS AS ((`col2` - `col2`)) VIRTUAL, + `col5x` int(11) GENERATED ALWAYS AS ((`col1` / `col1`)) VIRTUAL, + `col6x` int(11) GENERATED ALWAYS AS ((`col2` / `col4`)) VIRTUAL, + `col7x` int(11) GENERATED ALWAYS AS ((`col6` % `col6x`)) VIRTUAL, + `col8x` int(11) GENERATED ALWAYS AS ((`col6` / `col6`)) VIRTUAL, + `col9` text, + `col7c` int(11) GENERATED ALWAYS AS ((`col6x` % `col6x`)) VIRTUAL, + `col1b` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL, + `col3` int(11) DEFAULT NULL, + `col7` int(11) DEFAULT NULL, + `col5c` int(11) GENERATED ALWAYS AS ((`col5x` * `col6`)) VIRTUAL, + `col6c` varchar(20) GENERATED ALWAYS AS (`col5x`) VIRTUAL, + `col3b` bigint(20) GENERATED ALWAYS AS ((`col6x` * `col6`)) VIRTUAL, + `col1a` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL, + `col8` int(11) DEFAULT NULL, + UNIQUE KEY `col5` (`col5`), + UNIQUE KEY `col6x` (`col6x`), + UNIQUE KEY `col5_2` (`col5`), + KEY `idx2` (`col9`(10)), + KEY `idx4` (`col2`), + KEY `idx8` (`col9`(10),`col5`), + KEY `idx9` (`col6`), + KEY `idx6` (`col6`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE t CHANGE COLUMN col3b col8a BIGINT GENERATED ALWAYS AS +(col6x * col6) VIRTUAL, ADD UNIQUE KEY uidx ( col8a ); + +DROP TABLE t; + +--echo # +--echo # Bug 22141031 - GCOLS: PURGED THREAD DIES: TRIED TO PURGE +--echo # NON-DELETE-MARKED RECORD IN INDEX +--echo # +create table t ( + a int,b int,c text,d int,e int,f int,g int, + h text generated always as ('1') virtual, + i int,j int,k int,l int,m int, + primary key (c(1)),unique key (c(1)), + key (i),key (h(1)) +) engine=innodb default charset latin1; + +replace into t(c) values (''); +replace into t(c) values (''); +alter table t drop column d ; + +drop table t; + +--echo # +--echo # Bug 22139917 - ASSERTION: DICT_TABLE_GET_NTH_COL(USER_TABLE, NTH_COL) +--echo # ->LEN < NEW_LEN +--echo # + +create table t ( + a int generated always as (1) virtual, + b varbinary(1), + c varbinary(1) generated always as (b) virtual +) engine=innodb; +alter table t change column b b varbinary(2), algorithm=inplace; +alter table t change column c c varbinary(2) generated always as (b) virtual, algorithm=inplace; + +drop table t; + +# Bug22202788 GCOL:ASSERTION:0 IN ROW_SEL_GET_CLUST_REC_FOR_MYSQL AT +# ROW0SEL.CC +SET @@SESSION.sql_mode=0; + +CREATE TABLE t( + c1 INT AUTO_INCREMENT, + c2 INT, + c3 INT GENERATED ALWAYS AS(c2 + c2)VIRTUAL, + c3k INT GENERATED ALWAYS AS(c2 + c3)VIRTUAL, + c4 DATE, + c5 DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL, + c5k DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL, + c5time_gckey DATE, + c6 TIME, + c5time DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL, + c7 TIME GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL, + c5timek DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c7)) VIRTUAL, + c7k TIME GENERATED ALWAYS AS(ADDTIME(c5time,c6)) VIRTUAL, + c8 CHAR(10), + c9 CHAR(20)GENERATED ALWAYS AS (CONCAT(c8,c8)) VIRTUAL, + c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(c8,0)) VIRTUAL, + PRIMARY KEY(c1), + KEY(c3), + KEY(c9(10)), + UNIQUE KEY(c9k), + UNIQUE KEY(c3k,c9k(5),c5k,c7k,c5timek,c3,c9(5),c5,c7,c5time) +)ENGINE=INNODB; + +--error ER_DUP_ENTRY +INSERT INTO +t(c2,c4,c6,c5time_gckey,c8)VALUES(1,0,0,0,0),(0,0,0,0,'ityzg'),(0,0,1,0,'tyzgk +t'),(0,1,0,1,'yzgktb'),(0,0,0,0,'zgktb'),(0,0,0,0,'gktbkj'),(0,0,0,0,0),(0,0,1 +,0,1),(0,0,0,0,1),(0,0,0,0,'tbkjrkm'),(0,0,0,0,'bkjr'),(0,0,0,0,0),(0,0,0,0,0) +,(0,0,0,0,'rk'),(0,0,0,0,'kmqmknbtoe'),(1,0,0,0,'mqmknbt'),(0,1,0,0,'qmknb'),( +0,0,0,0,'mkn'),(0,0,0,0,'knbtoervql'),(0,0,1,0,1),(0,0,0,0,'nbtoerv'),(0,0,0,0 +,'btoerv'),(0,0,1,0,'toer'),(1,0,0,0,0),(0,0,0,0,'ervq'),(0,0,0,0,'rvqlzsvasu' +),(0,0,0,0,'vqlzs'),(0,0,0,0,0),(0,1,0,0,'lzsvasu'),(0,0,0,0,'zsvasurq'); + +SELECT +DISTINCT * FROM t +FORCE KEY(PRIMARY,c3k,c3,c9k,c9) +WHERE +(c9 IS NULL AND (c9=0)) +OR( +(c9k NOT IN ('ixfq','xfq','New Mexico','fq')OR c9 IS NULL) +) +OR(c9 BETWEEN 'hwstqua' AND 'wstquadcji' OR (c9k=0)) +AND(c3 IS NULL OR c3 IN (0,0,0)); + +drop table t; + +# +# BUG#22082762 RE-ENABLE SUPPORT FOR ADDING VIRTUAL INDEX WHILE DROPPING VIRTUAL COLUMN +# + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT +GENERATED ALWAYS AS(a+b+b), e INT GENERATED ALWAYS AS(a), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, DEFAULT, 'mm'); +CREATE INDEX idx ON t(c, d); +CREATE INDEX idx1 ON t(c); +CREATE INDEX idx2 ON t(e, c, d); + +# This will drop column c, drop index idx1 on column c, and build index +# idx and idx2, so they become idx(d) and idx2(e, d) respectively. +ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE; + +SELECT d FROM t; + +SHOW CREATE TABLE t; + +# Drop a column, adding a new column and also adding a index on this new column +# is not allowed for INPLACE algorithm +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE; + +# Add an index on existing column along with dropping a column is allowed +ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (e), ALGORITHM=INPLACE, LOCK=NONE; +SHOW CREATE TABLE t; + +# Add an index on existing column along with adding a virtual column and droping a virtual index +ALTER TABLE t ADD INDEX idx4(c, e), ADD COLUMN x VARCHAR(10) GENERATED ALWAYS AS(h), DROP INDEX idx, ALGORITHM=INPLACE, LOCK=NONE; +SHOW CREATE TABLE t; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD COLUMN j INT, ALGORITHM=INPLACE; + +# Add an index along with adding a regular column is allowed. +ALTER TABLE t ADD INDEX (x), ADD COLUMN j INT, ALGORITHM=INPLACE, LOCK=NONE; +SHOW CREATE TABLE t; + +# Online add an index on newly added virtual column is not allowed. +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (i), ALGORITHM=INPLACE, LOCK=NONE; + +ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (i), ALGORITHM=INPLACE, LOCK=SHARED; +SHOW CREATE TABLE t; + +SELECT i FROM t; + +SELECT * FROM t; + +DROP TABLE t; + +# +# BUG#22469459 WRONG VALUES IN ADDED INDEX WHILE DROPPING VIRTUAL COLUMN +# + +# Drop column with existing index on it. +CREATE TABLE t ( + a INT, + b INT, + c INT GENERATED ALWAYS AS(a+b), + d INT GENERATED ALWAYS AS(a+b+b), + KEY vidx (c, d) +)ENGINE=INNODB; + +INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL); + +SELECT c, d FROM t; + +SELECT * FROM t; + +ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE; + +SELECT d FROM t; + +SELECT * FROM t; + +DROP TABLE t; + +# Drop column with a new index. +CREATE TABLE t ( + a INT, + b INT, + c INT GENERATED ALWAYS AS(a+b), + d INT GENERATED ALWAYS AS(a+b+b) +)ENGINE=INNODB; + +INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL); + +SELECT * FROM t; + +ALTER TABLE t DROP COLUMN c, ADD INDEX vidx(d), ALGORITHM=INPLACE; + +SELECT d FROM t; + +SELECT * FROM t; + +DROP TABLE t; + +--echo # +--echo # Bug #22162200 MEMORY LEAK IN HA_INNOPART_SHARE +--echo # ::SET_V_TEMPL PARTITIONED ON VIRTUAL COLUMN +--echo # +create table t ( + c tinyint, + d longblob generated always as (c) virtual +) engine=innodb partition by key (c) partitions 2; + +select d in(select d from t)from t group by d; +drop table t; + +--echo # +--echo # BUG#23052231 - ASSERTION FAILURE: ROW0MERGE.CC:2100:ADD_AUTOINC +--echo # < DICT_TABLE_GET_N_USER_COLS +--echo # +CREATE TABLE `t` ( + `a` int(11) NOT NULL, + `d` int(11) NOT NULL, + `b` varchar(198) NOT NULL, + `c` char(177) DEFAULT NULL, + `vadcol` int(11) GENERATED ALWAYS AS ((`a` + length(`d`))) STORED, + `vbcol` char(2) GENERATED ALWAYS AS (substr(`b`,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL, + PRIMARY KEY (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c_renamed` (`c`(99),`b`(35)), + KEY `b` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t values (11, 1, "11", "aa", default, default, default); + +ALTER TABLE t ADD COLUMN nc01128 BIGINT AUTO_INCREMENT NOT NULL, ADD KEY auto_nc01128(nc01128); + +DROP TABLE t; + +--echo # +--echo #Bug #22965271 NEEDS REBUILD FOR COLUMN LENGTH CHANGE THAT IS +--echo #PART OF VIRTUAL INDEX. +--echo # + +CREATE TABLE t1( +a VARCHAR(45) CHARACTER SET LATIN1, +b VARCHAR(115) CHARACTER SET UTF8 GENERATED ALWAYS AS ('f1') VIRTUAL, +UNIQUE KEY (b,a))ENGINE=INNODB; +INSERT INTO t1(a) VALUES (''); +ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(85); +SELECT * FROM t1; +DROP TABLE t1; |