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 DEFAULT NULL,
14 `b` int GENERATED ALWAYS AS ((`a` * 2)) VIRTUAL,
15 UNIQUE KEY `b` (`b`)
16 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
17 describe t1;
18 Field Type Null Key Default Extra
19 a int YES NULL
20 b int 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 DEFAULT NULL,
27 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
28 UNIQUE KEY `b` (`b`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
30 describe t1;
31 Field Type Null Key Default Extra
32 a int YES NULL
33 b int 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 DEFAULT NULL,
40 `b` int GENERATED ALWAYS AS ((`a` * 2)) VIRTUAL,
41 UNIQUE KEY `b` (`b`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
43 describe t1;
44 Field Type Null Key Default Extra
45 a int YES NULL
46 b int 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 DEFAULT NULL,
53 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
54 UNIQUE KEY `b` (`b`)
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
56 describe t1;
57 Field Type Null Key Default Extra
58 a int YES NULL
59 b int 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 DEFAULT NULL,
78 `b` int GENERATED ALWAYS AS ((`a` * 2)) VIRTUAL,
79 KEY `b` (`b`)
80 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
81 describe t1;
82 Field Type Null Key Default Extra
83 a int YES NULL
84 b int 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 DEFAULT NULL,
93 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
94 KEY `b` (`b`)
95 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
96 describe t1;
97 Field Type Null Key Default Extra
98 a int YES NULL
99 b int 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 DEFAULT NULL,
106 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
107 KEY `a` (`a`,`b`)
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
109 describe t1;
110 Field Type Null Key Default Extra
111 a int YES MUL NULL
112 b int 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 t2 (a int primary key);
152 create table t1 (a int, b int generated always as (a+1) virtual,
153 foreign key (b) references t2(a));
154 ERROR HY000: Foreign key 't1_ibfk_1' uses virtual column 'b' which is not supported.
155 create table t1 (a int, b int generated always as (a+1) virtual);
156 alter table t1 add foreign key (b) references t2(a);
157 ERROR HY000: Foreign key 't1_ibfk_1' uses virtual column 'b' which is not supported.
158 drop table t1, t2;
159 # Allowed FK options.
160 create table t2 (a int primary key, b char(5));
161 create table t1 (a int, b int generated always as (a % 10) stored,
162 foreign key (b) references t2(a) on update restrict);
163 drop table t1;
164 create table t1 (a int, b int generated always as (a % 10) stored,
165 foreign key (b) references t2(a) on update no action);
166 drop table t1;
167 create table t1 (a int, b int generated always as (a % 10) stored,
168 foreign key (b) references t2(a) on delete restrict);
169 drop table t1;
170 create table t1 (a int, b int generated always as (a % 10) stored,
171 foreign key (b) references t2(a) on delete cascade);
172 drop table t1;
173 create table t1 (a int, b int generated always as (a % 10) stored,
174 foreign key (b) references t2(a) on delete no action);
175 drop table t1,t2;
176 #
177 # Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED
178 #
179 CREATE TABLE c (
180 pk integer AUTO_INCREMENT,
181 col_datetime_nokey DATETIME /*! NULL */,
182 col_time_nokey TIME /*! NULL */,
183 col_datetime_key DATETIME GENERATED ALWAYS AS
184 (ADDTIME(col_datetime_nokey, col_time_nokey)),
185 col_time_key TIME GENERATED ALWAYS AS
186 (ADDTIME(col_datetime_nokey, col_time_nokey)),
187 col_varchar_nokey VARCHAR(1) /*! NULL */,
188 PRIMARY KEY (pk),
189 KEY (col_time_key),
190 KEY (col_datetime_key));
191 INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values
192 ('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'),
193 ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL),
194 ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'),
195 ('18:56:33.027423','2003-04-01 00:00:00', 'i');
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 partitions type possible_keys key key_len ref rows filtered Extra
204 1 SIMPLE outr NULL index_merge col_time_key,col_datetime_key col_time_key,col_datetime_key 4,6 NULL x x Using sort_union(col_time_key,col_datetime_key); Using where
205 Warnings:
206 Note 1003 /* select#1 */ select `test`.`outr`.`col_time_key` AS `x` from `test`.`c` `outr` where ((`test`.`outr`.`col_varchar_nokey` in ('c','x','i')) and ((`test`.`outr`.`col_time_key` is null) or (`test`.`outr`.`col_datetime_key` = TIMESTAMP'2009-09-27 00:00:00')))
207 SELECT
208 outr.col_time_key AS x
209 FROM c AS outr
210 WHERE
211 outr.col_varchar_nokey in ('c', 'x', 'i')
212 AND (outr.col_time_key IS NULL OR
213 outr.col_datetime_key = '2009-09-27');
214 x
215 DROP TABLE c;
216 #
217 # Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP |
218 # INNOBASE/INCLUDE/DATA0DATA.IC:253
219 #
220 CREATE TABLE A (
221 col_varchar_nokey TEXT ,
222 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
223 KEY (col_varchar_key(50))
224 );
225 INSERT INTO A (col_varchar_nokey) VALUES ('');
226 CREATE TABLE D (
227 pk INTEGER AUTO_INCREMENT,
228 col_date_nokey BLOB,
229 col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL,
230 col_datetime_nokey LONGBLOB,
231 col_time_nokey LONGTEXT,
232 col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
233 col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)),
234 col_varchar_nokey TEXT,
235 col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)),
236 PRIMARY KEY (pk),
237 KEY (col_varchar_key(50)),
238 KEY (col_date_key(20)),
239 KEY (col_time_key(20)),
240 KEY (col_datetime_key(20)),
241 KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5))
242 );
243 INSERT INTO D (
244 col_date_nokey,
245 col_time_nokey,
246 col_datetime_nokey,
247 col_varchar_nokey
248 ) VALUES ('', '', '', ''),('', '', '', '');
249 DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON
250 ( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` );
251 DROP TABLE IF EXISTS A,D;
252 #
253 # Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL |
254 # INNOBASE/HANDLER/HANDLER0ALTER.CC
255 #
256 CREATE TABLE t1 (
257 col1 int(11) DEFAULT NULL,
258 col2 int(11) DEFAULT NULL,
259 col3 int(11) NOT NULL,
260 col4 int(11) DEFAULT NULL,
261 col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL,
262 col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
263 col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
264 col9 text,
265 col6 int(11) DEFAULT NULL,
266 PRIMARY KEY (`col3`),
267 UNIQUE KEY uidx (`col2`),
268 KEY idx (`col5`)
269 );
270 Warnings:
271 Warning 1681 Integer display width is deprecated and will be removed in a future release.
272 Warning 1681 Integer display width is deprecated and will be removed in a future release.
273 Warning 1681 Integer display width is deprecated and will be removed in a future release.
274 Warning 1681 Integer display width is deprecated and will be removed in a future release.
275 Warning 1681 Integer display width is deprecated and will be removed in a future release.
276 Warning 1681 Integer display width is deprecated and will be removed in a future release.
277 Warning 1681 Integer display width is deprecated and will be removed in a future release.
278 Warning 1681 Integer display width is deprecated and will be removed in a future release.
279 INSERT INTO t1(col1,col2,col3,col4,col9,col6)
280 VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL);
281 ALTER TABLE t1 ADD COLUMN extra INT;
282 DROP TABLE t1;
283 #
284 # Bug#21316860: WL8149:INNODB: FAILING ASSERTION:
285 # TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED
286 #
287 CREATE TABLE t1 (
288 pk int(11) NOT NULL,
289 col_int_nokey int(11),
290 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL NOT NULL,
291 col_date_nokey date,
292 col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL NOT NULL,
293 PRIMARY KEY (pk),
294 UNIQUE KEY col_int_key (col_int_key)
295 );
296 Warnings:
297 Warning 1681 Integer display width is deprecated and will be removed in a future release.
298 Warning 1681 Integer display width is deprecated and will be removed in a future release.
299 Warning 1681 Integer display width is deprecated and will be removed in a future release.
300 ALTER TABLE t1 DROP COLUMN pk;
301 DROP TABLE t1;
302 # Remove the impact on PK choose by index on virtual generated column
303 CREATE TABLE t1 (
304 pk int(11) NOT NULL,
305 col_int_nokey int(11) DEFAULT NULL,
306 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL NOT NULL,
307 UNIQUE KEY col_int_key (col_int_key)
308 );
309 Warnings:
310 Warning 1681 Integer display width is deprecated and will be removed in a future release.
311 Warning 1681 Integer display width is deprecated and will be removed in a future release.
312 Warning 1681 Integer display width is deprecated and will be removed in a future release.
313 ALTER TABLE t1 add unique index idx(pk), algorithm=inplace;
314 DESC t1;
315 Field Type Null Key Default Extra
316 pk int NO PRI NULL
317 col_int_nokey int YES NULL
318 col_int_key int NO UNI NULL VIRTUAL GENERATED
319 DROP TABLE t1;
320 #
321 # Bug#21346132: WL8149:INNODB: FAILING ASSERTION:
322 # PRIMARY_KEY_NO == -1 || PRIMARY_KEY_NO == 0
323 #
324 CREATE TABLE t1 (
325 col_int_nokey int(11) NOT NULL,
326 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) NOT NULL,
327 col_varchar_nokey varchar(1) NOT NULL,
328 col_varchar_key varchar(2) GENERATED ALWAYS AS (col_varchar_nokey) NOT NULL,
329 UNIQUE KEY col_int_key (col_int_key),
330 UNIQUE KEY col_varchar_key (col_varchar_key),
331 UNIQUE KEY col_int_key_2 (col_int_key,col_varchar_key),
332 UNIQUE KEY col_varchar_key_2 (col_varchar_key,col_varchar_nokey),
333 KEY col_int_key_3 (col_int_key,col_int_nokey)
334 );
335 Warnings:
336 Warning 1681 Integer display width is deprecated and will be removed in a future release.
337 Warning 1681 Integer display width is deprecated and will be removed in a future release.
338 ALTER TABLE t1 DROP COLUMN col_varchar_key;
339 Warnings:
340 Warning 1831 Duplicate index 'col_int_key_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
341 DROP TABLE t1;
342 #
343 # Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN
344 #
345 CREATE TABLE t1 (
346 id INTEGER NOT NULL,
347 b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL NOT NULL,
348 UNIQUE KEY (b)
349 );
350 INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);
351 EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
352 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
353 1 SIMPLE t1 NULL index NULL b 4 NULL 9 100.00 Using index
354 Warnings:
355 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`b`)
356 SELECT b FROM t1 FORCE INDEX(b);
357 b
358 3
359 4
360 5
361 6
362 7
363 8
364 9
365 10
366 11
367 EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
368 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
369 1 SIMPLE t1 NULL range b b 4 NULL 1 100.00 Using where; Using index
370 Warnings:
371 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`b`) where (`test`.`t1`.`b` between 1 and 5)
372 SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
373 b
374 3
375 4
376 5
377 DROP TABLE t1;
378
379 # Testing data manipulation operations involving FOREIGN KEY
380 # on generated columns can be found in:
381 # - gcol_ins_upd.inc
382 # - gcol_select.inc
383 #
384 # TODO: CHECK
385 #
386 # Test how optimizer picks indexes defined on a GC
387 #
388 SET optimizer_trace_max_mem_size=1048576;
389 SET optimizer_trace="enabled=on,one_line=off";
390 SET end_markers_in_json="on";
391 CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED PRIMARY KEY);
392 INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
393 ANALYZE TABLE t1;
394 Table Op Msg_type Msg_text
395 test.t1 analyze status OK
396 # Should use index
397 SELECT * FROM t1 WHERE f1 + 1 > 7;
398 f1 gc
399 7 8
400 8 9
401 9 10
402 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
403 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
404 1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
405 Warnings:
406 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 7)
407 SELECT * FROM information_schema.OPTIMIZER_TRACE;
408 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
409 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7 {
410 "steps": [
411 {
412 "join_preparation": {
413 "select#": 1,
414 "steps": [
415 {
416 "expanded_query": "/* select#1 */ select `t1`.`f1` AS `f1`,`t1`.`gc` AS `gc` from `t1` where ((`t1`.`f1` + 1) > 7)"
417 }
418 ] /* steps */
419 } /* join_preparation */
420 },
421 {
422 "join_optimization": {
423 "select#": 1,
424 "steps": [
425 {
426 "condition_processing": {
427 "condition": "WHERE",
428 "original_condition": "((`t1`.`f1` + 1) > 7)",
429 "steps": [
430 {
431 "transformation": "equality_propagation",
432 "resulting_condition": "((`t1`.`f1` + 1) > 7)"
433 },
434 {
435 "transformation": "constant_propagation",
436 "resulting_condition": "((`t1`.`f1` + 1) > 7)"
437 },
438 {
439 "transformation": "trivial_condition_removal",
440 "resulting_condition": "((`t1`.`f1` + 1) > 7)"
441 }
442 ] /* steps */
443 } /* condition_processing */
444 },
445 {
446 "substitute_generated_columns": {
447 "resulting_condition": "(`t1`.`gc` > 7)"
448 } /* substitute_generated_columns */
449 },
450 {
451 "table_dependencies": [
452 {
453 "table": "`t1`",
454 "row_may_be_null": false,
455 "map_bit": 0,
456 "depends_on_map_bits": [
457 ] /* depends_on_map_bits */
458 }
459 ] /* table_dependencies */
460 },
461 {
462 "ref_optimizer_key_uses": [
463 ] /* ref_optimizer_key_uses */
464 },
465 {
466 "rows_estimation": [
467 {
468 "table": "`t1`",
469 "range_analysis": {
470 "table_scan": {
471 "rows": 10,
472 "cost": 3.35
473 } /* table_scan */,
474 "potential_range_indexes": [
475 {
476 "index": "PRIMARY",
477 "usable": true,
478 "key_parts": [
479 "gc"
480 ] /* key_parts */
481 }
482 ] /* potential_range_indexes */,
483 "setup_range_conditions": [
484 ] /* setup_range_conditions */,
485 "group_index_range": {
486 "chosen": false,
487 "cause": "not_group_by_or_distinct"
488 } /* group_index_range */,
489 "skip_scan_range": {
490 "potential_skip_scan_indexes": [
491 {
492 "index": "PRIMARY",
493 "usable": false,
494 "cause": "query_references_nonkey_column"
495 }
496 ] /* potential_skip_scan_indexes */
497 } /* skip_scan_range */,
498 "analyzing_range_alternatives": {
499 "range_scan_alternatives": [
500 {
501 "index": "PRIMARY",
502 "ranges": [
503 "7 < gc"
504 ] /* ranges */,
505 "index_dives_for_eq_ranges": true,
506 "rowid_ordered": true,
507 "using_mrr": false,
508 "index_only": false,
509 "in_memory": 1,
510 "rows": 3,
511 "cost": 0.560618,
512 "chosen": true
513 }
514 ] /* range_scan_alternatives */,
515 "analyzing_roworder_intersect": {
516 "usable": false,
517 "cause": "too_few_roworder_scans"
518 } /* analyzing_roworder_intersect */
519 } /* analyzing_range_alternatives */,
520 "chosen_range_access_summary": {
521 "range_access_plan": {
522 "type": "range_scan",
523 "index": "PRIMARY",
524 "rows": 3,
525 "ranges": [
526 "7 < gc"
527 ] /* ranges */
528 } /* range_access_plan */,
529 "rows_for_plan": 3,
530 "cost_for_plan": 0.560618,
531 "chosen": true
532 } /* chosen_range_access_summary */
533 } /* range_analysis */
534 }
535 ] /* rows_estimation */
536 },
537 {
538 "considered_execution_plans": [
539 {
540 "plan_prefix": [
541 ] /* plan_prefix */,
542 "table": "`t1`",
543 "best_access_path": {
544 "considered_access_paths": [
545 {
546 "rows_to_scan": 3,
547 "filtering_effect": [
548 ] /* filtering_effect */,
549 "final_filtering_effect": 1,
550 "access_type": "range",
551 "range_details": {
552 "used_index": "PRIMARY"
553 } /* range_details */,
554 "resulting_rows": 3,
555 "cost": 0.860618,
556 "chosen": true
557 }
558 ] /* considered_access_paths */
559 } /* best_access_path */,
560 "condition_filtering_pct": 100,
561 "rows_for_plan": 3,
562 "cost_for_plan": 0.860618,
563 "chosen": true
564 }
565 ] /* considered_execution_plans */
566 },
567 {
568 "attaching_conditions_to_tables": {
569 "original_condition": "(`t1`.`gc` > 7)",
570 "attached_conditions_computation": [
571 ] /* attached_conditions_computation */,
572 "attached_conditions_summary": [
573 {
574 "table": "`t1`",
575 "attached": "(`t1`.`gc` > 7)"
576 }
577 ] /* attached_conditions_summary */
578 } /* attaching_conditions_to_tables */
579 },
580 {
581 "finalizing_table_conditions": [
582 {
583 "table": "`t1`",
584 "original_table_condition": "(`t1`.`gc` > 7)",
585 "final_table_condition ": "(`t1`.`gc` > 7)"
586 }
587 ] /* finalizing_table_conditions */
588 },
589 {
590 "refine_plan": [
591 {
592 "table": "`t1`"
593 }
594 ] /* refine_plan */
595 }
596 ] /* steps */
597 } /* join_optimization */
598 },
599 {
600 "join_explain": {
601 "select#": 1,
602 "steps": [
603 ] /* steps */
604 } /* join_explain */
605 }
606 ] /* steps */
607 } 0 0
608 SELECT * FROM t1 WHERE f1 + 1 = 7;
609 f1 gc
610 6 7
611 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
612 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
613 1 SIMPLE t1 NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
614 Warnings:
615 Note 1003 /* select#1 */ select '6' AS `f1`,'7' AS `gc` from `test`.`t1` where true
616 SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
617 f1 gc
618 4 5
619 6 7
620 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
621 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
622 1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using where
623 Warnings:
624 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` in (7,5))
625 SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
626 f1 gc
627 4 5
628 5 6
629 6 7
630 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
631 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
632 1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
633 Warnings:
634 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` between 5 and 7)
635 # Check that expression isn't transformed for a disabled key
636 SELECT * FROM t1 IGNORE KEY FOR JOIN(PRIMARY) WHERE f1 + 1 BETWEEN 5 AND 7;
637 f1 gc
638 4 5
639 5 6
640 6 7
641 EXPLAIN SELECT * FROM t1 IGNORE KEY FOR JOIN(PRIMARY) WHERE f1 + 1 BETWEEN 5 AND 7;
642 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
643 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using where
644 Warnings:
645 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` IGNORE INDEX FOR JOIN (PRIMARY) where ((`test`.`t1`.`f1` + 1) between 5 and 7)
646 # Check that ORDER BY could be optimized
647 SELECT * FROM t1 ORDER BY f1 + 1;
648 f1 gc
649 0 1
650 1 2
651 2 3
652 3 4
653 4 5
654 5 6
655 6 7
656 7 8
657 8 9
658 9 10
659 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
660 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
661 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 10 100.00 NULL
662 Warnings:
663 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` order by (`test`.`t1`.`f1` + 1)
664 SELECT * FROM information_schema.OPTIMIZER_TRACE;
665 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
666 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1 {
667 "steps": [
668 {
669 "join_preparation": {
670 "select#": 1,
671 "steps": [
672 {
673 "expanded_query": "/* select#1 */ select `t1`.`f1` AS `f1`,`t1`.`gc` AS `gc` from `t1` order by (`t1`.`f1` + 1)"
674 }
675 ] /* steps */
676 } /* join_preparation */
677 },
678 {
679 "join_optimization": {
680 "select#": 1,
681 "steps": [
682 {
683 "substitute_generated_columns": {
684 "resulting_ORDER_BY": "`t1`.`gc`"
685 } /* substitute_generated_columns */
686 },
687 {
688 "table_dependencies": [
689 {
690 "table": "`t1`",
691 "row_may_be_null": false,
692 "map_bit": 0,
693 "depends_on_map_bits": [
694 ] /* depends_on_map_bits */
695 }
696 ] /* table_dependencies */
697 },
698 {
699 "rows_estimation": [
700 {
701 "table": "`t1`",
702 "table_scan": {
703 "rows": 10,
704 "cost": 0.25
705 } /* table_scan */
706 }
707 ] /* rows_estimation */
708 },
709 {
710 "considered_execution_plans": [
711 {
712 "plan_prefix": [
713 ] /* plan_prefix */,
714 "table": "`t1`",
715 "best_access_path": {
716 "considered_access_paths": [
717 {
718 "rows_to_scan": 10,
719 "access_type": "scan",
720 "resulting_rows": 10,
721 "cost": 1.25,
722 "chosen": true,
723 "use_tmp_table": true
724 }
725 ] /* considered_access_paths */
726 } /* best_access_path */,
727 "condition_filtering_pct": 100,
728 "rows_for_plan": 10,
729 "cost_for_plan": 1.25,
730 "sort_cost": 10,
731 "new_cost_for_plan": 11.25,
732 "chosen": true
733 }
734 ] /* considered_execution_plans */
735 },
736 {
737 "attaching_conditions_to_tables": {
738 "original_condition": null,
739 "attached_conditions_computation": [
740 ] /* attached_conditions_computation */,
741 "attached_conditions_summary": [
742 {
743 "table": "`t1`",
744 "attached": null
745 }
746 ] /* attached_conditions_summary */
747 } /* attaching_conditions_to_tables */
748 },
749 {
750 "optimizing_distinct_group_by_order_by": {
751 "simplifying_order_by": {
752 "original_clause": "`t1`.`gc`",
753 "items": [
754 {
755 "item": "`t1`.`gc`"
756 }
757 ] /* items */,
758 "resulting_clause_is_simple": true,
759 "resulting_clause": "`t1`.`gc`"
760 } /* simplifying_order_by */
761 } /* optimizing_distinct_group_by_order_by */
762 },
763 {
764 "reconsidering_access_paths_for_index_ordering": {
765 "clause": "ORDER BY",
766 "steps": [
767 ] /* steps */,
768 "index_order_summary": {
769 "table": "`t1`",
770 "index_provides_order": true,
771 "order_direction": "asc",
772 "index": "PRIMARY",
773 "plan_changed": true,
774 "access_type": "index"
775 } /* index_order_summary */
776 } /* reconsidering_access_paths_for_index_ordering */
777 },
778 {
779 "finalizing_table_conditions": [
780 ] /* finalizing_table_conditions */
781 },
782 {
783 "refine_plan": [
784 {
785 "table": "`t1`"
786 }
787 ] /* refine_plan */
788 },
789 {
790 "considering_tmp_tables": [
791 ] /* considering_tmp_tables */
792 }
793 ] /* steps */
794 } /* join_optimization */
795 },
796 {
797 "join_explain": {
798 "select#": 1,
799 "steps": [
800 ] /* steps */
801 } /* join_explain */
802 }
803 ] /* steps */
804 } 0 0
805 EXPLAIN SELECT * FROM t1 IGNORE KEY FOR ORDER BY(PRIMARY) ORDER BY f1 + 1;
806 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
807 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using filesort
808 Warnings:
809 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` IGNORE INDEX FOR ORDER BY (PRIMARY) order by (`test`.`t1`.`f1` + 1)
810 # Check that GROUP BY could be optimized
811 SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
812 f1 + 1 MAX(GC)
813 1 1
814 10 10
815 2 2
816 3 3
817 4 4
818 5 5
819 6 6
820 7 7
821 8 8
822 9 9
823 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
824 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
825 1 SIMPLE t1 NULL index PRIMARY PRIMARY 4 NULL 10 100.00 NULL
826 Warnings:
827 Note 1003 /* select#1 */ select (`test`.`t1`.`f1` + 1) AS `f1 + 1`,max(`test`.`t1`.`gc`) AS `MAX(GC)` from `test`.`t1` group by (`test`.`t1`.`f1` + 1)
828 SELECT * FROM information_schema.OPTIMIZER_TRACE;
829 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
830 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1 {
831 "steps": [
832 {
833 "join_preparation": {
834 "select#": 1,
835 "steps": [
836 {
837 "expanded_query": "/* select#1 */ select (`t1`.`f1` + 1) AS `f1 + 1`,max(`t1`.`gc`) AS `MAX(GC)` from `t1` group by (`t1`.`f1` + 1)"
838 }
839 ] /* steps */
840 } /* join_preparation */
841 },
842 {
843 "join_optimization": {
844 "select#": 1,
845 "steps": [
846 {
847 "substitute_generated_columns": {
848 "resulting_GROUP_BY": "`t1`.`gc`"
849 } /* substitute_generated_columns */
850 },
851 {
852 "table_dependencies": [
853 {
854 "table": "`t1`",
855 "row_may_be_null": false,
856 "map_bit": 0,
857 "depends_on_map_bits": [
858 ] /* depends_on_map_bits */
859 }
860 ] /* table_dependencies */
861 },
862 {
863 "rows_estimation": [
864 {
865 "table": "`t1`",
866 "const_keys_added": {
867 "keys": [
868 "PRIMARY"
869 ] /* keys */,
870 "cause": "group_by"
871 } /* const_keys_added */,
872 "range_analysis": {
873 "table_scan": {
874 "rows": 10,
875 "cost": 3.35
876 } /* table_scan */,
877 "potential_range_indexes": [
878 {
879 "index": "PRIMARY",
880 "usable": true,
881 "key_parts": [
882 "gc"
883 ] /* key_parts */
884 }
885 ] /* potential_range_indexes */,
886 "group_index_range": {
887 "potential_group_range_indexes": [
888 {
889 "index": "PRIMARY",
890 "usable": false,
891 "cause": "not_covering"
892 }
893 ] /* potential_group_range_indexes */
894 } /* group_index_range */,
895 "skip_scan_range": {
896 "chosen": false,
897 "cause": "has_group_by"
898 } /* skip_scan_range */
899 } /* range_analysis */
900 }
901 ] /* rows_estimation */
902 },
903 {
904 "considered_execution_plans": [
905 {
906 "plan_prefix": [
907 ] /* plan_prefix */,
908 "table": "`t1`",
909 "best_access_path": {
910 "considered_access_paths": [
911 {
912 "rows_to_scan": 10,
913 "access_type": "scan",
914 "resulting_rows": 10,
915 "cost": 1.25,
916 "chosen": true,
917 "use_tmp_table": true
918 }
919 ] /* considered_access_paths */
920 } /* best_access_path */,
921 "condition_filtering_pct": 100,
922 "rows_for_plan": 10,
923 "cost_for_plan": 1.25,
924 "sort_cost": 10,
925 "new_cost_for_plan": 11.25,
926 "chosen": true
927 }
928 ] /* considered_execution_plans */
929 },
930 {
931 "attaching_conditions_to_tables": {
932 "original_condition": null,
933 "attached_conditions_computation": [
934 ] /* attached_conditions_computation */,
935 "attached_conditions_summary": [
936 {
937 "table": "`t1`",
938 "attached": null
939 }
940 ] /* attached_conditions_summary */
941 } /* attaching_conditions_to_tables */
942 },
943 {
944 "optimizing_distinct_group_by_order_by": {
945 "simplifying_group_by": {
946 "original_clause": "`t1`.`gc`",
947 "items": [
948 {
949 "item": "`t1`.`gc`"
950 }
951 ] /* items */,
952 "resulting_clause_is_simple": true,
953 "resulting_clause": "`t1`.`gc`"
954 } /* simplifying_group_by */
955 } /* optimizing_distinct_group_by_order_by */
956 },
957 {
958 "reconsidering_access_paths_for_index_ordering": {
959 "clause": "GROUP BY",
960 "steps": [
961 ] /* steps */,
962 "index_order_summary": {
963 "table": "`t1`",
964 "index_provides_order": true,
965 "order_direction": "asc",
966 "index": "PRIMARY",
967 "plan_changed": true,
968 "access_type": "index"
969 } /* index_order_summary */
970 } /* reconsidering_access_paths_for_index_ordering */
971 },
972 {
973 "finalizing_table_conditions": [
974 ] /* finalizing_table_conditions */
975 },
976 {
977 "refine_plan": [
978 {
979 "table": "`t1`"
980 }
981 ] /* refine_plan */
982 },
983 {
984 "considering_tmp_tables": [
985 ] /* considering_tmp_tables */
986 }
987 ] /* steps */
988 } /* join_optimization */
989 },
990 {
991 "join_explain": {
992 "select#": 1,
993 "steps": [
994 ] /* steps */
995 } /* join_explain */
996 }
997 ] /* steps */
998 } 0 0
999 EXPLAIN SELECT f1 + 1, MAX(GC)
1000 FROM t1 IGNORE KEY FOR GROUP BY(PRIMARY) GROUP BY f1 + 1;
1001 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1002 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using temporary
1003 Warnings:
1004 Note 1003 /* select#1 */ select (`test`.`t1`.`f1` + 1) AS `f1 + 1`,max(`test`.`t1`.`gc`) AS `MAX(GC)` from `test`.`t1` IGNORE INDEX FOR GROUP BY (PRIMARY) group by (`test`.`t1`.`f1` + 1)
1005 # Shouldn't use index
1006 SELECT * FROM t1 WHERE f1 + 1 > 7.0;
1007 f1 gc
1008 7 8
1009 8 9
1010 9 10
1011 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
1012 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1013 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using where
1014 Warnings:
1015 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` where ((`test`.`t1`.`f1` + 1) > 7.0)
1016 DROP TABLE t1;
1017 # Pick index with proper type
1018 CREATE TABLE t1 (f1 int,
1019 gc_int int AS (f1 + 1) STORED,
1020 gc_date DATE AS (f1 + 1) STORED,
1021 KEY gc_int_idx(gc_int),
1022 KEY gc_date_idx(gc_date));
1023 INSERT INTO t1(f1) VALUES
1024 (030303),(040404),
1025 (050505),(060606),
1026 (010101),(020202),
1027 (030303),(040404),
1028 (050505),(060606),
1029 (010101),(020202),
1030 (090909),(101010),
1031 (010101),(020202),
1032 (070707),(080808);
1033 ANALYZE TABLE t1;
1034 Table Op Msg_type Msg_text
1035 test.t1 analyze status OK
1036 SELECT * FROM t1 WHERE f1 + 1 > 070707;
1037 f1 gc_int gc_date
1038 101010 101011 2010-10-11
1039 70707 70708 2007-07-08
1040 80808 80809 2008-08-09
1041 90909 90910 2009-09-10
1042 # INT column & index should be picked
1043 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
1044 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1045 1 SIMPLE t1 NULL range gc_int_idx gc_int_idx 5 NULL 4 100.00 Using index condition
1046 Warnings:
1047 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc_int` AS `gc_int`,`test`.`t1`.`gc_date` AS `gc_date` from `test`.`t1` where (`test`.`t1`.`gc_int` > 70707)
1048 SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
1049 f1 gc_int gc_date
1050 101010 101011 2010-10-11
1051 70707 70708 2007-07-08
1052 80808 80809 2008-08-09
1053 90909 90910 2009-09-10
1054 # DATE column & index should be picked
1055 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
1056 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1057 1 SIMPLE t1 NULL range gc_date_idx gc_date_idx 4 NULL 4 100.00 Using index condition
1058 Warnings:
1059 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc_int` AS `gc_int`,`test`.`t1`.`gc_date` AS `gc_date` from `test`.`t1` where (`test`.`t1`.`gc_date` > <cache>(cast(70707 as date)))
1060 DROP TABLE t1;
1061 #
1062 # BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
1063 #
1064 CREATE TABLE t1 (
1065 pk int primary key auto_increment,
1066 col_int_key INTEGER ,
1067 col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
1068 KEY col_int_gc_key(col_int_gc_key)
1069 );
1070 INSERT INTO t1 ( col_int_key) VALUES (7);
1071 ANALYZE TABLE t1;
1072 Table Op Msg_type Msg_text
1073 test.t1 analyze status OK
1074 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
1075 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
1076 ORDER BY field1, field2;
1077 field1 field2
1078 8 7
1079 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
1080 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
1081 ORDER BY field1, field2;
1082 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1083 1 SIMPLE table1 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Using temporary; Using filesort
1084 1 SIMPLE table2 NULL eq_ref PRIMARY PRIMARY 4 test.table1.pk 1 100.00 NULL
1085 Warnings:
1086 Note 1003 /* select#1 */ select (`test`.`table1`.`col_int_key` + 1) AS `field1`,`test`.`table2`.`col_int_key` AS `field2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table2`.`pk` = `test`.`table1`.`pk`) order by `field1`,`field2`
1087 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
1088 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
1089 GROUP BY field1, field2;
1090 field1 field2
1091 8 7
1092 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
1093 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
1094 GROUP BY field1, field2;
1095 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1096 1 SIMPLE table1 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Using temporary
1097 1 SIMPLE table2 NULL eq_ref PRIMARY PRIMARY 4 test.table1.pk 1 100.00 NULL
1098 Warnings:
1099 Note 1003 /* select#1 */ select (`test`.`table1`.`col_int_key` + 1) AS `field1`,`test`.`table2`.`col_int_key` AS `field2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table2`.`pk` = `test`.`table1`.`pk`) group by `field1`,`field2`
1100 DROP TABLE t1;
1101 #
1102 # Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX
1103 # ON VIRTUAL COLUMN
1104 #
1105 CREATE TABLE t1 (
1106 col1 INTEGER NOT NULL,
1107 col2 INTEGER NOT NULL,
1108 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
1109 col3 INTEGER NOT NULL,
1110 col4 INTEGER NOT NULL,
1111 col5 INTEGER DEFAULT NULL,
1112 col6 INTEGER DEFAULT NULL,
1113 col7 INTEGER DEFAULT NULL,
1114 col8 INTEGER DEFAULT NULL,
1115 col9 INTEGER DEFAULT NULL,
1116 col10 INTEGER DEFAULT NULL,
1117 col11 INTEGER DEFAULT NULL,
1118 col12 INTEGER DEFAULT NULL,
1119 col13 INTEGER DEFAULT NULL,
1120 col14 INTEGER DEFAULT NULL,
1121 col15 INTEGER DEFAULT NULL,
1122 col16 INTEGER DEFAULT NULL,
1123 col17 INTEGER DEFAULT NULL,
1124 col18 INTEGER DEFAULT NULL,
1125 col19 INTEGER DEFAULT NULL,
1126 col20 INTEGER DEFAULT NULL,
1127 col21 INTEGER DEFAULT NULL,
1128 col22 INTEGER DEFAULT NULL,
1129 col23 INTEGER DEFAULT NULL,
1130 col24 INTEGER DEFAULT NULL,
1131 col25 INTEGER DEFAULT NULL,
1132 col26 INTEGER DEFAULT NULL,
1133 col27 INTEGER DEFAULT NULL,
1134 col28 INTEGER DEFAULT NULL,
1135 col29 INTEGER DEFAULT NULL,
1136 col30 INTEGER DEFAULT NULL,
1137 col31 INTEGER DEFAULT NULL,
1138 col32 INTEGER DEFAULT NULL,
1139 col33 INTEGER DEFAULT NULL,
1140 col34 INTEGER DEFAULT NULL,
1141 col35 INTEGER DEFAULT NULL,
1142 col36 INTEGER DEFAULT NULL,
1143 col37 INTEGER DEFAULT NULL,
1144 col38 INTEGER DEFAULT NULL,
1145 col39 INTEGER DEFAULT NULL,
1146 col40 INTEGER DEFAULT NULL,
1147 col41 INTEGER DEFAULT NULL,
1148 col42 INTEGER DEFAULT NULL,
1149 col43 INTEGER DEFAULT NULL,
1150 col44 INTEGER DEFAULT NULL,
1151 col45 INTEGER DEFAULT NULL,
1152 col46 INTEGER DEFAULT NULL,
1153 col47 INTEGER DEFAULT NULL,
1154 col48 INTEGER DEFAULT NULL,
1155 col49 INTEGER DEFAULT NULL,
1156 col50 INTEGER DEFAULT NULL,
1157 col51 INTEGER DEFAULT NULL,
1158 col52 INTEGER DEFAULT NULL,
1159 col53 INTEGER DEFAULT NULL,
1160 col54 INTEGER DEFAULT NULL,
1161 col55 INTEGER DEFAULT NULL,
1162 col56 INTEGER DEFAULT NULL,
1163 col57 INTEGER DEFAULT NULL,
1164 col58 INTEGER DEFAULT NULL,
1165 col59 INTEGER DEFAULT NULL,
1166 col60 INTEGER DEFAULT NULL,
1167 col61 INTEGER DEFAULT NULL,
1168 col62 INTEGER DEFAULT NULL,
1169 col63 INTEGER DEFAULT NULL,
1170 col64 INTEGER DEFAULT NULL,
1171 col65 INTEGER DEFAULT NULL,
1172 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
1173 KEY idx1 (gcol1)
1174 );
1175 INSERT INTO t1 (col1, col2, col3, col4)
1176 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
1177 ALTER TABLE t1 ADD COLUMN extra INTEGER;
1178 SELECT gcol1 FROM t1 FORCE INDEX(idx1);
1179 gcol1
1180 2
1181 4
1182 6
1183 8
1184 10
1185 DROP TABLE t1;
1186 CREATE TABLE t1 (
1187 col1 INTEGER NOT NULL,
1188 col2 INTEGER NOT NULL,
1189 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
1190 col3 INTEGER NOT NULL,
1191 col4 INTEGER NOT NULL,
1192 col5 INTEGER DEFAULT NULL,
1193 col6 INTEGER DEFAULT NULL,
1194 col7 INTEGER DEFAULT NULL,
1195 col8 INTEGER DEFAULT NULL,
1196 col9 INTEGER DEFAULT NULL,
1197 col10 INTEGER DEFAULT NULL,
1198 col11 INTEGER DEFAULT NULL,
1199 col12 INTEGER DEFAULT NULL,
1200 col13 INTEGER DEFAULT NULL,
1201 col14 INTEGER DEFAULT NULL,
1202 col15 INTEGER DEFAULT NULL,
1203 col16 INTEGER DEFAULT NULL,
1204 col17 INTEGER DEFAULT NULL,
1205 col18 INTEGER DEFAULT NULL,
1206 col19 INTEGER DEFAULT NULL,
1207 col20 INTEGER DEFAULT NULL,
1208 col21 INTEGER DEFAULT NULL,
1209 col22 INTEGER DEFAULT NULL,
1210 col23 INTEGER DEFAULT NULL,
1211 col24 INTEGER DEFAULT NULL,
1212 col25 INTEGER DEFAULT NULL,
1213 col26 INTEGER DEFAULT NULL,
1214 col27 INTEGER DEFAULT NULL,
1215 col28 INTEGER DEFAULT NULL,
1216 col29 INTEGER DEFAULT NULL,
1217 col30 INTEGER DEFAULT NULL,
1218 col31 INTEGER DEFAULT NULL,
1219 col32 INTEGER DEFAULT NULL,
1220 col33 INTEGER DEFAULT NULL,
1221 col34 INTEGER DEFAULT NULL,
1222 col35 INTEGER DEFAULT NULL,
1223 col36 INTEGER DEFAULT NULL,
1224 col37 INTEGER DEFAULT NULL,
1225 col38 INTEGER DEFAULT NULL,
1226 col39 INTEGER DEFAULT NULL,
1227 col40 INTEGER DEFAULT NULL,
1228 col41 INTEGER DEFAULT NULL,
1229 col42 INTEGER DEFAULT NULL,
1230 col43 INTEGER DEFAULT NULL,
1231 col44 INTEGER DEFAULT NULL,
1232 col45 INTEGER DEFAULT NULL,
1233 col46 INTEGER DEFAULT NULL,
1234 col47 INTEGER DEFAULT NULL,
1235 col48 INTEGER DEFAULT NULL,
1236 col49 INTEGER DEFAULT NULL,
1237 col50 INTEGER DEFAULT NULL,
1238 col51 INTEGER DEFAULT NULL,
1239 col52 INTEGER DEFAULT NULL,
1240 col53 INTEGER DEFAULT NULL,
1241 col54 INTEGER DEFAULT NULL,
1242 col55 INTEGER DEFAULT NULL,
1243 col56 INTEGER DEFAULT NULL,
1244 col57 INTEGER DEFAULT NULL,
1245 col58 INTEGER DEFAULT NULL,
1246 col59 INTEGER DEFAULT NULL,
1247 col60 INTEGER DEFAULT NULL,
1248 col61 INTEGER DEFAULT NULL,
1249 col62 INTEGER DEFAULT NULL,
1250 col63 INTEGER DEFAULT NULL,
1251 col64 INTEGER DEFAULT NULL,
1252 col65 INTEGER DEFAULT NULL,
1253 gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL,
1254 KEY idx1 (gcol2)
1255 );
1256 INSERT INTO t1 (col1, col2, col3, col4)
1257 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
1258 ALTER TABLE t1 ADD COLUMN extra INTEGER;
1259 SELECT gcol2 FROM t1 FORCE INDEX(idx1);
1260 gcol2
1261 1
1262 1
1263 1
1264 1
1265 1
1266 DROP TABLE t1;
1267 #
1268 # Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
1269 #
1270 Warnings:
1271 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1272 CREATE TABLE t (a INT,
1273 b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL,
1274 c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL);
1275 INSERT INTO t(a) VALUES (1);
1276 Warnings:
1277 Warning 1264 Out of range value for column 'b' at row 1
1278 SELECT * FROM t WHERE c = '0';
1279 a b c
1280 1 127 0
1281 ALTER TABLE t ADD UNIQUE INDEX (c(1));
1282 Warnings:
1283 Warning 1264 Out of range value for column 'b' at row 1
1284 SELECT * FROM t WHERE c = '0';
1285 a b c
1286 1 127 0
1287 DROP TABLE t;
1288 #
1289 # Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD()
1290 # DID NOT RETURN TRUE WITH DIVIDE 0
1291 #
1292 CREATE TABLE t (a INT, b INT, h VARCHAR(10));
1293 INSERT INTO t VALUES (12, 3, "ss");
1294 INSERT INTO t VALUES (13, 4, "ss");
1295 INSERT INTO t VALUES (14, 0, "ss");
1296 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
1297 CREATE INDEX idx ON t(c);
1298 ERROR 22012: Division by 0
1299 CALL mtr.add_suppression("\\[Warning\\] \\[[^]]*\\] InnoDB: Compute virtual column values failed");
1300 DROP TABLE t;
1301 #
1302 # Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
1303 # WITH LOGICAL OPERATORS
1304 #
1305 CREATE TABLE t (a INT, b INT,
1306 gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
1307 gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
1308 gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
1309 gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
1310 gc_case INT GENERATED ALWAYS AS
1311 (CASE WHEN (a AND b) THEN a ELSE b END) STORED,
1312 INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
1313 INDEX(gc_case));
1314 INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
1315 ANALYZE TABLE t;
1316 Table Op Msg_type Msg_text
1317 test.t analyze status OK
1318 EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
1319 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1320 1 SIMPLE t NULL ref gc_and gc_and 5 const 1 100.00 NULL
1321 Warnings:
1322 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_and` = 1)
1323 SELECT a, b FROM t WHERE (a AND b) = 1;
1324 a b
1325 1 1
1326 EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
1327 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1328 1 SIMPLE t NULL ref gc_and gc_and 5 const 1 100.00 NULL
1329 Warnings:
1330 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (1 = `test`.`t`.`gc_and`)
1331 SELECT a, b FROM t WHERE 1 = (a AND b);
1332 a b
1333 1 1
1334 EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
1335 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1336 1 SIMPLE t NULL range gc_and gc_and 5 NULL 3 100.00 Using index condition
1337 Warnings:
1338 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_and` in (1,2,3))
1339 SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
1340 a b
1341 1 1
1342 EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
1343 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1344 1 SIMPLE t NULL ref gc_or gc_or 5 const 3 100.00 NULL
1345 Warnings:
1346 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_or` = 1)
1347 SELECT a, b FROM t WHERE (a OR b) = 1;
1348 a b
1349 0 1
1350 1 0
1351 1 1
1352 EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
1353 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1354 1 SIMPLE t NULL range gc_or gc_or 5 NULL 3 100.00 Using index condition
1355 Warnings:
1356 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_or` between 1 and 10)
1357 SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
1358 a b
1359 0 1
1360 1 0
1361 1 1
1362 EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
1363 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1364 1 SIMPLE t NULL ref gc_xor gc_xor 5 const 2 100.00 NULL
1365 Warnings:
1366 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_xor` = 1)
1367 SELECT a, b FROM t WHERE (a XOR b) = 1;
1368 a b
1369 0 1
1370 1 0
1371 EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
1372 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1373 1 SIMPLE t NULL ref gc_not gc_not 5 const 2 100.00 NULL
1374 Warnings:
1375 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (`test`.`t`.`gc_not` = 1)
1376 SELECT a FROM t WHERE (NOT a) = 1;
1377 a
1378 0
1379 0
1380 EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
1381 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1382 1 SIMPLE t NULL ref gc_case gc_case 5 const 2 100.00 NULL
1383 Warnings:
1384 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (`test`.`t`.`gc_case` = 1)
1385 SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
1386 a
1387 0
1388 1
1389 EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
1390 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1391 1 SIMPLE t NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1392 Warnings:
1393 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (1 = ((0 <> `test`.`t`.`b`) and (0 <> `test`.`t`.`a`)))
1394 SELECT a, b FROM t WHERE 1 = (b AND a);
1395 a b
1396 1 1
1397 EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
1398 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1399 1 SIMPLE t NULL ALL NULL NULL NULL NULL 4 100.00 Using where
1400 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (1 = ((0 <> `test`.`t`.`b`) or (0 <> `test`.`t`.`a`)))
1401 Warnings:
1402 SELECT a, b FROM t WHERE 1 = (b OR a);
1403 a b
1404 0 1
1405 1 0
1406 1 1
1407 DROP TABLE t;
1408 #
1409 # Bug#21854241: QUERY USING JSON_EXTRACT() RETURNS WRONG RESULT
1410 # AFTER ADDING VIRTUAL INDEX
1411 #
1412 CREATE TABLE employees (
1413 data JSON,
1414 name1 VARCHAR(30) AS (JSON_EXTRACT(data, "$.name")) STORED,
1415 name2 VARCHAR(30) AS (JSON_UNQUOTE(JSON_EXTRACT(data, "$.name"))) STORED
1416 );
1417 INSERT INTO employees (data) VALUES('{"id": 1, "name": "Jane"}');
1418 INSERT INTO employees (data) VALUES('{"id": 2, "name": "Joe"}');
1419 ANALYZE TABLE employees;
1420 Table Op Msg_type Msg_text
1421 test.employees analyze status OK
1422 EXPLAIN SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
1423 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1424 1 SIMPLE employees NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1425 Warnings:
1426 Note 1003 /* select#1 */ select `test`.`employees`.`data` AS `data`,`test`.`employees`.`name1` AS `name1`,`test`.`employees`.`name2` AS `name2` from `test`.`employees` where (json_extract(`test`.`employees`.`data`,'$.name') = 'Jane')
1427 SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
1428 data name1 name2
1429 {"id": 1, "name": "Jane"} "Jane" Jane
1430 ALTER TABLE employees ADD INDEX name_idx1(name1);
1431 ANALYZE TABLE employees;
1432 Table Op Msg_type Msg_text
1433 test.employees analyze status OK
1434 EXPLAIN SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
1435 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1436 1 SIMPLE employees NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1437 Warnings:
1438 Note 1003 /* select#1 */ select `test`.`employees`.`data` AS `data`,`test`.`employees`.`name1` AS `name1`,`test`.`employees`.`name2` AS `name2` from `test`.`employees` where (json_extract(`test`.`employees`.`data`,'$.name') = 'Jane')
1439 SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
1440 data name1 name2
1441 {"id": 1, "name": "Jane"} "Jane" Jane
1442 ALTER TABLE employees ADD INDEX name_idx2(name2);
1443 ANALYZE TABLE employees;
1444 Table Op Msg_type Msg_text
1445 test.employees analyze status OK
1446 EXPLAIN SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
1447 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1448 1 SIMPLE employees NULL ref name_idx2 name_idx2 123 const 1 100.00 NULL
1449 Warnings:
1450 Note 1003 /* select#1 */ select `test`.`employees`.`data` AS `data`,`test`.`employees`.`name1` AS `name1`,`test`.`employees`.`name2` AS `name2` from `test`.`employees` where (`test`.`employees`.`name2` = 'Jane')
1451 SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
1452 data name1 name2
1453 {"id": 1, "name": "Jane"} "Jane" Jane
1454 DROP TABLE employees;
1455 #
1456 # Bug#22077611 UPDATE .. WHERE JSON_EXTRACT(..) = '..' NOT USING
1457 # VIRTUAL COL INDEX
1458 #
1459 CREATE TABLE t(a INT, b INT, gc INT GENERATED ALWAYS AS (a+1) STORED, KEY(gc));
1460 INSERT INTO t(a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5);
1461 ANALYZE TABLE t;
1462 Table Op Msg_type Msg_text
1463 test.t analyze status OK
1464 EXPLAIN UPDATE t SET b = 10 WHERE (a+1) = 3;
1465 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1466 1 UPDATE t NULL range gc gc 5 const 2 100.00 Using where
1467 Warnings:
1468 Note 1003 update `test`.`t` set `test`.`t`.`b` = 10 where (`test`.`t`.`gc` = 3)
1469 UPDATE t SET b = 10 WHERE (a+1) = 3;
1470 SELECT * FROM t ORDER BY a, b;
1471 a b gc
1472 1 NULL 2
1473 1 NULL 2
1474 2 10 3
1475 2 10 3
1476 3 NULL 4
1477 3 NULL 4
1478 4 NULL 5
1479 4 NULL 5
1480 5 NULL 6
1481 5 NULL 6
1482 ANALYZE TABLE t;
1483 Table Op Msg_type Msg_text
1484 test.t analyze status OK
1485 EXPLAIN UPDATE t SET b = 9 ORDER BY (a+1) LIMIT 1;
1486 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1487 1 UPDATE t NULL index NULL gc 5 NULL 1 100.00 Using temporary
1488 Warnings:
1489 Note 1003 update `test`.`t` set `test`.`t`.`b` = 9 order by `test`.`t`.`gc` limit 1
1490 UPDATE t SET b = 9 ORDER BY (a+1) LIMIT 1;
1491 SELECT * FROM t ORDER BY a, b;
1492 a b gc
1493 1 NULL 2
1494 1 9 2
1495 2 10 3
1496 2 10 3
1497 3 NULL 4
1498 3 NULL 4
1499 4 NULL 5
1500 4 NULL 5
1501 5 NULL 6
1502 5 NULL 6
1503 ANALYZE TABLE t;
1504 Table Op Msg_type Msg_text
1505 test.t analyze status OK
1506 EXPLAIN DELETE FROM t WHERE (a+1) = 2;
1507 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1508 1 DELETE t NULL range gc gc 5 const 2 100.00 Using where
1509 Warnings:
1510 Note 1003 delete from `test`.`t` where (`test`.`t`.`gc` = 2)
1511 DELETE FROM t WHERE (a+1) = 2;
1512 SELECT * FROM t ORDER BY a, b;
1513 a b gc
1514 2 10 3
1515 2 10 3
1516 3 NULL 4
1517 3 NULL 4
1518 4 NULL 5
1519 4 NULL 5
1520 5 NULL 6
1521 5 NULL 6
1522 ANALYZE TABLE t;
1523 Table Op Msg_type Msg_text
1524 test.t analyze status OK
1525 EXPLAIN DELETE FROM t ORDER BY (a+1) LIMIT 1;
1526 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1527 1 DELETE t NULL index NULL gc 5 NULL 1 100.00 NULL
1528 Warnings:
1529 Note 1003 delete from `test`.`t` order by `test`.`t`.`gc` limit 1
1530 DELETE FROM t ORDER BY (a+1) LIMIT 1;
1531 SELECT * FROM t ORDER BY a, b;
1532 a b gc
1533 2 10 3
1534 3 NULL 4
1535 3 NULL 4
1536 4 NULL 5
1537 4 NULL 5
1538 5 NULL 6
1539 5 NULL 6
1540 DROP TABLE t;
1541 #
1542 # Bug#22095783: ALTER TABLE ADD COLUMN FAILS WITH OUT
1543 # OF RANGE VALUE ERROR
1544 CREATE TABLE c1(doc JSON,_id VARCHAR(32) GENERATED ALWAYS
1545 AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED NOT NULL UNIQUE);
1546 INSERT INTO c1(doc) VALUES('{"X":10,"_id":9}');
1547 ALTER TABLE c1 ADD COLUMN vc1 DOUBLE(4, 3) GENERATED
1548 ALWAYS AS (JSON_EXTRACT(doc, '$.X')) VIRTUAL , ADD INDEX IX1 (vc1);
1549 ERROR 22003: Out of range value for column 'vc1' at row 1
1550 ALTER TABLE c1 ADD vc2 INT(11);
1551 Warnings:
1552 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1553 ALTER TABLE c1 ADD COLUMN vc3 INT(11) GENERATED ALWAYS AS
1554 (JSON_EXTRACT(doc, '$.X')) VIRTUAL , ADD UNIQUE INDEX IX2 (vc3);
1555 Warnings:
1556 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1557 ALTER TABLE c1 drop vc2;
1558 ALTER TABLE c1 ADD vc4 INT(11);
1559 Warnings:
1560 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1561 DROP TABLE c1;
1562 #
1563 # Bug#22810883: ASSERTION FAILED:
1564 # !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
1565 #
1566 CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
1567 a2 INTEGER, KEY (a1));
1568 INSERT INTO t1 VALUES ();
1569 CREATE TABLE t2 (b INTEGER);
1570 INSERT INTO t2 VALUES (1);
1571 ANALYZE TABLE t1, t2;
1572 Table Op Msg_type Msg_text
1573 test.t1 analyze status OK
1574 test.t2 analyze status OK
1575 # Used to choose the index on a1 and get wrong results.
1576 EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0;
1577 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1578 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1579 Warnings:
1580 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((0 <> `test`.`t1`.`a2`) and (0 <> `test`.`t1`.`a2`)) = 0)
1581 SELECT * FROM t1 WHERE (a2 AND a2) = 0;
1582 a1 a2
1583 # Used to get assertion or wrong results.
1584 EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
1585 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1586 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
1587 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
1588 Warnings:
1589 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((((0 <> `test`.`t2`.`b`) and (0 <> `test`.`t2`.`b`)) = 1) and (0 <> `test`.`t2`.`b`))
1590 SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
1591 a1 a2 b
1592 0 NULL 1
1593 DROP TABLE t1, t2;
1594 #
1595 # Bug#24345509: WRONG RESULTS WHEN GCOL INDEX IS USED
1596 #
1597 CREATE TABLE t1 (
1598 pk INT PRIMARY KEY AUTO_INCREMENT,
1599 i INT,
1600 vc VARCHAR(7),
1601 gc1 VARCHAR(14) GENERATED ALWAYS AS (concat(vc, vc)) VIRTUAL NOT NULL,
1602 gc2 VARCHAR(14) GENERATED ALWAYS AS (concat(gc1, 'x')) VIRTUAL NOT NULL,
1603 KEY gc2_key (gc2),
1604 KEY gc2_key_prefix (gc2(5))
1605 );
1606 INSERT INTO t1 (i, vc) VALUES
1607 (7, 'xcek'), (3, 'ceksat'), (3, 'eksate'), (3, 'ksatef'), (6, 's');
1608 ANALYZE TABLE t1;
1609 Table Op Msg_type Msg_text
1610 test.t1 analyze status OK
1611 EXPLAIN SELECT i FROM t1 WHERE gc2 <= 'ksatefksatefx';
1612 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1613 1 SIMPLE t1 NULL range gc2_key,gc2_key_prefix gc2_key 58 NULL 3 100.00 Using where
1614 Warnings:
1615 Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (`test`.`t1`.`gc2` <= 'ksatefksatefx')
1616 SELECT i FROM t1 WHERE gc2 <= 'ksatefksatefx';
1617 i
1618 3
1619 3
1620 3
1621 EXPLAIN SELECT i FROM t1 FORCE INDEX (gc2_key_prefix)
1622 WHERE gc2 <= 'ksatefksatefx';
1623 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1624 1 SIMPLE t1 NULL range gc2_key_prefix gc2_key_prefix 22 NULL 1 100.00 Using where
1625 Warnings:
1626 Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` FORCE INDEX (`gc2_key_prefix`) where (`test`.`t1`.`gc2` <= 'ksatefksatefx')
1627 SELECT i FROM t1 FORCE INDEX (gc2_key_prefix)
1628 WHERE gc2 <= 'ksatefksatefx';
1629 i
1630 3
1631 3
1632 3
1633 EXPLAIN SELECT COUNT(*) FROM t1 WHERE gc2 <= 'ksatefksatefx';
1634 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1635 1 SIMPLE t1 NULL range gc2_key,gc2_key_prefix gc2_key 58 NULL 3 100.00 Using where; Using index
1636 Warnings:
1637 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`gc2` <= 'ksatefksatefx')
1638 SELECT COUNT(*) FROM t1 WHERE gc2 <= 'ksatefksatefx';
1639 COUNT(*)
1640 3
1641 EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX (gc2_key_prefix)
1642 WHERE gc2 <= 'ksatefksatefx';
1643 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1644 1 SIMPLE t1 NULL range gc2_key_prefix gc2_key_prefix 22 NULL 3 100.00 Using where
1645 Warnings:
1646 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`gc2_key_prefix`) where (`test`.`t1`.`gc2` <= 'ksatefksatefx')
1647 SELECT COUNT(*) FROM t1 FORCE INDEX (gc2_key_prefix)
1648 WHERE gc2 <= 'ksatefksatefx';
1649 COUNT(*)
1650 3
1651 CREATE TABLE t2 (
1652 i INT NOT NULL,
1653 gc INT GENERATED ALWAYS AS (i) VIRTUAL NOT NULL,
1654 KEY (gc)
1655 );
1656 INSERT INTO t2 (i) VALUES (-1), (0), (1), (2);
1657 BEGIN;
1658 SELECT i FROM t2 FORCE INDEX (gc) WHERE gc <= -1;
1659 i
1660 -1
1661 SELECT gc FROM t2 FORCE INDEX (gc) WHERE gc <= -1;
1662 gc
1663 -1
1664 COMMIT;
1665 DROP TABLE t1, t2;
1666 #
1667 # Bug#27010089: ASSERTION FAILURE:
1668 # ROW0SEL.CC:2846:TEMPL->MYSQL_COL_LEN == LEN
1669 #
1670 CREATE TABLE t (
1671 pk INTEGER AUTO_INCREMENT PRIMARY KEY,
1672 col_int_key INTEGER GENERATED ALWAYS AS (1) VIRTUAL,
1673 col_varchar VARCHAR(7) ,
1674 col_varchar_key VARCHAR(14) GENERATED ALWAYS AS ('abc') VIRTUAL,
1675 KEY (col_varchar_key(5), col_int_key),
1676 KEY (col_int_key, col_varchar_key));
1677 INSERT INTO t VALUES (), ();
1678 SELECT 1 FROM t AS alias1
1679 WHERE EXISTS
1680 (SELECT SQ2_alias1.col_int_key AS SQ2_field1
1681 FROM t AS SQ2_alias1 JOIN t AS SQ2_alias2
1682 ON (SQ2_alias2.col_varchar_key = SQ2_alias1.col_varchar_key)
1683 WHERE SQ2_alias1.col_varchar <> alias1.col_varchar AND
1684 SQ2_alias1.col_int_key >= SQ2_alias2.pk);
1685 1
1686 DROP TABLE t;
1687 #
1688 # Bug#27403367: GENERATED COLUMN EXPRESSIONS IGNORED WITH PREFIX INDEX
1689 #
1690 CREATE TABLE t (vc VARCHAR(100),
1691 gc VARCHAR(100) GENERATED ALWAYS AS (REVERSE(vc)) STORED,
1692 KEY (gc(3)));
1693 INSERT INTO t(vc) VALUES ('a'), ('abc'), ('abcabc'), ('abcdef');
1694 ANALYZE TABLE t;
1695 Table Op Msg_type Msg_text
1696 test.t analyze status OK
1697 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) = 'cba';
1698 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1699 1 SIMPLE t NULL ref gc gc 15 const 2 100.00 Using where
1700 Warnings:
1701 Note 1003 /* select#1 */ select `test`.`t`.`vc` AS `vc`,`test`.`t`.`gc` AS `gc` from `test`.`t` where (`test`.`t`.`gc` = 'cba')
1702 SELECT * FROM t WHERE REVERSE(vc) = 'cba';
1703 vc gc
1704 abc cba
1705 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) = 'cbacba';
1706 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1707 1 SIMPLE t NULL ref gc gc 15 const 2 100.00 Using where
1708 Warnings:
1709 Note 1003 /* select#1 */ select `test`.`t`.`vc` AS `vc`,`test`.`t`.`gc` AS `gc` from `test`.`t` where (`test`.`t`.`gc` = 'cbacba')
1710 SELECT * FROM t WHERE REVERSE(vc) = 'cbacba';
1711 vc gc
1712 abcabc cbacba
1713 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) BETWEEN 'c' AND 'e';
1714 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1715 1 SIMPLE t NULL range gc gc 15 NULL 2 100.00 Using where
1716 Warnings:
1717 Note 1003 /* select#1 */ select `test`.`t`.`vc` AS `vc`,`test`.`t`.`gc` AS `gc` from `test`.`t` where (`test`.`t`.`gc` between 'c' and 'e')
1718 SELECT * FROM t WHERE REVERSE(vc) BETWEEN 'c' AND 'e';
1719 vc gc
1720 abc cba
1721 abcabc cbacba
1722 DROP TABLE t;
1723 #
1724 # Bug#35178672: Assertion `dptr <= buffer->ptr() +
1725 # buffer->alloced_length()' failed.
1726 #
1727 CREATE TABLE t (
1728 pk INT PRIMARY KEY AUTO_INCREMENT,
1729 x VARCHAR(1) NOT NULL,
1730 gc VARCHAR(2) GENERATED ALWAYS AS (CONCAT(x, x)),
1731 KEY (gc)
1732 );
1733 INSERT INTO t (pk, x) VALUES (1, 'a'), (2, 'b'), (3, 'c');
1734 SELECT COUNT(*) FROM t GROUP BY gc;
1735 COUNT(*)
1736 1
1737 1
1738 1
1739 SELECT gc FROM t ORDER BY HEX(gc);
1740 gc
1741 aa
1742 bb
1743 cc
1744 DROP TABLE t;
1745 #
1746 #
1747 # BUG#21365158 WL8149:ASSERTION `!TABLE || (!TABLE->WRITE_SET
1748 #
1749 CREATE TABLE t1 (
1750 pk INTEGER AUTO_INCREMENT,
1751 col_int_nokey INTEGER NOT NULL,
1752 col_varchar_nokey VARCHAR(1) NOT NULL,
1753 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
1754 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null,
1755 PRIMARY KEY (pk)
1756 );
1757 INSERT INTO t1 ( col_int_nokey, col_varchar_nokey)
1758 VALUES (4, 'b'),(9, 'o'),(4, 'k'),(5, 'a'),(5, 'f'),
1759 (9, 't'),(3, 'c'),(8, 'c'),(0, 'r'),(98, 'k');
1760 CREATE TABLE t2 (
1761 pk INTEGER AUTO_INCREMENT,
1762 col_int_nokey INTEGER NOT NULL,
1763 col_varchar_nokey VARCHAR(1) NOT NULL,
1764 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
1765 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null,
1766 PRIMARY KEY (pk),
1767 UNIQUE KEY (col_varchar_key)
1768 );
1769 INSERT INTO t2 ( col_int_nokey, col_varchar_nokey)
1770 VALUES (1, 'c'),(8, 'm'),(9, 'd'), (6, 'y'),(1, 't'),
1771 (2, 's'),(4, 'r');
1772 SELECT
1773 CONCAT( t2.col_varchar_nokey , t2.col_varchar_nokey ) AS f2,
1774 t1.col_varchar_key AS f5
1775 FROM
1776 t2 LEFT JOIN t1 ON t2.col_int_nokey > t1.col_int_nokey
1777 ORDER BY f2, f5;
1778 f2 f5
1779 cc rr
1780 dd aa
1781 dd bb
1782 dd cc
1783 dd cc
1784 dd ff
1785 dd kk
1786 dd rr
1787 mm aa
1788 mm bb
1789 mm cc
1790 mm ff
1791 mm kk
1792 mm rr
1793 rr cc
1794 rr rr
1795 ss rr
1796 tt rr
1797 yy aa
1798 yy bb
1799 yy cc
1800 yy ff
1801 yy kk
1802 yy rr
1803 DROP TABLE t1,t2;
1804 #
1805 DROP VIEW IF EXISTS v1,v2;
1806 DROP TABLE IF EXISTS t1,t2,t3;
1807 DROP PROCEDURE IF EXISTS p1;
1808 DROP FUNCTION IF EXISTS f1;
1809 DROP TRIGGER IF EXISTS trg1;
1810 DROP TRIGGER IF EXISTS trg2;
1811 set sql_warnings = 0;