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
18 --echo # - UNIQUE KEY
19 --echo # - INDEX
20 --echo # - FULLTEXT INDEX
21 --echo # - SPATIAL INDEX (not supported)
22 --echo # - FOREIGN INDEX (partially supported)
23 --echo # - CHECK (allowed but not used)
24
25 --echo # UNIQUE
26 if($support_virtual_index)
27 {
28 create table t1 (a int, b int generated always as (a*2) virtual unique);
29 show create table t1;
30 describe t1;
31 drop table t1;
32 }
33 create table t1 (a int, b int generated always as (a*2) stored unique);
34 show create table t1;
35 describe t1;
36 drop table t1;
37
38 if($support_virtual_index)
39 {
40 create table t1 (a int, b int generated always as (a*2) virtual, unique key (b));
41 show create table t1;
42 describe t1;
43 drop table t1;
44 }
45 create table t1 (a int, b int generated always as (a*2) stored, unique (b));
46 show create table t1;
47 describe t1;
48 drop table t1;
49
50 if($support_virtual_index)
51 {
52 create table t1 (a int, b int generated always as (a*2) virtual);
53 alter table t1 add unique key (b);
54 drop table t1;
55 }
56 create table t1 (a int, b int generated always as (a*2) stored);
57 alter table t1 add unique key (b);
58 drop table t1;
59
60 --echo # Testing data manipulation operations involving UNIQUE keys
61 --echo # on generated columns can be found in:
62 --echo # - gcol_ins_upd.inc
63 --echo # - gcol_select.inc
64
65 --echo #
66 --echo # INDEX
67 if($support_virtual_index)
68 {
69 create table t1 (a int, b int generated always as (a*2) virtual, index (b));
70 show create table t1;
71 describe t1;
72 drop table t1;
73
74 create table t1 (a int, b int generated always as (a*2) virtual, index (a,b));
75 drop table t1;
76 }
77
78 create table t1 (a int, b int generated always as (a*2) stored, index (b));
79 show create table t1;
80 describe t1;
81 drop table t1;
82
83 create table t1 (a int, b int generated always as (a*2) stored, index (a,b));
84 show create table t1;
85 describe t1;
86 drop table t1;
87
88 if($support_virtual_index)
89 {
90 create table t1 (a int, b int generated always as (a*2) virtual);
91 alter table t1 add index (b);
92
93 alter table t1 add index (a,b);
94 drop table t1;
95 }
96
97 create table t1 (a int, b int generated always as (a*2) stored);
98 alter table t1 add index (b);
99 drop table t1;
100
101 create table t1 (a int, b int generated always as (a*2) stored);
102 alter table t1 add index (a,b);
103 create table t2 like t1;
104 drop table t2;
105 drop table t1;
106
107 --echo # Testing data manipulation operations involving INDEX
108 --echo # on generated columns can be found in:
109 --echo # - gcol_select.inc
110
111 --echo #
112 --echo # TODO: FULLTEXT INDEX
113
114 --echo # SPATIAL INDEX
115 if (!$skip_spatial_index_check)
116 {
117 --echo # Error "All parts of a SPATIAL index must be geometrical"
118 --error 1687
119 create table t1 (a int, b int generated always as (a+1) stored, spatial index (b));
120 create table t1 (a int, b int generated always as (a+1) stored);
121 --error 1687
122 alter table t1 add spatial index (b);
123 drop table t1;
124 }
125
126 --echo # FOREIGN KEY
127
128 --echo # Rejected FK options.
129 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
130 create table t1 (a int, b int generated always as (a+1) stored,
131 foreign key (b) references t2(a) on update set null);
132 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
133 create table t1 (a int, b int generated always as (a+1) stored,
134 foreign key (b) references t2(a) on update cascade);
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 delete set null);
138
139 create table t1 (a int, b int generated always as (a+1) stored);
140 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
141 alter table t1 add foreign key (b) references t2(a) on update set null;
142 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
143 alter table t1 add foreign key (b) references t2(a) on update cascade;
144 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
145 alter table t1 add foreign key (b) references t2(a) on delete set null;
146 drop table t1;
147
148 if($support_virtual_index)
149 {
150 create table t2 (a int primary key);
151 --error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
152 create table t1 (a int, b int generated always as (a+1) virtual,
153 foreign key (b) references t2(a));
154
155 create table t1 (a int, b int generated always as (a+1) virtual);
156 --error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
157 alter table t1 add foreign key (b) references t2(a);
158 drop table t1, t2;
159 }
160
161 --echo # Allowed FK options.
162 create table t2 (a int primary key, b char(5));
163 create table t1 (a int, b int generated always as (a % 10) stored,
164 foreign key (b) references t2(a) on update restrict);
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 update no action);
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 restrict);
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 cascade);
174 drop table t1;
175 create table t1 (a int, b int generated always as (a % 10) stored,
176 foreign key (b) references t2(a) on delete no action);
177 drop table t1,t2;
178
179 if($support_virtual_index)
180 {
181 --echo #
182 --echo # Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED
183 --echo #
184 CREATE TABLE c (
185 pk integer AUTO_INCREMENT,
186 col_datetime_nokey DATETIME /*! NULL */,
187 col_time_nokey TIME /*! NULL */,
188 col_datetime_key DATETIME GENERATED ALWAYS AS
189 (ADDTIME(col_datetime_nokey, col_time_nokey)),
190 col_time_key TIME GENERATED ALWAYS AS
191 (ADDTIME(col_datetime_nokey, col_time_nokey)),
192 col_varchar_nokey VARCHAR(1) /*! NULL */,
193 PRIMARY KEY (pk),
194 KEY (col_time_key),
195 KEY (col_datetime_key));
196
197 INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values
198 ('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'),
199 ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL),
200 ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'),
201 ('18:56:33.027423','2003-04-01 00:00:00', 'i');
202
203 --replace_column 10 x 11 x
204 EXPLAIN SELECT
205 outr.col_time_key AS x
206 FROM c as outr
207 WHERE
208 outr.col_varchar_nokey in ('c', 'x', 'i')
209 AND (outr.col_time_key IS NULL OR
210 outr.col_datetime_key = '2009-09-27');
211
212 SELECT
213 outr.col_time_key AS x
214 FROM c AS outr
215 WHERE
216 outr.col_varchar_nokey in ('c', 'x', 'i')
217 AND (outr.col_time_key IS NULL OR
218 outr.col_datetime_key = '2009-09-27');
219
220 DROP TABLE c;
221
222 --echo #
223 --echo # Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP |
224 --echo # INNOBASE/INCLUDE/DATA0DATA.IC:253
225 --echo #
226 CREATE TABLE A (
227 col_varchar_nokey TEXT ,
228 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
229 KEY (col_varchar_key(50))
230 );
231
232 INSERT INTO A (col_varchar_nokey) VALUES ('');
233
234 CREATE TABLE D (
235 pk INTEGER AUTO_INCREMENT,
236 col_date_nokey BLOB,
237 col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL,
238 col_datetime_nokey LONGBLOB,
239 col_time_nokey LONGTEXT,
240
241 col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
242 col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
243
244 col_varchar_nokey TEXT,
245 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
246
247 PRIMARY KEY (pk),
248 KEY (col_varchar_key(50)),
249 KEY (col_date_key(20)),
250 KEY (col_time_key(20)),
251 KEY (col_datetime_key(20)),
252 KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5))
253 );
254
255 INSERT INTO D (
256 col_date_nokey,
257 col_time_nokey,
258 col_datetime_nokey,
259 col_varchar_nokey
260 ) VALUES ('', '', '', ''),('', '', '', '');
261
262 DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON
263 ( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` );
264
265 DROP TABLE IF EXISTS A,D;
266 --echo #
267 --echo # Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL |
268 --echo # INNOBASE/HANDLER/HANDLER0ALTER.CC
269 --echo #
270 CREATE TABLE t1 (
271 col1 int(11) DEFAULT NULL,
272 col2 int(11) DEFAULT NULL,
273 col3 int(11) NOT NULL,
274 col4 int(11) DEFAULT NULL,
275 col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL,
276 col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
277 col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
278 col9 text,
279 col6 int(11) DEFAULT NULL,
280 PRIMARY KEY (`col3`),
281 UNIQUE KEY uidx (`col2`),
282 KEY idx (`col5`)
283 );
284
285 INSERT INTO t1(col1,col2,col3,col4,col9,col6)
286 VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL);
287
288 ALTER TABLE t1 ADD COLUMN extra INT;
289 DROP TABLE t1;
290
291
292 --echo #
293 --echo # Bug#21316860: WL8149:INNODB: FAILING ASSERTION:
294 --echo # TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED
295 --echo #
296 CREATE TABLE t1 (
297 pk int(11) NOT NULL,
298 col_int_nokey int(11),
299 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL NOT NULL,
300 col_date_nokey date,
301 col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL NOT NULL,
302 PRIMARY KEY (pk),
303 UNIQUE KEY col_int_key (col_int_key)
304 );
305
306 ALTER TABLE t1 DROP COLUMN pk;
307 DROP TABLE t1;
308
309 --echo # Remove the impact on PK choose by index on virtual generated column
310 CREATE TABLE t1 (
311 pk int(11) NOT NULL,
312 col_int_nokey int(11) DEFAULT NULL,
313 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL NOT NULL,
314 UNIQUE KEY col_int_key (col_int_key)
315 );
316
317 ALTER TABLE t1 add unique index idx(pk), algorithm=inplace;
318 DESC t1;
319 DROP TABLE t1;
320
321 --echo #
322 --echo # Bug#21346132: WL8149:INNODB: FAILING ASSERTION:
323 --echo # PRIMARY_KEY_NO == -1 || PRIMARY_KEY_NO == 0
324 --echo #
325 CREATE TABLE t1 (
326 col_int_nokey int(11) NOT NULL,
327 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) NOT NULL,
328 col_varchar_nokey varchar(1) NOT NULL,
329 col_varchar_key varchar(2) GENERATED ALWAYS AS (col_varchar_nokey) NOT NULL,
330 UNIQUE KEY col_int_key (col_int_key),
331 UNIQUE KEY col_varchar_key (col_varchar_key),
332 UNIQUE KEY col_int_key_2 (col_int_key,col_varchar_key),
333 UNIQUE KEY col_varchar_key_2 (col_varchar_key,col_varchar_nokey),
334 KEY col_int_key_3 (col_int_key,col_int_nokey)
335 );
336
337 ALTER TABLE t1 DROP COLUMN col_varchar_key;
338 DROP TABLE t1;
339 --echo #
340 --echo # Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
341 --echo #
342
343 CREATE TABLE t1 (
344 id INTEGER NOT NULL,
345 b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL NOT NULL,
346 UNIQUE KEY (b)
347 );
348
349 INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);
350
351 --disable_query_log
352 --disable_result_log
353 ANALYZE TABLE t1;
354 --enable_result_log
355 --enable_query_log
356
357 # covering index scan
358 let query= SELECT b FROM t1 FORCE INDEX(b);
359 eval EXPLAIN $query;
360 eval $query;
361
362 # range scan
363 let $query= SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
364 eval EXPLAIN $query;
365 eval $query;
366
367 DROP TABLE t1;
368
369 }
370 --echo
371 --echo # Testing data manipulation operations involving FOREIGN KEY
372 --echo # on generated columns can be found in:
373 --echo # - gcol_ins_upd.inc
374 --echo # - gcol_select.inc
375
376 --echo #
377 --echo # TODO: CHECK
378
379 --echo #
380 --echo # Test how optimizer picks indexes defined on a GC
381 --echo #
382 SET optimizer_trace_max_mem_size=1048576; # 1MB
383 SET optimizer_trace="enabled=on,one_line=off";
384 SET end_markers_in_json="on";
385
386 CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED PRIMARY KEY);
387 INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
388 ANALYZE TABLE t1;
389 --echo # Should use index
390 --sorted_result
391 SELECT * FROM t1 WHERE f1 + 1 > 7;
392 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
393 --skip_if_hypergraph # Does not output the same optimizer trace.
394 SELECT * FROM information_schema.OPTIMIZER_TRACE;
395
396 SELECT * FROM t1 WHERE f1 + 1 = 7;
397 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
398 --sorted_result
399 SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
400 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
401 --sorted_result
402 SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
403 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
404
405 --echo # Check that expression isn't transformed for a disabled key
406 --sorted_result
407 SELECT * FROM t1 IGNORE KEY FOR JOIN(PRIMARY) WHERE f1 + 1 BETWEEN 5 AND 7;
408 EXPLAIN SELECT * FROM t1 IGNORE KEY FOR JOIN(PRIMARY) WHERE f1 + 1 BETWEEN 5 AND 7;
409
410 --echo # Check that ORDER BY could be optimized
411 SELECT * FROM t1 ORDER BY f1 + 1;
412 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
413 --skip_if_hypergraph # Does not output the same optimizer trace.
414 SELECT * FROM information_schema.OPTIMIZER_TRACE;
415 EXPLAIN SELECT * FROM t1 IGNORE KEY FOR ORDER BY(PRIMARY) ORDER BY f1 + 1;
416
417 --echo # Check that GROUP BY could be optimized
418 --sorted_result
419 SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
420 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
421 --skip_if_hypergraph # Does not output the same optimizer trace.
422 SELECT * FROM information_schema.OPTIMIZER_TRACE;
423 EXPLAIN SELECT f1 + 1, MAX(GC)
424 FROM t1 IGNORE KEY FOR GROUP BY(PRIMARY) GROUP BY f1 + 1;
425
426 --echo # Shouldn't use index
427 --sorted_result
428 SELECT * FROM t1 WHERE f1 + 1 > 7.0;
429 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
430
431 DROP TABLE t1;
432 --echo # Pick index with proper type
433 CREATE TABLE t1 (f1 int,
434 gc_int int AS (f1 + 1) STORED,
435 gc_date DATE AS (f1 + 1) STORED,
436 KEY gc_int_idx(gc_int),
437 KEY gc_date_idx(gc_date));
438 INSERT INTO t1(f1) VALUES
439 (030303),(040404),
440 (050505),(060606),
441 (010101),(020202),
442 (030303),(040404),
443 (050505),(060606),
444 (010101),(020202),
445 (090909),(101010),
446 (010101),(020202),
447 (070707),(080808);
448 ANALYZE TABLE t1;
449
450 --sorted_result
451 SELECT * FROM t1 WHERE f1 + 1 > 070707;
452 --echo # INT column & index should be picked
453 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
454 --sorted_result
455 SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
456 --echo # DATE column & index should be picked
457 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
458
459 DROP TABLE t1;
460
461 --echo #
462 --echo # BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
463 --echo #
464 CREATE TABLE t1 (
465 pk int primary key auto_increment,
466 col_int_key INTEGER ,
467 col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
468 KEY col_int_gc_key(col_int_gc_key)
469 );
470
471 INSERT INTO t1 ( col_int_key) VALUES (7);
472
473 ANALYZE TABLE t1;
474
475 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
476 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
477 ORDER BY field1, field2;
478
479 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
480 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
481 ORDER BY field1, field2;
482
483 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
484 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
485 GROUP BY field1, field2;
486
487 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
488 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
489 GROUP BY field1, field2;
490
491 DROP TABLE t1;
492
493 if($support_virtual_index)
494 {
495 --echo #
496 --echo # Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX
497 --echo # ON VIRTUAL COLUMN
498 --echo #
499
500 #
501 # Test 1: column number 2 and 66 are virtual and there is an index
502 # on column number 2.
503 #
504 CREATE TABLE t1 (
505 col1 INTEGER NOT NULL,
506 col2 INTEGER NOT NULL,
507 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
508 col3 INTEGER NOT NULL,
509 col4 INTEGER NOT NULL,
510 col5 INTEGER DEFAULT NULL,
511 col6 INTEGER DEFAULT NULL,
512 col7 INTEGER DEFAULT NULL,
513 col8 INTEGER DEFAULT NULL,
514 col9 INTEGER DEFAULT NULL,
515 col10 INTEGER DEFAULT NULL,
516 col11 INTEGER DEFAULT NULL,
517 col12 INTEGER DEFAULT NULL,
518 col13 INTEGER DEFAULT NULL,
519 col14 INTEGER DEFAULT NULL,
520 col15 INTEGER DEFAULT NULL,
521 col16 INTEGER DEFAULT NULL,
522 col17 INTEGER DEFAULT NULL,
523 col18 INTEGER DEFAULT NULL,
524 col19 INTEGER DEFAULT NULL,
525 col20 INTEGER DEFAULT NULL,
526 col21 INTEGER DEFAULT NULL,
527 col22 INTEGER DEFAULT NULL,
528 col23 INTEGER DEFAULT NULL,
529 col24 INTEGER DEFAULT NULL,
530 col25 INTEGER DEFAULT NULL,
531 col26 INTEGER DEFAULT NULL,
532 col27 INTEGER DEFAULT NULL,
533 col28 INTEGER DEFAULT NULL,
534 col29 INTEGER DEFAULT NULL,
535 col30 INTEGER DEFAULT NULL,
536 col31 INTEGER DEFAULT NULL,
537 col32 INTEGER DEFAULT NULL,
538 col33 INTEGER DEFAULT NULL,
539 col34 INTEGER DEFAULT NULL,
540 col35 INTEGER DEFAULT NULL,
541 col36 INTEGER DEFAULT NULL,
542 col37 INTEGER DEFAULT NULL,
543 col38 INTEGER DEFAULT NULL,
544 col39 INTEGER DEFAULT NULL,
545 col40 INTEGER DEFAULT NULL,
546 col41 INTEGER DEFAULT NULL,
547 col42 INTEGER DEFAULT NULL,
548 col43 INTEGER DEFAULT NULL,
549 col44 INTEGER DEFAULT NULL,
550 col45 INTEGER DEFAULT NULL,
551 col46 INTEGER DEFAULT NULL,
552 col47 INTEGER DEFAULT NULL,
553 col48 INTEGER DEFAULT NULL,
554 col49 INTEGER DEFAULT NULL,
555 col50 INTEGER DEFAULT NULL,
556 col51 INTEGER DEFAULT NULL,
557 col52 INTEGER DEFAULT NULL,
558 col53 INTEGER DEFAULT NULL,
559 col54 INTEGER DEFAULT NULL,
560 col55 INTEGER DEFAULT NULL,
561 col56 INTEGER DEFAULT NULL,
562 col57 INTEGER DEFAULT NULL,
563 col58 INTEGER DEFAULT NULL,
564 col59 INTEGER DEFAULT NULL,
565 col60 INTEGER DEFAULT NULL,
566 col61 INTEGER DEFAULT NULL,
567 col62 INTEGER DEFAULT NULL,
568 col63 INTEGER DEFAULT NULL,
569 col64 INTEGER DEFAULT NULL,
570 col65 INTEGER DEFAULT NULL,
571 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
572 KEY idx1 (gcol1)
573 );
574
575 INSERT INTO t1 (col1, col2, col3, col4)
576 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
577
578 # This will call my_eval_gcolumn_expr to compute the indexed column value
579 ALTER TABLE t1 ADD COLUMN extra INTEGER;
580
581 SELECT gcol1 FROM t1 FORCE INDEX(idx1);
582
583 DROP TABLE t1;
584
585 #
586 # Test 2: column number 2 and 66 are virtual and there is an index
587 # on column number 66.
588 #
589 CREATE TABLE t1 (
590 col1 INTEGER NOT NULL,
591 col2 INTEGER NOT NULL,
592 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
593 col3 INTEGER NOT NULL,
594 col4 INTEGER NOT NULL,
595 col5 INTEGER DEFAULT NULL,
596 col6 INTEGER DEFAULT NULL,
597 col7 INTEGER DEFAULT NULL,
598 col8 INTEGER DEFAULT NULL,
599 col9 INTEGER DEFAULT NULL,
600 col10 INTEGER DEFAULT NULL,
601 col11 INTEGER DEFAULT NULL,
602 col12 INTEGER DEFAULT NULL,
603 col13 INTEGER DEFAULT NULL,
604 col14 INTEGER DEFAULT NULL,
605 col15 INTEGER DEFAULT NULL,
606 col16 INTEGER DEFAULT NULL,
607 col17 INTEGER DEFAULT NULL,
608 col18 INTEGER DEFAULT NULL,
609 col19 INTEGER DEFAULT NULL,
610 col20 INTEGER DEFAULT NULL,
611 col21 INTEGER DEFAULT NULL,
612 col22 INTEGER DEFAULT NULL,
613 col23 INTEGER DEFAULT NULL,
614 col24 INTEGER DEFAULT NULL,
615 col25 INTEGER DEFAULT NULL,
616 col26 INTEGER DEFAULT NULL,
617 col27 INTEGER DEFAULT NULL,
618 col28 INTEGER DEFAULT NULL,
619 col29 INTEGER DEFAULT NULL,
620 col30 INTEGER DEFAULT NULL,
621 col31 INTEGER DEFAULT NULL,
622 col32 INTEGER DEFAULT NULL,
623 col33 INTEGER DEFAULT NULL,
624 col34 INTEGER DEFAULT NULL,
625 col35 INTEGER DEFAULT NULL,
626 col36 INTEGER DEFAULT NULL,
627 col37 INTEGER DEFAULT NULL,
628 col38 INTEGER DEFAULT NULL,
629 col39 INTEGER DEFAULT NULL,
630 col40 INTEGER DEFAULT NULL,
631 col41 INTEGER DEFAULT NULL,
632 col42 INTEGER DEFAULT NULL,
633 col43 INTEGER DEFAULT NULL,
634 col44 INTEGER DEFAULT NULL,
635 col45 INTEGER DEFAULT NULL,
636 col46 INTEGER DEFAULT NULL,
637 col47 INTEGER DEFAULT NULL,
638 col48 INTEGER DEFAULT NULL,
639 col49 INTEGER DEFAULT NULL,
640 col50 INTEGER DEFAULT NULL,
641 col51 INTEGER DEFAULT NULL,
642 col52 INTEGER DEFAULT NULL,
643 col53 INTEGER DEFAULT NULL,
644 col54 INTEGER DEFAULT NULL,
645 col55 INTEGER DEFAULT NULL,
646 col56 INTEGER DEFAULT NULL,
647 col57 INTEGER DEFAULT NULL,
648 col58 INTEGER DEFAULT NULL,
649 col59 INTEGER DEFAULT NULL,
650 col60 INTEGER DEFAULT NULL,
651 col61 INTEGER DEFAULT NULL,
652 col62 INTEGER DEFAULT NULL,
653 col63 INTEGER DEFAULT NULL,
654 col64 INTEGER DEFAULT NULL,
655 col65 INTEGER DEFAULT NULL,
656 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
657 KEY idx1 (gcol2)
658 );
659
660 INSERT INTO t1 (col1, col2, col3, col4)
661 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
662
663 # This will call my_eval_gcolumn_expr to compute the indexed column value
664 ALTER TABLE t1 ADD COLUMN extra INTEGER;
665
666 SELECT gcol2 FROM t1 FORCE INDEX(idx1);
667
668 DROP TABLE t1;
669 }
670
671 if($support_virtual_index)
672 {
673 --echo #
674 --echo # Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
675 --echo #
676 # When generating the value of column b, an out-of-range warning is
677 # raised. A warning is required in order to reproduce the bug, but it
678 # is promoted to an error on insertion unless we turn off strict mode.
679 --source include/turn_off_strict_mode.inc
680 CREATE TABLE t (a INT,
681 b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL,
682 c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL);
683 INSERT INTO t(a) VALUES (1);
684 # Before index was created, this query returned the expected one match.
685 SELECT * FROM t WHERE c = '0';
686 # Adding an index sometimes crashed, other times populated it with garbage ...
687 ALTER TABLE t ADD UNIQUE INDEX (c(1));
688 # ... so that this query found no match in the index.
689 SELECT * FROM t WHERE c = '0';
690 DROP TABLE t;
691 --source include/restore_strict_mode.inc
692
693 --echo #
694 --echo # Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD()
695 --echo # DID NOT RETURN TRUE WITH DIVIDE 0
696 --echo #
697 CREATE TABLE t (a INT, b INT, h VARCHAR(10));
698 INSERT INTO t VALUES (12, 3, "ss");
699 INSERT INTO t VALUES (13, 4, "ss");
700 INSERT INTO t VALUES (14, 0, "ss");
701 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
702 --error ER_DIVISION_BY_ZERO
703 CREATE INDEX idx ON t(c);
704 CALL mtr.add_suppression("\\[Warning\\] \\[[^]]*\\] InnoDB: Compute virtual column values failed");
705 DROP TABLE t;
706 }
707
708 --echo #
709 --echo # Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
710 --echo # WITH LOGICAL OPERATORS
711 --echo #
712 CREATE TABLE t (a INT, b INT,
713 gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
714 gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
715 gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
716 gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
717 gc_case INT GENERATED ALWAYS AS
718 (CASE WHEN (a AND b) THEN a ELSE b END) STORED,
719 INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
720 INDEX(gc_case));
721 INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
722 ANALYZE TABLE t;
723 EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
724 SELECT a, b FROM t WHERE (a AND b) = 1;
725 EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
726 SELECT a, b FROM t WHERE 1 = (a AND b);
727 EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
728 SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
729 EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
730 --sorted_result
731 SELECT a, b FROM t WHERE (a OR b) = 1;
732 EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
733 --sorted_result
734 SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
735 # XOR and NOT worked even before the bug fix, but we test all logical
736 # operators here for completeness.
737 EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
738 --sorted_result
739 SELECT a, b FROM t WHERE (a XOR b) = 1;
740 EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
741 SELECT a FROM t WHERE (NOT a) = 1;
742 # Also verify that a logical expression nested inside another
743 # expression doesn't prevent substitution.
744 EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
745 --sorted_result
746 SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
747 # The expression must be exactly the same as the generated expression.
748 # (b AND a) is not recognized as equivalent to (a AND b).
749 EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
750 SELECT a, b FROM t WHERE 1 = (b AND a);
751 --sorted_result
752 EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
753 --sorted_result
754 SELECT a, b FROM t WHERE 1 = (b OR a);
755 DROP TABLE t;
756
757 --echo #
758 --echo # Bug#21854241: QUERY USING JSON_EXTRACT() RETURNS WRONG RESULT
759 --echo # AFTER ADDING VIRTUAL INDEX
760 --echo #
761 CREATE TABLE employees (
762 data JSON,
763 name1 VARCHAR(30) AS (JSON_EXTRACT(data, "$.name")) STORED,
764 name2 VARCHAR(30) AS (JSON_UNQUOTE(JSON_EXTRACT(data, "$.name"))) STORED
765 );
766 INSERT INTO employees (data) VALUES('{"id": 1, "name": "Jane"}');
767 INSERT INTO employees (data) VALUES('{"id": 2, "name": "Joe"}');
768 let $query= SELECT * FROM employees WHERE JSON_EXTRACT(data, '\$.name') = 'Jane';
769 ANALYZE TABLE employees;
770 --eval EXPLAIN $query
771 --sorted_result
772 --eval $query
773 # The index on name1 should not be used, since it contains
774 # double-quoted values which don't match the string literal in the
775 # predicate. The query used to return zero rows.
776 ALTER TABLE employees ADD INDEX name_idx1(name1);
777 ANALYZE TABLE employees;
778 --eval EXPLAIN $query
779 --sorted_result
780 --eval $query
781 # The index on name2 contains unquoted strings and can be used.
782 ALTER TABLE employees ADD INDEX name_idx2(name2);
783 ANALYZE TABLE employees;
784 --eval EXPLAIN $query
785 --sorted_result
786 --eval $query
787 DROP TABLE employees;
788 --echo #
789 --echo # Bug#22077611 UPDATE .. WHERE JSON_EXTRACT(..) = '..' NOT USING
790 --echo # VIRTUAL COL INDEX
791 --echo #
792 CREATE TABLE t(a INT, b INT, gc INT GENERATED ALWAYS AS (a+1) STORED, KEY(gc));
793 INSERT INTO t(a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5);
794 ANALYZE TABLE t;
795 --let $query= UPDATE t SET b = 10 WHERE (a+1) = 3
796 --eval EXPLAIN $query
797 --eval $query
798 SELECT * FROM t ORDER BY a, b;
799 ANALYZE TABLE t;
800 --let $query= UPDATE t SET b = 9 ORDER BY (a+1) LIMIT 1
801 --eval EXPLAIN $query
802 --eval $query
803 SELECT * FROM t ORDER BY a, b;
804 ANALYZE TABLE t;
805 --let $query= DELETE FROM t WHERE (a+1) = 2
806 --eval EXPLAIN $query
807 --eval $query
808 SELECT * FROM t ORDER BY a, b;
809 ANALYZE TABLE t;
810 --let $query= DELETE FROM t ORDER BY (a+1) LIMIT 1
811 --eval EXPLAIN $query
812 --eval $query
813 SELECT * FROM t ORDER BY a, b;
814 DROP TABLE t;
815
816 if($support_virtual_index)
817 {
818 --echo #
819 --echo # Bug#22095783: ALTER TABLE ADD COLUMN FAILS WITH OUT
820 --echo # OF RANGE VALUE ERROR
821 CREATE TABLE c1(doc JSON,_id VARCHAR(32) GENERATED ALWAYS
822 AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED NOT NULL UNIQUE);
823 INSERT INTO c1(doc) VALUES('{"X":10,"_id":9}');
824 --error ER_WARN_DATA_OUT_OF_RANGE
825 ALTER TABLE c1 ADD COLUMN vc1 DOUBLE(4, 3) GENERATED
826 ALWAYS AS (JSON_EXTRACT(doc, '$.X')) VIRTUAL , ADD INDEX IX1 (vc1);
827 ALTER TABLE c1 ADD vc2 INT(11);
828 ALTER TABLE c1 ADD COLUMN vc3 INT(11) GENERATED ALWAYS AS
829 (JSON_EXTRACT(doc, '$.X')) VIRTUAL , ADD UNIQUE INDEX IX2 (vc3);
830 ALTER TABLE c1 drop vc2;
831 ALTER TABLE c1 ADD vc4 INT(11);
832 DROP TABLE c1;
833 }
834
835 --echo #
836 --echo # Bug#22810883: ASSERTION FAILED:
837 --echo # !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
838 --echo #
839 CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
840 a2 INTEGER, KEY (a1));
841 INSERT INTO t1 VALUES ();
842 CREATE TABLE t2 (b INTEGER);
843 INSERT INTO t2 VALUES (1);
844 ANALYZE TABLE t1, t2;
845 --echo # Used to choose the index on a1 and get wrong results.
846 --let $query= SELECT * FROM t1 WHERE (a2 AND a2) = 0
847 --eval EXPLAIN $query
848 --eval $query
849 --echo # Used to get assertion or wrong results.
850 --let $query= SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1
851 --eval EXPLAIN $query
852 --eval $query
853 DROP TABLE t1, t2;
854
855 if ($support_virtual_index) {
856 --echo #
857 --echo # Bug#24345509: WRONG RESULTS WHEN GCOL INDEX IS USED
858 --echo #
859 CREATE TABLE t1 (
860 pk INT PRIMARY KEY AUTO_INCREMENT,
861 i INT,
862 vc VARCHAR(7),
863 gc1 VARCHAR(14) GENERATED ALWAYS AS (concat(vc, vc)) VIRTUAL NOT NULL,
864 gc2 VARCHAR(14) GENERATED ALWAYS AS (concat(gc1, 'x')) VIRTUAL NOT NULL,
865 KEY gc2_key (gc2),
866 KEY gc2_key_prefix (gc2(5))
867 );
868
869 INSERT INTO t1 (i, vc) VALUES
870 (7, 'xcek'), (3, 'ceksat'), (3, 'eksate'), (3, 'ksatef'), (6, 's');
871
872 ANALYZE TABLE t1;
873
874 # Missing rows in the result when using a non-covering secondary index
875 # and the end-range condition referenced a virtual column.
876
877 let $query = SELECT i FROM t1 WHERE gc2 <= 'ksatefksatefx';
878 --eval EXPLAIN $query
879 --eval $query
880 let $query = SELECT i FROM t1 FORCE INDEX (gc2_key_prefix)
881 WHERE gc2 <= 'ksatefksatefx';
882 --eval EXPLAIN $query
883 --eval $query
884
885 # Similar test case with covering index. Used to miss rows when using
886 # a prefix index.
887
888 let $query = SELECT COUNT(*) FROM t1 WHERE gc2 <= 'ksatefksatefx';
889 --eval EXPLAIN $query
890 --eval $query
891 let $query = SELECT COUNT(*) FROM t1 FORCE INDEX (gc2_key_prefix)
892 WHERE gc2 <= 'ksatefksatefx';
893 --eval EXPLAIN $query
894 --eval $query
895
896 # After bug#23481444 InnoDB also checks the end-range condition on
897 # page boundaries if many not yet committed records have been scanned.
898 # Fill up a page with uncommitted rows at the beginning of the
899 # secondary index to exercise this code.
900 CREATE TABLE t2 (
901 i INT NOT NULL,
902 gc INT GENERATED ALWAYS AS (i) VIRTUAL NOT NULL,
903 KEY (gc)
904 );
905
906 INSERT INTO t2 (i) VALUES (-1), (0), (1), (2);
907
908 BEGIN;
909 --disable_query_log
910 --let $i = 2000
911 while ($i > 0)
912 {
913 INSERT INTO t2 (i) VALUES (-10);
914 dec $i;
915 }
916 --enable_query_log
917
918 CONNECT (con1,localhost,root,,);
919 # This query used to return zero rows.
920 SELECT i FROM t2 FORCE INDEX (gc) WHERE gc <= -1;
921 # Similar query using covering index was correct even before.
922 SELECT gc FROM t2 FORCE INDEX (gc) WHERE gc <= -1;
923 DISCONNECT con1;
924 CONNECTION default;
925
926 COMMIT;
927
928 DROP TABLE t1, t2;
929
930 --echo #
931 --echo # Bug#27010089: ASSERTION FAILURE:
932 --echo # ROW0SEL.CC:2846:TEMPL->MYSQL_COL_LEN == LEN
933 --echo #
934
935 CREATE TABLE t (
936 pk INTEGER AUTO_INCREMENT PRIMARY KEY,
937 col_int_key INTEGER GENERATED ALWAYS AS (1) VIRTUAL,
938 col_varchar VARCHAR(7) ,
939 col_varchar_key VARCHAR(14) GENERATED ALWAYS AS ('abc') VIRTUAL,
940 KEY (col_varchar_key(5), col_int_key),
941 KEY (col_int_key, col_varchar_key));
942
943 INSERT INTO t VALUES (), ();
944
945 SELECT 1 FROM t AS alias1
946 WHERE EXISTS
947 (SELECT SQ2_alias1.col_int_key AS SQ2_field1
948 FROM t AS SQ2_alias1 JOIN t AS SQ2_alias2
949 ON (SQ2_alias2.col_varchar_key = SQ2_alias1.col_varchar_key)
950 WHERE SQ2_alias1.col_varchar <> alias1.col_varchar AND
951 SQ2_alias1.col_int_key >= SQ2_alias2.pk);
952
953 DROP TABLE t;
954 }
955
956 --echo #
957 --echo # Bug#27403367: GENERATED COLUMN EXPRESSIONS IGNORED WITH PREFIX INDEX
958 --echo #
959
960 CREATE TABLE t (vc VARCHAR(100),
961 gc VARCHAR(100) GENERATED ALWAYS AS (REVERSE(vc)) STORED,
962 KEY (gc(3)));
963
964 INSERT INTO t(vc) VALUES ('a'), ('abc'), ('abcabc'), ('abcdef');
965
966 ANALYZE TABLE t;
967
968 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) = 'cba';
969 SELECT * FROM t WHERE REVERSE(vc) = 'cba';
970
971 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) = 'cbacba';
972 SELECT * FROM t WHERE REVERSE(vc) = 'cbacba';
973
974 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) BETWEEN 'c' AND 'e';
975 --sorted_result
976 SELECT * FROM t WHERE REVERSE(vc) BETWEEN 'c' AND 'e';
977
978 DROP TABLE t;
979
980 if ($support_virtual_index) {
981 --echo #
982 --echo # Bug#35178672: Assertion `dptr <= buffer->ptr() +
983 --echo # buffer->alloced_length()' failed.
984 --echo #
985 CREATE TABLE t (
986 pk INT PRIMARY KEY AUTO_INCREMENT,
987 x VARCHAR(1) NOT NULL,
988 gc VARCHAR(2) GENERATED ALWAYS AS (CONCAT(x, x)),
989 KEY (gc)
990 );
991 INSERT INTO t (pk, x) VALUES (1, 'a'), (2, 'b'), (3, 'c');
992 SELECT COUNT(*) FROM t GROUP BY gc;
993 SELECT gc FROM t ORDER BY HEX(gc);
994 DROP TABLE t;
995 }
996
997 --echo #