Debian Codegraph @

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

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