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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
SET debug= '+d,test_bka_unique';
set optimizer_switch='batched_key_access=on,mrr_cost_based=off';
set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int,
b int
) engine=innodb;
insert into t1 values (1,1),(1,1),(2,2);
create table t2 (
a int,
b int,
key(b)
) engine=innodb;
insert into t2 select a, a/2 from t0;
select * from t1;
a b
1 1
1 1
2 2
select * from t2;
a b
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 SIMPLE t2 ref b b 5 test.t1.a 1 End temporary; Using join buffer (Batched Key Access (unique))
select * from t2 where b in (select a from t1);
a b
1 1
2 1
3 2
4 2
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb;
insert into t3 select a,a, a,a,a from t0;
explain select * from t3 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 SIMPLE t3 ref b b 5 test.t1.a 1 End temporary; Using join buffer (Batched Key Access (unique))
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
2 2 2 2 2
set @save_max_heap_table_size= @@max_heap_table_size;
set max_heap_table_size=16384;
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size= 8192;
drop table t3;
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200),
primary key(pk1, pk2)
) engine=innodb;
insert into t3 select
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL NULL NULL NULL NULL # Using where; Start temporary
1 SIMPLE t3 ref b b 5 test.t0.a # End temporary; Using join buffer (Batched Key Access (unique))
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
a b pk1 pk2
0 0 0 0
1 1 1 1
10 10 10 10
11 11 11 11
12 12 12 12
13 13 13 13
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
set join_buffer_size= @save_join_buffer_size;
set max_heap_table_size= @save_max_heap_table_size;
explain select * from t1 where a in (select b from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref b b 5 test.t1.a 5 Using index; FirstMatch(t1)
select * from t1;
a b
1 1
1 1
2 2
select * from t1 where a in (select b from t2);
a b
1 1
1 1
2 2
drop table t0, t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb;
insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1;
insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1;
alter table t2 add filler1 int;
insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size=1;
select * from t2 where filler1 in ( select a from t1);
a b c filler1
set join_buffer_size=default;
drop table t1, t2;
BUG#42740: crash in optimize_semijoin_nests
create table t1 (c6 timestamp,key (c6)) engine=innodb;
create table t2 (c2 double) engine=innodb;
explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null) ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
drop table t1, t2;
#
# BUG#42742: crash in setup_sj_materialization, Copy_field::set
#
create table t3 ( c1 year) engine=innodb;
insert into t3 values (2135),(2142);
create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
# The following must not crash, EXPLAIN should show one SJ strategy, not a mix:
explain select 1 from t2 where
c2 in (select 1 from t3, t2) and
c1 in (select convert(c6,char(1)) from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 NULL
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2)
drop table t2, t3;
#
# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
#
CREATE TABLE t1 (
i INT
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (2),(4);
CREATE TABLE t2 (
i INT,
vc VARCHAR(1)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (8,NULL);
SELECT i
FROM t1
WHERE i IN (SELECT innr.i
FROM t2 LEFT JOIN t2 innr ON innr.vc)
AND i = 2;
i
DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
|
