Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_keys.inc

raw
1 ################################################################################
2 # inc/gcol_keys.inc #
3 # #
4 # Purpose: #
5 # Testing keys, indexes defined upon generated columns. #
6 # #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-02 #
12 # Change Author: #
13 # Change Date: #
14 # Change: #
15 ################################################################################
16
17 --source include/have_sequence.inc
18
19 if (!$support_virtual_index) {
20 let $skip_spatial_index_check=1;
21 let $skip_foreign_key_check=1;
22 }
23
24 --echo # - UNIQUE KEY
25 --echo # - INDEX
26 --echo # - FULLTEXT INDEX
27 --echo # - SPATIAL INDEX (not supported)
28 --echo # - FOREIGN INDEX (partially supported)
29 --echo # - CHECK (allowed but not used)
30
31 --echo # UNIQUE
32 if($support_virtual_index)
33 {
34 create table t1 (a int, b int generated always as (a*2) virtual unique);
35 show create table t1;
36 describe t1;
37 drop table t1;
38 }
39 create table t1 (a int, b int generated always as (a*2) stored unique);
40 show create table t1;
41 describe t1;
42 drop table t1;
43
44 if($support_virtual_index)
45 {
46 create table t1 (a int, b int generated always as (a*2) virtual, unique key (b));
47 show create table t1;
48 describe t1;
49 drop table t1;
50 }
51 create table t1 (a int, b int generated always as (a*2) stored, unique (b));
52 show create table t1;
53 describe t1;
54 drop table t1;
55
56 if($support_virtual_index)
57 {
58 create table t1 (a int, b int generated always as (a*2) virtual);
59 alter table t1 add unique key (b);
60 drop table t1;
61 }
62 create table t1 (a int, b int generated always as (a*2) stored);
63 alter table t1 add unique key (b);
64 drop table t1;
65
66 --echo # Testing data manipulation operations involving UNIQUE keys
67 --echo # on generated columns can be found in:
68 --echo # - gcol_ins_upd.inc
69 --echo # - gcol_select.inc
70
71 --echo #
72 --echo # INDEX
73 if($support_virtual_index)
74 {
75 create table t1 (a int, b int generated always as (a*2) virtual, index (b));
76 show create table t1;
77 describe t1;
78 drop table t1;
79
80 create table t1 (a int, b int generated always as (a*2) virtual, index (a,b));
81 drop table t1;
82 }
83
84 create table t1 (a int, b int generated always as (a*2) stored, index (b));
85 show create table t1;
86 describe t1;
87 drop table t1;
88
89 create table t1 (a int, b int generated always as (a*2) stored, index (a,b));
90 show create table t1;
91 describe t1;
92 drop table t1;
93
94 if($support_virtual_index)
95 {
96 create table t1 (a int, b int generated always as (a*2) virtual);
97 alter table t1 add index (b);
98
99 alter table t1 add index (a,b);
100 drop table t1;
101 }
102
103 create table t1 (a int, b int generated always as (a*2) stored);
104 alter table t1 add index (b);
105 drop table t1;
106
107 create table t1 (a int, b int generated always as (a*2) stored);
108 alter table t1 add index (a,b);
109 create table t2 like t1;
110 drop table t2;
111 drop table t1;
112
113 --echo # Testing data manipulation operations involving INDEX
114 --echo # on generated columns can be found in:
115 --echo # - gcol_select.inc
116
117 --echo #
118 --echo # TODO: FULLTEXT INDEX
119
120 --echo # SPATIAL INDEX
121 if (!$skip_spatial_index_check)
122 {
123 --echo # Error "All parts of a SPATIAL index must be geometrical"
124 --error ER_WRONG_ARGUMENTS
125 create table t1 (a int, b int generated always as (a+1) stored, spatial index (b));
126 create table t1 (a int, b int generated always as (a+1) stored);
127 --error ER_WRONG_ARGUMENTS
128 alter table t1 add spatial index (b);
129 drop table t1;
130 }
131
132 --echo # FOREIGN KEY
133
134 --echo # Rejected FK options.
135 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
136 create table t1 (a int, b int generated always as (a+1) stored,
137 foreign key (b) references t2(a) on update set null);
138 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
139 create table t1 (a int, b int generated always as (a+1) stored,
140 foreign key (b) references t2(a) on update cascade);
141 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
142 create table t1 (a int, b int generated always as (a+1) stored,
143 foreign key (b) references t2(a) on delete set null);
144
145 create table t1 (a int, b int generated always as (a+1) stored);
146 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
147 alter table t1 add foreign key (b) references t2(a) on update set null;
148 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
149 alter table t1 add foreign key (b) references t2(a) on update cascade;
150 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
151 alter table t1 add foreign key (b) references t2(a) on delete set null;
152 drop table t1;
153
154 if(!$skip_foreign_key_check)
155 {
156 --error ER_CANT_CREATE_TABLE
157 create table t1 (a int, b int generated always as (a+1) virtual,
158 foreign key (b) references t2(a));
159
160 create table t1 (a int, b int generated always as (a+1) virtual);
161 --error ER_CANT_CREATE_TABLE
162 alter table t1 add foreign key (b) references t2(a);
163 drop table t1;
164 }
165
166 --echo # Allowed FK options.
167 create table t2 (a int primary key, b char(5));
168 create table t1 (a int, b int generated always as (a % 10) stored,
169 foreign key (b) references t2(a) on update restrict);
170 drop table t1;
171 create table t1 (a int, b int generated always as (a % 10) stored,
172 foreign key (b) references t2(a) on update no action);
173 drop table t1;
174 create table t1 (a int, b int generated always as (a % 10) stored,
175 foreign key (b) references t2(a) on delete restrict);
176 drop table t1;
177 create table t1 (a int, b int generated always as (a % 10) stored,
178 foreign key (b) references t2(a) on delete cascade);
179 drop table t1;
180 create table t1 (a int, b int generated always as (a % 10) stored,
181 foreign key (b) references t2(a) on delete no action);
182 drop table t1,t2;
183
184 if($support_virtual_index)
185 {
186 --echo #
187 --echo # Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED
188 --echo #
189 CREATE TABLE c (
190 pk integer AUTO_INCREMENT,
191 col_datetime_nokey DATETIME /*! NULL */,
192 col_time_nokey TIME /*! NULL */,
193 col_datetime_key DATETIME GENERATED ALWAYS AS
194 (ADDTIME(col_datetime_nokey, col_time_nokey)),
195 col_time_key TIME GENERATED ALWAYS AS
196 (ADDTIME(col_datetime_nokey, col_time_nokey)),
197 col_varchar_nokey VARCHAR(1) /*! NULL */,
198 PRIMARY KEY (pk),
199 KEY (col_time_key),
200 KEY (col_datetime_key));
201
202 --disable_warnings
203 INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values
204 ('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'),
205 ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL),
206 ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'),
207 ('18:56:33.027423','2003-04-01 00:00:00', 'i');
208
209 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;
210 --enable_warnings
211
212 --replace_column 9 x 10 x
213 EXPLAIN SELECT
214 outr.col_time_key AS x
215 FROM c as outr
216 WHERE
217 outr.col_varchar_nokey in ('c', 'x', 'i')
218 AND (outr.col_time_key IS NULL OR
219 outr.col_datetime_key = '2009-09-27');
220
221 SELECT
222 outr.col_time_key AS x
223 FROM c AS outr
224 WHERE
225 outr.col_varchar_nokey in ('c', 'x', 'i')
226 AND (outr.col_time_key IS NULL OR
227 outr.col_datetime_key = '2009-09-27');
228
229 DROP TABLE c;
230
231 --echo #
232 --echo # Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP |
233 --echo # INNOBASE/INCLUDE/DATA0DATA.IC:253
234 --echo #
235 CREATE TABLE A (
236 col_varchar_nokey TEXT ,
237 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
238 KEY (col_varchar_key(50))
239 );
240
241 INSERT INTO A (col_varchar_nokey) VALUES ('');
242
243 CREATE TABLE D (
244 pk INTEGER AUTO_INCREMENT,
245 col_date_nokey BLOB,
246 col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL,
247 col_datetime_nokey LONGBLOB,
248 col_time_nokey LONGTEXT,
249
250 col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
251 col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
252
253 col_varchar_nokey TEXT,
254 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
255
256 PRIMARY KEY (pk),
257 KEY (col_varchar_key(50)),
258 KEY (col_date_key(20)),
259 KEY (col_time_key(20)),
260 KEY (col_datetime_key(20)),
261 KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5))
262 );
263
264 INSERT INTO D (
265 col_date_nokey,
266 col_time_nokey,
267 col_datetime_nokey,
268 col_varchar_nokey
269 ) VALUES ('', '', '', ''),('', '', '', '');
270
271 DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON
272 ( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` );
273
274 DROP TABLE IF EXISTS A,D;
275 --echo #
276 --echo # Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL |
277 --echo # INNOBASE/HANDLER/HANDLER0ALTER.CC
278 --echo #
279 CREATE TABLE t1 (
280 col1 int(11) DEFAULT NULL,
281 col2 int(11) DEFAULT NULL,
282 col3 int(11) NOT NULL,
283 col4 int(11) DEFAULT NULL,
284 col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL,
285 col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
286 col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
287 col9 text,
288 col6 int(11) DEFAULT NULL,
289 PRIMARY KEY (`col3`),
290 UNIQUE KEY uidx (`col2`),
291 KEY idx (`col5`)
292 );
293
294 INSERT INTO t1(col1,col2,col3,col4,col9,col6)
295 VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL);
296
297 ALTER TABLE t1 ADD COLUMN extra INT;
298 DROP TABLE t1;
299
300
301 --echo #
302 --echo # Bug#21316860: WL8149:INNODB: FAILING ASSERTION:
303 --echo # TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED
304 --echo #
305 CREATE TABLE t1 (
306 pk int(11) NOT NULL,
307 col_int_nokey int(11),
308 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
309 col_date_nokey date,
310 col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL,
311 PRIMARY KEY (pk),
312 UNIQUE KEY col_int_key (col_int_key)
313 );
314
315 ALTER TABLE t1 DROP COLUMN pk;
316 DROP TABLE t1;
317
318 --echo # Remove the impact on PK choose by index on virtual generated column
319 CREATE TABLE t1 (
320 pk int(11) NOT NULL,
321 col_int_nokey int(11) DEFAULT NULL,
322 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
323 UNIQUE KEY col_int_key (col_int_key)
324 );
325
326 ALTER TABLE t1 add unique index idx(pk);
327 DESC t1;
328 DROP TABLE t1;
329
330 --echo #
331 --echo # Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
332 --echo #
333
334 CREATE TABLE t1 (
335 id INTEGER NOT NULL,
336 b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL,
337 UNIQUE KEY (b)
338 );
339
340 INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);
341
342 --disable_query_log
343 --disable_result_log
344 ANALYZE TABLE t1;
345 --enable_result_log
346 --enable_query_log
347
348 # covering index scan
349 let query= SELECT b FROM t1 FORCE INDEX(b);
350 eval EXPLAIN $query;
351 eval $query;
352
353 # range scan
354 let $query= SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
355 eval EXPLAIN $query;
356 eval $query;
357
358 DROP TABLE t1;
359
360 }
361 --echo
362 --echo # Testing data manipulation operations involving FOREIGN KEY
363 --echo # on generated columns can be found in:
364 --echo # - gcol_ins_upd.inc
365 --echo # - gcol_select.inc
366
367 --echo #
368 --echo # TODO: CHECK
369
370 --echo #
371 --echo # Test how optimizer picks indexes defined on a GC
372 --echo #
373 CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc));
374 INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
375 ANALYZE TABLE t1;
376 --echo # Should use index
377 --sorted_result
378 SELECT * FROM t1 WHERE f1 + 1 > 7;
379 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
380
381 SELECT * FROM t1 WHERE f1 + 1 = 7;
382 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
383 --sorted_result
384 SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
385 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
386 --sorted_result
387 SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
388 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
389
390 --echo # Check that expression isn't transformed for a disabled key
391 --sorted_result
392 SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
393 EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7;
394
395 --echo # Check that ORDER BY could be optimized
396 SELECT * FROM t1 ORDER BY f1 + 1;
397 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
398 EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) ORDER BY f1 + 1;
399
400 --echo # Check that GROUP BY could be optimized
401 SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
402 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
403 EXPLAIN SELECT f1 + 1, MAX(GC)
404 FROM t1 IGNORE KEY (gc) GROUP BY f1 + 1;
405
406 --echo # Shouldn't use index
407 --sorted_result
408 SELECT * FROM t1 WHERE f1 + 1 > 7.0;
409 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
410
411 DROP TABLE t1;
412 --echo # Pick index with proper type
413 CREATE TABLE t1 (f1 int,
414 gc_int int AS (f1 + 1) STORED,
415 gc_date DATE AS (f1 + 1) STORED,
416 KEY gc_int_idx(gc_int),
417 KEY gc_date_idx(gc_date));
418 INSERT INTO t1(f1) VALUES
419 (030303),(040404),
420 (050505),(060606),
421 (010101),(020202),
422 (030303),(040404),
423 (050505),(060606),
424 (010101),(020202),
425 (090909),(101010),
426 (010101),(020202),
427 (070707),(080808);
428 ANALYZE TABLE t1;
429
430 --sorted_result
431 SELECT * FROM t1 WHERE f1 + 1 > 070707;
432 --echo # INT column & index should be picked
433 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
434 --sorted_result
435 SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
436 --echo # DATE column & index should be picked
437 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
438
439 DROP TABLE t1;
440
441 --echo #
442 --echo # BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
443 --echo #
444 CREATE TABLE t1 (
445 pk int primary key auto_increment,
446 col_int_key INTEGER ,
447 col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
448 KEY col_int_gc_key(col_int_gc_key)
449 );
450
451 INSERT INTO t1 ( col_int_key) VALUES (7);
452
453 ANALYZE TABLE t1;
454
455 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
456 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
457 ORDER BY field1, field2;
458
459 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
460 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
461 ORDER BY field1, field2;
462
463 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
464 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
465 GROUP BY field1, field2;
466
467 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
468 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
469 GROUP BY field1, field2;
470
471 DROP TABLE t1;
472
473 if($support_virtual_index)
474 {
475 --echo #
476 --echo # Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX
477 --echo # ON VIRTUAL COLUMN
478 --echo #
479
480 #
481 # Test 1: column number 2 and 66 are virtual and there is an index
482 # on column number 2.
483 #
484 CREATE TABLE t1 (
485 col1 INTEGER NOT NULL,
486 col2 INTEGER NOT NULL,
487 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
488 col3 INTEGER NOT NULL,
489 col4 INTEGER NOT NULL,
490 col5 INTEGER DEFAULT NULL,
491 col6 INTEGER DEFAULT NULL,
492 col7 INTEGER DEFAULT NULL,
493 col8 INTEGER DEFAULT NULL,
494 col9 INTEGER DEFAULT NULL,
495 col10 INTEGER DEFAULT NULL,
496 col11 INTEGER DEFAULT NULL,
497 col12 INTEGER DEFAULT NULL,
498 col13 INTEGER DEFAULT NULL,
499 col14 INTEGER DEFAULT NULL,
500 col15 INTEGER DEFAULT NULL,
501 col16 INTEGER DEFAULT NULL,
502 col17 INTEGER DEFAULT NULL,
503 col18 INTEGER DEFAULT NULL,
504 col19 INTEGER DEFAULT NULL,
505 col20 INTEGER DEFAULT NULL,
506 col21 INTEGER DEFAULT NULL,
507 col22 INTEGER DEFAULT NULL,
508 col23 INTEGER DEFAULT NULL,
509 col24 INTEGER DEFAULT NULL,
510 col25 INTEGER DEFAULT NULL,
511 col26 INTEGER DEFAULT NULL,
512 col27 INTEGER DEFAULT NULL,
513 col28 INTEGER DEFAULT NULL,
514 col29 INTEGER DEFAULT NULL,
515 col30 INTEGER DEFAULT NULL,
516 col31 INTEGER DEFAULT NULL,
517 col32 INTEGER DEFAULT NULL,
518 col33 INTEGER DEFAULT NULL,
519 col34 INTEGER DEFAULT NULL,
520 col35 INTEGER DEFAULT NULL,
521 col36 INTEGER DEFAULT NULL,
522 col37 INTEGER DEFAULT NULL,
523 col38 INTEGER DEFAULT NULL,
524 col39 INTEGER DEFAULT NULL,
525 col40 INTEGER DEFAULT NULL,
526 col41 INTEGER DEFAULT NULL,
527 col42 INTEGER DEFAULT NULL,
528 col43 INTEGER DEFAULT NULL,
529 col44 INTEGER DEFAULT NULL,
530 col45 INTEGER DEFAULT NULL,
531 col46 INTEGER DEFAULT NULL,
532 col47 INTEGER DEFAULT NULL,
533 col48 INTEGER DEFAULT NULL,
534 col49 INTEGER DEFAULT NULL,
535 col50 INTEGER DEFAULT NULL,
536 col51 INTEGER DEFAULT NULL,
537 col52 INTEGER DEFAULT NULL,
538 col53 INTEGER DEFAULT NULL,
539 col54 INTEGER DEFAULT NULL,
540 col55 INTEGER DEFAULT NULL,
541 col56 INTEGER DEFAULT NULL,
542 col57 INTEGER DEFAULT NULL,
543 col58 INTEGER DEFAULT NULL,
544 col59 INTEGER DEFAULT NULL,
545 col60 INTEGER DEFAULT NULL,
546 col61 INTEGER DEFAULT NULL,
547 col62 INTEGER DEFAULT NULL,
548 col63 INTEGER DEFAULT NULL,
549 col64 INTEGER DEFAULT NULL,
550 col65 INTEGER DEFAULT NULL,
551 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
552 KEY idx1 (gcol1)
553 );
554
555 INSERT INTO t1 (col1, col2, col3, col4)
556 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
557
558 # This will call my_eval_gcolumn_expr to compute the indexed column value
559 ALTER TABLE t1 ADD COLUMN extra INTEGER;
560
561 SELECT gcol1 FROM t1 FORCE INDEX(idx1);
562
563 DROP TABLE t1;
564
565 #
566 # Test 2: column number 2 and 66 are virtual and there is an index
567 # on column number 66.
568 #
569 CREATE TABLE t1 (
570 col1 INTEGER NOT NULL,
571 col2 INTEGER NOT NULL,
572 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
573 col3 INTEGER NOT NULL,
574 col4 INTEGER NOT NULL,
575 col5 INTEGER DEFAULT NULL,
576 col6 INTEGER DEFAULT NULL,
577 col7 INTEGER DEFAULT NULL,
578 col8 INTEGER DEFAULT NULL,
579 col9 INTEGER DEFAULT NULL,
580 col10 INTEGER DEFAULT NULL,
581 col11 INTEGER DEFAULT NULL,
582 col12 INTEGER DEFAULT NULL,
583 col13 INTEGER DEFAULT NULL,
584 col14 INTEGER DEFAULT NULL,
585 col15 INTEGER DEFAULT NULL,
586 col16 INTEGER DEFAULT NULL,
587 col17 INTEGER DEFAULT NULL,
588 col18 INTEGER DEFAULT NULL,
589 col19 INTEGER DEFAULT NULL,
590 col20 INTEGER DEFAULT NULL,
591 col21 INTEGER DEFAULT NULL,
592 col22 INTEGER DEFAULT NULL,
593 col23 INTEGER DEFAULT NULL,
594 col24 INTEGER DEFAULT NULL,
595 col25 INTEGER DEFAULT NULL,
596 col26 INTEGER DEFAULT NULL,
597 col27 INTEGER DEFAULT NULL,
598 col28 INTEGER DEFAULT NULL,
599 col29 INTEGER DEFAULT NULL,
600 col30 INTEGER DEFAULT NULL,
601 col31 INTEGER DEFAULT NULL,
602 col32 INTEGER DEFAULT NULL,
603 col33 INTEGER DEFAULT NULL,
604 col34 INTEGER DEFAULT NULL,
605 col35 INTEGER DEFAULT NULL,
606 col36 INTEGER DEFAULT NULL,
607 col37 INTEGER DEFAULT NULL,
608 col38 INTEGER DEFAULT NULL,
609 col39 INTEGER DEFAULT NULL,
610 col40 INTEGER DEFAULT NULL,
611 col41 INTEGER DEFAULT NULL,
612 col42 INTEGER DEFAULT NULL,
613 col43 INTEGER DEFAULT NULL,
614 col44 INTEGER DEFAULT NULL,
615 col45 INTEGER DEFAULT NULL,
616 col46 INTEGER DEFAULT NULL,
617 col47 INTEGER DEFAULT NULL,
618 col48 INTEGER DEFAULT NULL,
619 col49 INTEGER DEFAULT NULL,
620 col50 INTEGER DEFAULT NULL,
621 col51 INTEGER DEFAULT NULL,
622 col52 INTEGER DEFAULT NULL,
623 col53 INTEGER DEFAULT NULL,
624 col54 INTEGER DEFAULT NULL,
625 col55 INTEGER DEFAULT NULL,
626 col56 INTEGER DEFAULT NULL,
627 col57 INTEGER DEFAULT NULL,
628 col58 INTEGER DEFAULT NULL,
629 col59 INTEGER DEFAULT NULL,
630 col60 INTEGER DEFAULT NULL,
631 col61 INTEGER DEFAULT NULL,
632 col62 INTEGER DEFAULT NULL,
633 col63 INTEGER DEFAULT NULL,
634 col64 INTEGER DEFAULT NULL,
635 col65 INTEGER DEFAULT NULL,
636 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
637 KEY idx1 (gcol2)
638 );
639
640 INSERT INTO t1 (col1, col2, col3, col4)
641 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
642
643 # This will call my_eval_gcolumn_expr to compute the indexed column value
644 ALTER TABLE t1 ADD COLUMN extra INTEGER;
645
646 SELECT gcol2 FROM t1 FORCE INDEX(idx1);
647
648 DROP TABLE t1;
649 }
650
651 if($support_virtual_index)
652 {
653 --echo #
654 --echo # Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
655 --echo #
656 # When generating the value of column b, an out-of-range warning is
657 # raised. A warning is required in order to reproduce the bug, but it
658 # is promoted to an error on insertion unless we turn off strict mode.
659 CREATE TABLE t (a INT,
660 b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL,
661 c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL);
662 INSERT INTO t(a) VALUES (1);
663 # Before index was created, this query returned the expected one match.
664 SELECT * FROM t WHERE c = '0';
665 # Adding an index sometimes crashed, other times populated it with garbage ...
666 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
667 ALTER TABLE t ADD UNIQUE INDEX (c(1));
668 # ... so that this query found no match in the index.
669 SELECT * FROM t WHERE c = '0';
670 DROP TABLE t;
671
672 --echo #
673 --echo # Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD()
674 --echo # DID NOT RETURN TRUE WITH DIVIDE 0
675 --echo #
676 CREATE TABLE t (a INT, b INT, h VARCHAR(10));
677 INSERT INTO t VALUES (12, 3, "ss");
678 INSERT INTO t VALUES (13, 4, "ss");
679 INSERT INTO t VALUES (14, 0, "ss");
680 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
681 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
682 --error ER_DIVISION_BY_ZERO
683 CREATE INDEX idx ON t(c);
684 CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed");
685 DROP TABLE t;
686 }
687
688 --echo #
689 --echo # Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
690 --echo # WITH LOGICAL OPERATORS
691 --echo #
692 CREATE TABLE t (a INT, b INT,
693 gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
694 gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
695 gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
696 gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
697 gc_case INT GENERATED ALWAYS AS
698 (CASE WHEN (a AND b) THEN a ELSE b END) STORED,
699 INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
700 INDEX(gc_case));
701 INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
702 ANALYZE TABLE t;
703 EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
704 SELECT a, b FROM t WHERE (a AND b) = 1;
705 EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
706 SELECT a, b FROM t WHERE 1 = (a AND b);
707 EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
708 SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
709 EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
710 --sorted_result
711 SELECT a, b FROM t WHERE (a OR b) = 1;
712 EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
713 --sorted_result
714 SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
715 # XOR and NOT worked even before the bug fix, but we test all logical
716 # operators here for completeness.
717 EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
718 --sorted_result
719 SELECT a, b FROM t WHERE (a XOR b) = 1;
720 EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
721 SELECT a FROM t WHERE (NOT a) = 1;
722 # Also verify that a logical expression nested inside another
723 # expression doesn't prevent substitution.
724 EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
725 --sorted_result
726 SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
727 # The expression must be exactly the same as the generated expression.
728 # (b AND a) is not recognized as equivalent to (a AND b).
729 EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
730 SELECT a, b FROM t WHERE 1 = (b AND a);
731 --sorted_result
732 EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
733 SELECT a, b FROM t WHERE 1 = (b OR a);
734 DROP TABLE t;
735
736 --echo #
737 --echo # Bug#22810883: ASSERTION FAILED:
738 --echo # !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
739 --echo #
740 CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
741 a2 INTEGER, KEY (a1));
742 INSERT INTO t1 VALUES ();
743 CREATE TABLE t2 (b INTEGER);
744 INSERT INTO t2 VALUES (1);
745 ANALYZE TABLE t1, t2;
746 --echo # Used to choose the index on a1 and get wrong results.
747 --let $query= SELECT * FROM t1 WHERE (a2 AND a2) = 0
748 --eval EXPLAIN $query
749 --eval $query
750 --echo # Used to get assertion or wrong results.
751 --let $query= SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1
752 --eval EXPLAIN $query
753 --eval $query
754 DROP TABLE t1, t2;
755
756 if($support_virtual_index)
757 {
758 --echo #
759 --echo # MDEV-20618 Assertion `btr_validate_index(index, 0, false)' failed
760 --echo # in row_upd_sec_index_entry
761 --echo #
762 CREATE TABLE t1 (A BIT(15), VA BIT(10) GENERATED ALWAYS AS (A),PK INT,
763 PRIMARY KEY (PK), UNIQUE KEY (VA));
764
765 INSERT IGNORE INTO t1 VALUES ( '\r1','a',1);
766 --error ER_DATA_TOO_LONG
767 REPLACE INTO t1 (PK) VALUES (1);
768
769 DROP TABLE t1;
770
771 --echo #
772 --echo # MDEV-17890 Record in index was not found on update, server crash in
773 --echo # row_upd_build_difference_binary or
774 --echo # Assertion `0' failed in row_upd_sec_index_entry
775 --echo #
776 CREATE TABLE t1 (
777 pk BIGINT AUTO_INCREMENT,
778 b BIT(15),
779 v BIT(10) AS (b) VIRTUAL,
780 PRIMARY KEY(pk),
781 UNIQUE(v)
782 );
783
784 INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101');
785 SELECT pk, b INTO OUTFILE 'load.data' FROM t1;
786 --error ER_DATA_TOO_LONG
787 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b);
788
789 --let $datadir= `SELECT @@datadir`
790 --remove_file $datadir/test/load.data
791 DROP TABLE t1;
792
793 --echo #
794 --echo # MDEV-17834 Server crashes in row_upd_build_difference_binary
795 --echo # on LOAD DATA into table with indexed virtual column
796 --echo #
797 CREATE TABLE t1 (
798 pk INT,
799 i TINYINT,
800 ts TIMESTAMP NULL,
801 vi TINYINT AS (i+1) PERSISTENT,
802 vts TIMESTAMP(5) AS (ts) VIRTUAL,
803 PRIMARY KEY(pk),
804 UNIQUE(vts)
805 );
806
807 INSERT IGNORE INTO t1 (pk,i) VALUES (1,127);
808
809 --write_file $MYSQLTEST_VARDIR/tmp/load.data
810 1 4 2019-01-01 00:00:00
811 EOF
812 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
813 --error ER_WARN_DATA_OUT_OF_RANGE
814 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/load.data' REPLACE INTO TABLE t1 (pk,i,ts);
815
816 --remove_file $MYSQLTEST_VARDIR/tmp/load.data
817 DROP TABLE t1;
818
819 --echo # MDEV-19011 Assertion `file->s->base.reclength < file->s->vreclength'
820 --echo # failed in ha_myisam::setup_vcols_for_repair
821 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL);
822 ALTER TABLE t1 ADD KEY (a);
823
824 DROP TABLE t1;
825
826 }