Debian Codegraph @

mysql-test/suite/gcol/t/innodb_virtual_index.test

raw
1 --source include/have_innodb.inc
2 --source include/have_sequence.inc
3
4 SET default_storage_engine= innodb;
5
6 --echo #
7 --echo # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
8 --echo # THE NUMBER OF VIRTUAL COLUMNS
9 --echo #
10
11 CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT
12 GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4
13 INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL,
14 a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a)
15 VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS
16 (a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ;
17
18 INSERT INTO t1(a) VALUES(10);
19
20 SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND
21 a6=10 AND a7=10 AND a8=10 AND a9=10;
22
23 DROP TABLE t1;
24
25 --echo #
26 --echo # Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS(
27 --echo # OFFSETS)
28 --echo #
29 SET @@SESSION.sql_mode=0;
30
31 CREATE TABLE t1(
32 c1 int(1)AUTO_INCREMENT,
33 c2 int(1),
34 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
35 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
36 c5 date,
37 c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
38 c7 DATE,
39 c8 time,
40 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
41 c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
42 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
43 c12 CHAR(1),
44 c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
45 c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
46 PRIMARY KEY(c1),
47 KEY c4_6(c4,c11)
48 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
49
50 CREATE TABLE t2(
51 c1 int(1)AUTO_INCREMENT,
52 c2 int(1),
53 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
54 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
55 c5 date,
56 c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
57 c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL,
58 c7 DATE,
59 c8 time,
60 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
61 c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
62 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
63 c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL,
64 c12 CHAR(1),
65 c13 CHAR(2)GENERATED ALWAYS AS (concat(RTRIM(c12),RTRIM(c12))) VIRTUAL,
66 c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
67 PRIMARY KEY(c1),
68 KEY c13(c13),
69 KEY c4_6(c4,c11)
70 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
71 INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v');
72
73 CREATE TABLE t3(
74 c1 int(1)AUTO_INCREMENT,
75 c2 int(1),
76 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
77 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
78 c5 date,
79 c7 DATE,
80 c8 time,
81 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
82 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
83 c12 CHAR(1),
84 PRIMARY KEY(c1),
85 KEY c4_6(c4,c11)
86 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
87 INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES
88 (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'),
89 (0,1,0,0,0,'c'),(0,0,0,0,0,'x');
90
91 UPDATE
92 t2 AS O1,t3 AS O2
93 SET O1.c12=1
94 WHERE O1.c14 NOT IN
95 (
96 SELECT
97 DISTINCT I1.c14 AS y
98 FROM t1 AS I1
99 ORDER BY I1.c14);
100
101 SET @@SESSION.sql_mode=default;
102 --source ../../innodb/include/wait_all_purged.inc
103
104 DROP TABLE t1, t2, t3;
105
106 --echo #
107 --echo # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
108 --echo #
109 CREATE TABLE `ibstd_08` (
110 `nc00577` tinyint(4) DEFAULT NULL,
111 `nc07844` varchar(41) DEFAULT NULL,
112 `gc01908` point NOT NULL,
113 `nc04156` char(17) DEFAULT NULL,
114 `nc09536` longblob NOT NULL,
115 `nc09231` decimal(10,0) NOT NULL,
116 `a` int(11) NOT NULL,
117 `b` varchar(198) NOT NULL,
118 `nc04560` mediumtext,
119 `c` char(187) DEFAULT NULL,
120 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
121 `gc00881` polygon NOT NULL,
122 `nc05121` int(11) NOT NULL DEFAULT '85941481',
123 KEY `a` (`a`),
124 KEY `b` (`b`(3),`a`),
125 KEY `c` (`c`(99),`b`(25)),
126 KEY `b_2` (`b`(5),`c`(10),`a`),
127 KEY `vbidxcol` (`vbidxcol`),
128 KEY `a_2` (`a`,`vbidxcol`),
129 KEY `vbidxcol_2` (`vbidxcol`),
130 FULLTEXT KEY `ftsic` (`c`,`b`)
131 ) ENGINE=InnoDB;
132
133
134 ALTER TABLE ibstd_08 ADD COLUMN nc07006 BIGINT AUTO_INCREMENT NOT NULL , ADD KEY auto_nc07006(nc07006);
135
136 DROP TABLE ibstd_08;
137
138 --echo #
139 --echo # Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256)
140 --echo # AND SEGFAULT
141 --echo #
142 set sql_mode="";
143 create table t (a int) engine=innodb;
144 create table s (
145 b int generated always as (1) virtual,
146 c int,
147 d int generated always as (1) virtual,
148 key (d)
149 ) engine=innodb;
150
151 insert into t(a) values ((select d from s for update));
152 insert into s(c) values ('');
153
154 SET sql_mode = default;
155 drop table if exists t,s;
156
157 --echo #
158 --echo # Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V
159 --echo #
160 CREATE TABLE t1 (
161 col1 int(11) NOT NULL,
162 col2 int(11) DEFAULT NULL,
163 col3 int(11) NOT NULL,
164 col4 int(11) DEFAULT NULL,
165 col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL,
166 col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
167 col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL,
168 col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL,
169 col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL,
170 col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL,
171 col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
172 col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL,
173 col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL,
174 col9 text,
175 col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
176 col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL,
177 col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
178 col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL,
179 extra int(11) DEFAULT NULL,
180 col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL,
181 col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL,
182 col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL,
183 col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL,
184 col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
185 col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
186 col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL,
187 col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL,
188 UNIQUE KEY idx7 (col1,col3,col2),
189 UNIQUE KEY uidx (col9(10)),
190 KEY idx15 (col9(10) DESC,col2 DESC),
191 KEY idx10 (col9(10) DESC,col1 DESC),
192 KEY idx11 (col6x DESC),
193 KEY idx6 (col9(10) DESC,col7 DESC),
194 KEY idx14 (col6 DESC)
195 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
196
197 # Cannot add virtual column along with create FULLTEXT index with
198 # adding a hidden FTS_DOC_ID column (which require a table rebuild)
199 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
200 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
201 VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
202
203 # This will add a hidden FTS_DOC_ID column
204 CREATE FULLTEXT INDEX idx ON t1(col9);
205
206 # Since there is no table rebuild needed, now the alter would be sucessful
207 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
208 VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
209
210 DROP TABLE t1;
211
212 CREATE TABLE t1 (
213 col1 int(11) NOT NULL,
214 col2 int(11) DEFAULT NULL,
215 col3 int(11) NOT NULL,
216 col4 int(11) DEFAULT NULL) engine=innodb;
217
218 # This secondary key idx will be coverted to a new Primary Key, thus a table
219 # rebuild. It is blocked since there is an adding of virtual columns
220 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
221 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
222 VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
223
224 DROP TABLE t1;
225
226 --echo #
227 --echo # Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7
228 --echo #
229 CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
230 ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2);
231 SHOW CREATE TABLE t1;
232 DROP TABLE t1;
233
234 --echo #
235 --echo # Bug #27968952 INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
236 --echo #
237
238 CREATE TABLE t1(
239 a INT NOT NULL UNIQUE,
240 b INT NOT NULL,
241 c TEXT GENERATED ALWAYS AS (a <> b) VIRTUAL,
242 d TEXT NOT NULL,
243 UNIQUE KEY (c(1)), KEY(d(1))
244 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
245 SET @t = REPEAT('t',@@innodb_page_size);
246 INSERT INTO t1 (a,b,d) VALUES (1,0,@t), (0,0,@t);
247 --error ER_DUP_ENTRY
248 UPDATE t1 SET b = a;
249 REPLACE INTO t1 SET a = 0, b = 1, d = 'd';
250 SELECT * FROM t1;
251 CHECK TABLE t1;
252 DROP TABLE t1;
253
254 --disable_query_log
255 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
256 SET GLOBAL innodb_read_only_compressed=OFF;
257 --enable_query_log
258 CREATE TABLE t1(
259 a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
260 b VARCHAR(1000) NOT NULL,
261 c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED,
262 KEY (b(1)),
263 KEY (a(1))
264 ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
265 INSERT INTO t1(b) VALUES(REPEAT('b',1000));
266 DELETE FROM t1;
267 --disable_query_log
268 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
269 --enable_query_log
270 DROP TABLE t1;
271
272 --echo #
273 --echo # Bug #22990029 GCOLS: INCORRECT BEHAVIOR
274 --echo # AFTER DATA INSERTED WITH IGNORE KEYWORD
275 --echo #
276
277 CREATE TABLE t1(a INT PRIMARY KEY, b INT, vb DATE AS(b) VIRTUAL, KEY(vb))
278 ENGINE=InnoDB;
279 INSERT IGNORE INTO t1 (a,b) VALUES(1,20190132);
280 SELECT * FROM t1;
281 BEGIN;
282 DELETE FROM t1;
283 INSERT INTO t1 (a,b) VALUES(1,20190123);
284 SELECT * FROM t1;
285 ROLLBACK;
286 SELECT * FROM t1;
287 CHECK TABLE t1;
288 DROP TABLE t1;
289
290 --echo #
291 --echo # MDEV-25872 InnoDB: Assertion failure in row_merge_read_clustered_index
292 --echo # upon ALTER on table with indexed virtual columns
293 --echo #
294
295 CREATE TABLE t1 (
296 id BIGINT AUTO_INCREMENT PRIMARY KEY,
297 a INT,
298 va INT ZEROFILL AS (a) VIRTUAL,
299 b TIMESTAMP,
300 c CHAR(204),
301 vc CHAR(8),
302 KEY(vc,c(64),b,va)
303 ) ENGINE=InnoDB CHARACTER SET utf32;
304 INSERT INTO t1 (id) SELECT NULL FROM seq_1_to_75;
305 INSERT IGNORE INTO t1 (id, a) VALUES (NULL, -1);
306 --error ER_WARN_DATA_OUT_OF_RANGE
307 ALTER TABLE t1 FORCE;
308 DROP TABLE t1;
309
310 --echo #
311 --echo # MDEV-24713 Assertion `dict_table_is_comp(index->table)' failed
312 --echo # in row_merge_buf_add()
313 --echo #
314 CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(3),
315 b CHAR(8) AS (a) VIRTUAL, KEY(b))
316 ROW_FORMAT=REDUNDANT ENGINE=InnoDB
317 CHARACTER SET utf8;
318 INSERT INTO t1 (id,a) VALUES (1,'foo');
319 OPTIMIZE TABLE t1;
320 DROP TABLE t1;
321
322 --echo #
323 --echo # MDEV-20154 Assertion `len <= col->len || ((col->mtype) == 5
324 --echo # || (col->mtype) == 14)' failed in row_merge_buf_add
325 --echo #
326
327 CREATE TABLE t1 (
328 a VARCHAR(2500),
329 b VARCHAR(2499) AS (a) VIRTUAL
330 ) ENGINE=InnoDB;
331 INSERT INTO t1 (a) VALUES ('foo');
332
333 ALTER TABLE t1 MODIFY a VARCHAR(2600), ALGORITHM=INPLACE;
334 ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE;
335
336 --echo # Cleanup
337 DROP TABLE t1;
338
339 --echo # End of 10.2 tests
340
341 --echo #
342 --echo # MDEV-29299 SELECT from table with vcol index reports warning
343 --echo #
344
345 CREATE TABLE t(fld1 INT NOT NULL,
346 fld2 INT AS (100/fld1) VIRTUAL,
347 KEY(fld1), KEY(fld2));
348 CREATE TABLE t_odd(id int);
349 INSERT INTO t(fld1) VALUES(1), (2);
350
351 --connect stop_purge,localhost,root
352 # This prevents purge for records in t
353 START TRANSACTION WITH CONSISTENT SNAPSHOT;
354 INSERT INTO t_odd VALUES(10000);
355
356 --connection default
357 UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2;
358 UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3;
359 UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4;
360 SELECT fld2 FROM t FORCE INDEX(fld2);
361 SELECT fld2 FROM t FORCE INDEX(fld1);
362
363 --disconnect stop_purge
364 DROP TABLE t, t_odd;
365
366 --echo #
367 --echo # MDEV-29753 An error is wrongly reported during INSERT with vcol index
368 --echo # See also Bug #22990029
369 --echo #
370
371 CREATE TABLE t(pk INT PRIMARY KEY,
372 fld1 INT NOT NULL,
373 fld2 INT AS (100/fld1) VIRTUAL,
374 KEY(fld1), KEY(fld2));
375 INSERT IGNORE t(pk, fld1) VALUES(1, 0);
376 SELECT * FROM t;
377 BEGIN;
378 DELETE FROM t;
379 INSERT INTO t (pk, fld1) VALUES(1,1);
380 SELECT * FROM t;
381
382 --echo # Cleanup
383 ROLLBACK;
384 DROP TABLE t;
385
386 --echo # End of 10.3 tests