Debian Codegraph @

mysql-test/suite/innodb/t/virtual_index.test

raw
1 --source include/have_innodb_max_16k.inc
2 --echo #
3 --echo # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
4 --echo # THE NUMBER OF VIRTUAL COLUMNS
5 --echo #
6
7 CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT
8 GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4
9 INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL,
10 a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a)
11 VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS
12 (a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ;
13
14 INSERT INTO t1(a) VALUES(10);
15
16 SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND
17 a6=10 AND a7=10 AND a8=10 AND a9=10;
18
19 DROP TABLE t1;
20
21 --echo #
22 --echo # Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS(
23 --echo # OFFSETS)
24 --echo #
25 SET @@SESSION.sql_mode=0;
26
27 CREATE TABLE t1(
28 c1 int(1)AUTO_INCREMENT,
29 c2 int(1),
30 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
31 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
32 c5 date,
33 c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
34 c7 DATE,
35 c8 time,
36 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
37 c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
38 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
39 c12 CHAR(1),
40 c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
41 c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
42 PRIMARY KEY(c1),
43 KEY c4_6(c4,c11)
44 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
45
46 CREATE TABLE t2(
47 c1 int(1)AUTO_INCREMENT,
48 c2 int(1),
49 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
50 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
51 c5 date,
52 c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
53 c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL,
54 c7 DATE,
55 c8 time,
56 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
57 c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
58 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
59 c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL,
60 c12 CHAR(1),
61 c13 CHAR(2)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
62 c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
63 PRIMARY KEY(c1),
64 KEY c13(c13),
65 KEY c4_6(c4,c11)
66 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
67 INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v');
68
69 CREATE TABLE t3(
70 c1 int(1)AUTO_INCREMENT,
71 c2 int(1),
72 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
73 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
74 c5 date,
75 c7 DATE,
76 c8 time,
77 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
78 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
79 c12 CHAR(1),
80 PRIMARY KEY(c1),
81 KEY c4_6(c4,c11)
82 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
83 INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES
84 (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'),
85 (0,1,0,0,0,'c'),(0,0,0,0,0,'x');
86
87 UPDATE
88 t2 AS O1,t3 AS O2
89 SET O1.c12=1
90 WHERE O1.c14 NOT IN
91 (
92 SELECT
93 DISTINCT I1.c14 AS y
94 FROM t1 AS I1
95 ORDER BY I1.c14);
96
97 SET @@SESSION.sql_mode=default;
98
99 DROP TABLE t1, t2, t3;
100
101 --echo #
102 --echo # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
103 --echo #
104 CREATE TABLE `ibstd_08` (
105 `nc00577` tinyint(4) DEFAULT NULL,
106 `nc07844` varchar(41) DEFAULT NULL,
107 `gc01908` point NOT NULL,
108 `nc04156` char(17) DEFAULT NULL,
109 `nc09536` longblob NOT NULL,
110 `nc09231` decimal(10,0) NOT NULL,
111 `a` int(11) NOT NULL,
112 `b` varchar(198) NOT NULL,
113 `nc04560` mediumtext,
114 `c` char(187) DEFAULT NULL,
115 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
116 `gc00881` polygon NOT NULL,
117 `nc05121` int(11) NOT NULL DEFAULT '85941481',
118 KEY `a` (`a`),
119 KEY `b` (`b`(3),`a`),
120 KEY `c` (`c`(99),`b`(25)),
121 KEY `b_2` (`b`(5),`c`(10),`a`),
122 KEY `vbidxcol` (`vbidxcol`),
123 KEY `a_2` (`a`,`vbidxcol`),
124 KEY `vbidxcol_2` (`vbidxcol`),
125 FULLTEXT KEY `ftsic` (`c`,`b`)
126 ) ENGINE=InnoDB;
127
128
129 ALTER TABLE ibstd_08 ADD COLUMN nc07006 BIGINT AUTO_INCREMENT NOT NULL , ADD KEY auto_nc07006(nc07006);
130
131 DROP TABLE ibstd_08;
132
133 --echo #
134 --echo # Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256)
135 --echo # AND SEGFAULT
136 --echo #
137 set sql_mode="";
138 create table t (a int) engine=innodb;
139 create table s (
140 b int generated always as (1) virtual,
141 c int,
142 d int generated always as (1) virtual not null,
143 key (d)
144 ) engine=innodb;
145
146 insert into t(a) values ((select d from s for update));
147 insert into s(c) values ('');
148
149 SET sql_mode = default;
150 drop table if exists t,s;
151
152 --echo #
153 --echo # Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V
154 --echo #
155 CREATE TABLE t1 (
156 col1 int(11) NOT NULL,
157 col2 int(11) DEFAULT NULL,
158 col3 int(11) NOT NULL,
159 col4 int(11) DEFAULT NULL,
160 col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL,
161 col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
162 col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL,
163 col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL,
164 col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL,
165 col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL,
166 col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
167 col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL,
168 col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL,
169 col9 text,
170 col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
171 col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL,
172 col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
173 col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL,
174 extra int(11) DEFAULT NULL,
175 col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL,
176 col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL,
177 col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL,
178 col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL,
179 col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
180 col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
181 col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL,
182 col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL,
183 UNIQUE KEY idx7 (col1,col3,col2),
184 UNIQUE KEY uidx (col9(10)),
185 KEY idx15 (col9(10) DESC,col2 DESC),
186 KEY idx10 (col9(10) DESC,col1 DESC),
187 KEY idx11 (col6x DESC),
188 KEY idx6 (col9(10) DESC,col7 DESC),
189 KEY idx14 (col6 DESC)
190 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
191
192 # Cannot add virtual column along with create FULLTEXT index with
193 # adding a hidden FTS_DOC_ID column (which require a table rebuild)
194 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
195 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
196 VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
197
198 # This will add a hidden FTS_DOC_ID column
199 CREATE FULLTEXT INDEX idx ON t1(col9);
200
201 # Since there is no table rebuild needed, now the alter would be sucessful
202 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
203 VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
204
205 DROP TABLE t1;
206
207 CREATE TABLE t1 (
208 col1 int(11) NOT NULL,
209 col2 int(11) DEFAULT NULL,
210 col3 int(11) NOT NULL,
211 col4 int(11) DEFAULT NULL);
212
213 # This secondary key idx will be coverted to a new Primary Key, thus a table
214 # rebuild. It is blocked since there is an adding of virtual columns
215 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
216 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
217 VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
218
219 DROP TABLE t1;
220
221 CREATE TABLE t1 (
222 col1 int(11) NOT NULL,
223 col2 int(11) DEFAULT NULL,
224 col3 int(11) NOT NULL,
225 col4 int(11) DEFAULT NULL,
226 col5 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
227 col6 int(11) GENERATED ALWAYS AS ((col5 + col1)) VIRTUAL,
228 col7 bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
229 col8 int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
230 col9 text,
231 KEY idx1 (col5 DESC),
232 KEY idx2 (col6)
233 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
234 SHOW CREATE TABLE t1;
235 CHECK TABLE t1;
236
237 INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (10, 50, 40, 300, "lope");
238 INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (100, 3240, 4000, 11, "dskf");
239 INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (5, 400, 20, 900, "hqal");
240
241 SELECT * FROM t1;
242 --sorted_result
243 SELECT col5 FROM t1;
244 --sorted_result
245 SELECT col6 FROM t1;
246
247 --sorted_result
248 SELECT col7 FROM t1;
249 ALTER TABLE t1 ADD INDEX idx3 (col7 DESC);
250 CHECK TABLE t1;
251 --sorted_result
252 SELECT col7 FROM t1;
253
254 --sorted_result
255 SELECT col7 FROM t1;
256 ALTER TABLE t1 DROP INDEX idx3, ADD INDEX idx3 (col7 ASC);
257 CHECK TABLE t1;
258 --sorted_result
259 SELECT col7 FROM t1;
260
261 DROP TABLE t1;
262
263 --echo #
264 --echo # Bug 25899959 - SERVER CRASH WHILE TRYING TO ADD A VIRTUAL COLUMN AND ADD INDEX ON IT
265 --echo #
266
267 CREATE TABLE t1 (doc json DEFAULT NULL);
268 ALTER TABLE t1 ADD Name char(52) AS (doc->>"$.Name"), ADD INDEX n (Name);
269 SHOW CREATE TABLE t1;
270
271 DROP TABLE t1;
272
273 --echo #
274 --echo # Bug #27968952 INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
275 --echo #
276 SET sql_mode='';
277 CREATE TABLE t1(
278 a INT not null,
279 b CHAR(4) not null,
280 c TEXT GENERATED ALWAYS AS ((a <> b)) VIRTUAL,
281 UNIQUE KEY i1 (a),
282 UNIQUE KEY i2 (c(1))
283 ) ENGINE=INNODB;
284 insert into t1 set a=1;
285 insert into t1 set a=0;
286 replace into t1 set b ='1';
287 DROP TABLE t1;
288
289 SET NAMES utf8;
290 CREATE TABLE t1 (
291 a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
292 b VARCHAR(1000) NOT NULL,
293 c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED NOT NULL,
294 KEY (b(1)),
295 KEY (a(1))
296 ) ENGINE=INNODB
297 ROW_FORMAT=COMPRESSED
298 KEY_BLOCK_SIZE=1;
299 INSERT INTO t1(b) VALUES(REPEAT('a',947));
300 DELETE FROM t1;
301 DROP TABLE t1;
302 SET NAMES DEFAULT;
303 SET sql_mode = DEFAULT;
304
305 --echo #
306 --echo # Bug 23014521 - mysqld crash at row_upd_sec_index_entry_low
307 --echo #
308 SET @org_mode=@@sql_mode;
309 SET @@sql_mode='';
310
311 CREATE TABLE t1 (
312 col1 blob,
313 col3 blob,
314 col4 blob,
315 col5 blob GENERATED ALWAYS AS ((col1 - col4)) VIRTUAL,
316 col5x blob GENERATED ALWAYS AS ((col1 / col3)) VIRTUAL,
317 col9 text,
318 col2b blob GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
319 KEY idx7 (col9(10), col5(5)),
320 KEY idx10 (col9(10), col2b(5))
321 ) ENGINE=InnoDB;
322 INSERT INTO t1 (col1,col3,col4,col9) VALUES (REPEAT('a',20),REPEAT('a',20),REPEAT('a',20),REPEAT('a',20));
323 UPDATE t1 SET col1 = 50;
324 DELETE FROM t1;
325
326 DROP TABLE t1;
327
328 SET @@sql_mode=@org_mode;