← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/r/subselect_mat.result

  • Committer: Brian Aker
  • Date: 2008-07-08 16:17:31 UTC
  • Revision ID: brian@tangent.org-20080708161731-io36j7igglok79py
DATE cleanup.

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/r/subselect_mat.result
1
1
drop table if exists t1, t2, t3, t1i, t2i, t3i;
2
2
create table t1 (a1 char(8), a2 char(8));
3
 
create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
 
3
create table t2 (b1 char(8), b2 char(8));
4
4
create table t3 (c1 char(8), c2 char(8));
5
5
insert into t1 values ('1 - 00', '2 - 00');
6
6
insert into t1 values ('1 - 01', '2 - 01');
14
14
insert into t3 values ('1 - 02', '2 - 02');
15
15
insert into t3 values ('1 - 03', '2 - 03');
16
16
insert into t3 values ('1 - 04', '2 - 04');
17
 
create temporary table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;
 
17
create table t1i (a1 char(8), a2 char(8));
18
18
create table t2i (b1 char(8), b2 char(8));
19
 
create temporary table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;
 
19
create table t3i (c1 char(8), c2 char(8));
20
20
create index it1i1 on t1i (a1);
21
21
create index it1i2 on t1i (a2);
22
22
create index it1i3 on t1i (a1, a2);
29
29
insert into t1i select * from t1;
30
30
insert into t2i select * from t2;
31
31
insert into t3i select * from t3;
32
 
/********************************************************************
 
32
set @@optimizer_switch=no_semijoin;
 
33
/******************************************************************************
33
34
* Simple tests.
34
 
********************************************************************/
 
35
******************************************************************************/
35
36
# non-indexed nullable fields
36
37
explain extended
37
38
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
38
39
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
39
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
40
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
40
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
41
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
41
42
Warnings:
42
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
 
43
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>("test"."t1"."a1","test"."t1"."a1" in ( <materialize> (select "test"."t2"."b1" AS "b1" from "test"."t2" where ("test"."t2"."b1" > '0') ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
43
44
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
44
45
a1      a2
45
46
1 - 01  2 - 01
47
48
explain extended
48
49
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
49
50
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
50
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
51
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using temporary; Using filesort
 
51
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
52
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using temporary; Using filesort
52
53
Warnings:
53
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
 
54
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>("test"."t1"."a1","test"."t1"."a1" in ( <materialize> (select "test"."t2"."b1" AS "b1" from "test"."t2" where ("test"."t2"."b1" > '0') group by "test"."t2"."b1" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
54
55
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
55
56
a1      a2
56
57
1 - 01  2 - 01
58
59
explain extended
59
60
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
60
61
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
61
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
62
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using temporary; Using filesort
 
62
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
63
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using temporary; Using filesort
63
64
Warnings:
64
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
 
65
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ("test"."t2"."b1" > '0') group by "test"."t2"."b1","test"."t2"."b2" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
65
66
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
66
67
a1      a2
67
68
1 - 01  2 - 01
69
70
explain extended
70
71
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
71
72
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
72
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
73
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using temporary; Using filesort
74
 
Warnings:
75
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
 
73
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
74
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using temporary; Using filesort
 
75
Warnings:
 
76
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t2"."b1" AS "b1",min("test"."t2"."b2") AS "min(b2)" from "test"."t2" where ("test"."t2"."b1" > '0') group by "test"."t2"."b1" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
76
77
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
77
 
a1      a2
78
 
1 - 01  2 - 01
79
 
1 - 02  2 - 02
80
 
explain extended
81
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
82
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
83
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
84
 
2       SUBQUERY        t2i     index   it2i1,it2i3     it2i1   35      NULL    #       40.00   Using where; Using index
85
 
Warnings:
86
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))
87
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
88
 
a1      a2
89
 
1 - 01  2 - 01
90
 
1 - 02  2 - 02
91
 
explain extended
92
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
93
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
94
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
95
 
2       SUBQUERY        t2i     range   it2i1,it2i3     it2i1   35      NULL    #       100.00  Using where; Using index for group-by
96
 
Warnings:
97
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))
98
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
99
 
a1      a2
100
 
1 - 01  2 - 01
101
 
1 - 02  2 - 02
102
 
explain extended
103
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
104
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
105
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
106
 
2       SUBQUERY        t2i     index   it2i1,it2i3     it2i3   70      NULL    #       40.00   Using where; Using index
107
 
Warnings:
108
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))
109
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
110
 
a1      a2
111
 
1 - 01  2 - 01
112
 
1 - 02  2 - 02
113
 
explain extended
114
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
115
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
116
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
117
 
2       SUBQUERY        t2i     range   it2i1,it2i3     it2i3   70      NULL    #       100.00  Using where; Using index for group-by
118
 
Warnings:
119
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))
120
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
121
 
a1      a2
122
 
1 - 01  2 - 01
123
 
1 - 02  2 - 02
124
 
explain extended
125
 
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
126
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
127
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
128
 
2       SUBQUERY        t2i     range   it2i1,it2i3     it2i3   70      NULL    #       100.00  Using where; Using index for group-by
129
 
Warnings:
130
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))
131
 
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
132
 
a1      a2
133
 
1 - 01  2 - 01
134
 
1 - 02  2 - 02
135
 
explain extended
136
 
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
137
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
138
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
139
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
140
 
Warnings:
141
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
142
 
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
143
 
a1      a2
144
 
1 - 01  2 - 01
145
 
1 - 02  2 - 02
146
 
explain extended
147
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
148
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
149
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
150
 
2       SUBQUERY        t2i     index   NULL    it2i3   70      NULL    #       100.00  Using index
151
 
Warnings:
152
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))
153
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
154
 
a1      a2
155
 
1 - 01  2 - 01
156
 
1 - 02  2 - 02
157
 
explain extended
158
 
select * from t1
159
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
160
 
(a1, a2) in (select c1, c2 from t3
161
 
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
162
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
163
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
164
 
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
165
 
4       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
166
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
167
 
Warnings:
168
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))))
169
 
select * from t1
170
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
171
 
(a1, a2) in (select c1, c2 from t3
172
 
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
173
 
a1      a2
174
 
1 - 01  2 - 01
175
 
1 - 02  2 - 02
176
 
explain extended
177
 
select * from t1i
178
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
179
 
(a1, a2) in (select c1, c2 from t3i
180
 
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
181
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
182
 
1       PRIMARY t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
183
 
3       SUBQUERY        t3i     index   NULL    it3i3   70      NULL    #       100.00  Using where; Using index
184
 
4       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
185
 
2       SUBQUERY        t2i     index   it2i1,it2i3     it2i3   70      NULL    #       40.00   Using where; Using index
186
 
Warnings:
187
 
Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where <in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key))))
188
 
