~drizzle-trunk/drizzle/development : revision 1462

1

drop table if exists t1, t2, t3, t1i, t2i, t3i;

2

create table t1 (a1 char(8), a2 char(8));

3

create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;

4

create table t3 (c1 char(8), c2 char(8));

5

insert into t1 values ('1 - 00', '2 - 00');

6

insert into t1 values ('1 - 01', '2 - 01');

7

insert into t1 values ('1 - 02', '2 - 02');

8

insert into t2 values ('1 - 01', '2 - 01');

9

insert into t2 values ('1 - 01', '2 - 01');

10

insert into t2 values ('1 - 02', '2 - 02');

11

insert into t2 values ('1 - 02', '2 - 02');

12

insert into t2 values ('1 - 03', '2 - 03');

13

insert into t3 values ('1 - 01', '2 - 01');

14

insert into t3 values ('1 - 02', '2 - 02');

15

insert into t3 values ('1 - 03', '2 - 03');

16

insert into t3 values ('1 - 04', '2 - 04');

17

create temporary table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;

18

create table t2i (b1 char(8), b2 char(8));

19

create temporary table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;

20

create index it1i1 on t1i (a1);

21

create index it1i2 on t1i (a2);

22

create index it1i3 on t1i (a1, a2);

23

create index it2i1 on t2i (b1);

24

create index it2i2 on t2i (b2);

25

create index it2i3 on t2i (b1, b2);

26

create index it3i1 on t3i (c1);

27

create index it3i2 on t3i (c2);

28

create index it3i3 on t3i (c1, c2);

29

insert into t1i select * from t1;

30

insert into t2i select * from t2;

31

insert into t3i select * from t3;

32

/********************************************************************

33

* Simple tests.

34

********************************************************************/

35

# non-indexed nullable fields

36

explain extended

37

select * from t1 where a1 in (select b1 from t2 where b1 > '0');

38

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

41

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

select * from t1 where a1 in (select b1 from t2 where b1 > '0');

44

a1 a2

45

1 - 01 2 - 01

46

1 - 02 2 - 02

47

explain extended

48

select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);

49

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

52

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

select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);

55

a1 a2

56

1 - 01 2 - 01

57

1 - 02 2 - 02

58

explain extended

59

select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);

60

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

63

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

select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);

66

a1 a2

67

1 - 01 2 - 01

68

1 - 02 2 - 02

69

explain extended

70

select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);

71

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)))

76

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 ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where

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 ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where; Using temporary; Using filesort

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 ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where

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 ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where; Using temporary; Using filesort

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 ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where; Using temporary; Using filesort

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 ALL NULL NULL NULL NULL # 100.00

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

185

2 SUBQUERY t2i ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

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

222

where b2 in (select c2 from t3 t3a where c1 = a1) or

223

b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and

224

