subselect_sj_mat.rdiff 25 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260
97,98c97,98
< 1	PRIMARY	t2i	index	it2i1,it2i3	it2i1	#	NULL	5	40.00	Using where; Using index; LooseScan
< 1	PRIMARY	t1i	ref	_it1_idx	_it1_idx	#	_ref_	1	100.00	
---
> 1	PRIMARY	t1i	index	_it1_idx	_it1_idx	#	NULL	3	33.33	Using where; 
> 1	PRIMARY	t2i	ref	it2i1,it2i3	it2i1	#	test.t1i.a1	1	100.00	Using index; Start temporary; End temporary
100c100
< Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
---
> Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t1i`.`a1` > '0'))
108,110c108,110
< 1	PRIMARY	t1i	index	it1i1,it1i3	#	18	#	3	100.00	#
< 1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	9	#	1	100.00	#
< 2	MATERIALIZED	t2i	index	it2i1,it2i3	#	9	#	5	100.00	#
---
> 1	PRIMARY	<subquery2>	ALL	distinct_key	#	NULL	#	1	100.00	#
> 1	PRIMARY	t1i	ref	it1i1,it1i3	#	9	#	1	100.00	#
> 2	MATERIALIZED	t2i	index	it2i1,it2i3	#	9	#	5	20.00	#
112c112
< Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`max(b1)` = `test`.`t1i`.`a1`)
---
> Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `<subquery2>`.`max(b1)`)
120,121c120,121
< 1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	#	NULL	5	40.00	Using where; Using index; LooseScan
< 1	PRIMARY	t1i	ref	_it1_idx	_it1_idx	#	_ref_	1	100.00	
---
> 1	PRIMARY	t1i	index	_it1_idx	_it1_idx	#	NULL	3	33.33	Using where; 
> 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	it2i3	#	test.t1i.a1,test.t1i.a2	1	100.00	Using index; Start temporary; End temporary
123c123
< Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
---
> Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t1i`.`a1` > '0'))
131,133c131,133
< 1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	100.00	#
< 1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	#	#	1	100.00	#
< 2	MATERIALIZED	t2i	range	it2i1,it2i3	#	#	#	3	100.00	#
---
> 1	PRIMARY	<subquery2>	ALL	distinct_key	#	#	#	1	100.00	#
> 1	PRIMARY	t1i	ref	it1i1,it1i2,it1i3	#	#	#	1	100.00	#
> 2	MATERIALIZED	t2i	range	it2i1,it2i3	#	#	#	1	100.00	#
135c135
< Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`<subquery2>`.`max(b2)` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`))
---
> Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `<subquery2>`.`max(b2)`) and (`test`.`t1i`.`a1` = `<subquery2>`.`b1`))
143,145c143,145
< 1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	100.00	#
< 1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	#	#	1	100.00	#
< 2	MATERIALIZED	t2i	range	it2i1,it2i3	#	#	#	3	100.00	#
---
> 1	PRIMARY	<subquery2>	ALL	distinct_key	#	#	#	1	100.00	#
> 1	PRIMARY	t1i	ref	it1i1,it1i2,it1i3	#	#	#	1	100.00	#
> 2	MATERIALIZED	t2i	range	it2i1,it2i3	#	#	#	1	100.00	#
147c147
< Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`<subquery2>`.`min(b2)` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`))
---
> Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `<subquery2>`.`min(b2)`) and (`test`.`t1i`.`a1` = `<subquery2>`.`b1`))
157c157
< 2	MATERIALIZED	t2i	range	NULL	it2i3	9	NULL	3	100.00	Using index for group-by
---
> 2	MATERIALIZED	t2i	index	NULL	it2i3	18	NULL	5	100.00	Using index
169c169
< 2	MATERIALIZED	t2i	range	NULL	it2i3	9	NULL	3	Using index for group-by
---
> 2	MATERIALIZED	t2i	index	NULL	it2i3	18	NULL	5	Using index
174c174
< 2	MATERIALIZED	t2i	range	NULL	it2i3	9	NULL	3	Using index for group-by
---
> 2	MATERIALIZED	t2i	index	NULL	it2i3	18	NULL	5	Using index
187,189c187,189
< 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
< 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
< 2	MATERIALIZED	t2i	range	it2i1,it2i3	it2i3	18	NULL	3	100.00	Using where; Using index for group-by
---
> 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	100.00	
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
> 2	MATERIALIZED	t2i	range	it2i1,it2i3	it2i3	18	NULL	1	100.00	Using where; Using index for group-by
191c191
< Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
---
> Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`test`.`t1`.`a2` = `<subquery2>`.`min(b2)`) and (`test`.`t1`.`a1` = `<subquery2>`.`b1`))
301,302c301,302
< 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
< 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
---
> 1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
304,305c304,305
< 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
< 3	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
---
> 3	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	20.00	Using where; Using index
> 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
308c308
< Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t2`.`b1` > '0') and (`test`.`t3`.`c2` > '0'))
---
> Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t3`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t1`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))
322,323c322
< 1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	40.00	#
< 1	PRIMARY	t1i	ref	it1i1,it1i2,it1i3	#	#	#	1	100.00	#
---
> 1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	33.33	#
325c324,325
< 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	#	#	#	2	100.00	#
---
> 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	#	#	#	1	100.00	#
> 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	#	#	#	1	100.00	#
327c327
< Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))
---
> Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t1i`.`a1` > '0') and (`test`.`t1i`.`a2` > '0'))
343,344c343,344
< 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
< 1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
---
> 1	PRIMARY	<subquery5>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
346,347c346,347
< 5	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
< 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
---
> 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	20.00	Using where; Using index
> 5	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
352c352
< Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3`.`c2` > '0'))
---
> Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t3`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t1`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1`.`a1` = `test`.`t2i`.`b1`) and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t2i`.`b2` > '0'))
372,373c372,373
< 5	MATERIALIZED	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
< 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
---
> 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	20.00	Using where; Using index
> 5	MATERIALIZED	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
378c378
< Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3c`.`c2` > '0'))
---
> Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t3c`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t2i`.`b2` > '0'))
401a402
> 1	PRIMARY	<subquery5>	ALL	distinct_key	#	#	#	4	100.00	#
403d403
< 1	PRIMARY	<subquery5>	eq_ref	distinct_key	#	#	#	1	100.00	#
404a405
> 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	20.00	#
406d406
< 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	80.00	#
410,411c410
< 7	UNION	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	40.00	#
< 7	UNION	t1i	ref	it1i1,it1i2,it1i3	#	#	#	1	100.00	#
---
> 7	UNION	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	33.33	#
413c412,413
< 7	UNION	t2i	ref	it2i1,it2i2,it2i3	#	#	#	2	100.00	#
---
> 7	UNION	t2i	ref	it2i1,it2i2,it2i3	#	#	#	1	100.00	#
> 7	UNION	t2i	ref	it2i1,it2i2,it2i3	#	#	#	1	100.00	#
416c416
< Note	1003	(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3`.`c2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0')))
---
> Note	1003	(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t3`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t1`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1`.`a1` = `test`.`t2i`.`b1`) and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t2i`.`b2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t1i`.`a1` > '0') and (`test`.`t1i`.`a2` > '0')))
438,441c438,441
< 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
< 1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
< 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
< 4	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
---
> 1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
> 4	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	20.00	Using where; Using index
> 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
446c446
< Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`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`,`test`.`t2`.`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 (`test`.`t3`.`c2` > '0'))
---
> Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t3`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t1`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1`.`a1` = `test`.`t2i`.`b1`) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`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`,`test`.`t2`.`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 (`test`.`t2i`.`b2` > '0'))
461a462
> 1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	Using where
463,465c464,465
< 1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
< 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
< 4	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
---
> 4	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	20.00	Using where; Using index
> 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
470c470
< 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` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`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`,`test`.`t2`.`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 (`test`.`t3`.`c2` > '0'))
---
> 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` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`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`,`test`.`t2`.`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 (`test`.`t2i`.`b2` > '0'))
509c509
< 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	it2i3	18	test.t1.a1,test.t1.a2	2	100.00	Using index; Start temporary
---
> 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	it2i3	18	test.t1.a1,test.t1.a2	1	100.00	Using index; Start temporary
1091c1091
< 1	PRIMARY	t1	ref	it1a	it1a	4	test.t2.c	2	100.00	Using index
---
> 1	PRIMARY	t1	ref	it1a	it1a	4	test.t2.c	1	100.00	Using index
1189c1189
< 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
---
> 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	1	Using index
1191c1191
< 2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
---
> 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	0	Using temporary
1200,1201c1200,1201
< 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
< 2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
---
> 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	1	Using index
> 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	0	Using temporary
1209,1210c1209,1210
< 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
< 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
---
> 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	1	Using index
> 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	0	Using where
1219c1219,1220
< 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
---
> 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	0	Using where; FirstMatch
> 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (flat, BNL join)
1226,1227c1227,1228
< 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
< 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
---
> 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	1	Using index
> 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	0	Using where
1275,1276c1276,1277
< 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
< 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	5	const	1	
---
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
> 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	5	test.t1.t1i	1	
1385,1386c1386,1388
< 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
< 3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
---
> 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	0	
> 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
> 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	0	
1512,1514c1514,1516
< 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
< 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
< 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Rowid-ordered scan
---
> 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
> 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using where
1832d1833
< 1	PRIMARY	t1	system	a	NULL	NULL	NULL	1	100.00	
1833a1835
> 1	PRIMARY	t1	ref	a	a	5	const	1	100.00	Using where; Using index
1836c1838
< Note	1003	select 8 AS `a` from dual where 1
---
> Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(8))
1868c1870,1873
< 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
---
> 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	0	Using where
> 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
> 1	PRIMARY	alias3	ref	c	c	11	test.t1.a	1	Using where
> 1	PRIMARY	alias4	ref	c	c	11	test.alias3.b	1	Using where; Using index; FirstMatch(alias2)