Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 # - UNIQUE KEY
3 # - INDEX
4 # - FULLTEXT INDEX
5 # - SPATIAL INDEX (not supported)
6 # - FOREIGN INDEX (partially supported)
7 # - CHECK (allowed but not used)
8 # UNIQUE
9 create table t1 (a int, b int generated always as (a*2) virtual unique);
10 show create table t1;
11 Table Create Table
12 t1 CREATE TABLE `t1` (
13 `a` int(11) DEFAULT NULL,
14 `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
15 UNIQUE KEY `b` (`b`)
16 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
17 describe t1;
18 Field Type Null Key Default Extra
19 a int(11) YES NULL
20 b int(11) YES UNI NULL VIRTUAL GENERATED
21 drop table t1;
22 create table t1 (a int, b int generated always as (a*2) stored unique);
23 show create table t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `a` int(11) DEFAULT NULL,
27 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
28 UNIQUE KEY `b` (`b`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
30 describe t1;
31 Field Type Null Key Default Extra
32 a int(11) YES NULL
33 b int(11) YES UNI NULL STORED GENERATED
34 drop table t1;
35 create table t1 (a int, b int generated always as (a*2) virtual, unique key (b));
36 show create table t1;
37 Table Create Table
38 t1 CREATE TABLE `t1` (
39 `a` int(11) DEFAULT NULL,
40 `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
41 UNIQUE KEY `b` (`b`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
43 describe t1;
44 Field Type Null Key Default Extra
45 a int(11) YES NULL
46 b int(11) YES UNI NULL VIRTUAL GENERATED
47 drop table t1;
48 create table t1 (a int, b int generated always as (a*2) stored, unique (b));
49 show create table t1;
50 Table Create Table
51 t1 CREATE TABLE `t1` (
52 `a` int(11) DEFAULT NULL,
53 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
54 UNIQUE KEY `b` (`b`)
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
56 describe t1;
57 Field Type Null Key Default Extra
58 a int(11) YES NULL
59 b int(11) YES UNI NULL STORED GENERATED
60 drop table t1;
61 create table t1 (a int, b int generated always as (a*2) virtual);
62 alter table t1 add unique key (b);
63 drop table t1;
64 create table t1 (a int, b int generated always as (a*2) stored);
65 alter table t1 add unique key (b);
66 drop table t1;
67 # Testing data manipulation operations involving UNIQUE keys
68 # on generated columns can be found in:
69 # - gcol_ins_upd.inc
70 # - gcol_select.inc
71 #
72 # INDEX
73 create table t1 (a int, b int generated always as (a*2) virtual, index (b));
74 show create table t1;
75 Table Create Table
76 t1 CREATE TABLE `t1` (
77 `a` int(11) DEFAULT NULL,
78 `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL,
79 KEY `b` (`b`)
80 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
81 describe t1;
82 Field Type Null Key Default Extra
83 a int(11) YES NULL
84 b int(11) YES MUL NULL VIRTUAL GENERATED
85 drop table t1;
86 create table t1 (a int, b int generated always as (a*2) virtual, index (a,b));
87 drop table t1;
88 create table t1 (a int, b int generated always as (a*2) stored, index (b));
89 show create table t1;
90 Table Create Table
91 t1 CREATE TABLE `t1` (
92 `a` int(11) DEFAULT NULL,
93 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
94 KEY `b` (`b`)
95 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
96 describe t1;
97 Field Type Null Key Default Extra
98 a int(11) YES NULL
99 b int(11) YES MUL NULL STORED GENERATED
100 drop table t1;
101 create table t1 (a int, b int generated always as (a*2) stored, index (a,b));
102 show create table t1;
103 Table Create Table
104 t1 CREATE TABLE `t1` (
105 `a` int(11) DEFAULT NULL,
106 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
107 KEY `a` (`a`,`b`)
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
109 describe t1;
110 Field Type Null Key Default Extra
111 a int(11) YES MUL NULL
112 b int(11) YES NULL STORED GENERATED
113 drop table t1;
114 create table t1 (a int, b int generated always as (a*2) virtual);
115 alter table t1 add index (b);
116 alter table t1 add index (a,b);
117 drop table t1;
118 create table t1 (a int, b int generated always as (a*2) stored);
119 alter table t1 add index (b);
120 drop table t1;
121 create table t1 (a int, b int generated always as (a*2) stored);
122 alter table t1 add index (a,b);
123 create table t2 like t1;
124 drop table t2;
125 drop table t1;
126 # Testing data manipulation operations involving INDEX
127 # on generated columns can be found in:
128 # - gcol_select.inc
129 #
130 # TODO: FULLTEXT INDEX
131 # SPATIAL INDEX
132 # FOREIGN KEY
133 # Rejected FK options.
134 create table t1 (a int, b int generated always as (a+1) stored,
135 foreign key (b) references t2(a) on update set null);
136 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
137 create table t1 (a int, b int generated always as (a+1) stored,
138 foreign key (b) references t2(a) on update cascade);
139 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
140 create table t1 (a int, b int generated always as (a+1) stored,
141 foreign key (b) references t2(a) on delete set null);
142 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
143 create table t1 (a int, b int generated always as (a+1) stored);
144 alter table t1 add foreign key (b) references t2(a) on update set null;
145 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
146 alter table t1 add foreign key (b) references t2(a) on update cascade;
147 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
148 alter table t1 add foreign key (b) references t2(a) on delete set null;
149 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
150 drop table t1;
151 create table t1 (a int, b int generated always as (a+1) virtual,
152 foreign key (b) references t2(a));
153 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
154 create table t1 (a int, b int generated always as (a+1) virtual);
155 alter table t1 add foreign key (b) references t2(a);
156 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
157 drop table t1;
158 # Allowed FK options.
159 create table t2 (a int primary key, b char(5));
160 create table t1 (a int, b int generated always as (a % 10) stored,
161 foreign key (b) references t2(a) on update restrict);
162 drop table t1;
163 create table t1 (a int, b int generated always as (a % 10) stored,
164 foreign key (b) references t2(a) on update no action);
165 drop table t1;
166 create table t1 (a int, b int generated always as (a % 10) stored,
167 foreign key (b) references t2(a) on delete restrict);
168 drop table t1;
169 create table t1 (a int, b int generated always as (a % 10) stored,
170 foreign key (b) references t2(a) on delete cascade);
171 drop table t1;
172 create table t1 (a int, b int generated always as (a % 10) stored,
173 foreign key (b) references t2(a) on delete no action);
174 drop table t1,t2;
175 #
176 # Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED
177 #
178 CREATE TABLE c (
179 pk integer AUTO_INCREMENT,
180 col_datetime_nokey DATETIME /*! NULL */,
181 col_time_nokey TIME /*! NULL */,
182 col_datetime_key DATETIME GENERATED ALWAYS AS
183 (ADDTIME(col_datetime_nokey, col_time_nokey)),
184 col_time_key TIME GENERATED ALWAYS AS
185 (ADDTIME(col_datetime_nokey, col_time_nokey)),
186 col_varchar_nokey VARCHAR(1) /*! NULL */,
187 PRIMARY KEY (pk),
188 KEY (col_time_key),
189 KEY (col_datetime_key));
190 INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values
191 ('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'),
192 ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL),
193 ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'),
194 ('18:56:33.027423','2003-04-01 00:00:00', 'i');
195 insert into c (col_time_nokey,col_datetime_nokey,col_varchar_nokey) select '10:10:10', '2021-12-24 01:50:27', 'z' from seq_1_to_10;
196 EXPLAIN SELECT
197 outr.col_time_key AS x
198 FROM c as outr
199 WHERE
200 outr.col_varchar_nokey in ('c', 'x', 'i')
201 AND (outr.col_time_key IS NULL OR
202 outr.col_datetime_key = '2009-09-27');
203 id select_type table type possible_keys key key_len ref rows Extra
204 1 SIMPLE outr index_merge col_time_key,col_datetime_key col_time_key,col_datetime_key 4,6 NULL x x
205 SELECT
206 outr.col_time_key AS x
207 FROM c AS outr
208 WHERE
209 outr.col_varchar_nokey in ('c', 'x', 'i')
210 AND (outr.col_time_key IS NULL OR
211 outr.col_datetime_key = '2009-09-27');
212 x
213 DROP TABLE c;
214 #
215 # Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP |
216 # INNOBASE/INCLUDE/DATA0DATA.IC:253
217 #
218 CREATE TABLE A (
219 col_varchar_nokey TEXT ,
220 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
221 KEY (col_varchar_key(50))
222 );
223 INSERT INTO A (col_varchar_nokey) VALUES ('');
224 CREATE TABLE D (
225 pk INTEGER AUTO_INCREMENT,
226 col_date_nokey BLOB,
227 col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL,
228 col_datetime_nokey LONGBLOB,
229 col_time_nokey LONGTEXT,
230 col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
231 col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
232 col_varchar_nokey TEXT,
233 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
234 PRIMARY KEY (pk),
235 KEY (col_varchar_key(50)),
236 KEY (col_date_key(20)),
237 KEY (col_time_key(20)),
238 KEY (col_datetime_key(20)),
239 KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5))
240 );
241 INSERT INTO D (
242 col_date_nokey,
243 col_time_nokey,
244 col_datetime_nokey,
245 col_varchar_nokey
246 ) VALUES ('', '', '', ''),('', '', '', '');
247 DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON
248 ( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` );
249 DROP TABLE IF EXISTS A,D;
250 #
251 # Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL |
252 # INNOBASE/HANDLER/HANDLER0ALTER.CC
253 #
254 CREATE TABLE t1 (
255 col1 int(11) DEFAULT NULL,
256 col2 int(11) DEFAULT NULL,
257 col3 int(11) NOT NULL,
258 col4 int(11) DEFAULT NULL,
259 col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL,
260 col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
261 col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
262 col9 text,
263 col6 int(11) DEFAULT NULL,
264 PRIMARY KEY (`col3`),
265 UNIQUE KEY uidx (`col2`),
266 KEY idx (`col5`)
267 );
268 INSERT INTO t1(col1,col2,col3,col4,col9,col6)
269 VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL);
270 ALTER TABLE t1 ADD COLUMN extra INT;
271 DROP TABLE t1;
272 #
273 # Bug#21316860: WL8149:INNODB: FAILING ASSERTION:
274 # TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED
275 #
276 CREATE TABLE t1 (
277 pk int(11) NOT NULL,
278 col_int_nokey int(11),
279 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
280 col_date_nokey date,
281 col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL,
282 PRIMARY KEY (pk),
283 UNIQUE KEY col_int_key (col_int_key)
284 );
285 ALTER TABLE t1 DROP COLUMN pk;
286 DROP TABLE t1;
287 # Remove the impact on PK choose by index on virtual generated column
288 CREATE TABLE t1 (
289 pk int(11) NOT NULL,
290 col_int_nokey int(11) DEFAULT NULL,
291 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
292 UNIQUE KEY col_int_key (col_int_key)
293 );
294 ALTER TABLE t1 add unique index idx(pk);
295 DESC t1;
296 Field Type Null Key Default Extra
297 pk int(11) NO PRI NULL
298 col_int_nokey int(11) YES NULL
299 col_int_key int(11) YES UNI NULL VIRTUAL GENERATED
300 DROP TABLE t1;
301 #
302 # Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
303 #
304 CREATE TABLE t1 (
305 id INTEGER NOT NULL,
306 b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL,
307 UNIQUE KEY (b)
308 );
309 INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);
310 EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
311 id select_type table type possible_keys key key_len ref rows Extra
312 1 SIMPLE t1 index NULL b 5 NULL 9 Using index
313 SELECT b FROM t1 FORCE INDEX(b);
314 b
315 3
316 4
317 5
318 6
319 7
320 8
321 9
322 10
323 11
324 EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
325 id select_type table type possible_keys key key_len ref rows Extra
326 1 SIMPLE t1 range b b 5 NULL 3 Using where; Using index
327 SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
328 b
329 3
330 4
331 5
332 DROP TABLE t1;
333
334 # Testing data manipulation operations involving FOREIGN KEY
335 # on generated columns can be found in:
336 # - gcol_ins_upd.inc
337 # - gcol_select.inc
338 #
339 # TODO: CHECK
340 #
341 # Test how optimizer picks indexes defined on a GC
342 #
343 CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc));
344 INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
345 ANALYZE TABLE t1;
346 Table Op Msg_type Msg_text
347 test.t1 analyze status Engine-independent statistics collected
348 test.t1 analyze status OK
349 # Should use index
350 SELECT * FROM t1 WHERE f1 + 1 > 7;
351 f1 gc
352 7 8
353 8 9
354 9 10
355 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
356 id select_type table type possible_keys key key_len ref rows Extra
357 1 SIMPLE t1 range gc gc 5 NULL 3 Using index condition
358 SELECT * FROM t1 WHERE f1 + 1 = 7;
359 f1 gc
360 6 7
361 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
362 id select_type table type possible_keys key key_len ref rows Extra
363 1 SIMPLE t1 const gc gc 5 const 1
364 SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
365 f1 gc
366 4 5
367 6 7
368 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
369 id select_type table type possible_keys key key_len ref rows Extra
370 1 SIMPLE t1 range gc gc 5 NULL 2 Using index condition
371 SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
372 f1 gc
373 4 5
374 5 6
375 6 7
376 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
377 id select_type table type possible_keys key key_len ref rows Extra
378 1 SIMPLE t1 range gc gc 5 NULL 3 Using index condition
379 # Check that expression isn't transformed for a disabled key
380 SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
381 f1 gc
382 4 5
383 5 6
384 6 7
385 EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
386 id select_type table type possible_keys key key_len ref rows Extra
387 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
388 # Check that ORDER BY could be optimized
389 SELECT * FROM t1 ORDER BY f1 + 1;
390 f1 gc
391 0 1
392 1 2
393 2 3
394 3 4
395 4 5
396 5 6
397 6 7
398 7 8
399 8 9
400 9 10
401 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
402 id select_type table type possible_keys key key_len ref rows Extra
403 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort
404 EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) ORDER BY f1 + 1;
405 id select_type table type possible_keys key key_len ref rows Extra
406 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort
407 # Check that GROUP BY could be optimized
408 SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
409 f1 + 1 MAX(GC)
410 1 1
411 2 2
412 3 3
413 4 4
414 5 5
415 6 6
416 7 7
417 8 8
418 9 9
419 10 10
420 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
421 id select_type table type possible_keys key key_len ref rows Extra
422 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
423 EXPLAIN SELECT f1 + 1, MAX(GC)
424 FROM t1 IGNORE KEY (gc) GROUP BY f1 + 1;
425 id select_type table type possible_keys key key_len ref rows Extra
426 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
427 # Shouldn't use index
428 SELECT * FROM t1 WHERE f1 + 1 > 7.0;
429 f1 gc
430 7 8
431 8 9
432 9 10
433 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
434 id select_type table type possible_keys key key_len ref rows Extra
435 1 SIMPLE t1 range gc gc 5 NULL 3 Using index condition
436 DROP TABLE t1;
437 # Pick index with proper type
438 CREATE TABLE t1 (f1 int,
439 gc_int int AS (f1 + 1) STORED,
440 gc_date DATE AS (f1 + 1) STORED,
441 KEY gc_int_idx(gc_int),
442 KEY gc_date_idx(gc_date));
443 INSERT INTO t1(f1) VALUES
444 (030303),(040404),
445 (050505),(060606),
446 (010101),(020202),
447 (030303),(040404),
448 (050505),(060606),
449 (010101),(020202),
450 (090909),(101010),
451 (010101),(020202),
452 (070707),(080808);
453 ANALYZE TABLE t1;
454 Table Op Msg_type Msg_text
455 test.t1 analyze status Engine-independent statistics collected
456 test.t1 analyze status OK
457 SELECT * FROM t1 WHERE f1 + 1 > 070707;
458 f1 gc_int gc_date
459 101010 101011 2010-10-11
460 70707 70708 2007-07-08
461 80808 80809 2008-08-09
462 90909 90910 2009-09-10
463 # INT column & index should be picked
464 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
465 id select_type table type possible_keys key key_len ref rows Extra
466 1 SIMPLE t1 range gc_int_idx gc_int_idx 5 NULL 4 Using index condition
467 SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
468 f1 gc_int gc_date
469 101010 101011 2010-10-11
470 70707 70708 2007-07-08
471 80808 80809 2008-08-09
472 90909 90910 2009-09-10
473 # DATE column & index should be picked
474 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
475 id select_type table type possible_keys key key_len ref rows Extra
476 1 SIMPLE t1 ALL gc_int_idx NULL NULL NULL 18 Using where
477 Warnings:
478 Note 1105 Cannot use key `gc_int_idx` part[0] for lookup: `test`.`t1`.`gc_int` of type `int` > "cast(70707 as date)" of type `date`
479 DROP TABLE t1;
480 #
481 # BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
482 #
483 CREATE TABLE t1 (
484 pk int primary key auto_increment,
485 col_int_key INTEGER ,
486 col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
487 KEY col_int_gc_key(col_int_gc_key)
488 );
489 INSERT INTO t1 ( col_int_key) VALUES (7);
490 ANALYZE TABLE t1;
491 Table Op Msg_type Msg_text
492 test.t1 analyze status Engine-independent statistics collected
493 test.t1 analyze status OK
494 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
495 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
496 ORDER BY field1, field2;
497 field1 field2
498 8 7
499 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
500 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
501 ORDER BY field1, field2;
502 id select_type table type possible_keys key key_len ref rows Extra
503 1 SIMPLE table1 ALL PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
504 1 SIMPLE table2 eq_ref PRIMARY PRIMARY 4 test.table1.pk 1
505 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
506 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
507 GROUP BY field1, field2;
508 field1 field2
509 8 7
510 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
511 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
512 GROUP BY field1, field2;
513 id select_type table type possible_keys key key_len ref rows Extra
514 1 SIMPLE table1 ALL PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
515 1 SIMPLE table2 eq_ref PRIMARY PRIMARY 4 test.table1.pk 1
516 DROP TABLE t1;
517 #
518 # Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX
519 # ON VIRTUAL COLUMN
520 #
521 CREATE TABLE t1 (
522 col1 INTEGER NOT NULL,
523 col2 INTEGER NOT NULL,
524 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
525 col3 INTEGER NOT NULL,
526 col4 INTEGER NOT NULL,
527 col5 INTEGER DEFAULT NULL,
528 col6 INTEGER DEFAULT NULL,
529 col7 INTEGER DEFAULT NULL,
530 col8 INTEGER DEFAULT NULL,
531 col9 INTEGER DEFAULT NULL,
532 col10 INTEGER DEFAULT NULL,
533 col11 INTEGER DEFAULT NULL,
534 col12 INTEGER DEFAULT NULL,
535 col13 INTEGER DEFAULT NULL,
536 col14 INTEGER DEFAULT NULL,
537 col15 INTEGER DEFAULT NULL,
538 col16 INTEGER DEFAULT NULL,
539 col17 INTEGER DEFAULT NULL,
540 col18 INTEGER DEFAULT NULL,
541 col19 INTEGER DEFAULT NULL,
542 col20 INTEGER DEFAULT NULL,
543 col21 INTEGER DEFAULT NULL,
544 col22 INTEGER DEFAULT NULL,
545 col23 INTEGER DEFAULT NULL,
546 col24 INTEGER DEFAULT NULL,
547 col25 INTEGER DEFAULT NULL,
548 col26 INTEGER DEFAULT NULL,
549 col27 INTEGER DEFAULT NULL,
550 col28 INTEGER DEFAULT NULL,
551 col29 INTEGER DEFAULT NULL,
552 col30 INTEGER DEFAULT NULL,
553 col31 INTEGER DEFAULT NULL,
554 col32 INTEGER DEFAULT NULL,
555 col33 INTEGER DEFAULT NULL,
556 col34 INTEGER DEFAULT NULL,
557 col35 INTEGER DEFAULT NULL,
558 col36 INTEGER DEFAULT NULL,
559 col37 INTEGER DEFAULT NULL,
560 col38 INTEGER DEFAULT NULL,
561 col39 INTEGER DEFAULT NULL,
562 col40 INTEGER DEFAULT NULL,
563 col41 INTEGER DEFAULT NULL,
564 col42 INTEGER DEFAULT NULL,
565 col43 INTEGER DEFAULT NULL,
566 col44 INTEGER DEFAULT NULL,
567 col45 INTEGER DEFAULT NULL,
568 col46 INTEGER DEFAULT NULL,
569 col47 INTEGER DEFAULT NULL,
570 col48 INTEGER DEFAULT NULL,
571 col49 INTEGER DEFAULT NULL,
572 col50 INTEGER DEFAULT NULL,
573 col51 INTEGER DEFAULT NULL,
574 col52 INTEGER DEFAULT NULL,
575 col53 INTEGER DEFAULT NULL,
576 col54 INTEGER DEFAULT NULL,
577 col55 INTEGER DEFAULT NULL,
578 col56 INTEGER DEFAULT NULL,
579 col57 INTEGER DEFAULT NULL,
580 col58 INTEGER DEFAULT NULL,
581 col59 INTEGER DEFAULT NULL,
582 col60 INTEGER DEFAULT NULL,
583 col61 INTEGER DEFAULT NULL,
584 col62 INTEGER DEFAULT NULL,
585 col63 INTEGER DEFAULT NULL,
586 col64 INTEGER DEFAULT NULL,
587 col65 INTEGER DEFAULT NULL,
588 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
589 KEY idx1 (gcol1)
590 );
591 INSERT INTO t1 (col1, col2, col3, col4)
592 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
593 ALTER TABLE t1 ADD COLUMN extra INTEGER;
594 SELECT gcol1 FROM t1 FORCE INDEX(idx1);
595 gcol1
596 2
597 4
598 6
599 8
600 10
601 DROP TABLE t1;
602 CREATE TABLE t1 (
603 col1 INTEGER NOT NULL,
604 col2 INTEGER NOT NULL,
605 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
606 col3 INTEGER NOT NULL,
607 col4 INTEGER NOT NULL,
608 col5 INTEGER DEFAULT NULL,
609 col6 INTEGER DEFAULT NULL,
610 col7 INTEGER DEFAULT NULL,
611 col8 INTEGER DEFAULT NULL,
612 col9 INTEGER DEFAULT NULL,
613 col10 INTEGER DEFAULT NULL,
614 col11 INTEGER DEFAULT NULL,
615 col12 INTEGER DEFAULT NULL,
616 col13 INTEGER DEFAULT NULL,
617 col14 INTEGER DEFAULT NULL,
618 col15 INTEGER DEFAULT NULL,
619 col16 INTEGER DEFAULT NULL,
620 col17 INTEGER DEFAULT NULL,
621 col18 INTEGER DEFAULT NULL,
622 col19 INTEGER DEFAULT NULL,
623 col20 INTEGER DEFAULT NULL,
624 col21 INTEGER DEFAULT NULL,
625 col22 INTEGER DEFAULT NULL,
626 col23 INTEGER DEFAULT NULL,
627 col24 INTEGER DEFAULT NULL,
628 col25 INTEGER DEFAULT NULL,
629 col26 INTEGER DEFAULT NULL,
630 col27 INTEGER DEFAULT NULL,
631 col28 INTEGER DEFAULT NULL,
632 col29 INTEGER DEFAULT NULL,
633 col30 INTEGER DEFAULT NULL,
634 col31 INTEGER DEFAULT NULL,
635 col32 INTEGER DEFAULT NULL,
636 col33 INTEGER DEFAULT NULL,
637 col34 INTEGER DEFAULT NULL,
638 col35 INTEGER DEFAULT NULL,
639 col36 INTEGER DEFAULT NULL,
640 col37 INTEGER DEFAULT NULL,
641 col38 INTEGER DEFAULT NULL,
642 col39 INTEGER DEFAULT NULL,
643 col40 INTEGER DEFAULT NULL,
644 col41 INTEGER DEFAULT NULL,
645 col42 INTEGER DEFAULT NULL,
646 col43 INTEGER DEFAULT NULL,
647 col44 INTEGER DEFAULT NULL,
648 col45 INTEGER DEFAULT NULL,
649 col46 INTEGER DEFAULT NULL,
650 col47 INTEGER DEFAULT NULL,
651 col48 INTEGER DEFAULT NULL,
652 col49 INTEGER DEFAULT NULL,
653 col50 INTEGER DEFAULT NULL,
654 col51 INTEGER DEFAULT NULL,
655 col52 INTEGER DEFAULT NULL,
656 col53 INTEGER DEFAULT NULL,
657 col54 INTEGER DEFAULT NULL,
658 col55 INTEGER DEFAULT NULL,
659 col56 INTEGER DEFAULT NULL,
660 col57 INTEGER DEFAULT NULL,
661 col58 INTEGER DEFAULT NULL,
662 col59 INTEGER DEFAULT NULL,
663 col60 INTEGER DEFAULT NULL,
664 col61 INTEGER DEFAULT NULL,
665 col62 INTEGER DEFAULT NULL,
666 col63 INTEGER DEFAULT NULL,
667 col64 INTEGER DEFAULT NULL,
668 col65 INTEGER DEFAULT NULL,
669 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
670 KEY idx1 (gcol2)
671 );
672 INSERT INTO t1 (col1, col2, col3, col4)
673 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
674 ALTER TABLE t1 ADD COLUMN extra INTEGER;
675 SELECT gcol2 FROM t1 FORCE INDEX(idx1);
676 gcol2
677 1
678 1
679 1
680 1
681 1
682 DROP TABLE t1;
683 #
684 # Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
685 #
686 CREATE TABLE t (a INT,
687 b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL,
688 c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL);
689 INSERT INTO t(a) VALUES (1);
690 SELECT * FROM t WHERE c = '0';
691 a b c
692 1 127 0
693 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
694 ALTER TABLE t ADD UNIQUE INDEX (c(1));
695 Warnings:
696 Warning 1264 Out of range value for column 'b' at row 0
697 SELECT * FROM t WHERE c = '0';
698 a b c
699 1 127 0
700 DROP TABLE t;
701 #
702 # Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD()
703 # DID NOT RETURN TRUE WITH DIVIDE 0
704 #
705 CREATE TABLE t (a INT, b INT, h VARCHAR(10));
706 INSERT INTO t VALUES (12, 3, "ss");
707 INSERT INTO t VALUES (13, 4, "ss");
708 INSERT INTO t VALUES (14, 0, "ss");
709 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
710 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
711 CREATE INDEX idx ON t(c);
712 ERROR 22012: Division by 0
713 CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed");
714 DROP TABLE t;
715 #
716 # Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
717 # WITH LOGICAL OPERATORS
718 #
719 CREATE TABLE t (a INT, b INT,
720 gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
721 gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
722 gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
723 gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
724 gc_case INT GENERATED ALWAYS AS
725 (CASE WHEN (a AND b) THEN a ELSE b END) STORED,
726 INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
727 INDEX(gc_case));
728 INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
729 ANALYZE TABLE t;
730 Table Op Msg_type Msg_text
731 test.t analyze status Engine-independent statistics collected
732 test.t analyze status OK
733 EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
734 id select_type table type possible_keys key key_len ref rows Extra
735 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
736 SELECT a, b FROM t WHERE (a AND b) = 1;
737 a b
738 1 1
739 EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
740 id select_type table type possible_keys key key_len ref rows Extra
741 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
742 SELECT a, b FROM t WHERE 1 = (a AND b);
743 a b
744 1 1
745 EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
746 id select_type table type possible_keys key key_len ref rows Extra
747 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
748 SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
749 a b
750 1 1
751 EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
752 id select_type table type possible_keys key key_len ref rows Extra
753 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
754 SELECT a, b FROM t WHERE (a OR b) = 1;
755 a b
756 0 1
757 1 0
758 1 1
759 EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
760 id select_type table type possible_keys key key_len ref rows Extra
761 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
762 SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
763 a b
764 0 1
765 1 0
766 1 1
767 EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
768 id select_type table type possible_keys key key_len ref rows Extra
769 1 SIMPLE t ref gc_xor gc_xor 5 const 2
770 SELECT a, b FROM t WHERE (a XOR b) = 1;
771 a b
772 0 1
773 1 0
774 EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
775 id select_type table type possible_keys key key_len ref rows Extra
776 1 SIMPLE t ref gc_not gc_not 5 const 2
777 SELECT a FROM t WHERE (NOT a) = 1;
778 a
779 0
780 0
781 EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
782 id select_type table type possible_keys key key_len ref rows Extra
783 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
784 SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
785 a
786 0
787 1
788 EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
789 id select_type table type possible_keys key key_len ref rows Extra
790 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
791 SELECT a, b FROM t WHERE 1 = (b AND a);
792 a b
793 1 1
794 EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
795 id select_type table type possible_keys key key_len ref rows Extra
796 1 SIMPLE t ALL NULL NULL NULL NULL 4 Using where
797 SELECT a, b FROM t WHERE 1 = (b OR a);
798 a b
799 0 1
800 1 0
801 1 1
802 DROP TABLE t;
803 #
804 # Bug#22810883: ASSERTION FAILED:
805 # !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
806 #
807 CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
808 a2 INTEGER, KEY (a1));
809 INSERT INTO t1 VALUES ();
810 CREATE TABLE t2 (b INTEGER);
811 INSERT INTO t2 VALUES (1);
812 ANALYZE TABLE t1, t2;
813 Table Op Msg_type Msg_text
814 test.t1 analyze status Engine-independent statistics collected
815 test.t1 analyze status OK
816 test.t2 analyze status Engine-independent statistics collected
817 test.t2 analyze status OK
818 # Used to choose the index on a1 and get wrong results.
819 EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0;
820 id select_type table type possible_keys key key_len ref rows Extra
821 1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where
822 SELECT * FROM t1 WHERE (a2 AND a2) = 0;
823 a1 a2
824 # Used to get assertion or wrong results.
825 EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
826 id select_type table type possible_keys key key_len ref rows Extra
827 1 SIMPLE t1 ALL NULL NULL NULL NULL 1
828 1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
829 SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
830 a1 a2 b
831 0 NULL 1
832 DROP TABLE t1, t2;
833 #
834 # MDEV-20618 Assertion `btr_validate_index(index, 0, false)' failed
835 # in row_upd_sec_index_entry
836 #
837 CREATE TABLE t1 (A BIT(15), VA BIT(10) GENERATED ALWAYS AS (A),PK INT,
838 PRIMARY KEY (PK), UNIQUE KEY (VA));
839 INSERT IGNORE INTO t1 VALUES ( '\r1','a',1);
840 Warnings:
841 Warning 1906 The value specified for generated column 'VA' in table 't1' has been ignored
842 Warning 1264 Out of range value for column 'VA' at row 1
843 REPLACE INTO t1 (PK) VALUES (1);
844 ERROR 22001: Data too long for column 'VA' at row 1
845 DROP TABLE t1;
846 #
847 # MDEV-17890 Record in index was not found on update, server crash in
848 # row_upd_build_difference_binary or
849 # Assertion `0' failed in row_upd_sec_index_entry
850 #
851 CREATE TABLE t1 (
852 pk BIGINT AUTO_INCREMENT,
853 b BIT(15),
854 v BIT(10) AS (b) VIRTUAL,
855 PRIMARY KEY(pk),
856 UNIQUE(v)
857 );
858 INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101');
859 Warnings:
860 Warning 1264 Out of range value for column 'v' at row 1
861 SELECT pk, b INTO OUTFILE 'load.data' FROM t1;
862 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b);
863 ERROR 22001: Data too long for column 'v' at row 1
864 DROP TABLE t1;
865 #
866 # MDEV-17834 Server crashes in row_upd_build_difference_binary
867 # on LOAD DATA into table with indexed virtual column
868 #
869 CREATE TABLE t1 (
870 pk INT,
871 i TINYINT,
872 ts TIMESTAMP NULL,
873 vi TINYINT AS (i+1) PERSISTENT,
874 vts TIMESTAMP(5) AS (ts) VIRTUAL,
875 PRIMARY KEY(pk),
876 UNIQUE(vts)
877 );
878 INSERT IGNORE INTO t1 (pk,i) VALUES (1,127);
879 Warnings:
880 Warning 1264 Out of range value for column 'vi' at row 1
881 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/load.data' REPLACE INTO TABLE t1 (pk,i,ts);
882 ERROR 22003: Out of range value for column 'vi' at row 1
883 DROP TABLE t1;
884 # MDEV-19011 Assertion `file->s->base.reclength < file->s->vreclength'
885 # failed in ha_myisam::setup_vcols_for_repair
886 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL);
887 ALTER TABLE t1 ADD KEY (a);
888 DROP TABLE t1;
889 #
890 # BUG#21365158 WL8149:ASSERTION `!TABLE || (!TABLE->WRITE_SET
891 #
892 CREATE TABLE t1 (
893 pk INTEGER AUTO_INCREMENT,
894 col_int_nokey INTEGER NOT NULL,
895 col_varchar_nokey VARCHAR(1),
896 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
897 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
898 PRIMARY KEY (pk)
899 );
900 INSERT INTO t1 ( col_int_nokey, col_varchar_nokey)
901 VALUES (4, 'b'),(9, 'o'),(4, 'k'),(5, 'a'),(5, 'f'),
902 (9, 't'),(3, 'c'),(8, 'c'),(0, 'r'),(98, 'k');
903 CREATE TABLE t2 (
904 pk INTEGER AUTO_INCREMENT,
905 col_int_nokey INTEGER NOT NULL,
906 col_varchar_nokey VARCHAR(1) NOT NULL,
907 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
908 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
909 PRIMARY KEY (pk),
910 UNIQUE KEY (col_varchar_key)
911 );
912 INSERT INTO t2 ( col_int_nokey, col_varchar_nokey)
913 VALUES (1, 'c'),(8, 'm'),(9, 'd'), (6, 'y'),(1, 't'),
914 (2, 's'),(4, 'r');
915 SELECT
916 CONCAT( t2.col_varchar_nokey , t2.col_varchar_nokey ) AS f2,
917 t1.col_varchar_key AS f5
918 FROM
919 t2 LEFT JOIN t1 ON t2.col_int_nokey > t1.col_int_nokey
920 ORDER BY f2, f5;
921 f2 f5
922 cc rr
923 dd aa
924 dd bb
925 dd cc
926 dd cc
927 dd ff
928 dd kk
929 dd rr
930 mm aa
931 mm bb
932 mm cc
933 mm ff
934 mm kk
935 mm rr
936 rr cc
937 rr rr
938 ss rr
939 tt rr
940 yy aa
941 yy bb
942 yy cc
943 yy ff
944 yy kk
945 yy rr
946 DROP TABLE t1,t2;
947 #
948 DROP VIEW IF EXISTS v1,v2;
949 DROP TABLE IF EXISTS t1,t2,t3;
950 DROP PROCEDURE IF EXISTS p1;
951 DROP FUNCTION IF EXISTS f1;
952 DROP TRIGGER IF EXISTS trg1;
953 DROP TRIGGER IF EXISTS trg2;
954 set sql_warnings = 0;