(a1, a2) in (select c1, c2 from t3 t3c

225

where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

226

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

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

233

Warnings:

234

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

select * from t1

237

where (a1, a2) in (select b1, b2 from t2

238

where b2 in (select c2 from t3 t3a where c1 = a1) or

239

b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and

240

(a1, a2) in (select c1, c2 from t3 t3c

241

where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

242

a1 a2

243

1 - 01 2 - 01

244

1 - 02 2 - 02

245

explain extended

246

(select * from t1

247

where (a1, a2) in (select b1, b2 from t2

248

where b2 in (select c2 from t3 where c2 LIKE '%02') or

249

b2 in (select c2 from t3 where c2 LIKE '%03')

250

group by b1, b2) and

251

(a1, a2) in (select c1, c2 from t3

252

where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))

253

UNION

254

(select * from t1i

255

where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and

256

(a1, a2) in (select c1, c2 from t3i

257

where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));

258

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

268

8 SUBQUERY t2i ALL it2i1,it2i3 NULL NULL NULL # 100.00 Using where

269

NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL # NULL

270

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

(select * from t1

273

where (a1, a2) in (select b1, b2 from t2

274

where b2 in (select c2 from t3 where c2 LIKE '%02') or

275

b2 in (select c2 from t3 where c2 LIKE '%03')

276

group by b1, b2) and

277

(a1, a2) in (select c1, c2 from t3

278

where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))

279

UNION

280

(select * from t1i

281

where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and

282

(a1, a2) in (select c1, c2 from t3i

283

where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));

284

a1 a2

285

1 - 02 2 - 02

286

1 - 01 2 - 01

287

explain extended

288

select * from t1

289

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

290

(a1, a2) in (select c1, c2 from t3

291

where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

292

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

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

299

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

select * from t1

302

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

303

(a1, a2) in (select c1, c2 from t3

304

where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

305

a1 a2

306

1 - 01 2 - 01

307

1 - 02 2 - 02

308

explain extended

309

select * from t1, t3

310

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

311

(c1, c2) in (select c1, c2 from t3

312

where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and

313

a1 = c1;

314

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 ALL it2i2 NULL NULL NULL # 100.00 Using where

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

322

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

select * from t1, t3

325

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

326

(c1, c2) in (select c1, c2 from t3

327

where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and

328

a1 = c1;

329

a1 a2 c1 c2

330

1 - 01 2 - 01 1 - 01 2 - 01

331

1 - 02 2 - 02 1 - 02 2 - 02

332

/*********************************************************************

333

* Negative tests, where materialization should not be applied.

334

**********************************************************************/

335

# UNION in a subquery

336

explain extended

337

select * from t3

338

where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');

339

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

344

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

select * from t3

347

where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');

348

c1 c2

349

1 - 01 2 - 01

350

1 - 02 2 - 02

351

1 - 03 2 - 03

352

explain extended

353

select * from t1

354

where (a1, a2) in (select b1, b2 from t2

355

where b2 in (select c2 from t3 t3a where c1 = a1) or

356

b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and

357

(a1, a2) in (select c1, c2 from t3 t3c

358

where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));

359

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 it2i1 35 func # 100.00 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

366

Warnings:

367

Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1

368

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 it2i1 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');

379

a1 a2

380

1 - 01 2 - 01

381

/******************************************************************************

382

* Subqueries in other uncovered clauses.

383

******************************************************************************/

384

/* SELECT clause */

385

select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;

386

((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL

387

0

388

0

389

0

390

/* GROUP BY clause */

391

create temporary table columns (col int key) ENGINE=MyISAM;

392

insert into columns values (1), (2);

393

explain extended

394

select * from t1 group by (select col from columns limit 1);

395

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

398

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)

400

select * from t1 group by (select col from columns limit 1);

401

a1 a2

402

1 - 00 2 - 00

403

explain extended

404

select * from t1 group by (a1 in (select col from columns));

405

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

408

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`)))))

410

select * from t1 group by (a1 in (select col from columns));

411

a1 a2

412

1 - 00 2 - 00

413

/* ORDER BY clause */

414

explain extended

415

select * from t1 order by (select col from columns limit 1);

416

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

419

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)

421

select * from t1 order by (select col from columns limit 1);

422

a1 a2

423

1 - 00 2 - 00

424

1 - 01 2 - 01

425

1 - 02 2 - 02

426

DROP TABLE columns;

427

/******************************************************************************

428

* Column types/sizes that affect materialization.

429

******************************************************************************/

430

/*

431

Test that BLOBs are not materialized (except when arguments of some functions).

432

*/

433

# force materialization to be always considered

434

set @prefix_len = 6;

435

set @blob_len = 16;

436

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;

440

insert into t1_16 values

441

(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

442

insert into t1_16 values

443

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

444

insert into t1_16 values

445

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

446

insert into t2_16 values

447

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

448

insert into t2_16 values

449

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

450

insert into t2_16 values

451

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

452

insert into t3_16 values

453

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

454

insert into t3_16 values

455

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

456

insert into t3_16 values

457

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

458

insert into t3_16 values

459

(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

460

explain extended select left(a1,7), left(a2,7)

461

from t1_16

462

where a1 in (select b1 from t2_16 where b1 > '0');

463

id select_type table type possible_keys key key_len ref rows filtered Extra

464

1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where

465

2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where

466

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`))))

468

select left(a1,7), left(a2,7)

469

from t1_16

470

where a1 in (select b1 from t2_16 where b1 > '0');

471

left(a1,7) left(a2,7)

472

1 - 01x 2 - 01x

473

1 - 02x 2 - 02x

474

explain extended select left(a1,7), left(a2,7)

475

from t1_16

476

where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');

477

id select_type table type possible_keys key key_len ref rows filtered Extra

478

1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where

479

2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where

480

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`))))

482

select left(a1,7), left(a2,7)

483

from t1_16

484

where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');

485

left(a1,7) left(a2,7)

486

1 - 01x 2 - 01x

487

1 - 02x 2 - 02x

488

explain extended select left(a1,7), left(a2,7)

489

from t1_16

490

where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');

491

id select_type table type possible_keys key key_len ref rows filtered Extra

492

1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where

493

2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where

494

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)))

496

select left(a1,7), left(a2,7)

497

from t1_16

498

where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');

499

left(a1,7) left(a2,7)

500

1 - 01x 2 - 01x

501

1 - 02x 2 - 02x

502

explain extended select left(a1,7), left(a2,7)

503

from t1_16

504

where a1 in (select group_concat(b1) from t2_16 group by b2);

505

id select_type table type possible_keys key key_len ref rows filtered Extra

506

1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where

507

2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort

508

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 ',')))))

510

select left(a1,7), left(a2,7)

511

from t1_16

512

where a1 in (select group_concat(b1) from t2_16 group by b2);

513

left(a1,7) left(a2,7)

514

1 - 01x 2 - 01x

515

1 - 02x 2 - 02x

516

set @@group_concat_max_len = 256;

517

explain extended select left(a1,7), left(a2,7)

518

from t1_16

519

where a1 in (select group_concat(b1) from t2_16 group by b2);

520

id select_type table type possible_keys key key_len ref rows filtered Extra

521

1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where

522

2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort

523

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)))

525

select left(a1,7), left(a2,7)

526

from t1_16

527

where a1 in (select group_concat(b1) from t2_16 group by b2);

528

left(a1,7) left(a2,7)

529

1 - 01x 2 - 01x

530

1 - 02x 2 - 02x

531

explain extended

532

select * from t1

533

where concat(a1,'x') IN

534

(select left(a1,8) from t1_16

535

where (a1, a2) IN

536

(select t2_16.b1, t2_16.b2 from t2_16, t2

537

where t2.b2 = substring(t2_16.b2,1,6) and

538

t2.b1 IN (select c1 from t3 where c2 > '0')));

539

id select_type table type possible_keys key key_len ref rows filtered Extra

540

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where

541

2 SUBQUERY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where

542

3 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where

543

3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer

544

4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where

545

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)))

547

drop table t1_16, t2_16, t3_16;

548

set @blob_len = 512;

549

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;

553

insert into t1_512 values

554

(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

555

insert into t1_512 values

556

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

557

insert into t1_512 values

558

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

559

insert into t2_512 values

560

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

561

insert into t2_512 values

562

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

563

insert into t2_512 values

564

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

565

insert into t3_512 values

566

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

567

insert into t3_512 values

568

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

569

insert into t3_512 values

570

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

571

insert into t3_512 values

572

(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

573

explain extended select left(a1,7), left(a2,7)

574

from t1_512

575

where a1 in (select b1 from t2_512 where b1 > '0');

576

id select_type table type possible_keys key key_len ref rows filtered Extra

577

1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where

578

2 DEPENDENT SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where

579

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`))))