select * from t1i
189
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
190
 
(a1, a2) in (select c1, c2 from t3i
191
 
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
192
 
a1      a2
193
 
1 - 01  2 - 01
194
 
1 - 02  2 - 02
195
 
explain extended
196
 
select * from t1
197
 
where (a1, a2) in (select b1, b2 from t2
198
 
where b2 in (select c2 from t3 where c2 LIKE '%02') or
199
 
b2 in (select c2 from t3 where c2 LIKE '%03')) and
200
 
(a1, a2) in (select c1, c2 from t3
201
 
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
202
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
203
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
204
 
5       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
205
 
6       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
206
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
207
 
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
208
 
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
209
 
Warnings:
210
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key)))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))))
211
 
select * from t1
212
 
where (a1, a2) in (select b1, b2 from t2
213
 
where b2 in (select c2 from t3 where c2 LIKE '%02') or
214
 
b2 in (select c2 from t3 where c2 LIKE '%03')) and
215
 
(a1, a2) in (select c1, c2 from t3
216
 
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
217
 
a1      a2
218
 
1 - 02  2 - 02
219
 
explain extended
220
 
select * from t1
221
 
where (a1, a2) in (select b1, b2 from t2
 
78
a1      a2
 
79
1 - 01  2 - 01
 
80
1 - 02  2 - 02
 
81
explain extended
 
82
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 
83
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
84
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
85
2       SUBQUERY        t2i     index   it2i1,it2i3     it2i1   9       NULL    5       100.00  Using where; Using index
 
86
Warnings:
 
87
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>("test"."t1i"."a1","test"."t1i"."a1" in ( <materialize> (select "test"."t2i"."b1" AS "b1" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
 
88
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 
89
a1      a2
 
90
1 - 01  2 - 01
 
91
1 - 02  2 - 02
 
92
explain extended
 
93
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
 
94
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
95
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
96
2       SUBQUERY        t2i     range   it2i1,it2i3     it2i1   9       NULL    3       100.00  Using where; Using index for group-by
 
97
Warnings:
 
98
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>("test"."t1i"."a1","test"."t1i"."a1" in ( <materialize> (select "test"."t2i"."b1" AS "b1" from "test"."t2i" where ("test"."t2i"."b1" > '0') group by "test"."t2i"."b1" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
 
99
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
 
100
a1      a2
 
101
1 - 01  2 - 01
 
102
1 - 02  2 - 02
 
103
explain extended
 
104
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 
105
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
106
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
107
2       SUBQUERY        t2i     index   it2i1,it2i3     it2i3   18      NULL    5       100.00  Using where; Using index
 
108
Warnings:
 
109
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
 
110
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 
111
a1      a2
 
112
1 - 01  2 - 01
 
113
1 - 02  2 - 02
 
114
explain extended
 
115
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
 
116
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
117
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
118
2       SUBQUERY        t2i     range   it2i1,it2i3     it2i3   18      NULL    3       100.00  Using where; Using index for group-by
 
119
Warnings:
 
120
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') group by "test"."t2i"."b1","test"."t2i"."b2" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
 
121
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
 
122
a1      a2
 
123
1 - 01  2 - 01
 
124
1 - 02  2 - 02
 
125
explain extended
 
126
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
 
127
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
128
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
129
2       SUBQUERY        t2i     range   it2i1,it2i3     it2i3   18      NULL    3       100.00  Using where; Using index for group-by
 
130
Warnings:
 
131
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t2i"."b1" AS "b1",min("test"."t2i"."b2") AS "min(b2)" from "test"."t2i" where ("test"."t2i"."b1" > '0') group by "test"."t2i"."b1" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
 
132
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
 
133
a1      a2
 
134
1 - 01  2 - 01
 
135
1 - 02  2 - 02
 
136
explain extended
 
137
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
 
138
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
139
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
140
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  
 
141
Warnings:
 
142
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" order by "test"."t2"."b1","test"."t2"."b2" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key)))
 
143
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
 
144
a1      a2
 
145
1 - 01  2 - 01
 
146
1 - 02  2 - 02
 
147
explain extended
 
148
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
 
149
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
150
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
151
2       SUBQUERY        t2i     index   NULL    it2i3   18      NULL    5       100.00  Using index
 
152
Warnings:
 
153
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" order by "test"."t2i"."b1","test"."t2i"."b2" ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))
 
154
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
 
155
a1      a2
 
156
1 - 01  2 - 01
 
157
1 - 02  2 - 02
 
158
explain extended
 
159
select * from t1
 
160
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
 
161
(a1, a2) in (select c1, c2 from t3
 
162
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
163
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
164
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
165
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
166
4       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
167
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
168
Warnings:
 
169
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (<in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ("test"."t2"."b1" > '0') ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
 
170
select * from t1
 
171
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
 
172
(a1, a2) in (select c1, c2 from t3
 
173
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
174
a1      a2
 
175
1 - 01  2 - 01
 
176
1 - 02  2 - 02
 
177
explain extended
 
178
select * from t1i
 
179
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 
180
(a1, a2) in (select c1, c2 from t3i
 
181
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
182
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
183
1       PRIMARY t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
184
3       SUBQUERY        t3i     index   NULL    it3i3   18      NULL    4       100.00  Using where; Using index
 
185
4       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
186
2       SUBQUERY        t2i     index   it2i1,it2i3     it2i3   18      NULL    5       100.00  Using where; Using index
 
187
Warnings:
 
188
Note    1003    select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where (<in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t3i"."c1" AS "c1","test"."t3i"."c2" AS "c2" from "test"."t3i" where <in_optimizer>(("test"."t3i"."c1","test"."t3i"."c2"),("test"."t3i"."c1","test"."t3i"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3i"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key))))
 
189
select * from t1i
 
190
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 
191
(a1, a2) in (select c1, c2 from t3i
 
192
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
193
a1      a2
 
194
1 - 01  2 - 01
 
195
1 - 02  2 - 02
 
196
explain extended
 
197
select * from t1
 
198
where (a1, a2) in (select b1, b2 from t2
 
199
where b2 in (select c2 from t3 where c2 LIKE '%02') or
 
200
b2 in (select c2 from t3 where c2 LIKE '%03')) and
 
201
(a1, a2) in (select c1, c2 from t3
 
202
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
203
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
204
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
205
5       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
206
6       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
207
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
208
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
209
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
210
Warnings:
 
211
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (<in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where (<in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%02') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key))) or <in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
 
212
select * from t1
 
213
where (a1, a2) in (select b1, b2 from t2
 
214
where b2 in (select c2 from t3 where c2 LIKE '%02') or
 
215
b2 in (select c2 from t3 where c2 LIKE '%03')) and
 
216
(a1, a2) in (select c1, c2 from t3
 
217
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
218
a1      a2
 
219
1 - 02  2 - 02
 
220
explain extended
 
221
select * from t1
 
222
where (a1, a2) in (select b1, b2 from t2
222
223
where b2 in (select c2 from t3 t3a where c1 = a1) or
223
224
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
224
225
(a1, a2) in (select c1, c2 from t3 t3c
225
226
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
226
227
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
227
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
228
 
5       SUBQUERY        t3c     ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
229
 
6       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
230
 
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
231
 
4       SUBQUERY        t3b     ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
232
 
3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
228
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
229
5       SUBQUERY        t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
230
6       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
231
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
232
4       SUBQUERY        t3b     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
233
3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
233
234
Warnings:
234
235
Note    1276    Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
235
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key)))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where <in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3c`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))))
 
236
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (<in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ((<in_optimizer>("test"."t2"."b2",<exists>(select 1 AS "Not_used" from "test"."t3" "t3a" where (("test"."t3a"."c1" = "test"."t1"."a1") and (<cache>("test"."t2"."b2") = "test"."t3a"."c2")))) or <in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3b"."c2" AS "c2" from "test"."t3" "t3b" where ("test"."t3b"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) and (<cache>("test"."t1"."a1") = "test"."t2"."b1") and (<cache>("test"."t1"."a2") = "test"."t2"."b2")))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3c"."c1" AS "c1","test"."t3c"."c2" AS "c2" from "test"."t3" "t3c" where <in_optimizer>(("test"."t3c"."c1","test"."t3c"."c2"),("test"."t3c"."c1","test"."t3c"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3c"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
236
237
select * from t1
237
238
where (a1, a2) in (select b1, b2 from t2
238
239
where b2 in (select c2 from t3 t3a where c1 = a1) or
256
257
(a1, a2) in (select c1, c2 from t3i
257
258
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
258
259
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
259
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
260
 
5       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
261
 
6       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
262
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using temporary; Using filesort
263
 
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
264
 
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
265
 
7       UNION   t1i     index   NULL    it1i3   70      NULL    #       100.00  Using where; Using index
266
 
9       SUBQUERY        t3i     index   NULL    it3i3   70      NULL    #       100.00  Using where; Using index
267
 
10      SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
268
 
8       SUBQUERY        t2i     index   it2i1,it2i3     it2i3   70      NULL    #       40.00   Using where; Using index
269
 
NULL    UNION RESULT    <union1,7>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
260
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
261
5       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
262
6       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
263
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using temporary; Using filesort
 
264
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
265
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
266
7       UNION   t1i     index   NULL    it1i3   18      NULL    3       100.00  Using where; Using index
 
267
9       SUBQUERY        t3i     index   NULL    it3i3   18      NULL    4       100.00  Using where; Using index
 
268
10      SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
269
8       SUBQUERY        t2i     index   it2i1,it2i3     it2i3   18      NULL    5       100.00  Using where; Using index
 
270
NULL    UNION RESULT    <union1,7>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
270
271
Warnings:
271
 
Note    1003    (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key)))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where <in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key)))))
 
272
Note    1003    (select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (<in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where (<in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%02') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key))) or <in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3"."c2" AS "c2" from "test"."t3" where ("test"."t3"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) group by "test"."t2"."b1","test"."t2"."b2" ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))) union (select "test"."t1i"."a1" AS "a1","test"."t1i"."a2" AS "a2" from "test"."t1i" where (<in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b1" > '0') ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key))) and <in_optimizer>(("test"."t1i"."a1","test"."t1i"."a2"),("test"."t1i"."a1","test"."t1i"."a2") in ( <materialize> (select "test"."t3i"."c1" AS "c1","test"."t3i"."c2" AS "c2" from "test"."t3i" where <in_optimizer>(("test"."t3i"."c1","test"."t3i"."c2"),("test"."t3i"."c1","test"."t3i"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3i"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1i"."a1" in <temporary table> on distinct_key)))))
272
273
(select * from t1
273
274
where (a1, a2) in (select b1, b2 from t2
274
275
where b2 in (select c2 from t3 where c2 LIKE '%02') or
290
291
(a1, a2) in (select c1, c2 from t3
291
292
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
292
293
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
293
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
294
 
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
295
 
5       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
296
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
297
 
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
298
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
294
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
295
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
296
5       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
297
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
298
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
299
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
299
300
Warnings:
300
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key))))
 
301
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (<in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (("test"."t1"."a1" > '0') and (<cache>("test"."t1"."a1") = "test"."t1"."a1") and (<cache>("test"."t1"."a2") = "test"."t1"."a2")) union select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where (("test"."t2"."b1" < '9') and (<cache>("test"."t1"."a1") = "test"."t2"."b1") and (<cache>("test"."t1"."a2") = "test"."t2"."b2")))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),("test"."t1"."a1","test"."t1"."a2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t1"."a1" in <temporary table> on distinct_key))))
301
302
select * from t1
302
303
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
303
304
(a1, a2) in (select c1, c2 from t3
312
313
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
313
314
a1 = c1;
314
315
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
315
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
316
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using join buffer
317
 
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
318
 
5       SUBQUERY        t2i     index   it2i2   it2i3   70      NULL    #       40.00   Using where; Using index
319
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
320
 
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
321
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
316
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
317
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Using join buffer
 
318
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
319
5       SUBQUERY        t2i     index   it2i2   it2i3   18      NULL    5       100.00  Using where; Using index
 
320
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
321
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
322
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
322
323
Warnings:
323
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))) ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))))
 
324
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2","test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t1" join "test"."t3" where (("test"."t3"."c1" = "test"."t1"."a1") and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (("test"."t1"."a1" > '0') and (<cache>("test"."t1"."a1") = "test"."t1"."a1") and (<cache>("test"."t1"."a2") = "test"."t1"."a2")) union select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where (("test"."t2"."b1" < '9') and (<cache>("test"."t1"."a1") = "test"."t2"."b1") and (<cache>("test"."t1"."a2") = "test"."t2"."b2")))) and <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>(("test"."t3"."c1","test"."t3"."c2"),("test"."t3"."c1","test"."t3"."c2") in ( <materialize> (select "test"."t2i"."b1" AS "b1","test"."t2i"."b2" AS "b2" from "test"."t2i" where ("test"."t2i"."b2" > '0') ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))) ), <primary_index_lookup>("test"."t3"."c1" in <temporary table> on distinct_key))))
324
325
select * from t1, t3
325
326
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
326
327
(c1, c2) in (select c1, c2 from t3
329
330
a1      a2      c1      c2
330
331
1 - 01  2 - 01  1 - 01  2 - 01
331
332
1 - 02  2 - 02  1 - 02  2 - 02
332
 
/*********************************************************************
 
333
/******************************************************************************
333
334
* Negative tests, where materialization should not be applied.
334
 
**********************************************************************/
 
335
******************************************************************************/
335
336
# UNION in a subquery
336
337
explain extended
337
338
select * from t3
338
339
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
339
340
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
340
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
341
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
342
 
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
343
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
341
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
342
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
343
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
344
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
344
345
Warnings:
345
 
Note    1003    select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`c1`,<exists>(select 1 AS `Not_used` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1`)) union select 1 AS `Not_used` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`))))
 
346
Note    1003    select "test"."t3"."c1" AS "c1","test"."t3"."c2" AS "c2" from "test"."t3" where <in_optimizer>("test"."t3"."c1",<exists>(select 1 AS "Not_used" from "test"."t1" where (("test"."t1"."a1" > '0') and (<cache>("test"."t3"."c1") = "test"."t1"."a1")) union select 1 AS "Not_used" from "test"."t2" where (("test"."t2"."b1" < '9') and (<cache>("test"."t3"."c1") = "test"."t2"."b1"))))
346
347
select * from t3
347
348
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
348
349
c1      c2
357
358
(a1, a2) in (select c1, c2 from t3 t3c
358
359
where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
359
360
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
360
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
361
 
5       DEPENDENT SUBQUERY      t3c     ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
362
 
6       DEPENDENT SUBQUERY      t2i     index_subquery  it2i1,it2i2,it2i3       it2i3   70      func,func       #       100.00  Using index; Using where
363
 
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
364
 
4       SUBQUERY        t3b     ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
365
 
3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
361
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
362
5       DEPENDENT SUBQUERY      t3c     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
363
6       DEPENDENT SUBQUERY      t2i     index_subquery  it2i1,it2i2,it2i3       it2i3   18      func,func       2       100.00  Using index; Using where
 
364
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
 
365
4       SUBQUERY        t3b     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
366
3       DEPENDENT SUBQUERY      t3a     ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
366
367
Warnings:
367
368
Note    1276    Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
368
369
Note    1276    Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
369
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key)))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (<in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`)))))
370
 
DROP TABLE t1i, t2i, t3i;
371
 
explain extended
372
 
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
373
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
374
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
375
 
2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    No tables used
376
 
Warnings:
377
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01' AS `1 - 01`,'2 - 01' AS `2 - 01` having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01'))))
378
 
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 
370
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where (<in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t2"."b1" AS "b1","test"."t2"."b2" AS "b2" from "test"."t2" where ((<in_optimizer>("test"."t2"."b2",<exists>(select 1 AS "Not_used" from "test"."t3" "t3a" where (("test"."t3a"."c1" = "test"."t1"."a1") and (<cache>("test"."t2"."b2") = "test"."t3a"."c2")))) or <in_optimizer>("test"."t2"."b2","test"."t2"."b2" in ( <materialize> (select "test"."t3b"."c2" AS "c2" from "test"."t3" "t3b" where ("test"."t3b"."c2" like '%03') ), <primary_index_lookup>("test"."t2"."b2" in <temporary table> on distinct_key)))) and (<cache>("test"."t1"."a1") = "test"."t2"."b1") and (<cache>("test"."t1"."a2") = "test"."t2"."b2")))) and <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select "test"."t3c"."c1" AS "c1","test"."t3c"."c2" AS "c2" from "test"."t3" "t3c" where (<in_optimizer>(("test"."t3c"."c1","test"."t3c"."c2"),<exists>(<index_lookup>(<cache>("test"."t3c"."c1") in t2i on it2i3 where ((("test"."t2i"."b2" > '0') or ("test"."t2i"."b2" = "test"."t1"."a2")) and (<cache>("test"."t3c"."c1") = "test"."t2i"."b1") and (<cache>("test"."t3c"."c2") = "test"."t2i"."b2"))))) and (<cache>("test"."t1"."a1") = "test"."t3c"."c1") and (<cache>("test"."t1"."a2") = "test"."t3c"."c2")))))
 
371
explain extended
 
372
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 
373
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
374
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
375
2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 
376
Warnings:
 
377
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select '1 - 01' AS "1 - 01",'2 - 01' AS "2 - 01" having ((<cache>("test"."t1"."a1") = '1 - 01') and (<cache>("test"."t1"."a2") = '2 - 01'))))
 
378
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 
379
a1      a2
 
380
1 - 01  2 - 01
 
381
explain extended
 
382
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
 
383
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
384
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
385
2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 
386
Warnings:
 
387
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(("test"."t1"."a1","test"."t1"."a2"),<exists>(select '1 - 01' AS "1 - 01",'2 - 01' AS "2 - 01" having ((<cache>("test"."t1"."a1") = '1 - 01') and (<cache>("test"."t1"."a2") = '2 - 01'))))
 
388
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
379
389
a1      a2
380
390
1 - 01  2 - 01
381
391
/******************************************************************************
388
398
0
389
399
0
390
400
/* GROUP BY clause */
391
 
create temporary table columns (col int key) ENGINE=MyISAM;
 
401
create table columns (col int key);
392
402
insert into columns values (1), (2);
393
403
explain extended
394
404
select * from t1 group by (select col from columns limit 1);
395
405
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
396
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
397
 
2       SUBQUERY        columns index   NULL    PRIMARY 4       NULL    #       100.00  Using index
 
406
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
407
2       SUBQUERY        columns index   NULL    PRIMARY 4       NULL    2       100.00  Using index
398
408
Warnings:
399
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (select `test`.`columns`.`col` AS `col` from `test`.`columns` limit 1)
 
409
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" group by (select "test"."columns"."col" AS "col" from "test"."columns" limit 1)
400
410
select * from t1 group by (select col from columns limit 1);
401
411
a1      a2
402
412
1 - 00  2 - 00
403
413
explain extended
404
414
select * from t1 group by (a1 in (select col from columns));
405
415
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
406
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using temporary; Using filesort
407
 
2       DEPENDENT SUBQUERY      columns unique_subquery PRIMARY PRIMARY 4       func    #       100.00  Using index; Using where; Full scan on NULL key
 
416
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using temporary; Using filesort
 
417
2       DEPENDENT SUBQUERY      columns unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where; Full scan on NULL key
408
418
Warnings:
409
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))
 
