Debian Codegraph @

mysql-test/suite/innodb/r/virtual_index.result

raw
1 #
2 # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
3 # THE NUMBER OF VIRTUAL COLUMNS
4 #
5 CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT
6 GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4
7 INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL,
8 a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a)
9 VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS
10 (a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ;
11 INSERT INTO t1(a) VALUES(10);
12 SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND
13 a6=10 AND a7=10 AND a8=10 AND a9=10;
14 a a1 a2 a3 a4 a5 a6 a7 a8 a9
15 10 10 10 10 10 10 10 10 10 10
16 DROP TABLE t1;
17 #
18 # Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS(
19 # OFFSETS)
20 #
21 SET @@SESSION.sql_mode=0;
22 CREATE TABLE t1(
23 c1 int(1)AUTO_INCREMENT,
24 c2 int(1),
25 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
26 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
27 c5 date,
28 c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
29 c7 DATE,
30 c8 time,
31 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
32 c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
33 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
34 c12 CHAR(1),
35 c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
36 c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
37 PRIMARY KEY(c1),
38 KEY c4_6(c4,c11)
39 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
40 Warnings:
41 Warning 1681 Integer display width is deprecated and will be removed in a future release.
42 Warning 1681 Integer display width is deprecated and will be removed in a future release.
43 Warning 1681 Integer display width is deprecated and will be removed in a future release.
44 Warning 1681 Integer display width is deprecated and will be removed in a future release.
45 CREATE TABLE t2(
46 c1 int(1)AUTO_INCREMENT,
47 c2 int(1),
48 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
49 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
50 c5 date,
51 c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
52 c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL,
53 c7 DATE,
54 c8 time,
55 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
56 c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
57 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
58 c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL,
59 c12 CHAR(1),
60 c13 CHAR(2)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
61 c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
62 PRIMARY KEY(c1),
63 KEY c13(c13),
64 KEY c4_6(c4,c11)
65 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
66 Warnings:
67 Warning 1681 Integer display width is deprecated and will be removed in a future release.
68 Warning 1681 Integer display width is deprecated and will be removed in a future release.
69 Warning 1681 Integer display width is deprecated and will be removed in a future release.
70 Warning 1681 Integer display width is deprecated and will be removed in a future release.
71 INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v');
72 CREATE TABLE t3(
73 c1 int(1)AUTO_INCREMENT,
74 c2 int(1),
75 c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
76 c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
77 c5 date,
78 c7 DATE,
79 c8 time,
80 c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
81 c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
82 c12 CHAR(1),
83 PRIMARY KEY(c1),
84 KEY c4_6(c4,c11)
85 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
86 Warnings:
87 Warning 1681 Integer display width is deprecated and will be removed in a future release.
88 Warning 1681 Integer display width is deprecated and will be removed in a future release.
89 Warning 1681 Integer display width is deprecated and will be removed in a future release.
90 Warning 1681 Integer display width is deprecated and will be removed in a future release.
91 INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES
92 (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'),
93 (0,1,0,0,0,'c'),(0,0,0,0,0,'x');
94 UPDATE
95 t2 AS O1,t3 AS O2
96 SET O1.c12=1
97 WHERE O1.c14 NOT IN
98 (
99 SELECT
100 DISTINCT I1.c14 AS y
101 FROM t1 AS I1
102 ORDER BY I1.c14);
103 SET @@SESSION.sql_mode=default;
104 DROP TABLE t1, t2, t3;
105 #
106 # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
107 #
108 CREATE TABLE `ibstd_08` (
109 `nc00577` tinyint(4) DEFAULT NULL,
110 `nc07844` varchar(41) DEFAULT NULL,
111 `gc01908` point NOT NULL,
112 `nc04156` char(17) DEFAULT NULL,
113 `nc09536` longblob NOT NULL,
114 `nc09231` decimal(10,0) NOT NULL,
115 `a` int(11) NOT NULL,
116 `b` varchar(198) NOT NULL,
117 `nc04560` mediumtext,
118 `c` char(187) DEFAULT NULL,
119 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
120 `gc00881` polygon NOT NULL,
121 `nc05121` int(11) NOT NULL DEFAULT '85941481',
122 KEY `a` (`a`),
123 KEY `b` (`b`(3),`a`),
124 KEY `c` (`c`(99),`b`(25)),
125 KEY `b_2` (`b`(5),`c`(10),`a`),
126 KEY `vbidxcol` (`vbidxcol`),
127 KEY `a_2` (`a`,`vbidxcol`),
128 KEY `vbidxcol_2` (`vbidxcol`),
129 FULLTEXT KEY `ftsic` (`c`,`b`)
130 ) ENGINE=InnoDB;
131 Warnings:
132 Warning 1681 Integer display width is deprecated and will be removed in a future release.
133 Warning 1681 Integer display width is deprecated and will be removed in a future release.
134 Warning 1681 Integer display width is deprecated and will be removed in a future release.
135 Warning 1831 Duplicate index 'vbidxcol_2' defined on the table 'test.ibstd_08'. This is deprecated and will be disallowed in a future release.
136 ALTER TABLE ibstd_08 ADD COLUMN nc07006 BIGINT AUTO_INCREMENT NOT NULL , ADD KEY auto_nc07006(nc07006);
137 Warnings:
138 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
139 DROP TABLE ibstd_08;
140 #
141 # Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256)
142 # AND SEGFAULT
143 #
144 set sql_mode="";
145 create table t (a int) engine=innodb;
146 create table s (
147 b int generated always as (1) virtual,
148 c int,
149 d int generated always as (1) virtual not null,
150 key (d)
151 ) engine=innodb;
152 insert into t(a) values ((select d from s for update));
153 insert into s(c) values ('');
154 Warnings:
155 Warning 1366 Incorrect integer value: '' for column 'c' at row 1
156 SET sql_mode = default;
157 drop table if exists t,s;
158 #
159 # Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V
160 #
161 CREATE TABLE t1 (
162 col1 int(11) NOT NULL,
163 col2 int(11) DEFAULT NULL,
164 col3 int(11) NOT NULL,
165 col4 int(11) DEFAULT NULL,
166 col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL,
167 col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
168 col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL,
169 col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL,
170 col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL,
171 col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL,
172 col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
173 col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL,
174 col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL,
175 col9 text,
176 col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
177 col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL,
178 col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
179 col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL,
180 extra int(11) DEFAULT NULL,
181 col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL,
182 col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL,
183 col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL,
184 col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL,
185 col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
186 col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
187 col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL,
188 col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL,
189 UNIQUE KEY idx7 (col1,col3,col2),
190 UNIQUE KEY uidx (col9(10)),
191 KEY idx15 (col9(10) DESC,col2 DESC),
192 KEY idx10 (col9(10) DESC,col1 DESC),
193 KEY idx11 (col6x DESC),
194 KEY idx6 (col9(10) DESC,col7 DESC),
195 KEY idx14 (col6 DESC)
196 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
197 Warnings:
198 Warning 1681 Integer display width is deprecated and will be removed in a future release.
199 Warning 1681 Integer display width is deprecated and will be removed in a future release.
200 Warning 1681 Integer display width is deprecated and will be removed in a future release.
201 Warning 1681 Integer display width is deprecated and will be removed in a future release.
202 Warning 1681 Integer display width is deprecated and will be removed in a future release.
203 Warning 1681 Integer display width is deprecated and will be removed in a future release.
204 Warning 1681 Integer display width is deprecated and will be removed in a future release.
205 Warning 1681 Integer display width is deprecated and will be removed in a future release.
206 Warning 1681 Integer display width is deprecated and will be removed in a future release.
207 Warning 1681 Integer display width is deprecated and will be removed in a future release.
208 Warning 1681 Integer display width is deprecated and will be removed in a future release.
209 Warning 1681 Integer display width is deprecated and will be removed in a future release.
210 Warning 1681 Integer display width is deprecated and will be removed in a future release.
211 Warning 1681 Integer display width is deprecated and will be removed in a future release.
212 Warning 1681 Integer display width is deprecated and will be removed in a future release.
213 Warning 1681 Integer display width is deprecated and will be removed in a future release.
214 Warning 1681 Integer display width is deprecated and will be removed in a future release.
215 Warning 1681 Integer display width is deprecated and will be removed in a future release.
216 Warning 1681 Integer display width is deprecated and will be removed in a future release.
217 Warning 1681 Integer display width is deprecated and will be removed in a future release.
218 Warning 1681 Integer display width is deprecated and will be removed in a future release.
219 Warning 1681 Integer display width is deprecated and will be removed in a future release.
220 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
221 VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
222 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.
223 CREATE FULLTEXT INDEX idx ON t1(col9);
224 Warnings:
225 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
226 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
227 VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
228 Warnings:
229 Warning 1831 Duplicate index 'ftidx' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
230 DROP TABLE t1;
231 CREATE TABLE t1 (
232 col1 int(11) NOT NULL,
233 col2 int(11) DEFAULT NULL,
234 col3 int(11) NOT NULL,
235 col4 int(11) DEFAULT NULL);
236 Warnings:
237 Warning 1681 Integer display width is deprecated and will be removed in a future release.
238 Warning 1681 Integer display width is deprecated and will be removed in a future release.
239 Warning 1681 Integer display width is deprecated and will be removed in a future release.
240 Warning 1681 Integer display width is deprecated and will be removed in a future release.
241 ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
242 VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
243 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.
244 DROP TABLE t1;
245 CREATE TABLE t1 (
246 col1 int(11) NOT NULL,
247 col2 int(11) DEFAULT NULL,
248 col3 int(11) NOT NULL,
249 col4 int(11) DEFAULT NULL,
250 col5 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
251 col6 int(11) GENERATED ALWAYS AS ((col5 + col1)) VIRTUAL,
252 col7 bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
253 col8 int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
254 col9 text,
255 KEY idx1 (col5 DESC),
256 KEY idx2 (col6)
257 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
258 Warnings:
259 Warning 1681 Integer display width is deprecated and will be removed in a future release.
260 Warning 1681 Integer display width is deprecated and will be removed in a future release.
261 Warning 1681 Integer display width is deprecated and will be removed in a future release.
262 Warning 1681 Integer display width is deprecated and will be removed in a future release.
263 Warning 1681 Integer display width is deprecated and will be removed in a future release.
264 Warning 1681 Integer display width is deprecated and will be removed in a future release.
265 Warning 1681 Integer display width is deprecated and will be removed in a future release.
266 Warning 1681 Integer display width is deprecated and will be removed in a future release.
267 SHOW CREATE TABLE t1;
268 Table Create Table
269 t1 CREATE TABLE `t1` (
270 `col1` int NOT NULL,
271 `col2` int DEFAULT NULL,
272 `col3` int NOT NULL,
273 `col4` int DEFAULT NULL,
274 `col5` int GENERATED ALWAYS AS ((`col2` - `col4`)) VIRTUAL,
275 `col6` int GENERATED ALWAYS AS ((`col5` + `col1`)) VIRTUAL,
276 `col7` bigint GENERATED ALWAYS AS ((`col2` / `col1`)) VIRTUAL,
277 `col8` int GENERATED ALWAYS AS ((`col5` % `col5`)) VIRTUAL,
278 `col9` text,
279 KEY `idx1` (`col5` DESC),
280 KEY `idx2` (`col6`)
281 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
282 CHECK TABLE t1;
283 Table Op Msg_type Msg_text
284 test.t1 check status OK
285 INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (10, 50, 40, 300, "lope");
286 INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (100, 3240, 4000, 11, "dskf");
287 INSERT INTO t1(col1, col2, col3, col4, col9) VALUES (5, 400, 20, 900, "hqal");
288 SELECT * FROM t1;
289 col1 col2 col3 col4 col5 col6 col7 col8 col9
290 10 50 40 300 -250 -240 5 0 lope
291 100 3240 4000 11 3229 3329 32 0 dskf
292 5 400 20 900 -500 -495 80 0 hqal
293 SELECT col5 FROM t1;
294 col5
295 -250
296 -500
297 3229
298 SELECT col6 FROM t1;
299 col6
300 -240
301 -495
302 3329
303 SELECT col7 FROM t1;
304 col7
305 32
306 5
307 80
308 ALTER TABLE t1 ADD INDEX idx3 (col7 DESC);
309 CHECK TABLE t1;
310 Table Op Msg_type Msg_text
311 test.t1 check status OK
312 SELECT col7 FROM t1;
313 col7
314 32
315 5
316 80
317 SELECT col7 FROM t1;
318 col7
319 32
320 5
321 80
322 ALTER TABLE t1 DROP INDEX idx3, ADD INDEX idx3 (col7 ASC);
323 CHECK TABLE t1;
324 Table Op Msg_type Msg_text
325 test.t1 check status OK
326 SELECT col7 FROM t1;
327 col7
328 32
329 5
330 80
331 DROP TABLE t1;
332 #
333 # Bug 25899959 - SERVER CRASH WHILE TRYING TO ADD A VIRTUAL COLUMN AND ADD INDEX ON IT
334 #
335 CREATE TABLE t1 (doc json DEFAULT NULL);
336 ALTER TABLE t1 ADD Name char(52) AS (doc->>"$.Name"), ADD INDEX n (Name);
337 SHOW CREATE TABLE t1;
338 Table Create Table
339 t1 CREATE TABLE `t1` (
340 `doc` json DEFAULT NULL,
341 `Name` char(52) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.Name'))) VIRTUAL,
342 KEY `n` (`Name`)
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
344 DROP TABLE t1;
345 #
346 # Bug #27968952 INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
347 #
348 SET sql_mode='';
349 CREATE TABLE t1(
350 a INT not null,
351 b CHAR(4) not null,
352 c TEXT GENERATED ALWAYS AS ((a <> b)) VIRTUAL,
353 UNIQUE KEY i1 (a),
354 UNIQUE KEY i2 (c(1))
355 ) ENGINE=INNODB;
356 insert into t1 set a=1;
357 Warnings:
358 Warning 1364 Field 'b' doesn't have a default value
359 insert into t1 set a=0;
360 Warnings:
361 Warning 1364 Field 'b' doesn't have a default value
362 replace into t1 set b ='1';
363 Warnings:
364 Warning 1364 Field 'a' doesn't have a default value
365 DROP TABLE t1;
366 SET NAMES utf8;
367 Warnings:
368 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
369 CREATE TABLE t1 (
370 a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
371 b VARCHAR(1000) NOT NULL,
372 c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED NOT NULL,
373 KEY (b(1)),
374 KEY (a(1))
375 ) ENGINE=INNODB
376 ROW_FORMAT=COMPRESSED
377 KEY_BLOCK_SIZE=1;
378 INSERT INTO t1(b) VALUES(REPEAT('a',947));
379 DELETE FROM t1;
380 DROP TABLE t1;
381 SET NAMES DEFAULT;
382 SET sql_mode = DEFAULT;
383 #
384 # Bug 23014521 - mysqld crash at row_upd_sec_index_entry_low
385 #
386 SET @org_mode=@@sql_mode;
387 SET @@sql_mode='';
388 CREATE TABLE t1 (
389 col1 blob,
390 col3 blob,
391 col4 blob,
392 col5 blob GENERATED ALWAYS AS ((col1 - col4)) VIRTUAL,
393 col5x blob GENERATED ALWAYS AS ((col1 / col3)) VIRTUAL,
394 col9 text,
395 col2b blob GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
396 KEY idx7 (col9(10), col5(5)),
397 KEY idx10 (col9(10), col2b(5))
398 ) ENGINE=InnoDB;
399 INSERT INTO t1 (col1,col3,col4,col9) VALUES (REPEAT('a',20),REPEAT('a',20),REPEAT('a',20),REPEAT('a',20));
400 UPDATE t1 SET col1 = 50;
401 DELETE FROM t1;
402 DROP TABLE t1;
403 SET @@sql_mode=@org_mode;