mirror of
https://github.com/MariaDB/server.git
synced 2025-07-05 19:08:12 +02:00
1cae1af6f9
* 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
220 lines
7.2 KiB
Text
220 lines
7.2 KiB
Text
--source include/have_innodb.inc
|
|
|
|
--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(c12,c12)) VIRTUAL,
|
|
c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
|
|
PRIMARY KEY(c1),
|
|
KEY c13(c13),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
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;
|
|
|
|
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;
|