419
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" group by <in_optimizer>("test"."t1"."a1",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a1") in columns on PRIMARY where trigcond((<cache>("test"."t1"."a1") = "test"."columns"."col")))))
410
420
select * from t1 group by (a1 in (select col from columns));
411
421
a1      a2
412
422
1 - 00  2 - 00
414
424
explain extended
415
425
select * from t1 order by (select col from columns limit 1);
416
426
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
417
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
418
 
2       SUBQUERY        columns index   NULL    PRIMARY 4       NULL    #       100.00  Using index
 
427
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
428
2       SUBQUERY        columns index   NULL    PRIMARY 4       NULL    2       100.00  Using index
419
429
Warnings:
420
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (select `test`.`columns`.`col` AS `col` from `test`.`columns` limit 1)
 
430
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" order by (select "test"."columns"."col" AS "col" from "test"."columns" limit 1)
421
431
select * from t1 order by (select col from columns limit 1);
422
432
a1      a2
423
433
1 - 00  2 - 00
424
434
1 - 01  2 - 01
425
435
1 - 02  2 - 02
426
 
DROP TABLE columns;
427
436
/******************************************************************************
428
437
* Column types/sizes that affect materialization.
429
438
******************************************************************************/
431
440
Test that BLOBs are not materialized (except when arguments of some functions).
432
441
*/
433
442
# force materialization to be always considered
 
