mirror of
https://github.com/MariaDB/server.git
synced 2025-11-03 20:36:16 +01:00
1481 lines
43 KiB
Text
1481 lines
43 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_partition.inc
|
|
--source include/not_embedded.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;
|
|
|
|
let MYSQLD_DATADIR=`select @@datadir`;
|
|
let PAGE_SIZE=`select @@innodb_page_size`;
|
|
--source include/shutdown_mysqld.inc
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/include/crc32.pl";
|
|
my $file = "$ENV{MYSQLD_DATADIR}/ibdata1";
|
|
open(FILE, "+<$file") || die "Unable to open $file";
|
|
binmode FILE;
|
|
my $ps= $ENV{PAGE_SIZE};
|
|
my $page;
|
|
die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps;
|
|
my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS
|
|
sysseek(FILE, 7*$ps, 0) || die "Unable to seek $file\n";
|
|
die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps;
|
|
substr($page,54,4)=pack("N",0xc001cafe); # 32 MSB of 64-bit DICT_HDR_INDEX_ID
|
|
my $polynomial = 0x82f63b78; # CRC-32C
|
|
if ($full_crc32)
|
|
{
|
|
my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial);
|
|
substr($page, $ps-4, 4) = pack("N", $ck);
|
|
}
|
|
else
|
|
{
|
|
my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^
|
|
mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial));
|
|
substr($page,0,4)=$ck;
|
|
substr($page,$ps-8,4)=$ck;
|
|
}
|
|
sysseek(FILE, 7*$ps, 0) || die "Unable to rewind $file\n";
|
|
syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n";
|
|
close(FILE) || die "Unable to close $file";
|
|
EOF
|
|
--source include/start_mysqld.inc
|
|
set default_storage_engine=innodb;
|
|
|
|
/* 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, RTRIM(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_GENERATED_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_GENERATED_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_GENERATED_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_GENERATED_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;
|
|
--disable_query_log
|
|
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
--source inc/innodb_v_large_col.inc
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
|
|
--enable_query_log
|
|
|
|
# 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, 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');
|
|
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE;
|
|
ALTER TABLE t1 ADD INDEX idx12 (c), LOCK=NONE;
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE;
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), 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_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
|
|
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;
|
|
|
|
# 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(RTRIM(c8),RTRIM(c8))) VIRTUAL,
|
|
c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(RTRIM(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;
|
|
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t ADD INDEX (x), ADD COLUMN j INT, ALGORITHM=INPLACE, LOCK=NONE;
|
|
ALTER TABLE t ADD INDEX (x), ADD COLUMN j INT, ALGORITHM=INPLACE;
|
|
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 WHERE d > 0;
|
|
|
|
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;
|
|
|
|
CREATE TABLE t1 (a INT GENERATED ALWAYS AS(1) VIRTUAL) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD b INT GENERATED ALWAYS AS (2) VIRTUAL;
|
|
ALTER TABLE t1 ADD c INT;
|
|
SELECT * FROM t1;
|
|
INSERT INTO t1 SET c=3;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|