581

select left(a1,7), left(a2,7)

582

from t1_512

583

where a1 in (select b1 from t2_512 where b1 > '0');

584

left(a1,7) left(a2,7)

585

1 - 01x 2 - 01x

586

1 - 02x 2 - 02x

587

explain extended select left(a1,7), left(a2,7)

588

from t1_512

589

where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');

590

id select_type table type possible_keys key key_len ref rows filtered Extra

591

1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where

592

2 DEPENDENT SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where

593

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`))))

595

select left(a1,7), left(a2,7)

596

from t1_512

597

where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');

598

left(a1,7) left(a2,7)

599

1 - 01x 2 - 01x

600

1 - 02x 2 - 02x

601

explain extended select left(a1,7), left(a2,7)

602

from t1_512

603

where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');

604

id select_type table type possible_keys key key_len ref rows filtered Extra

605

1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where

606

2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where

607

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)))

609

select left(a1,7), left(a2,7)

610

from t1_512

611

where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');

612

left(a1,7) left(a2,7)

613

1 - 01x 2 - 01x

614

1 - 02x 2 - 02x

615

explain extended select left(a1,7), left(a2,7)

616

from t1_512

617

where a1 in (select group_concat(b1) from t2_512 group by b2);

618

id select_type table type possible_keys key key_len ref rows filtered Extra

619

1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where

620

2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort

621

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)))

623

select left(a1,7), left(a2,7)

624

from t1_512

625

where a1 in (select group_concat(b1) from t2_512 group by b2);

626

left(a1,7) left(a2,7)

627

1 - 01x 2 - 01x

628

1 - 02x 2 - 02x

629

set @@group_concat_max_len = 256;

630

explain extended select left(a1,7), left(a2,7)

631

from t1_512

632

where a1 in (select group_concat(b1) from t2_512 group by b2);

633

id select_type table type possible_keys key key_len ref rows filtered Extra

634

1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where

635

2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort

636

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)))

638

select left(a1,7), left(a2,7)

639

from t1_512

640

where a1 in (select group_concat(b1) from t2_512 group by b2);

641

left(a1,7) left(a2,7)

642

1 - 01x 2 - 01x

643

1 - 02x 2 - 02x

644

drop table t1_512, t2_512, t3_512;

645

set @blob_len = 1024;

646

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;

650

insert into t1_1024 values

651

(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

652

insert into t1_1024 values

653

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

654

insert into t1_1024 values

655

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

656

insert into t2_1024 values

657

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

658

insert into t2_1024 values

659

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

660

insert into t2_1024 values

661

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

662

insert into t3_1024 values

663

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

664

insert into t3_1024 values

665

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

666

insert into t3_1024 values

667

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

668

insert into t3_1024 values

669

(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

670

explain extended select left(a1,7), left(a2,7)

671

from t1_1024

672

where a1 in (select b1 from t2_1024 where b1 > '0');

673

id select_type table type possible_keys key key_len ref rows filtered Extra

674

1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

675

2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

676

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`))))