443
set @@optimizer_switch=no_semijoin;
434
444
set @prefix_len = 6;
435
445
set @blob_len = 16;
436
446
set @suffix_len = @blob_len - @prefix_len;
437
 
create temporary table t1_16 (a1 blob, a2 blob) ENGINE=MyISAM;
438
 
create temporary table t2_16 (b1 blob, b2 blob) ENGINE=MyISAM;
439
 
create temporary table t3_16 (c1 blob, c2 blob) ENGINE=MyISAM;
 
447
create table t1_16 (a1 blob(16), a2 blob(16));
 
448
create table t2_16 (b1 blob(16), b2 blob(16));
 
449
create table t3_16 (c1 blob(16), c2 blob(16));
440
450
insert into t1_16 values
441
451
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
442
452
insert into t1_16 values
464
474
1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
465
475
2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
466
476
Warnings:
467
 
Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in (select 1 AS `Not_used` from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))
 
477
Note    1003    select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in (select 1 AS "Not_used" from "test"."t2_16" where (("test"."t2_16"."b1" > '0') and (<cache>("test"."t1_16"."a1") = "test"."t2_16"."b1"))))
468
478
select left(a1,7), left(a2,7)
469
479
from t1_16
470
480
where a1 in (select b1 from t2_16 where b1 > '0');
478
488
1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
479
489
2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
480
490
Warnings:
481
 
Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`) in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`))))
 
