Debian Codegraph @

mysql-test/suite/gcol_ndb/r/gcol_keys_ndb.result

raw
1 SET @@session.default_storage_engine = 'ndbcluster';
2 SET ndb_index_stat_enable=OFF;
3 # - UNIQUE KEY
4 # - INDEX
5 # - FULLTEXT INDEX
6 # - SPATIAL INDEX (not supported)
7 # - FOREIGN INDEX (partially supported)
8 # - CHECK (allowed but not used)
9 # UNIQUE
10 create table t1 (a int, b int generated always as (a*2) stored unique);
11 show create table t1;
12 Table Create Table
13 t1 CREATE TABLE `t1` (
14 `a` int DEFAULT NULL,
15 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
16 UNIQUE KEY `b` (`b`)
17 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
18 describe t1;
19 Field Type Null Key Default Extra
20 a int YES NULL
21 b int YES UNI NULL STORED GENERATED
22 drop table t1;
23 create table t1 (a int, b int generated always as (a*2) stored, unique (b));
24 show create table t1;
25 Table Create Table
26 t1 CREATE TABLE `t1` (
27 `a` int DEFAULT NULL,
28 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
29 UNIQUE KEY `b` (`b`)
30 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
31 describe t1;
32 Field Type Null Key Default Extra
33 a int YES NULL
34 b int YES UNI NULL STORED GENERATED
35 drop table t1;
36 create table t1 (a int, b int generated always as (a*2) stored);
37 alter table t1 add unique key (b);
38 drop table t1;
39 # Testing data manipulation operations involving UNIQUE keys
40 # on generated columns can be found in:
41 # - gcol_ins_upd.inc
42 # - gcol_select.inc
43 #
44 # INDEX
45 create table t1 (a int, b int generated always as (a*2) stored, index (b));
46 show create table t1;
47 Table Create Table
48 t1 CREATE TABLE `t1` (
49 `a` int DEFAULT NULL,
50 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
51 KEY `b` (`b`)
52 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
53 describe t1;
54 Field Type Null Key Default Extra
55 a int YES NULL
56 b int YES MUL NULL STORED GENERATED
57 drop table t1;
58 create table t1 (a int, b int generated always as (a*2) stored, index (a,b));
59 show create table t1;
60 Table Create Table
61 t1 CREATE TABLE `t1` (
62 `a` int DEFAULT NULL,
63 `b` int GENERATED ALWAYS AS ((`a` * 2)) STORED,
64 KEY `a` (`a`,`b`)
65 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
66 describe t1;
67 Field Type Null Key Default Extra
68 a int YES MUL NULL
69 b int YES NULL STORED GENERATED
70 drop table t1;
71 create table t1 (a int, b int generated always as (a*2) stored);
72 alter table t1 add index (b);
73 drop table t1;
74 create table t1 (a int, b int generated always as (a*2) stored);
75 alter table t1 add index (a,b);
76 create table t2 like t1;
77 drop table t2;
78 drop table t1;
79 # Testing data manipulation operations involving INDEX
80 # on generated columns can be found in:
81 # - gcol_select.inc
82 #
83 # TODO: FULLTEXT INDEX
84 # SPATIAL INDEX
85 # FOREIGN KEY
86 # Rejected FK options.
87 create table t1 (a int, b int generated always as (a+1) stored,
88 foreign key (b) references t2(a) on update set null);
89 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
90 create table t1 (a int, b int generated always as (a+1) stored,
91 foreign key (b) references t2(a) on update cascade);
92 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
93 create table t1 (a int, b int generated always as (a+1) stored,
94 foreign key (b) references t2(a) on delete set null);
95 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
96 create table t1 (a int, b int generated always as (a+1) stored);
97 alter table t1 add foreign key (b) references t2(a) on update set null;
98 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
99 alter table t1 add foreign key (b) references t2(a) on update cascade;
100 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
101 alter table t1 add foreign key (b) references t2(a) on delete set null;
102 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
103 drop table t1;
104 # Allowed FK options.
105 create table t2 (a int primary key, b char(5));
106 create table t1 (a int, b int generated always as (a % 10) stored,
107 foreign key (b) references t2(a) on update restrict);
108 drop table t1;
109 create table t1 (a int, b int generated always as (a % 10) stored,
110 foreign key (b) references t2(a) on update no action);
111 drop table t1;
112 create table t1 (a int, b int generated always as (a % 10) stored,
113 foreign key (b) references t2(a) on delete restrict);
114 drop table t1;
115 create table t1 (a int, b int generated always as (a % 10) stored,
116 foreign key (b) references t2(a) on delete cascade);
117 drop table t1;
118 create table t1 (a int, b int generated always as (a % 10) stored,
119 foreign key (b) references t2(a) on delete no action);
120 drop table t1,t2;
121
122 # Testing data manipulation operations involving FOREIGN KEY
123 # on generated columns can be found in:
124 # - gcol_ins_upd.inc
125 # - gcol_select.inc
126 #
127 # TODO: CHECK
128 #
129 # Test how optimizer picks indexes defined on a GC
130 #
131 SET optimizer_trace_max_mem_size=1048576;
132 SET optimizer_trace="enabled=on,one_line=off";
133 SET end_markers_in_json="on";
134 CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED PRIMARY KEY);
135 INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6);
136 ANALYZE TABLE t1;
137 Table Op Msg_type Msg_text
138 test.t1 analyze status OK
139 # Should use index
140 SELECT * FROM t1 WHERE f1 + 1 > 7;
141 f1 gc
142 7 8
143 8 9
144 9 10
145 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7;
146 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
147 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t1`.`gc` > 7); Using MRR
148 Warnings:
149 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 7)
150 SELECT * FROM information_schema.OPTIMIZER_TRACE;
151 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
152 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7 {
153 "steps": [
154 {
155 "join_preparation": {
156 "select#": 1,
157 "steps": [
158 {
159 "expanded_query": "/* select#1 */ select `t1`.`f1` AS `f1`,`t1`.`gc` AS `gc` from `t1` where ((`t1`.`f1` + 1) > 7)"
160 }
161 ] /* steps */
162 } /* join_preparation */
163 },
164 {
165 "join_optimization": {
166 "select#": 1,
167 "steps": [
168 {
169 "condition_processing": {
170 "condition": "WHERE",
171 "original_condition": "((`t1`.`f1` + 1) > 7)",
172 "steps": [
173 {
174 "transformation": "equality_propagation",
175 "resulting_condition": "((`t1`.`f1` + 1) > 7)"
176 },
177 {
178 "transformation": "constant_propagation",
179 "resulting_condition": "((`t1`.`f1` + 1) > 7)"
180 },
181 {
182 "transformation": "trivial_condition_removal",
183 "resulting_condition": "((`t1`.`f1` + 1) > 7)"
184 }
185 ] /* steps */
186 } /* condition_processing */
187 },
188 {
189 "substitute_generated_columns": {
190 "resulting_condition": "(`t1`.`gc` > 7)"
191 } /* substitute_generated_columns */
192 },
193 {
194 "table_dependencies": [
195 {
196 "table": "`t1`",
197 "row_may_be_null": false,
198 "map_bit": 0,
199 "depends_on_map_bits": [
200 ] /* depends_on_map_bits */
201 }
202 ] /* table_dependencies */
203 },
204 {
205 "ref_optimizer_key_uses": [
206 ] /* ref_optimizer_key_uses */
207 },
208 {
209 "rows_estimation": [
210 {
211 "table": "`t1`",
212 "range_analysis": {
213 "table_scan": {
214 "rows": 10,
215 "cost": 2503.1
216 } /* table_scan */,
217 "potential_range_indexes": [
218 {
219 "index": "PRIMARY",
220 "usable": true,
221 "key_parts": [
222 "gc"
223 ] /* key_parts */
224 }
225 ] /* potential_range_indexes */,
226 "setup_range_conditions": [
227 ] /* setup_range_conditions */,
228 "group_index_range": {
229 "chosen": false,
230 "cause": "not_group_by_or_distinct"
231 } /* group_index_range */,
232 "skip_scan_range": {
233 "potential_skip_scan_indexes": [
234 {
235 "index": "PRIMARY",
236 "usable": false,
237 "cause": "query_references_nonkey_column"
238 }
239 ] /* potential_skip_scan_indexes */
240 } /* skip_scan_range */,
241 "analyzing_range_alternatives": {
242 "range_scan_alternatives": [
243 {
244 "index": "PRIMARY",
245 "ranges": [
246 "7 < gc"
247 ] /* ranges */,
248 "index_dives_for_eq_ranges": true,
249 "rowid_ordered": false,
250 "using_mrr": true,
251 "index_only": false,
252 "in_memory": -1,
253 "rows": 3,
254 "cost": 2.31,
255 "chosen": true
256 }
257 ] /* range_scan_alternatives */,
258 "analyzing_roworder_intersect": {
259 "usable": false,
260 "cause": "too_few_roworder_scans"
261 } /* analyzing_roworder_intersect */
262 } /* analyzing_range_alternatives */,
263 "chosen_range_access_summary": {
264 "range_access_plan": {
265 "type": "range_scan",
266 "index": "PRIMARY",
267 "rows": 3,
268 "ranges": [
269 "7 < gc"
270 ] /* ranges */
271 } /* range_access_plan */,
272 "rows_for_plan": 3,
273 "cost_for_plan": 2.31,
274 "chosen": true
275 } /* chosen_range_access_summary */
276 } /* range_analysis */
277 }
278 ] /* rows_estimation */
279 },
280 {
281 "considered_execution_plans": [
282 {
283 "plan_prefix": [
284 ] /* plan_prefix */,
285 "table": "`t1`",
286 "best_access_path": {
287 "considered_access_paths": [
288 {
289 "rows_to_scan": 3,
290 "filtering_effect": [
291 ] /* filtering_effect */,
292 "final_filtering_effect": 1,
293 "access_type": "range",
294 "range_details": {
295 "used_index": "PRIMARY"
296 } /* range_details */,
297 "resulting_rows": 3,
298 "cost": 2.61,
299 "chosen": true
300 }
301 ] /* considered_access_paths */
302 } /* best_access_path */,
303 "condition_filtering_pct": 100,
304 "rows_for_plan": 3,
305 "cost_for_plan": 2.61,
306 "chosen": true
307 }
308 ] /* considered_execution_plans */
309 },
310 {
311 "attaching_conditions_to_tables": {
312 "original_condition": "(`t1`.`gc` > 7)",
313 "attached_conditions_computation": [
314 ] /* attached_conditions_computation */,
315 "attached_conditions_summary": [
316 {
317 "table": "`t1`",
318 "attached": "(`t1`.`gc` > 7)"
319 }
320 ] /* attached_conditions_summary */
321 } /* attaching_conditions_to_tables */
322 },
323 {
324 "finalizing_table_conditions": [
325 {
326 "table": "`t1`",
327 "original_table_condition": "(`t1`.`gc` > 7)",
328 "final_table_condition ": "(`t1`.`gc` > 7)"
329 }
330 ] /* finalizing_table_conditions */
331 },
332 {
333 "refine_plan": [
334 {
335 "table": "`t1`"
336 }
337 ] /* refine_plan */
338 }
339 ] /* steps */
340 } /* join_optimization */
341 },
342 {
343 "join_explain": {
344 "select#": 1,
345 "steps": [
346 ] /* steps */
347 } /* join_explain */
348 }
349 ] /* steps */
350 } 0 0
351 SELECT * FROM t1 WHERE f1 + 1 = 7;
352 f1 gc
353 6 7
354 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7;
355 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
356 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const 1 100.00 NULL
357 Warnings:
358 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` = 7)
359 SELECT * FROM t1 WHERE f1 + 1 IN (7,5);
360 f1 gc
361 4 5
362 6 7
363 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5);
364 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
365 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 2 100.00 Using pushed condition (`test`.`t1`.`gc` in (7,5)); Using MRR
366 Warnings:
367 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))
368 SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
369 f1 gc
370 4 5
371 5 6
372 6 7
373 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7;
374 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
375 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t1`.`gc` between 5 and 7); Using MRR
376 Warnings:
377 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)
378 # Check that expression isn't transformed for a disabled key
379 SELECT * FROM t1 IGNORE KEY FOR JOIN(PRIMARY) WHERE f1 + 1 BETWEEN 5 AND 7;
380 f1 gc
381 4 5
382 5 6
383 6 7
384 EXPLAIN SELECT * FROM t1 IGNORE KEY FOR JOIN(PRIMARY) WHERE f1 + 1 BETWEEN 5 AND 7;
385 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
386 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 10 100.00 Using where
387 Warnings:
388 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)
389 # Check that ORDER BY could be optimized
390 SELECT * FROM t1 ORDER BY f1 + 1;
391 f1 gc
392 0 1
393 1 2
394 2 3
395 3 4
396 4 5
397 5 6
398 6 7
399 7 8
400 8 9
401 9 10
402 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1;
403 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
404 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL 10 100.00 NULL
405 Warnings:
406 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc` AS `gc` from `test`.`t1` order by (`test`.`t1`.`f1` + 1)
407 SELECT * FROM information_schema.OPTIMIZER_TRACE;
408 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
409 EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1 {
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` order by (`t1`.`f1` + 1)"
417 }
418 ] /* steps */
419 } /* join_preparation */
420 },
421 {
422 "join_optimization": {
423 "select#": 1,
424 "steps": [
425 {
426 "substitute_generated_columns": {
427 "resulting_ORDER_BY": "`t1`.`gc`"
428 } /* substitute_generated_columns */
429 },
430 {
431 "table_dependencies": [
432 {
433 "table": "`t1`",
434 "row_may_be_null": false,
435 "map_bit": 0,
436 "depends_on_map_bits": [
437 ] /* depends_on_map_bits */
438 }
439 ] /* table_dependencies */
440 },
441 {
442 "rows_estimation": [
443 {
444 "table": "`t1`",
445 "table_scan": {
446 "rows": 10,
447 "cost": 2500
448 } /* table_scan */
449 }
450 ] /* rows_estimation */
451 },
452 {
453 "considered_execution_plans": [
454 {
455 "plan_prefix": [
456 ] /* plan_prefix */,
457 "table": "`t1`",
458 "best_access_path": {
459 "considered_access_paths": [
460 {
461 "rows_to_scan": 10,
462 "access_type": "scan",
463 "resulting_rows": 10,
464 "cost": 2501,
465 "chosen": true,
466 "use_tmp_table": true
467 }
468 ] /* considered_access_paths */
469 } /* best_access_path */,
470 "condition_filtering_pct": 100,
471 "rows_for_plan": 10,
472 "cost_for_plan": 2501,
473 "sort_cost": 10,
474 "new_cost_for_plan": 2511,
475 "chosen": true
476 }
477 ] /* considered_execution_plans */
478 },
479 {
480 "attaching_conditions_to_tables": {
481 "original_condition": null,
482 "attached_conditions_computation": [
483 ] /* attached_conditions_computation */,
484 "attached_conditions_summary": [
485 {
486 "table": "`t1`",
487 "attached": null
488 }
489 ] /* attached_conditions_summary */
490 } /* attaching_conditions_to_tables */
491 },
492 {
493 "optimizing_distinct_group_by_order_by": {
494 "simplifying_order_by": {
495 "original_clause": "`t1`.`gc`",
496 "items": [
497 {
498 "item": "`t1`.`gc`"
499 }
500 ] /* items */,
501 "resulting_clause_is_simple": true,
502 "resulting_clause": "`t1`.`gc`"
503 } /* simplifying_order_by */
504 } /* optimizing_distinct_group_by_order_by */
505 },
506 {
507 "reconsidering_access_paths_for_index_ordering": {
508 "clause": "ORDER BY",
509 "steps": [
510 ] /* steps */,
511 "index_order_summary": {
512 "table": "`t1`",
513 "index_provides_order": true,
514 "order_direction": "asc",
515 "index": "PRIMARY",
516 "plan_changed": true,
517 "access_type": "index"
518 } /* index_order_summary */
519 } /* reconsidering_access_paths_for_index_ordering */
520 },
521 {
522 "finalizing_table_conditions": [
523 ] /* finalizing_table_conditions */
524 },
525 {
526 "refine_plan": [
527 {
528 "table": "`t1`"
529 }
530 ] /* refine_plan */
531 },
532 {
533 "considering_tmp_tables": [
534 ] /* considering_tmp_tables */
535 }
536 ] /* steps */
537 } /* join_optimization */
538 },
539 {
540 "join_explain": {
541 "select#": 1,
542 "steps": [
543 ] /* steps */
544 } /* join_explain */
545 }
546 ] /* steps */
547 } 0 0
548 EXPLAIN SELECT * FROM t1 IGNORE KEY FOR ORDER BY(PRIMARY) ORDER BY f1 + 1;
549 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
550 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 10 100.00 Using filesort
551 Warnings:
552 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)
553 # Check that GROUP BY could be optimized
554 SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
555 f1 + 1 MAX(GC)
556 1 1
557 10 10
558 2 2
559 3 3
560 4 4
561 5 5
562 6 6
563 7 7
564 8 8
565 9 9
566 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1;
567 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
568 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index PRIMARY PRIMARY 4 NULL 10 100.00 NULL
569 Warnings:
570 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)
571 SELECT * FROM information_schema.OPTIMIZER_TRACE;
572 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
573 EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1 {
574 "steps": [
575 {
576 "join_preparation": {
577 "select#": 1,
578 "steps": [
579 {
580 "expanded_query": "/* select#1 */ select (`t1`.`f1` + 1) AS `f1 + 1`,max(`t1`.`gc`) AS `MAX(GC)` from `t1` group by (`t1`.`f1` + 1)"
581 }
582 ] /* steps */
583 } /* join_preparation */
584 },
585 {
586 "join_optimization": {
587 "select#": 1,
588 "steps": [
589 {
590 "substitute_generated_columns": {
591 "resulting_GROUP_BY": "`t1`.`gc`"
592 } /* substitute_generated_columns */
593 },
594 {
595 "table_dependencies": [
596 {
597 "table": "`t1`",
598 "row_may_be_null": false,
599 "map_bit": 0,
600 "depends_on_map_bits": [
601 ] /* depends_on_map_bits */
602 }
603 ] /* table_dependencies */
604 },
605 {
606 "rows_estimation": [
607 {
608 "table": "`t1`",
609 "const_keys_added": {
610 "keys": [
611 "PRIMARY"
612 ] /* keys */,
613 "cause": "group_by"
614 } /* const_keys_added */,
615 "range_analysis": {
616 "table_scan": {
617 "rows": 10,
618 "cost": 2503.1
619 } /* table_scan */,
620 "potential_range_indexes": [
621 {
622 "index": "PRIMARY",
623 "usable": true,
624 "key_parts": [
625 "gc"
626 ] /* key_parts */
627 }
628 ] /* potential_range_indexes */,
629 "group_index_range": {
630 "potential_group_range_indexes": [
631 {
632 "index": "PRIMARY",
633 "usable": false,
634 "cause": "not_covering"
635 }
636 ] /* potential_group_range_indexes */
637 } /* group_index_range */,
638 "skip_scan_range": {
639 "chosen": false,
640 "cause": "has_group_by"
641 } /* skip_scan_range */
642 } /* range_analysis */
643 }
644 ] /* rows_estimation */
645 },
646 {
647 "considered_execution_plans": [
648 {
649 "plan_prefix": [
650 ] /* plan_prefix */,
651 "table": "`t1`",
652 "best_access_path": {
653 "considered_access_paths": [
654 {
655 "rows_to_scan": 10,
656 "access_type": "scan",
657 "resulting_rows": 10,
658 "cost": 2501,
659 "chosen": true,
660 "use_tmp_table": true
661 }
662 ] /* considered_access_paths */
663 } /* best_access_path */,
664 "condition_filtering_pct": 100,
665 "rows_for_plan": 10,
666 "cost_for_plan": 2501,
667 "sort_cost": 10,
668 "new_cost_for_plan": 2511,
669 "chosen": true
670 }
671 ] /* considered_execution_plans */
672 },
673 {
674 "attaching_conditions_to_tables": {
675 "original_condition": null,
676 "attached_conditions_computation": [
677 ] /* attached_conditions_computation */,
678 "attached_conditions_summary": [
679 {
680 "table": "`t1`",
681 "attached": null
682 }
683 ] /* attached_conditions_summary */
684 } /* attaching_conditions_to_tables */
685 },
686 {
687 "optimizing_distinct_group_by_order_by": {
688 "simplifying_group_by": {
689 "original_clause": "`t1`.`gc`",
690 "items": [
691 {
692 "item": "`t1`.`gc`"
693 }
694 ] /* items */,
695 "resulting_clause_is_simple": true,
696 "resulting_clause": "`t1`.`gc`"
697 } /* simplifying_group_by */
698 } /* optimizing_distinct_group_by_order_by */
699 },
700 {
701 "reconsidering_access_paths_for_index_ordering": {
702 "clause": "GROUP BY",
703 "steps": [
704 ] /* steps */,
705 "index_order_summary": {
706 "table": "`t1`",
707 "index_provides_order": true,
708 "order_direction": "asc",
709 "index": "PRIMARY",
710 "plan_changed": true,
711 "access_type": "index"
712 } /* index_order_summary */
713 } /* reconsidering_access_paths_for_index_ordering */
714 },
715 {
716 "finalizing_table_conditions": [
717 ] /* finalizing_table_conditions */
718 },
719 {
720 "refine_plan": [
721 {
722 "table": "`t1`"
723 }
724 ] /* refine_plan */
725 },
726 {
727 "considering_tmp_tables": [
728 ] /* considering_tmp_tables */
729 }
730 ] /* steps */
731 } /* join_optimization */
732 },
733 {
734 "join_explain": {
735 "select#": 1,
736 "steps": [
737 ] /* steps */
738 } /* join_explain */
739 }
740 ] /* steps */
741 } 0 0
742 EXPLAIN SELECT f1 + 1, MAX(GC)
743 FROM t1 IGNORE KEY FOR GROUP BY(PRIMARY) GROUP BY f1 + 1;
744 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
745 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 10 100.00 Using temporary
746 Warnings:
747 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)
748 # Shouldn't use index
749 SELECT * FROM t1 WHERE f1 + 1 > 7.0;
750 f1 gc
751 7 8
752 8 9
753 9 10
754 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0;
755 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
756 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 10 100.00 Using where
757 Warnings:
758 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)
759 DROP TABLE t1;
760 # Pick index with proper type
761 CREATE TABLE t1 (f1 int,
762 gc_int int AS (f1 + 1) STORED,
763 gc_date DATE AS (f1 + 1) STORED,
764 KEY gc_int_idx(gc_int),
765 KEY gc_date_idx(gc_date));
766 INSERT INTO t1(f1) VALUES
767 (030303),(040404),
768 (050505),(060606),
769 (010101),(020202),
770 (030303),(040404),
771 (050505),(060606),
772 (010101),(020202),
773 (090909),(101010),
774 (010101),(020202),
775 (070707),(080808);
776 ANALYZE TABLE t1;
777 Table Op Msg_type Msg_text
778 test.t1 analyze status OK
779 SELECT * FROM t1 WHERE f1 + 1 > 070707;
780 f1 gc_int gc_date
781 101010 101011 2010-10-11
782 70707 70708 2007-07-08
783 80808 80809 2008-08-09
784 90909 90910 2009-09-10
785 # INT column & index should be picked
786 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707;
787 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
788 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range gc_int_idx gc_int_idx 5 NULL 3 100.00 Using pushed condition (`test`.`t1`.`gc_int` > 70707); Using MRR
789 Warnings:
790 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)
791 SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
792 f1 gc_int gc_date
793 101010 101011 2010-10-11
794 70707 70708 2007-07-08
795 80808 80809 2008-08-09
796 90909 90910 2009-09-10
797 # DATE column & index should be picked
798 EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
799 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
800 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range gc_date_idx gc_date_idx 4 NULL 3 100.00 Using pushed condition (`test`.`t1`.`gc_date` > <cache>(cast(70707 as date))); Using MRR
801 Warnings:
802 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)))
803 DROP TABLE t1;
804 #
805 # BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST
806 #
807 CREATE TABLE t1 (
808 pk int primary key auto_increment,
809 col_int_key INTEGER ,
810 col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED,
811 KEY col_int_gc_key(col_int_gc_key)
812 );
813 INSERT INTO t1 ( col_int_key) VALUES (7);
814 ANALYZE TABLE t1;
815 Table Op Msg_type Msg_text
816 test.t1 analyze status OK
817 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
818 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
819 ORDER BY field1, field2;
820 field1 field2
821 8 7
822 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
823 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
824 ORDER BY field1, field2;
825 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
826 1 SIMPLE table1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using temporary; Using filesort
827 1 SIMPLE table2 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 test.table1.pk 1 100.00 Child of 'table1' in pushed join@1
828 Warnings:
829 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`
830 SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
831 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
832 GROUP BY field1, field2;
833 field1 field2
834 8 7
835 EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2
836 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk))
837 GROUP BY field1, field2;
838 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
839 1 SIMPLE table1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using temporary
840 1 SIMPLE table2 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 test.table1.pk 1 100.00 Child of 'table1' in pushed join@1
841 Warnings:
842 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`
843 DROP TABLE t1;
844 #
845 # Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS
846 # WITH LOGICAL OPERATORS
847 #
848 CREATE TABLE t (a INT, b INT,
849 gc_and INT GENERATED ALWAYS AS (a AND b) STORED,
850 gc_or INT GENERATED ALWAYS AS (a OR b) STORED,
851 gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED,
852 gc_not INT GENERATED ALWAYS AS (NOT a) STORED,
853 gc_case INT GENERATED ALWAYS AS
854 (CASE WHEN (a AND b) THEN a ELSE b END) STORED,
855 INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not),
856 INDEX(gc_case));
857 INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1);
858 ANALYZE TABLE t;
859 Table Op Msg_type Msg_text
860 test.t analyze status OK
861 EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1;
862 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
863 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc_and gc_and 5 const 3 100.00 NULL
864 Warnings:
865 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_and` = 1)
866 SELECT a, b FROM t WHERE (a AND b) = 1;
867 a b
868 1 1
869 EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b);
870 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
871 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc_and gc_and 5 const 3 100.00 NULL
872 Warnings:
873 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (1 = `test`.`t`.`gc_and`)
874 SELECT a, b FROM t WHERE 1 = (a AND b);
875 a b
876 1 1
877 EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
878 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
879 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 range gc_and gc_and 5 NULL 9 100.00 Using pushed condition (`test`.`t`.`gc_and` in (1,2,3)); Using MRR
880 Warnings:
881 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))
882 SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3);
883 a b
884 1 1
885 EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1;
886 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
887 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc_or gc_or 5 const 3 100.00 NULL
888 Warnings:
889 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_or` = 1)
890 SELECT a, b FROM t WHERE (a OR b) = 1;
891 a b
892 0 1
893 1 0
894 1 1
895 EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
896 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
897 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 range gc_or gc_or 5 NULL 3 100.00 Using pushed condition (`test`.`t`.`gc_or` between 1 and 10); Using MRR
898 Warnings:
899 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)
900 SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10;
901 a b
902 0 1
903 1 0
904 1 1
905 EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1;
906 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
907 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc_xor gc_xor 5 const 3 100.00 NULL
908 Warnings:
909 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`gc_xor` = 1)
910 SELECT a, b FROM t WHERE (a XOR b) = 1;
911 a b
912 0 1
913 1 0
914 EXPLAIN SELECT a FROM t WHERE (NOT a) = 1;
915 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
916 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc_not gc_not 5 const 3 100.00 NULL
917 Warnings:
918 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (`test`.`t`.`gc_not` = 1)
919 SELECT a FROM t WHERE (NOT a) = 1;
920 a
921 0
922 0
923 EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
924 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
925 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc_case gc_case 5 const 3 100.00 NULL
926 Warnings:
927 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (`test`.`t`.`gc_case` = 1)
928 SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1;
929 a
930 0
931 1
932 EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a);
933 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
934 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 Using where
935 Warnings:
936 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`)))
937 SELECT a, b FROM t WHERE 1 = (b AND a);
938 a b
939 1 1
940 EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a);
941 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
942 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 Using where
943 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`)))
944 Warnings:
945 SELECT a, b FROM t WHERE 1 = (b OR a);
946 a b
947 0 1
948 1 0
949 1 1
950 DROP TABLE t;
951 #
952 # Bug#21854241: QUERY USING JSON_EXTRACT() RETURNS WRONG RESULT
953 # AFTER ADDING VIRTUAL INDEX
954 #
955 CREATE TABLE employees (
956 data JSON,
957 name1 VARCHAR(30) AS (JSON_EXTRACT(data, "$.name")) STORED,
958 name2 VARCHAR(30) AS (JSON_UNQUOTE(JSON_EXTRACT(data, "$.name"))) STORED
959 );
960 Warnings:
961 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
962 INSERT INTO employees (data) VALUES('{"id": 1, "name": "Jane"}');
963 INSERT INTO employees (data) VALUES('{"id": 2, "name": "Joe"}');
964 ANALYZE TABLE employees;
965 Table Op Msg_type Msg_text
966 test.employees analyze status OK
967 EXPLAIN SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
968 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
969 1 SIMPLE employees p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using where
970 Warnings:
971 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')
972 SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
973 data name1 name2
974 {"id": 1, "name": "Jane"} "Jane" Jane
975 ALTER TABLE employees ADD INDEX name_idx1(name1);
976 ANALYZE TABLE employees;
977 Table Op Msg_type Msg_text
978 test.employees analyze status OK
979 EXPLAIN SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
980 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
981 1 SIMPLE employees p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using where
982 Warnings:
983 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')
984 SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
985 data name1 name2
986 {"id": 1, "name": "Jane"} "Jane" Jane
987 ALTER TABLE employees ADD INDEX name_idx2(name2);
988 ANALYZE TABLE employees;
989 Table Op Msg_type Msg_text
990 test.employees analyze status OK
991 EXPLAIN SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
992 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
993 1 SIMPLE employees p0,p1,p2,p3,p4,p5,p6,p7 ref name_idx2 name_idx2 123 const 2 100.00 NULL
994 Warnings:
995 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')
996 SELECT * FROM employees WHERE JSON_EXTRACT(data, '$.name') = 'Jane';
997 data name1 name2
998 {"id": 1, "name": "Jane"} "Jane" Jane
999 DROP TABLE employees;
1000 #
1001 # Bug#22077611 UPDATE .. WHERE JSON_EXTRACT(..) = '..' NOT USING
1002 # VIRTUAL COL INDEX
1003 #
1004 CREATE TABLE t(a INT, b INT, gc INT GENERATED ALWAYS AS (a+1) STORED, KEY(gc));
1005 INSERT INTO t(a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5);
1006 ANALYZE TABLE t;
1007 Table Op Msg_type Msg_text
1008 test.t analyze status OK
1009 EXPLAIN UPDATE t SET b = 10 WHERE (a+1) = 3;
1010 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1011 1 UPDATE t p0,p1,p2,p3,p4,p5,p6,p7 range gc gc 5 const 3 100.00 Using where; Using pushed condition (`test`.`t`.`gc` = 3); Using MRR
1012 Warnings:
1013 Note 1003 update `test`.`t` set `test`.`t`.`b` = 10 where (`test`.`t`.`gc` = 3)
1014 UPDATE t SET b = 10 WHERE (a+1) = 3;
1015 SELECT * FROM t ORDER BY a, b;
1016 a b gc
1017 1 NULL 2
1018 1 NULL 2
1019 2 10 3
1020 2 10 3
1021 3 NULL 4
1022 3 NULL 4
1023 4 NULL 5
1024 4 NULL 5
1025 5 NULL 6
1026 5 NULL 6
1027 ANALYZE TABLE t;
1028 Table Op Msg_type Msg_text
1029 test.t analyze status OK
1030 EXPLAIN UPDATE t SET b = 9 ORDER BY (a+1) LIMIT 1;
1031 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1032 1 UPDATE t p0,p1,p2,p3,p4,p5,p6,p7 index NULL gc 5 NULL 1 100.00 Using temporary
1033 Warnings:
1034 Note 1003 update `test`.`t` set `test`.`t`.`b` = 9 order by `test`.`t`.`gc` limit 1
1035 UPDATE t SET b = 9 ORDER BY (a+1) LIMIT 1;
1036 SELECT * FROM t ORDER BY a, b;
1037 a b gc
1038 1 NULL 2
1039 1 9 2
1040 2 10 3
1041 2 10 3
1042 3 NULL 4
1043 3 NULL 4
1044 4 NULL 5
1045 4 NULL 5
1046 5 NULL 6
1047 5 NULL 6
1048 ANALYZE TABLE t;
1049 Table Op Msg_type Msg_text
1050 test.t analyze status OK
1051 EXPLAIN DELETE FROM t WHERE (a+1) = 2;
1052 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1053 1 DELETE t p0,p1,p2,p3,p4,p5,p6,p7 range gc gc 5 const 3 100.00 Using where; Using pushed condition (`test`.`t`.`gc` = 2); Using MRR
1054 Warnings:
1055 Note 1003 delete from `test`.`t` where (`test`.`t`.`gc` = 2)
1056 DELETE FROM t WHERE (a+1) = 2;
1057 SELECT * FROM t ORDER BY a, b;
1058 a b gc
1059 2 10 3
1060 2 10 3
1061 3 NULL 4
1062 3 NULL 4
1063 4 NULL 5
1064 4 NULL 5
1065 5 NULL 6
1066 5 NULL 6
1067 ANALYZE TABLE t;
1068 Table Op Msg_type Msg_text
1069 test.t analyze status OK
1070 EXPLAIN DELETE FROM t ORDER BY (a+1) LIMIT 1;
1071 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1072 1 DELETE t p0,p1,p2,p3,p4,p5,p6,p7 index NULL gc 5 NULL 1 100.00 NULL
1073 Warnings:
1074 Note 1003 delete from `test`.`t` order by `test`.`t`.`gc` limit 1
1075 DELETE FROM t ORDER BY (a+1) LIMIT 1;
1076 SELECT * FROM t ORDER BY a, b;
1077 a b gc
1078 2 10 3
1079 3 NULL 4
1080 3 NULL 4
1081 4 NULL 5
1082 4 NULL 5
1083 5 NULL 6
1084 5 NULL 6
1085 DROP TABLE t;
1086 #
1087 # Bug#22810883: ASSERTION FAILED:
1088 # !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))
1089 #
1090 CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED,
1091 a2 INTEGER, KEY (a1));
1092 INSERT INTO t1 VALUES ();
1093 CREATE TABLE t2 (b INTEGER);
1094 INSERT INTO t2 VALUES (1);
1095 ANALYZE TABLE t1, t2;
1096 Table Op Msg_type Msg_text
1097 test.t1 analyze status OK
1098 test.t2 analyze status OK
1099 # Used to choose the index on a1 and get wrong results.
1100 EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0;
1101 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1102 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Using where
1103 Warnings:
1104 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)
1105 SELECT * FROM t1 WHERE (a2 AND a2) = 0;
1106 a1 a2
1107 # Used to get assertion or wrong results.
1108 EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
1109 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1110 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 NULL
1111 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 50.00 Using where; Using pushed condition (0 <> `test`.`t2`.`b`); Using join buffer (hash join)
1112 Warnings:
1113 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
1114 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`))
1115 SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1;
1116 a1 a2 b
1117 0 NULL 1
1118 DROP TABLE t1, t2;
1119 #
1120 # Bug#27403367: GENERATED COLUMN EXPRESSIONS IGNORED WITH PREFIX INDEX
1121 #
1122 CREATE TABLE t (vc VARCHAR(100),
1123 gc VARCHAR(100) GENERATED ALWAYS AS (REVERSE(vc)) STORED,
1124 KEY (gc(3)));
1125 INSERT INTO t(vc) VALUES ('a'), ('abc'), ('abcabc'), ('abcdef');
1126 ANALYZE TABLE t;
1127 Table Op Msg_type Msg_text
1128 test.t analyze status OK
1129 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) = 'cba';
1130 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1131 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc gc 403 const 3 100.00 NULL
1132 Warnings:
1133 Note 1003 /* select#1 */ select `test`.`t`.`vc` AS `vc`,`test`.`t`.`gc` AS `gc` from `test`.`t` where (`test`.`t`.`gc` = 'cba')
1134 SELECT * FROM t WHERE REVERSE(vc) = 'cba';
1135 vc gc
1136 abc cba
1137 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) = 'cbacba';
1138 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1139 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ref gc gc 403 const 3 100.00 NULL
1140 Warnings:
1141 Note 1003 /* select#1 */ select `test`.`t`.`vc` AS `vc`,`test`.`t`.`gc` AS `gc` from `test`.`t` where (`test`.`t`.`gc` = 'cbacba')
1142 SELECT * FROM t WHERE REVERSE(vc) = 'cbacba';
1143 vc gc
1144 abcabc cbacba
1145 EXPLAIN SELECT * FROM t WHERE REVERSE(vc) BETWEEN 'c' AND 'e';
1146 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1147 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 range gc gc 403 NULL 3 100.00 Using pushed condition (`test`.`t`.`gc` between 'c' and 'e'); Using MRR
1148 Warnings:
1149 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')
1150 SELECT * FROM t WHERE REVERSE(vc) BETWEEN 'c' AND 'e';
1151 vc gc
1152 abc cba
1153 abcabc cbacba
1154 DROP TABLE t;
1155 #
1156 DROP VIEW IF EXISTS v1,v2;
1157 DROP TABLE IF EXISTS t1,t2,t3;
1158 DROP PROCEDURE IF EXISTS p1;
1159 DROP FUNCTION IF EXISTS f1;
1160 DROP TRIGGER IF EXISTS trg1;
1161 DROP TRIGGER IF EXISTS trg2;
1162 set sql_warnings = 0;