678

select left(a1,7), left(a2,7)

679

from t1_1024

680

where a1 in (select b1 from t2_1024 where b1 > '0');

681

left(a1,7) left(a2,7)

682

1 - 01x 2 - 01x

683

1 - 02x 2 - 02x

684

explain extended select left(a1,7), left(a2,7)

685

from t1_1024

686

where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');

687

id select_type table type possible_keys key key_len ref rows filtered Extra

688

1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

689

2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

690

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`))))

692

select left(a1,7), left(a2,7)

693

from t1_1024

694

where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');

695

left(a1,7) left(a2,7)

696

1 - 01x 2 - 01x

697

1 - 02x 2 - 02x

698

explain extended select left(a1,7), left(a2,7)

699

from t1_1024

700

where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');

701

id select_type table type possible_keys key key_len ref rows filtered Extra

702

1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

703

2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

704

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)))))

706

select left(a1,7), left(a2,7)

707

from t1_1024

708

where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');

709

left(a1,7) left(a2,7)

710

1 - 01x 2 - 01x

711

1 - 02x 2 - 02x

712

explain extended select left(a1,7), left(a2,7)

713

from t1_1024

714

where a1 in (select group_concat(b1) from t2_1024 group by b2);

715

id select_type table type possible_keys key key_len ref rows filtered Extra

716

1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

717

2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort

718

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)))

720

select left(a1,7), left(a2,7)

721

from t1_1024

722

where a1 in (select group_concat(b1) from t2_1024 group by b2);

723

left(a1,7) left(a2,7)

724

1 - 01x 2 - 01x

725

1 - 02x 2 - 02x

726

set @@group_concat_max_len = 256;

727

explain extended select left(a1,7), left(a2,7)

728

from t1_1024

729

where a1 in (select group_concat(b1) from t2_1024 group by b2);

730

id select_type table type possible_keys key key_len ref rows filtered Extra

731

1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where

732

2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort

733

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)))

735

select left(a1,7), left(a2,7)

736

from t1_1024

737

where a1 in (select group_concat(b1) from t2_1024 group by b2);

738

left(a1,7) left(a2,7)

739

1 - 01x 2 - 01x

740

1 - 02x 2 - 02x

741

drop table t1_1024, t2_1024, t3_1024;

742

set @blob_len = 1025;

743

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;

747

insert into t1_1025 values

748

(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

749

insert into t1_1025 values

750

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

751

insert into t1_1025 values

752

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

753

insert into t2_1025 values

754

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

755

insert into t2_1025 values

756

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

757

insert into t2_1025 values

758

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

759

insert into t3_1025 values

760

(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

761

insert into t3_1025 values

762

(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

763

insert into t3_1025 values

764

(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

765

insert into t3_1025 values

766

(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

767

explain extended select left(a1,7), left(a2,7)

768

from t1_1025

769

where a1 in (select b1 from t2_1025 where b1 > '0');

770

id select_type table type possible_keys key key_len ref rows filtered Extra

771

1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

772

2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

773

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`))))