491
Note    1003    select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>(("test"."t1_16"."a1","test"."t1_16"."a2"),("test"."t1_16"."a1","test"."t1_16"."a2") in (select "test"."t2_16"."b1" AS "b1","test"."t2_16"."b2" AS "b2" from "test"."t2_16" where (("test"."t2_16"."b1" > '0') and (<cache>("test"."t1_16"."a1") = "test"."t2_16"."b1") and (<cache>("test"."t1_16"."a2") = "test"."t2_16"."b2"))))
482
492
select left(a1,7), left(a2,7)
483
493
from t1_16
484
494
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
492
502
1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
493
503
2       SUBQUERY        t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
494
504
Warnings:
495
 
Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on distinct_key)))
 
505
Note    1003    select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in ( <materialize> (select substr("test"."t2_16"."b1",1,16) AS "substring(b1,1,16)" from "test"."t2_16" where ("test"."t2_16"."b1" > '0') ), <primary_index_lookup>("test"."t1_16"."a1" in <temporary table> on distinct_key)))
496
506
select left(a1,7), left(a2,7)
497
507
from t1_16
498
508
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
506
516
1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
507
517
2       DEPENDENT SUBQUERY      t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
508
518
Warnings:
509
 
Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) = <ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ',')))))
 
519
Note    1003    select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in (select group_concat("test"."t2_16"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_16" group by "test"."t2_16"."b2" having (<cache>("test"."t1_16"."a1") = <ref_null_helper>(group_concat("test"."t2_16"."b1" separator ',')))))
510
520
select left(a1,7), left(a2,7)
511
521
from t1_16
512
522
where a1 in (select group_concat(b1) from t2_16 group by b2);
521
531
1       PRIMARY t1_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
522
532
2       SUBQUERY        t2_16   ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
523
533
Warnings:
524
 
Note    1003    select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2` ), <primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on distinct_key)))
 
534
Note    1003    select left("test"."t1_16"."a1",7) AS "left(a1,7)",left("test"."t1_16"."a2",7) AS "left(a2,7)" from "test"."t1_16" where <in_optimizer>("test"."t1_16"."a1","test"."t1_16"."a1" in ( <materialize> (select group_concat("test"."t2_16"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_16" group by "test"."t2_16"."b2" ), <primary_index_lookup>("test"."t1_16"."a1" in <temporary table> on distinct_key)))
525
535
select left(a1,7), left(a2,7)
526
536
from t1_16
527
537
where a1 in (select group_concat(b1) from t2_16 group by b2);
543
553
3       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using join buffer
544
554
4       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
545
555
Warnings:
546
 
Note    1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),concat(`test`.`t1`.`a1`,'x') in ( <materialize> (select left(`test`.`t1_16`.`a1`,8) AS `left(a1,8)` from `test`.`t1_16` where <in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`) in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16` join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and <in_optimizer>(`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ), <primary_index_lookup>(`test`.`t2`.`b1` in <temporary table> on distinct_key))) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) ), <primary_index_lookup>(concat(`test`.`t1`.`a1`,'x') in <temporary table> on distinct_key)))
 
556
Note    1003    select "test"."t1"."a1" AS "a1","test"."t1"."a2" AS "a2" from "test"."t1" where <in_optimizer>(concat("test"."t1"."a1",'x'),concat("test"."t1"."a1",'x') in ( <materialize> (select left("test"."t1_16"."a1",8) AS "left(a1,8)" from "test"."t1_16" where <in_optimizer>(("test"."t1_16"."a1","test"."t1_16"."a2"),("test"."t1_16"."a1","test"."t1_16"."a2") in (select "test"."t2_16"."b1" AS "b1","test"."t2_16"."b2" AS "b2" from "test"."t2_16" join "test"."t2" where (("test"."t2"."b2" = substr("test"."t2_16"."b2",1,6)) and <in_optimizer>("test"."t2"."b1","test"."t2"."b1" in ( <materialize> (select "test"."t3"."c1" AS "c1" from "test"."t3" where ("test"."t3"."c2" > '0') ), <primary_index_lookup>("test"."t2"."b1" in <temporary table> on distinct_key))) and (<cache>("test"."t1_16"."a1") = "test"."t2_16"."b1") and (<cache>("test"."t1_16"."a2") = "test"."t2_16"."b2")))) ), <primary_index_lookup>(concat("test"."t1"."a1",'x') in <temporary table> on distinct_key)))
547
557
drop table t1_16, t2_16, t3_16;
548
558
set @blob_len = 512;
549
559
set @suffix_len = @blob_len - @prefix_len;
550
 
create temporary table t1_512 (a1 blob, a2 blob) ENGINE=MyISAM;
551
 
