1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
#
# WL#6526: FORCE INDEX to avoid index dives when possible
#
# Non-traditional formats differ in hypergraph. This file tests only EXPLAIN.
--source include/not_hypergraph.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/have_innodb_16k.inc
# All the queries use EXPLAIN FOR CONNECTION. There will be no change in
# EXPLAIN output.
CREATE TABLE t1 (
pk_col1 INT NOT NULL,
a1 CHAR(64),
a2 CHAR(64),
PRIMARY KEY(pk_col1),
KEY t1_a1_idx (a1),
KEY t1_a1_a2_idx (a1, a2)
) ENGINE=INNODB;
INSERT INTO t1 (pk_col1, a1, a2) VALUES (1,'a','b'), (2,'a','b'), (3,'d','c'),
(4,'b','c'), (5,'c','d'), (6,'a','b');
CREATE TABLE t2 (
pk_col1 INT NOT NULL,
pk_col2 INT NOT NULL,
a1 CHAR(64),
a2 CHAR(64),
PRIMARY KEY(pk_col1, pk_col2),
KEY t2_a1_idx (a1),
KEY t2_a1_a2_idx (a1, a2)
) ENGINE=INNODB;
INSERT INTO t2 (pk_col1, pk_col2, a1, a2) VALUES (1,1,'a','b'),(1,2,'a','b'),
(1,3,'d','c'),(1,4,'b','c'),
(2,1,'c','d'),(3,1,'a','b');
ANALYZE TABLE t1;
ANALYZE TABLE t2;
#range scan
let query1=SELECT a1 FROM t1 WHERE a1 > 'b';
let query2=SELECT a1 FROM t1 FORCE INDEX(t1_a1_a2_idx) WHERE a1 > 'b';
#ref access
let query3=SELECT a1 FROM t1 WHERE a1 = 'a';
let query4=SELECT a1 FROM t1 FORCE INDEX(t1_a1_a2_idx) WHERE a1 = 'a';
#index scan (no effect with this WL.)
let query5=SELECT a1 FROM t1;
let query6=SELECT a1 FROM t1 FORCE INDEX(t1_a1_a2_idx);
# This query chose index scan before this WL, but will change to range.
# EXPLAIN FOR CONNECTION will reflect this switch. This happens because we now
# set rows = 1 in handler::multi_range_read_info_const.
let query7=SELECT a1 FROM t1 FORCE INDEX(t1_a1_a2_idx) WHERE a1 >= 'a';
#Cases when FORCE INDEX should be ignored (because index is not applicable)
let query8=SELECT a1 FROM t1 FORCE INDEX(t1_a1_idx) WHERE a2 > 'a';
#shouldn't skip index dives because it is a JOIN.
let query9=
SELECT t2.a1, t2.a2
FROM t1 FORCE INDEX(t1_a1_a2_idx) JOIN t2 ON (t1.pk_col1 = t2.pk_col2)
WHERE t1.a1 > 'a';
let $QID= `SELECT CONNECTION_ID()`;
let $point= before_join_exec;
let $err=0;
connect (ce, localhost, root,, test);
connect (cq, localhost, root,, test);
connection cq;
let $query= $query1;
--source include/skip_records_in_range.inc
let $query= $query2;
--source include/skip_records_in_range.inc
let $query= $query3;
--source include/skip_records_in_range.inc
let $query= $query4;
--source include/skip_records_in_range.inc
let $query= $query5;
--source include/skip_records_in_range.inc
let $query= $query6;
--source include/skip_records_in_range.inc
let $query= $query7;
--source include/skip_records_in_range.inc
let $query= $query8;
--source include/skip_records_in_range.inc
let $query= $query9;
--source include/skip_records_in_range.inc
DROP TABLE t1, t2;
--echo #
--echo # Bug #26627136: WL#6526: ASSERTION
--echo # `!ALL_SELECTS_LIST->NEXT_SELECT_IN_LIST()' FAILED.
--echo #
CREATE TABLE t1 (v3 INT, KEY(v3));
CREATE view v1 AS SELECT v3 FROM t1 FORCE KEY (v3) GROUP BY v3;
let query1= select * from v1;
eval EXPLAIN $query1;
eval $query1;
DROP VIEW v1;
DROP TABLE t1;
|
