Debian Codegraph @

mysql-test/suite/gcol/r/innodb_virtual_index.result

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