create temporary table t2_512 (b1 blob, b2 blob) ENGINE=MyISAM;
552
 
create temporary table t3_512 (c1 blob, c2 blob) ENGINE=MyISAM;
 
560
create table t1_512 (a1 blob(512), a2 blob(512));
 
561
create table t2_512 (b1 blob(512), b2 blob(512));
 
562
create table t3_512 (c1 blob(512), c2 blob(512));
553
563
insert into t1_512 values
554
564
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
555
565
insert into t1_512 values
577
587
1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
578
588
2       DEPENDENT SUBQUERY      t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
579
589
Warnings:
580
 
Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in (select 1 AS `Not_used` from `test`.`t2_512` where ((`test`.`t2_512`.`b1` > '0') and (<cache>(`test`.`t1_512`.`a1`) = `test`.`t2_512`.`b1`))))
 
590
Note    1003    select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in (select 1 AS "Not_used" from "test"."t2_512" where (("test"."t2_512"."b1" > '0') and (<cache>("test"."t1_512"."a1") = "test"."t2_512"."b1"))))
581
591
select left(a1,7), left(a2,7)
582
592
from t1_512
583
593
where a1 in (select b1 from t2_512 where b1 > '0');
591
601
1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
592
602
2       DEPENDENT SUBQUERY      t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
593
603
Warnings:
594
 
Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>((`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`),(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`) in (select `test`.`t2_512`.`b1` AS `b1`,`test`.`t2_512`.`b2` AS `b2` from `test`.`t2_512` where ((`test`.`t2_512`.`b1` > '0') and (<cache>(`test`.`t1_512`.`a1`) = `test`.`t2_512`.`b1`) and (<cache>(`test`.`t1_512`.`a2`) = `test`.`t2_512`.`b2`))))
 
604
Note    1003    select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>(("test"."t1_512"."a1","test"."t1_512"."a2"),("test"."t1_512"."a1","test"."t1_512"."a2") in (select "test"."t2_512"."b1" AS "b1","test"."t2_512"."b2" AS "b2" from "test"."t2_512" where (("test"."t2_512"."b1" > '0') and (<cache>("test"."t1_512"."a1") = "test"."t2_512"."b1") and (<cache>("test"."t1_512"."a2") = "test"."t2_512"."b2"))))
595
605
select left(a1,7), left(a2,7)
596
606
from t1_512
597
607
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
605
615
1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
606
616
2       SUBQUERY        t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
607
617
Warnings:
608
 
Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key)))
 
618
Note    1003    select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in ( <materialize> (select substr("test"."t2_512"."b1",1,512) AS "substring(b1,1,512)" from "test"."t2_512" where ("test"."t2_512"."b1" > '0') ), <primary_index_lookup>("test"."t1_512"."a1" in <temporary table> on distinct_key)))
609
619
select left(a1,7), left(a2,7)
610
620
from t1_512
611
621
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
619
629
1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
620
630
2       SUBQUERY        t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
621
631
Warnings:
622
 
Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key)))
 
632
Note    1003    select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in ( <materialize> (select group_concat("test"."t2_512"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_512" group by "test"."t2_512"."b2" ), <primary_index_lookup>("test"."t1_512"."a1" in <temporary table> on distinct_key)))
623
633
select left(a1,7), left(a2,7)
624
634
from t1_512
625
635
where a1 in (select group_concat(b1) from t2_512 group by b2);
634
644
1       PRIMARY t1_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
635
645
2       SUBQUERY        t2_512  ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
636
646
Warnings:
637
 
Note    1003    select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key)))
 
647
Note    1003    select left("test"."t1_512"."a1",7) AS "left(a1,7)",left("test"."t1_512"."a2",7) AS "left(a2,7)" from "test"."t1_512" where <in_optimizer>("test"."t1_512"."a1","test"."t1_512"."a1" in ( <materialize> (select group_concat("test"."t2_512"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_512" group by "test"."t2_512"."b2" ), <primary_index_lookup>("test"."t1_512"."a1" in <temporary table> on distinct_key)))
638
648
select left(a1,7), left(a2,7)
639
649
from t1_512
640
650
where a1 in (select group_concat(b1) from t2_512 group by b2);
644
654
drop table t1_512, t2_512, t3_512;
645
655
set @blob_len = 1024;
646
656
set @suffix_len = @blob_len - @prefix_len;
647
 
create temporary table t1_1024 (a1 blob, a2 blob) ENGINE=MyISAM;
648
 
create temporary table t2_1024 (b1 blob, b2 blob) ENGINE=MyISAM;
649
 
create temporary table t3_1024 (c1 blob, c2 blob) ENGINE=MyISAM;
 
657
create table t1_1024 (a1 blob(1024), a2 blob(1024));
 
658
create table t2_1024 (b1 blob(1024), b2 blob(1024));
 
659
create table t3_1024 (c1 blob(1024), c2 blob(1024));
650
660
insert into t1_1024 values
651
661
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
652
662
insert into t1_1024 values
674
684
1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
675
685
2       DEPENDENT SUBQUERY      t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
676
686
Warnings:
677
 
Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS `Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`))))
 
687
Note    1003    select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in (select 1 AS "Not_used" from "test"."t2_1024" where (("test"."t2_1024"."b1" > '0') and (<cache>("test"."t1_1024"."a1") = "test"."t2_1024"."b1"))))
678
688
select left(a1,7), left(a2,7)
679
689
from t1_1024
680
690
where a1 in (select b1 from t2_1024 where b1 > '0');
688
698
1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
689
699
2       DEPENDENT SUBQUERY      t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
690
700
Warnings:
691
 
Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>((`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`),(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`) in (select `test`.`t2_1024`.`b1` AS `b1`,`test`.`t2_1024`.`b2` AS `b2` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`) and (<cache>(`test`.`t1_1024`.`a2`) = `test`.`t2_1024`.`b2`))))
 
701
Note    1003    select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>(("test"."t1_1024"."a1","test"."t1_1024"."a2"),("test"."t1_1024"."a1","test"."t1_1024"."a2") in (select "test"."t2_1024"."b1" AS "b1","test"."t2_1024"."b2" AS "b2" from "test"."t2_1024" where (("test"."t2_1024"."b1" > '0') and (<cache>("test"."t1_1024"."a1") = "test"."t2_1024"."b1") and (<cache>("test"."t1_1024"."a2") = "test"."t2_1024"."b2"))))
692
702
select left(a1,7), left(a2,7)
693
703
from t1_1024
694
704
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
702
712
1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
703
713
2       DEPENDENT SUBQUERY      t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
704
714
Warnings:
705
 
Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS `Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = substr(`test`.`t2_1024`.`b1`,1,1024)))))
 
