1
drop table if exists t0, t1, t2, t10, t11, t12;
2
create table t0 (a int);
3
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
4
create table t1(a int, b int);
5
insert into t1 values (0,0),(1,1),(2,2);
6
create table t2 as select * from t1;
7
create table t11(a int, b int);
8
create table t10 (pk int, a int, primary key(pk));
9
insert into t10 select a,a from t0;
10
create table t12 like t10;
11
insert into t12 select * from t10;
12
Flattened because of dependency, t10=func(t1)
13
explain select * from t1 where a in (select pk from t10);
14
id select_type table type possible_keys key key_len ref rows Extra
15
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
16
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
17
select * from t1 where a in (select pk from t10);
22
A confluent case of dependency
23
explain select * from t1 where a in (select a from t10 where pk=12);
24
id select_type table type possible_keys key key_len ref rows Extra
25
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
26
1 PRIMARY t10 const PRIMARY PRIMARY 4 const 1 Using where
27
select * from t1 where a in (select a from t10 where pk=12);
30
explain select * from t1 where a in (select a from t11);
31
id select_type table type possible_keys key key_len ref rows Extra
32
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
33
select * from t1 where a in (select a from t11);
35
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
36
id select_type table type possible_keys key key_len ref rows Extra
37
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
38
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
39
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
40
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
45
flattening a nested subquery
46
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
47
id select_type table type possible_keys key key_len ref rows Extra
48
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
49
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
50
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
51
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
56
flattening subquery w/ several tables
57
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
58
id select_type table type possible_keys key key_len ref rows filtered Extra
59
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
60
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
61
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
63
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10` join `test`.`t12`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
64
subqueries within outer joins go into ON expr.
66
select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.A in (select pk from t10));
67
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
68
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
69
1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00
70
1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00
71
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.B.A 1 100.00 Using index
73
Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`B`.`A` = `test`.`t10`.`pk`))) where 1
74
t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
76
select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
77
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
78
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
79
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
80
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t2.A 1 100.00 Using index
82
Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2`) on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`t2`.`A` = `test`.`t10`.`pk`))) where 1
83
we shouldn't flatten if we're going to get a join of > MAX_TABLES.
85
t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
86
t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19,
87
t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29,
88
t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39,
89
t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49
93
t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09,
94
t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19
96
id select_type table type possible_keys key key_len ref rows Extra
97
1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where
98
1 PRIMARY s01 ALL NULL NULL NULL NULL 3 Using join buffer
99
1 PRIMARY s02 ALL NULL NULL NULL NULL 3 Using join buffer
100
1 PRIMARY s03 ALL NULL NULL NULL NULL 3 Using join buffer
101
1 PRIMARY s04 ALL NULL NULL NULL NULL 3 Using join buffer
102
1 PRIMARY s05 ALL NULL NULL NULL NULL 3 Using join buffer
103
1 PRIMARY s06 ALL NULL NULL NULL NULL 3 Using join buffer
104
1 PRIMARY s07 ALL NULL NULL NULL NULL 3 Using join buffer
105
1 PRIMARY s08 ALL NULL NULL NULL NULL 3 Using join buffer
106
1 PRIMARY s09 ALL NULL NULL NULL NULL 3 Using join buffer
107
1 PRIMARY s10 ALL NULL NULL NULL NULL 3 Using join buffer
108
1 PRIMARY s11 ALL NULL NULL NULL NULL 3 Using join buffer
109
1 PRIMARY s12 ALL NULL NULL NULL NULL 3 Using join buffer
110
1 PRIMARY s13 ALL NULL NULL NULL NULL 3 Using join buffer
111
1 PRIMARY s14 ALL NULL NULL NULL NULL 3 Using join buffer
112
1 PRIMARY s15 ALL NULL NULL NULL NULL 3 Using join buffer
113
1 PRIMARY s16 ALL NULL NULL NULL NULL 3 Using join buffer
114
1 PRIMARY s17 ALL NULL NULL NULL NULL 3 Using join buffer
115
1 PRIMARY s18 ALL NULL NULL NULL NULL 3 Using join buffer
116
1 PRIMARY s19 ALL NULL NULL NULL NULL 3 Using join buffer
117
1 PRIMARY s20 ALL NULL NULL NULL NULL 3 Using join buffer
118
1 PRIMARY s21 ALL NULL NULL NULL NULL 3 Using join buffer
119
1 PRIMARY s22 ALL NULL NULL NULL NULL 3 Using join buffer
120
1 PRIMARY s23 ALL NULL NULL NULL NULL 3 Using join buffer
121
1 PRIMARY s24 ALL NULL NULL NULL NULL 3 Using join buffer
122
1 PRIMARY s25 ALL NULL NULL NULL NULL 3 Using join buffer
123
1 PRIMARY s26 ALL NULL NULL NULL NULL 3 Using join buffer
124
1 PRIMARY s27 ALL NULL NULL NULL NULL 3 Using join buffer
125
1 PRIMARY s28 ALL NULL NULL NULL NULL 3 Using join buffer
126
1 PRIMARY s29 ALL NULL NULL NULL NULL 3 Using join buffer
127
1 PRIMARY s30 ALL NULL NULL NULL NULL 3 Using join buffer
128
1 PRIMARY s31 ALL NULL NULL NULL NULL 3 Using join buffer
129
1 PRIMARY s32 ALL NULL NULL NULL NULL 3 Using join buffer
130
1 PRIMARY s33 ALL NULL NULL NULL NULL 3 Using join buffer
131
1 PRIMARY s34 ALL NULL NULL NULL NULL 3 Using join buffer
132
1 PRIMARY s35 ALL NULL NULL NULL NULL 3 Using join buffer
133
1 PRIMARY s36 ALL NULL NULL NULL NULL 3 Using join buffer
134
1 PRIMARY s37 ALL NULL NULL NULL NULL 3 Using join buffer
135
1 PRIMARY s38 ALL NULL NULL NULL NULL 3 Using join buffer
136
1 PRIMARY s39 ALL NULL NULL NULL NULL 3 Using join buffer
137
1 PRIMARY s40 ALL NULL NULL NULL NULL 3 Using join buffer
138
1 PRIMARY s41 ALL NULL NULL NULL NULL 3 Using join buffer
139
1 PRIMARY s42 ALL NULL NULL NULL NULL 3 Using join buffer
140
1 PRIMARY s43 ALL NULL NULL NULL NULL 3 Using join buffer
141
1 PRIMARY s44 ALL NULL NULL NULL NULL 3 Using join buffer
142
1 PRIMARY s45 ALL NULL NULL NULL NULL 3 Using join buffer
143
1 PRIMARY s46 ALL NULL NULL NULL NULL 3 Using join buffer
144
1 PRIMARY s47 ALL NULL NULL NULL NULL 3 Using join buffer
145
1 PRIMARY s48 ALL NULL NULL NULL NULL 3 Using join buffer
146
1 PRIMARY s49 ALL NULL NULL NULL NULL 3 Using join buffer
147
2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where
148
2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 Using join buffer
149
2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 Using join buffer
150
2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 Using join buffer
151
2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 Using join buffer
152
2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 Using join buffer
153
2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 Using join buffer
154
2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 Using join buffer
155
2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 Using join buffer
156
2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 Using join buffer
157
2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 Using join buffer
158
2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 Using join buffer
159
2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 Using join buffer
160
2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 Using join buffer
161
2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 Using join buffer
162
2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 Using join buffer
163
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer
164
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer
165
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer
166
2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
168
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
176
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
188
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
189
explain extended select * from t1 where a in (select pk from t10 where pk<3);
190
id select_type table type possible_keys key key_len ref rows filtered Extra
191
1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where
192
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
194
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
196
drop table t10, t11, t12;
added
removed
mysql-test/r/subselect_sj.result