775

select left(a1,7), left(a2,7)

776

from t1_1025

777

where a1 in (select b1 from t2_1025 where b1 > '0');

778

left(a1,7) left(a2,7)

779

1 - 01x 2 - 01x

780

1 - 02x 2 - 02x

781

explain extended select left(a1,7), left(a2,7)

782

from t1_1025

783

where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');

784

id select_type table type possible_keys key key_len ref rows filtered Extra

785

1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

786

2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

787

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`))))

789

select left(a1,7), left(a2,7)

790

from t1_1025

791

where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');

792

left(a1,7) left(a2,7)

793

1 - 01x 2 - 01x

794

1 - 02x 2 - 02x

795

explain extended select left(a1,7), left(a2,7)

796

from t1_1025

797

where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');

798

id select_type table type possible_keys key key_len ref rows filtered Extra

799

1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

800

2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

801

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)))))

803

select left(a1,7), left(a2,7)

804

from t1_1025

805

where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');

806

left(a1,7) left(a2,7)

807

1 - 01x 2 - 01x

808

1 - 02x 2 - 02x

809

explain extended select left(a1,7), left(a2,7)

810

from t1_1025

811

where a1 in (select group_concat(b1) from t2_1025 group by b2);

812

id select_type table type possible_keys key key_len ref rows filtered Extra

813

1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

814

2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort

815

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)))

817

select left(a1,7), left(a2,7)

818

from t1_1025

819

where a1 in (select group_concat(b1) from t2_1025 group by b2);

820

left(a1,7) left(a2,7)

821

1 - 01x 2 - 01x

822

1 - 02x 2 - 02x

823

set @@group_concat_max_len = 256;

824

explain extended select left(a1,7), left(a2,7)

825

from t1_1025

826

where a1 in (select group_concat(b1) from t2_1025 group by b2);

827

id select_type table type possible_keys key key_len ref rows filtered Extra

828

1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where

829

2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort

830

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)))

832

select left(a1,7), left(a2,7)

833

from t1_1025

834

where a1 in (select group_concat(b1) from t2_1025 group by b2);

835

left(a1,7) left(a2,7)

836

1 - 01x 2 - 01x

837

1 - 02x 2 - 02x

838

drop table t1_1025, t2_1025, t3_1025;

839

drop table t1, t2, t3;

840

/******************************************************************************

841

* Test the cache of the left operand of IN.

842

******************************************************************************/

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;

846

insert into t1 values (5),(1),(0);

847

insert into t2 values (0), (1);

848

select s2 from t2 where s2 in (select s1 from t1);

849

s2

850

0

851

1

852

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;

856

insert into t1 values (1,10);

857

insert into t1 values (1,20);

858

insert into t1 values (2,10);

859

insert into t1 values (2,20);

860

insert into t1 values (2,30);

861

insert into t1 values (3,20);

862

insert into t1 values (4,40);

863

insert into t2 values (2,10);

864

insert into t2 values (2,20);

865

insert into t2 values (2,40);

866

insert into t2 values (3,20);

867

insert into t2 values (4,10);

868

insert into t2 values (5,10);

869

insert into t3 values (10);

870

insert into t3 values (10);

871

insert into t3 values (20);

872

insert into t3 values (30);

873

explain extended

874

select a from t1 where a in (select c from t2 where d >= 20);

875

id select_type table type possible_keys key key_len ref rows filtered Extra

876

1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where

877

2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where

878

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)))

880

select a from t1 where a in (select c from t2 where d >= 20);

881

a

882

2

883

2

884

2

885

3

886

create index it1a on t1(a);

887

explain extended

888

select a from t1 where a in (select c from t2 where d >= 20);

889

id select_type table type possible_keys key key_len ref rows filtered Extra

890

1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index

891

2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where

892

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)))

894

select a from t1 where a in (select c from t2 where d >= 20);

895

a

896

2

897

2

898

2

899

3

900

insert into t2 values (1,10);

901

explain extended

902

select a from t1 where a in (select c from t2 where d >= 20);

903

id select_type table type possible_keys key key_len ref rows filtered Extra

904

1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index

905

2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where

906

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)))

908

select a from t1 where a in (select c from t2 where d >= 20);

909

a

910

2

911

2

912

2

913

3

914

explain extended

915

select a from t1 group by a having a in (select c from t2 where d >= 20);

916

id select_type table type possible_keys key key_len ref rows filtered Extra

917

1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index

918

2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where

919

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)))

921

select a from t1 group by a having a in (select c from t2 where d >= 20);

922

a

923

2

924

3

925

create index iab on t1(a, b);

926

explain extended

927

select a from t1 group by a having a in (select c from t2 where d >= 20);

928

id select_type table type possible_keys key key_len ref rows filtered Extra

929

1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index

930

2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where

931

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)))

933

select a from t1 group by a having a in (select c from t2 where d >= 20);

934

a

935

2

936

3

937

explain extended

938

select a from t1 group by a

939

having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));

940

id select_type table type possible_keys key key_len ref rows filtered Extra

941

1 PRIMARY t1 index NULL iab 8 NULL 7 100.00 Using index

942

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where

943

3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where

944

Warnings:

945

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`))))

947

select a from t1 group by a

948

having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));

949

a

950

2

951

3

952

explain extended

953

select a from t1

954

where a in (select c from t2 where d >= some(select e from t3 where b=e));

955

id select_type table type possible_keys key key_len ref rows filtered Extra

956

1 PRIMARY t1 index NULL iab 8 NULL 7 100.00 Using where; Using index

957

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where

958

3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where

959

Warnings:

960

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`))))

962

select a from t1

963

where a in (select c from t2 where d >= some(select e from t3 where b=e));

964

a

965

1

966

2

967

2

968

2

969

3

970

drop table t1, t2, t3;