715
Note    1003    select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in (select 1 AS "Not_used" from "test"."t2_1024" where (("test"."t2_1024"."b1" > '0') and (<cache>("test"."t1_1024"."a1") = substr("test"."t2_1024"."b1",1,1024)))))
706
716
select left(a1,7), left(a2,7)
707
717
from t1_1024
708
718
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
716
726
1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
717
727
2       SUBQUERY        t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
718
728
Warnings:
719
 
Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), <primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on distinct_key)))
 
729
Note    1003    select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in ( <materialize> (select group_concat("test"."t2_1024"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1024" group by "test"."t2_1024"."b2" ), <primary_index_lookup>("test"."t1_1024"."a1" in <temporary table> on distinct_key)))
720
730
select left(a1,7), left(a2,7)
721
731
from t1_1024
722
732
where a1 in (select group_concat(b1) from t2_1024 group by b2);
731
741
1       PRIMARY t1_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
732
742
2       SUBQUERY        t2_1024 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
733
743
Warnings:
734
 
Note    1003    select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), <primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on distinct_key)))
 
744
Note    1003    select left("test"."t1_1024"."a1",7) AS "left(a1,7)",left("test"."t1_1024"."a2",7) AS "left(a2,7)" from "test"."t1_1024" where <in_optimizer>("test"."t1_1024"."a1","test"."t1_1024"."a1" in ( <materialize> (select group_concat("test"."t2_1024"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1024" group by "test"."t2_1024"."b2" ), <primary_index_lookup>("test"."t1_1024"."a1" in <temporary table> on distinct_key)))
735
745
select left(a1,7), left(a2,7)
736
746
from t1_1024
737
747
where a1 in (select group_concat(b1) from t2_1024 group by b2);
741
751
drop table t1_1024, t2_1024, t3_1024;
742
752
set @blob_len = 1025;
743
753
set @suffix_len = @blob_len - @prefix_len;
744
 
create temporary table t1_1025 (a1 blob, a2 blob) ENGINE=MyISAM;
745
 
create temporary table t2_1025 (b1 blob, b2 blob) ENGINE=MyISAM;
746
 
create temporary table t3_1025 (c1 blob, c2 blob) ENGINE=MyISAM;
 
754
create table t1_1025 (a1 blob(1025), a2 blob(1025));
 
755
create table t2_1025 (b1 blob(1025), b2 blob(1025));
 
756
create table t3_1025 (c1 blob(1025), c2 blob(1025));
747
757
insert into t1_1025 values
748
758
(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
749
759
insert into t1_1025 values
771
781
1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
772
782
2       DEPENDENT SUBQUERY      t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
773
783
Warnings:
774
 
Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS `Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`))))
 
784
Note    1003    select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in (select 1 AS "Not_used" from "test"."t2_1025" where (("test"."t2_1025"."b1" > '0') and (<cache>("test"."t1_1025"."a1") = "test"."t2_1025"."b1"))))
775
785
select left(a1,7), left(a2,7)
776
786
from t1_1025
777
787
where a1 in (select b1 from t2_1025 where b1 > '0');
785
795
1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
786
796
2       DEPENDENT SUBQUERY      t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
787
797
Warnings:
788
 
Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>((`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`),(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`) in (select `test`.`t2_1025`.`b1` AS `b1`,`test`.`t2_1025`.`b2` AS `b2` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`) and (<cache>(`test`.`t1_1025`.`a2`) = `test`.`t2_1025`.`b2`))))
 
