mirror of
https://github.com/MariaDB/server.git
synced 2025-04-10 01:05:48 +02:00
128356b4b1
See also commits aa8a31da and 64678c for a Bug #22990029 fix.
In this scenario INSERT chose to check if delete unmarking is available for
a just deleted record. To build an update vector, it needed to calculate
the vcols as well. Since this INSERT was not IGNORE-flagged, recalculation
failed.
Solutiuon: temporarily set abort_on_warning=true, while calculating the
column for delete-unmarked insert.
384 lines
12 KiB
Text
384 lines
12 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
SET default_storage_engine= innodb;
|
|
|
|
# Ensure that the history list length will actually be decremented by purge.
|
|
SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
|
|
SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;
|
|
|
|
--echo #
|
|
--echo # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
|
|
--echo # THE NUMBER OF VIRTUAL COLUMNS
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT
|
|
GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4
|
|
INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL,
|
|
a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a)
|
|
VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS
|
|
(a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ;
|
|
|
|
INSERT INTO t1(a) VALUES(10);
|
|
|
|
SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND
|
|
a6=10 AND a7=10 AND a8=10 AND a9=10;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS(
|
|
--echo # OFFSETS)
|
|
--echo #
|
|
SET @@SESSION.sql_mode=0;
|
|
|
|
CREATE TABLE t1(
|
|
c1 int(1)AUTO_INCREMENT,
|
|
c2 int(1),
|
|
c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
|
|
c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
|
|
c5 date,
|
|
c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
|
|
c7 DATE,
|
|
c8 time,
|
|
c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
|
|
c12 CHAR(1),
|
|
c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
|
|
c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
|
|
PRIMARY KEY(c1),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE t2(
|
|
c1 int(1)AUTO_INCREMENT,
|
|
c2 int(1),
|
|
c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
|
|
c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
|
|
c5 date,
|
|
c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
|
|
c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL,
|
|
c7 DATE,
|
|
c8 time,
|
|
c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
|
|
c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL,
|
|
c12 CHAR(1),
|
|
c13 CHAR(2)GENERATED ALWAYS AS (concat(RTRIM(c12),RTRIM(c12))) VIRTUAL,
|
|
c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
|
|
PRIMARY KEY(c1),
|
|
KEY c13(c13),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v');
|
|
|
|
CREATE TABLE t3(
|
|
c1 int(1)AUTO_INCREMENT,
|
|
c2 int(1),
|
|
c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
|
|
c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
|
|
c5 date,
|
|
c7 DATE,
|
|
c8 time,
|
|
c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
|
|
c12 CHAR(1),
|
|
PRIMARY KEY(c1),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES
|
|
(0,0,0,0,0,'q'),(0,0,0,0,0,'g'),(0,0,0,0,0,'l'),(0,0,0,0,0,1),(0,0,0,0,0,'v'),
|
|
(0,1,0,0,0,'c'),(0,0,0,0,0,'x');
|
|
|
|
UPDATE
|
|
t2 AS O1,t3 AS O2
|
|
SET O1.c12=1
|
|
WHERE O1.c14 NOT IN
|
|
(
|
|
SELECT
|
|
DISTINCT I1.c14 AS y
|
|
FROM t1 AS I1
|
|
ORDER BY I1.c14);
|
|
|
|
SET @@SESSION.sql_mode=default;
|
|
--source ../../innodb/include/wait_all_purged.inc
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
--echo #
|
|
--echo # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
|
|
--echo #
|
|
CREATE TABLE `ibstd_08` (
|
|
`nc00577` tinyint(4) DEFAULT NULL,
|
|
`nc07844` varchar(41) DEFAULT NULL,
|
|
`gc01908` point NOT NULL,
|
|
`nc04156` char(17) DEFAULT NULL,
|
|
`nc09536` longblob NOT NULL,
|
|
`nc09231` decimal(10,0) NOT NULL,
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(198) NOT NULL,
|
|
`nc04560` mediumtext,
|
|
`c` char(187) DEFAULT NULL,
|
|
`vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
|
|
`gc00881` polygon NOT NULL,
|
|
`nc05121` int(11) NOT NULL DEFAULT '85941481',
|
|
KEY `a` (`a`),
|
|
KEY `b` (`b`(3),`a`),
|
|
KEY `c` (`c`(99),`b`(25)),
|
|
KEY `b_2` (`b`(5),`c`(10),`a`),
|
|
KEY `vbidxcol` (`vbidxcol`),
|
|
KEY `a_2` (`a`,`vbidxcol`),
|
|
KEY `vbidxcol_2` (`vbidxcol`),
|
|
FULLTEXT KEY `ftsic` (`c`,`b`)
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
ALTER TABLE ibstd_08 ADD COLUMN nc07006 BIGINT AUTO_INCREMENT NOT NULL , ADD KEY auto_nc07006(nc07006);
|
|
|
|
DROP TABLE ibstd_08;
|
|
|
|
--echo #
|
|
--echo # Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256)
|
|
--echo # AND SEGFAULT
|
|
--echo #
|
|
set sql_mode="";
|
|
create table t (a int) engine=innodb;
|
|
create table s (
|
|
b int generated always as (1) virtual,
|
|
c int,
|
|
d int generated always as (1) virtual,
|
|
key (d)
|
|
) engine=innodb;
|
|
|
|
insert into t(a) values ((select d from s for update));
|
|
insert into s(c) values ('');
|
|
|
|
SET sql_mode = default;
|
|
drop table if exists t,s;
|
|
|
|
--echo #
|
|
--echo # Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V
|
|
--echo #
|
|
CREATE TABLE t1 (
|
|
col1 int(11) NOT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) NOT NULL,
|
|
col4 int(11) DEFAULT NULL,
|
|
col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL,
|
|
col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
|
|
col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL,
|
|
col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL,
|
|
col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL,
|
|
col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL,
|
|
col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
|
|
col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL,
|
|
col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL,
|
|
col9 text,
|
|
col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
|
|
col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL,
|
|
col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
|
|
col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL,
|
|
extra int(11) DEFAULT NULL,
|
|
col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL,
|
|
col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL,
|
|
col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL,
|
|
col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL,
|
|
col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
|
|
col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
|
|
col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL,
|
|
col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL,
|
|
UNIQUE KEY idx7 (col1,col3,col2),
|
|
UNIQUE KEY uidx (col9(10)),
|
|
KEY idx15 (col9(10) DESC,col2 DESC),
|
|
KEY idx10 (col9(10) DESC,col1 DESC),
|
|
KEY idx11 (col6x DESC),
|
|
KEY idx6 (col9(10) DESC,col7 DESC),
|
|
KEY idx14 (col6 DESC)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
# Cannot add virtual column along with create FULLTEXT index with
|
|
# adding a hidden FTS_DOC_ID column (which require a table rebuild)
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
|
|
VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
|
|
|
|
# This will add a hidden FTS_DOC_ID column
|
|
CREATE FULLTEXT INDEX idx ON t1(col9);
|
|
|
|
# Since there is no table rebuild needed, now the alter would be sucessful
|
|
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
|
|
VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (
|
|
col1 int(11) NOT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) NOT NULL,
|
|
col4 int(11) DEFAULT NULL) engine=innodb;
|
|
|
|
# This secondary key idx will be coverted to a new Primary Key, thus a table
|
|
# rebuild. It is blocked since there is an adding of virtual columns
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
|
|
VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
|
|
|
|
DROP TABLE t1;
|
|
SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
|
|
|
|
--echo #
|
|
--echo # Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7
|
|
--echo #
|
|
CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2);
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #27968952 INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
|
|
--echo #
|
|
|
|
CREATE TABLE t1(
|
|
a INT NOT NULL UNIQUE,
|
|
b INT NOT NULL,
|
|
c TEXT GENERATED ALWAYS AS (a <> b) VIRTUAL,
|
|
d TEXT NOT NULL,
|
|
UNIQUE KEY (c(1)), KEY(d(1))
|
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
SET @t = REPEAT('t',@@innodb_page_size);
|
|
INSERT INTO t1 (a,b,d) VALUES (1,0,@t), (0,0,@t);
|
|
--error ER_DUP_ENTRY
|
|
UPDATE t1 SET b = a;
|
|
REPLACE INTO t1 SET a = 0, b = 1, d = 'd';
|
|
SELECT * FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
|
|
b VARCHAR(1000) NOT NULL,
|
|
c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED,
|
|
KEY (b(1)),
|
|
KEY (a(1))
|
|
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
|
|
INSERT INTO t1(b) VALUES(REPEAT('b',1000));
|
|
DELETE FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #22990029 GCOLS: INCORRECT BEHAVIOR
|
|
--echo # AFTER DATA INSERTED WITH IGNORE KEYWORD
|
|
--echo #
|
|
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT, vb DATE AS(b) VIRTUAL, KEY(vb))
|
|
ENGINE=InnoDB;
|
|
INSERT IGNORE INTO t1 (a,b) VALUES(1,20190132);
|
|
SELECT * FROM t1;
|
|
BEGIN;
|
|
DELETE FROM t1;
|
|
INSERT INTO t1 (a,b) VALUES(1,20190123);
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
SELECT * FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-25872 InnoDB: Assertion failure in row_merge_read_clustered_index
|
|
--echo # upon ALTER on table with indexed virtual columns
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
a INT,
|
|
va INT ZEROFILL AS (a) VIRTUAL,
|
|
b TIMESTAMP,
|
|
c CHAR(204),
|
|
vc CHAR(8),
|
|
KEY(vc,c(64),b,va)
|
|
) ENGINE=InnoDB CHARACTER SET utf32;
|
|
INSERT INTO t1 (id) SELECT NULL FROM seq_1_to_75;
|
|
INSERT IGNORE INTO t1 (id, a) VALUES (NULL, -1);
|
|
--error ER_WARN_DATA_OUT_OF_RANGE
|
|
ALTER TABLE t1 FORCE;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-24713 Assertion `dict_table_is_comp(index->table)' failed
|
|
--echo # in row_merge_buf_add()
|
|
--echo #
|
|
CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(3),
|
|
b CHAR(8) AS (a) VIRTUAL, KEY(b))
|
|
ROW_FORMAT=REDUNDANT ENGINE=InnoDB
|
|
CHARACTER SET utf8;
|
|
INSERT INTO t1 (id,a) VALUES (1,'foo');
|
|
OPTIMIZE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-20154 Assertion `len <= col->len || ((col->mtype) == 5
|
|
--echo # || (col->mtype) == 14)' failed in row_merge_buf_add
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
a VARCHAR(2500),
|
|
b VARCHAR(2499) AS (a) VIRTUAL
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 (a) VALUES ('foo');
|
|
|
|
ALTER TABLE t1 MODIFY a VARCHAR(2600), ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE;
|
|
|
|
--echo # Cleanup
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of 10.2 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-29299 SELECT from table with vcol index reports warning
|
|
--echo #
|
|
|
|
CREATE TABLE t(fld1 INT NOT NULL,
|
|
fld2 INT AS (100/fld1) VIRTUAL,
|
|
KEY(fld1), KEY(fld2));
|
|
CREATE TABLE t_odd(id int);
|
|
INSERT INTO t(fld1) VALUES(1), (2);
|
|
|
|
--connect stop_purge,localhost,root
|
|
# This prevents purge for records in t
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
INSERT INTO t_odd VALUES(10000);
|
|
|
|
--connection default
|
|
UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2;
|
|
UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3;
|
|
UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4;
|
|
SELECT fld2 FROM t FORCE INDEX(fld2);
|
|
SELECT fld2 FROM t FORCE INDEX(fld1);
|
|
|
|
--disconnect stop_purge
|
|
DROP TABLE t, t_odd;
|
|
|
|
--echo #
|
|
--echo # MDEV-29753 An error is wrongly reported during INSERT with vcol index
|
|
--echo # See also Bug #22990029
|
|
--echo #
|
|
|
|
CREATE TABLE t(pk INT PRIMARY KEY,
|
|
fld1 INT NOT NULL,
|
|
fld2 INT AS (100/fld1) VIRTUAL,
|
|
KEY(fld1), KEY(fld2));
|
|
INSERT IGNORE t(pk, fld1) VALUES(1, 0);
|
|
SELECT * FROM t;
|
|
BEGIN;
|
|
DELETE FROM t;
|
|
INSERT INTO t (pk, fld1) VALUES(1,1);
|
|
SELECT * FROM t;
|
|
|
|
--echo # Cleanup
|
|
ROLLBACK;
|
|
DROP TABLE t;
|
|
|
|
--echo # End of 10.3 tests
|