798
Note    1003    select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>(("test"."t1_1025"."a1","test"."t1_1025"."a2"),("test"."t1_1025"."a1","test"."t1_1025"."a2") in (select "test"."t2_1025"."b1" AS "b1","test"."t2_1025"."b2" AS "b2" from "test"."t2_1025" where (("test"."t2_1025"."b1" > '0') and (<cache>("test"."t1_1025"."a1") = "test"."t2_1025"."b1") and (<cache>("test"."t1_1025"."a2") = "test"."t2_1025"."b2"))))
789
799
select left(a1,7), left(a2,7)
790
800
from t1_1025
791
801
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
799
809
1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
800
810
2       DEPENDENT SUBQUERY      t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
801
811
Warnings:
802
 
Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS `Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = substr(`test`.`t2_1025`.`b1`,1,1025)))))
 
812
Note    1003    select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in (select 1 AS "Not_used" from "test"."t2_1025" where (("test"."t2_1025"."b1" > '0') and (<cache>("test"."t1_1025"."a1") = substr("test"."t2_1025"."b1",1,1025)))))
803
813
select left(a1,7), left(a2,7)
804
814
from t1_1025
805
815
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
813
823
1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
814
824
2       SUBQUERY        t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
815
825
Warnings:
816
 
Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), <primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on distinct_key)))
 
826
Note    1003    select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in ( <materialize> (select group_concat("test"."t2_1025"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1025" group by "test"."t2_1025"."b2" ), <primary_index_lookup>("test"."t1_1025"."a1" in <temporary table> on distinct_key)))
817
827
select left(a1,7), left(a2,7)
818
828
from t1_1025
819
829
where a1 in (select group_concat(b1) from t2_1025 group by b2);
828
838
1       PRIMARY t1_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
829
839
2       SUBQUERY        t2_1025 ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
830
840
Warnings:
831
 
Note    1003    select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), <primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on distinct_key)))
 
841
Note    1003    select left("test"."t1_1025"."a1",7) AS "left(a1,7)",left("test"."t1_1025"."a2",7) AS "left(a2,7)" from "test"."t1_1025" where <in_optimizer>("test"."t1_1025"."a1","test"."t1_1025"."a1" in ( <materialize> (select group_concat("test"."t2_1025"."b1" separator ',') AS "group_concat(b1)" from "test"."t2_1025" group by "test"."t2_1025"."b2" ), <primary_index_lookup>("test"."t1_1025"."a1" in <temporary table> on distinct_key)))
832
842
select left(a1,7), left(a2,7)
833
843
from t1_1025
834
844
where a1 in (select group_concat(b1) from t2_1025 group by b2);
836
846
1 - 01x 2 - 01x
837
847
1 - 02x 2 - 02x
838
848
drop table t1_1025, t2_1025, t3_1025;
839
 
drop table t1, t2, t3;
 
849
create table t1bit (a1 bit(3), a2 bit(3));
 
850
create table t2bit (b1 bit(3), b2 bit(3));
 
851
insert into t1bit values (b'000', b'100');
 
852
insert into t1bit values (b'001', b'101');
 
853
insert into t1bit values (b'010', b'110');
 
854
insert into t2bit values (b'001', b'101');
 
855
insert into t2bit values (b'010', b'110');
 
856
insert into t2bit values (b'110', b'111');
 
857
set @@optimizer_switch=no_semijoin;
 
858
explain extended select bin(a1), bin(a2)
 
859
from t1bit
 
860
where (a1, a2) in (select b1, b2 from t2bit);
 
861
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
862
1       PRIMARY t1bit   ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
863
2       SUBQUERY        t2bit   ALL     NULL    NULL    NULL    NULL    3       100.00  
 
864
Warnings:
 
865
Note    1003    select conv("test"."t1bit"."a1",10,2) AS "bin(a1)",conv("test"."t1bit"."a2",10,2) AS "bin(a2)" from "test"."t1bit" where <in_optimizer>(("test"."t1bit"."a1","test"."t1bit"."a2"),("test"."t1bit"."a1","test"."t1bit"."a2") in ( <materialize> (select "test"."t2bit"."b1" AS "b1","test"."t2bit"."b2" AS "b2" from "test"."t2bit" ), <primary_index_lookup>("test"."t1bit"."a1" in <temporary table> on distinct_key)))
 
866
select bin(a1), bin(a2)
 
867
from t1bit
 
868
where (a1, a2) in (select b1, b2 from t2bit);
 
869
bin(a1) bin(a2)
 
870
1       101
 
871
10      110
 
872
drop table t1bit, t2bit;
 
873
create table t1bb (a1 bit(3), a2 blob(3));
 
874
create table t2bb (b1 bit(3), b2 blob(3));
 
875
insert into t1bb values (b'000', '100');
 
876
insert into t1bb values (b'001', '101');
 
877
insert into t1bb values (b'010', '110');
 
878
insert into t2bb values (b'001', '101');
 
879
insert into t2bb values (b'010', '110');
 
880
insert into t2bb values (b'110', '111');
 
881
explain extended select bin(a1), a2
 
882
from t1bb
 
883
where (a1, a2) in (select b1, b2 from t2bb);
 
884
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
885
1       PRIMARY t1bb    ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
886
2       DEPENDENT SUBQUERY      t2bb    ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
887
Warnings:
 
888
Note    1003    select conv("test"."t1bb"."a1",10,2) AS "bin(a1)","test"."t1bb"."a2" AS "a2" from "test"."t1bb" where <in_optimizer>(("test"."t1bb"."a1","test"."t1bb"."a2"),("test"."t1bb"."a1","test"."t1bb"."a2") in (select "test"."t2bb"."b1" AS "b1","test"."t2bb"."b2" AS "b2" from "test"."t2bb" where ((<cache>("test"."t1bb"."a1") = "test"."t2bb"."b1") and (<cache>("test"."t1bb"."a2") = "test"."t2bb"."b2"))))
 
889
select bin(a1), a2
 
890
from t1bb
 
891
where (a1, a2) in (select b1, b2 from t2bb);
 
892
bin(a1) a2
 
893
1       101
 
894
10      110
 
895
drop table t1bb, t2bb;
 
896
drop table t1, t2, t3, t1i, t2i, t3i, columns;
840
897
/******************************************************************************
841
898
* Test the cache of the left operand of IN.
842
899
******************************************************************************/
843
 
# Test that default values of Cached_item are not used for comparison
844
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
845
 
create temporary table t2 (s2 int) ENGINE=MyISAM;
 
900
set @@optimizer_switch=no_semijoin;
 
901
create table t1 (s1 int);
 
902
create table t2 (s2 int);
846
903
insert into t1 values (5),(1),(0);
847
904
insert into t2 values (0), (1);
848
905
select s2 from t2 where s2 in (select s1 from t1);
850
907
0
851
908
1
852
909
drop table t1, t2;
853
 
create temporary table t1 (a int not null, b int not null) ENGINE=MyISAM;
854
 
create temporary table t2 (c int not null, d int not null) ENGINE=MyISAM;
855
 
create temporary table t3 (e int not null) ENGINE=MyISAM;
 
910
create table t1 (a int not null, b int not null);
 
911
create table t2 (c int not null, d int not null);
 
912
create table t3 (e int not null);
856
913
insert into t1 values (1,10);
857
914
insert into t1 values (1,20);
858
915
insert into t1 values (2,10);
876
933
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
877
934
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where
878
935
Warnings:
879
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
 
936
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
880
937
select a from t1 where a in (select c from t2 where d >= 20);
881
938
a
882
939
2
890
947
1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using where; Using index
891
948
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where
892
949
Warnings:
893
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
 
950
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
894
951
select a from t1 where a in (select c from t2 where d >= 20);
895
952
a
896
953
2
904
961
1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using where; Using index
905
962
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
906
963
Warnings:
907
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
 
964
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
908
965
select a from t1 where a in (select c from t2 where d >= 20);
909
966
a
910
967
2
917
974
1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using index
918
975
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
919
976
Warnings:
920
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
 
977
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" group by "test"."t1"."a" having <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
921
978
select a from t1 group by a having a in (select c from t2 where d >= 20);
922
979
a
923
980
2
929
986
1       PRIMARY t1      index   NULL    it1a    4       NULL    7       100.00  Using index
930
987
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    7       100.00  Using where
931
988
Warnings:
932
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key)))
 
989
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" group by "test"."t1"."a" having <in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (select "test"."t2"."c" AS "c" from "test"."t2" where ("test"."t2"."d" >= 20) ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key)))
933
990
select a from t1 group by a having a in (select c from t2 where d >= 20);
934
991
a
935
992
2
943
1000
3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
944
1001
Warnings:
945
1002
Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
946
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` where (<nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` AS `e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
 
1003
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" group by "test"."t1"."a" having <in_optimizer>("test"."t1"."a",<exists>(select 1 AS "Not_used" from "test"."t2" where (<nop>(<in_optimizer>("test"."t2"."d",<exists>(select "test"."t3"."e" AS "e" from "test"."t3" where (max("test"."t1"."b") = "test"."t3"."e") having (<cache>("test"."t2"."d") >= <ref_null_helper>("test"."t3"."e"))))) and (<cache>("test"."t1"."a") = "test"."t2"."c"))))
947
1004
select a from t1 group by a
948
1005
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
949
1006
a
958
1015
3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
959
1016
Warnings:
960
1017
Note    1276    Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
961
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` where (<nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 AS `Not_used` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
 
1018
Note    1003    select "test"."t1"."a" AS "a" from "test"."t1" where <in_optimizer>("test"."t1"."a",<exists>(select 1 AS "Not_used" from "test"."t2" where (<nop>(<in_optimizer>("test"."t2"."d",<exists>(select 1 AS "Not_used" from "test"."t3" where (("test"."t1"."b" = "test"."t3"."e") and (<cache>("test"."t2"."d") >= "test"."t3"."e"))))) and (<cache>("test"."t1"."a") = "test"."t2"."c"))))
962
1019
select a from t1
963
1020
where a in (select c from t2 where d >= some(select e from t3 where b=e));
964
1021
a