Debian Codegraph @

mysql-test/suite/ndb/r/ndb_endian.result

raw
1 create table t1_innodb (
2 t1 tinyint,
3 t1u tinyint unsigned,
4 s1 smallint,
5 s1u smallint unsigned,
6 m1 mediumint,
7 m1u mediumint unsigned,
8 i1 int,
9 i1u int unsigned,
10 b1 bigint,
11 b1u bigint unsigned,
12 f1 float,
13 d1 double,
14 dec1 decimal(10,4),
15 dec2 decimal(10,2)
16 ) engine=innodb;
17 insert into t1_innodb values (1,1,1,1,1,1,1,1,1,1,
18 1234.5678, 9876.21,
19 1234.5678, 9876.21);
20 create table t1 (
21 t1 tinyint,
22 t1u tinyint unsigned,
23 s1 smallint,
24 s1u smallint unsigned,
25 m1 mediumint,
26 m1u mediumint unsigned,
27 i1 int,
28 i1u int unsigned,
29 b1 bigint,
30 b1u bigint unsigned,
31 f1 float,
32 d1 double,
33 dec1 decimal(10,4),
34 dec2 decimal(10,2)
35 ) engine=ndbcluster;
36 insert into t1 values (1,1,1,1,1,1,1,1,1,1,
37 1234.5678, 9876.21,
38 1234.5678, 9876.21);
39 set @@optimizer_switch='block_nested_loop=off';
40 create index ix on t1(t1,t1u);
41 explain select count(*)
42 from t1_innodb as t1
43 straight_join t1 as t2
44 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t1u = t1.t1u;
45 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
46 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
47 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 2 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t1` is not null); Using MRR
48 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t2.t1,test.t1.t1u 2 100.00 Child of 't2' in pushed join@1
49 Warnings:
50 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t1u` = `test`.`t1`.`t1u`) and (`test`.`t3`.`t1` = `test`.`t2`.`t1`))
51 select count(*)
52 from t1_innodb as t1
53 straight_join t1 as t2
54 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t1u = t1.t1u;
55 count(*)
56 1
57 explain select count(*)
58 from t1_innodb as t1
59 straight_join t1 as t2
60 straight_join t1 as t3 on t3.t1u = t2.t1u and t3.t1 = t1.t1;
61 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
62 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
63 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t1u` is not null)
64 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t1.t1,test.t2.t1u 2 100.00 Child of 't2' in pushed join@1
65 Warnings:
66 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t1` = `test`.`t1`.`t1`) and (`test`.`t3`.`t1u` = `test`.`t2`.`t1u`))
67 select count(*)
68 from t1_innodb as t1
69 straight_join t1 as t2
70 straight_join t1 as t3 on t3.t1u = t2.t1u and t3.t1 = t1.t1;
71 count(*)
72 1
73 alter table t1 drop index ix;
74 create index ix on t1(s1,s1u);
75 explain select count(*)
76 from t1_innodb as t1
77 straight_join t1 as t2
78 straight_join t1 as t3 on t3.s1 = t2.s1 and t3.s1u = t1.s1u;
79 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
80 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
81 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 3 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`s1` is not null); Using MRR
82 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 6 test.t2.s1,test.t1.s1u 2 100.00 Child of 't2' in pushed join@1
83 Warnings:
84 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`s1u` = `test`.`t1`.`s1u`) and (`test`.`t3`.`s1` = `test`.`t2`.`s1`))
85 select count(*)
86 from t1_innodb as t1
87 straight_join t1 as t2
88 straight_join t1 as t3 on t3.s1 = t2.s1 and t3.s1u = t1.s1u;
89 count(*)
90 1
91 explain select count(*)
92 from t1_innodb as t1
93 straight_join t1 as t2
94 straight_join t1 as t3 on t3.s1u = t2.s1u and t3.s1 = t1.s1;
95 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
96 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
97 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`s1u` is not null)
98 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 6 test.t1.s1,test.t2.s1u 2 100.00 Child of 't2' in pushed join@1
99 Warnings:
100 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`s1` = `test`.`t1`.`s1`) and (`test`.`t3`.`s1u` = `test`.`t2`.`s1u`))
101 select count(*)
102 from t1_innodb as t1
103 straight_join t1 as t2
104 straight_join t1 as t3 on t3.s1u = t2.s1u and t3.s1 = t1.s1;
105 count(*)
106 1
107 alter table t1 drop index ix;
108 create index ix on t1(m1,m1u);
109 explain select count(*)
110 from t1_innodb as t1
111 straight_join t1 as t2
112 straight_join t1 as t3 on t3.m1 = t2.m1 and t3.m1u = t1.m1u;
113 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
114 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
115 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`m1` is not null); Using MRR
116 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t2.m1,test.t1.m1u 2 100.00 Child of 't2' in pushed join@1
117 Warnings:
118 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`m1u` = `test`.`t1`.`m1u`) and (`test`.`t3`.`m1` = `test`.`t2`.`m1`))
119 select count(*)
120 from t1_innodb as t1
121 straight_join t1 as t2
122 straight_join t1 as t3 on t3.m1 = t2.m1 and t3.m1u = t1.m1u;
123 count(*)
124 1
125 explain select count(*)
126 from t1_innodb as t1
127 straight_join t1 as t2
128 straight_join t1 as t3 on t3.m1u = t2.m1u and t3.m1 = t1.m1;
129 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
130 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
131 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`m1u` is not null)
132 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t1.m1,test.t2.m1u 2 100.00 Child of 't2' in pushed join@1
133 Warnings:
134 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`m1` = `test`.`t1`.`m1`) and (`test`.`t3`.`m1u` = `test`.`t2`.`m1u`))
135 select count(*)
136 from t1_innodb as t1
137 straight_join t1 as t2
138 straight_join t1 as t3 on t3.m1u = t2.m1u and t3.m1 = t1.m1;
139 count(*)
140 1
141 alter table t1 drop index ix;
142 create index ix on t1(i1,i1u);
143 explain select count(*)
144 from t1_innodb as t1
145 straight_join t1 as t2
146 straight_join t1 as t3 on t3.i1 = t2.i1 and t3.i1u = t1.i1u;
147 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
148 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
149 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`i1` is not null); Using MRR
150 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t2.i1,test.t1.i1u 2 100.00 Child of 't2' in pushed join@1
151 Warnings:
152 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`i1u` = `test`.`t1`.`i1u`) and (`test`.`t3`.`i1` = `test`.`t2`.`i1`))
153 select count(*)
154 from t1_innodb as t1
155 straight_join t1 as t2
156 straight_join t1 as t3 on t3.i1 = t2.i1 and t3.i1u = t1.i1u;
157 count(*)
158 1
159 explain select count(*)
160 from t1_innodb as t1
161 straight_join t1 as t2
162 straight_join t1 as t3 on t3.i1u = t2.i1u and t3.i1 = t1.i1;
163 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
164 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
165 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`i1u` is not null)
166 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t1.i1,test.t2.i1u 2 100.00 Child of 't2' in pushed join@1
167 Warnings:
168 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`i1` = `test`.`t1`.`i1`) and (`test`.`t3`.`i1u` = `test`.`t2`.`i1u`))
169 select count(*)
170 from t1_innodb as t1
171 straight_join t1 as t2
172 straight_join t1 as t3 on t3.i1u = t2.i1u and t3.i1 = t1.i1;
173 count(*)
174 1
175 alter table t1 drop index ix;
176 create index ix on t1(b1,b1u);
177 explain select count(*)
178 from t1_innodb as t1
179 straight_join t1 as t2
180 straight_join t1 as t3 on t3.b1 = t2.b1 and t3.b1u = t1.b1u;
181 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
182 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
183 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 9 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`b1` is not null); Using MRR
184 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 18 test.t2.b1,test.t1.b1u 2 100.00 Child of 't2' in pushed join@1
185 Warnings:
186 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`b1u` = `test`.`t1`.`b1u`) and (`test`.`t3`.`b1` = `test`.`t2`.`b1`))
187 select count(*)
188 from t1_innodb as t1
189 straight_join t1 as t2
190 straight_join t1 as t3 on t3.b1 = t2.b1 and t3.b1u = t1.b1u;
191 count(*)
192 1
193 explain select count(*)
194 from t1_innodb as t1
195 straight_join t1 as t2
196 straight_join t1 as t3 on t3.b1u = t2.b1u and t3.b1 = t1.b1;
197 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
198 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
199 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`b1u` is not null)
200 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 18 test.t1.b1,test.t2.b1u 2 100.00 Child of 't2' in pushed join@1
201 Warnings:
202 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`b1` = `test`.`t1`.`b1`) and (`test`.`t3`.`b1u` = `test`.`t2`.`b1u`))
203 select count(*)
204 from t1_innodb as t1
205 straight_join t1 as t2
206 straight_join t1 as t3 on t3.b1u = t2.b1u and t3.b1 = t1.b1;
207 count(*)
208 1
209 alter table t1 drop index ix;
210 create index ix on t1(f1,d1);
211 explain select count(*)
212 from t1_innodb as t1
213 straight_join t1 as t2
214 straight_join t1 as t3 on t3.f1 = t2.f1 and t3.d1 = t1.d1;
215 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
216 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
217 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`f1` is not null); Using MRR
218 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 14 test.t2.f1,test.t1.d1 2 100.00 Child of 't2' in pushed join@1
219 Warnings:
220 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`d1` = `test`.`t1`.`d1`) and (`test`.`t3`.`f1` = `test`.`t2`.`f1`))
221 select count(*)
222 from t1_innodb as t1
223 straight_join t1 as t2
224 straight_join t1 as t3 on t3.f1 = t2.f1 and t3.d1 = t1.d1;
225 count(*)
226 1
227 explain select count(*)
228 from t1_innodb as t1
229 straight_join t1 as t2
230 straight_join t1 as t3 on t3.d1 = t2.d1 and t3.f1 = t1.f1;
231 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
232 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
233 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`d1` is not null)
234 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 14 test.t1.f1,test.t2.d1 2 100.00 Child of 't2' in pushed join@1
235 Warnings:
236 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`f1` = `test`.`t1`.`f1`) and (`test`.`t3`.`d1` = `test`.`t2`.`d1`))
237 select count(*)
238 from t1_innodb as t1
239 straight_join t1 as t2
240 straight_join t1 as t3 on t3.d1 = t2.d1 and t3.f1 = t1.f1;
241 count(*)
242 1
243 alter table t1 drop index ix;
244 create index ix on t1(dec1,dec2);
245 explain select count(*)
246 from t1_innodb as t1
247 straight_join t1 as t2
248 straight_join t1 as t3 on t3.dec1 = t2.dec1 and t3.dec2 = t1.dec2;
249 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
250 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
251 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 6 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dec1` is not null); Using MRR
252 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t2.dec1,test.t1.dec2 2 100.00 Child of 't2' in pushed join@1
253 Warnings:
254 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dec2` = `test`.`t1`.`dec2`) and (`test`.`t3`.`dec1` = `test`.`t2`.`dec1`))
255 select count(*)
256 from t1_innodb as t1
257 straight_join t1 as t2
258 straight_join t1 as t3 on t3.dec1 = t2.dec1 and t3.dec2 = t1.dec2;
259 count(*)
260 1
261 explain select count(*)
262 from t1_innodb as t1
263 straight_join t1 as t2
264 straight_join t1 as t3 on t3.dec2 = t2.dec2 and t3.dec1 = t1.dec1;
265 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
266 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
267 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dec2` is not null)
268 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t1.dec1,test.t2.dec2 2 100.00 Child of 't2' in pushed join@1
269 Warnings:
270 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dec1` = `test`.`t1`.`dec1`) and (`test`.`t3`.`dec2` = `test`.`t2`.`dec2`))
271 select count(*)
272 from t1_innodb as t1
273 straight_join t1 as t2
274 straight_join t1 as t3 on t3.dec2 = t2.dec2 and t3.dec1 = t1.dec1;
275 count(*)
276 1
277 alter table t1 drop index ix;
278 drop table t1;
279 drop table t1_innodb;
280 create table t1_innodb (
281 c1 char,
282 c2 char(16),
283 vc1 varchar(32),
284 vc2 varchar(512)
285 ) engine=innodb;
286 insert into t1_innodb values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
287 'A long varchar, not that long really...');
288 create table t1 (
289 c1 char,
290 c2 char(16),
291 vc1 varchar(32),
292 vc2 varchar(512)
293 ) engine=ndbcluster;
294 insert into t1 values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
295 'A long varchar, not that long really...');
296 create index ix on t1(c1,c2);
297 explain select count(*)
298 from t1_innodb as t1
299 straight_join t1 as t2
300 straight_join t1 as t3 on t3.c1 = t2.c1 and t3.c2 = t1.c2;
301 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
302 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
303 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`c1` is not null); Using MRR
304 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 70 test.t2.c1,test.t1.c2 2 100.00 Child of 't2' in pushed join@1
305 Warnings:
306 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`c2` = `test`.`t1`.`c2`) and (`test`.`t3`.`c1` = `test`.`t2`.`c1`))
307 select count(*)
308 from t1_innodb as t1
309 straight_join t1 as t2
310 straight_join t1 as t3 on t3.c1 = t2.c1 and t3.c2 = t1.c2;
311 count(*)
312 1
313 explain select count(*)
314 from t1_innodb as t1
315 straight_join t1 as t2
316 straight_join t1 as t3 on t3.c2 = t2.c2 and t3.c1 = t1.c1;
317 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
318 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
319 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`c2` is not null)
320 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 70 test.t1.c1,test.t2.c2 2 100.00 Child of 't2' in pushed join@1
321 Warnings:
322 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`c1`) and (`test`.`t3`.`c2` = `test`.`t2`.`c2`))
323 select count(*)
324 from t1_innodb as t1
325 straight_join t1 as t2
326 straight_join t1 as t3 on t3.c2 = t2.c2 and t3.c1 = t1.c1;
327 count(*)
328 1
329 alter table t1 drop index ix;
330 create index ix on t1(vc1,vc2);
331 explain select count(*)
332 from t1_innodb as t1
333 straight_join t1 as t2
334 straight_join t1 as t3 on t3.vc1 = t2.vc1 and t3.vc2 = t1.vc2;
335 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
336 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
337 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 131 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`vc1` is not null); Using MRR
338 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 2182 test.t2.vc1,test.t1.vc2 2 100.00 Child of 't2' in pushed join@1
339 Warnings:
340 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`vc2` = `test`.`t1`.`vc2`) and (`test`.`t3`.`vc1` = `test`.`t2`.`vc1`))
341 select count(*)
342 from t1_innodb as t1
343 straight_join t1 as t2
344 straight_join t1 as t3 on t3.vc1 = t2.vc1 and t3.vc2 = t1.vc2;
345 count(*)
346 1
347 explain select count(*)
348 from t1_innodb as t1
349 straight_join t1 as t2
350 straight_join t1 as t3 on t3.vc2 = t2.vc2 and t3.vc1 = t1.vc1;
351 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
352 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
353 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`vc2` is not null)
354 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 2182 test.t1.vc1,test.t2.vc2 2 100.00 Child of 't2' in pushed join@1
355 Warnings:
356 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`vc1` = `test`.`t1`.`vc1`) and (`test`.`t3`.`vc2` = `test`.`t2`.`vc2`))
357 select count(*)
358 from t1_innodb as t1
359 straight_join t1 as t2
360 straight_join t1 as t3 on t3.vc2 = t2.vc2 and t3.vc1 = t1.vc1;
361 count(*)
362 1
363 alter table t1 drop index ix;
364 drop table t1;
365 drop table t1_innodb;
366 create table t1_innodb (
367 y1 year,
368 y2 year(4),
369 d1 date,
370 d2 date,
371 t1 time,
372 t2 time(0),
373 t3 time(6),
374 dt1 datetime,
375 dt2 datetime(0),
376 dt3 datetime(6),
377 ts1 timestamp,
378 ts2 timestamp(0),
379 ts3 timestamp(6)
380 ) engine=innodb;
381 Warnings:
382 Warning 1287 'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
383 insert into t1_innodb values (99, 2018, '2018-12-04', '2005-01-26',
384 '12:00:00', '11:59:59', '11:59:59.999',
385 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
386 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
387 );
388 create table t1 (
389 y1 year,
390 y2 year(4),
391 d1 date,
392 d2 date,
393 t1 time,
394 t2 time(0),
395 t3 time(6),
396 dt1 datetime,
397 dt2 datetime(0),
398 dt3 datetime(6),
399 ts1 timestamp,
400 ts2 timestamp(0),
401 ts3 timestamp(6)
402 ) engine=ndbcluster;
403 Warnings:
404 Warning 1287 'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
405 insert into t1 values (99, 2018, '2018-12-04', '2005-01-26',
406 '12:00:00', '11:59:59', '11:59:59.999',
407 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
408 '2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
409 );
410 create index ix on t1(y1,y2);
411 explain select count(*)
412 from t1_innodb as t1
413 straight_join t1 as t2
414 straight_join t1 as t3 on t3.y1 = t2.y1 and t3.y2 = t1.y2;
415 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
416 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
417 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 2 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`y1` is not null); Using MRR
418 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t2.y1,test.t1.y2 2 100.00 Child of 't2' in pushed join@1
419 Warnings:
420 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`y2` = `test`.`t1`.`y2`) and (`test`.`t3`.`y1` = `test`.`t2`.`y1`))
421 select count(*)
422 from t1_innodb as t1
423 straight_join t1 as t2
424 straight_join t1 as t3 on t3.y1 = t2.y1 and t3.y2 = t1.y2;
425 count(*)
426 1
427 explain select count(*)
428 from t1_innodb as t1
429 straight_join t1 as t2
430 straight_join t1 as t3 on t3.y2 = t2.y2 and t3.y1 = t1.y1;
431 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
432 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
433 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`y2` is not null)
434 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t1.y1,test.t2.y2 2 100.00 Child of 't2' in pushed join@1
435 Warnings:
436 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`y1` = `test`.`t1`.`y1`) and (`test`.`t3`.`y2` = `test`.`t2`.`y2`))
437 select count(*)
438 from t1_innodb as t1
439 straight_join t1 as t2
440 straight_join t1 as t3 on t3.y2 = t2.y2 and t3.y1 = t1.y1;
441 count(*)
442 1
443 alter table t1 drop index ix;
444 create index ix on t1(d1,d2);
445 explain select count(*)
446 from t1_innodb as t1
447 straight_join t1 as t2
448 straight_join t1 as t3 on t3.d1 = t2.d1 and t3.d2 = t1.d2;
449 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
450 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
451 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`d1` is not null); Using MRR
452 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t2.d1,test.t1.d2 2 100.00 Child of 't2' in pushed join@1
453 Warnings:
454 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`d2` = `test`.`t1`.`d2`) and (`test`.`t3`.`d1` = `test`.`t2`.`d1`))
455 select count(*)
456 from t1_innodb as t1
457 straight_join t1 as t2
458 straight_join t1 as t3 on t3.d1 = t2.d1 and t3.d2 = t1.d2;
459 count(*)
460 1
461 alter table t1 drop index ix;
462 create index ix on t1(t1,t2,t3);
463 explain select count(*)
464 from t1_innodb as t1
465 straight_join t1 as t2
466 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t1.t2;
467 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
468 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
469 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t1` is not null); Using MRR
470 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t2.t1,test.t1.t2 2 100.00 Child of 't2' in pushed join@1
471 Warnings:
472 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t2` = `test`.`t1`.`t2`) and (`test`.`t3`.`t1` = `test`.`t2`.`t1`))
473 select count(*)
474 from t1_innodb as t1
475 straight_join t1 as t2
476 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t1.t2;
477 count(*)
478 1
479 explain select count(*)
480 from t1_innodb as t1
481 straight_join t1 as t2
482 straight_join t1 as t3 on t3.t2 = t2.t2 and t3.t1 = t1.t1;
483 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
484 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
485 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t2` is not null)
486 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t1.t1,test.t2.t2 2 100.00 Child of 't2' in pushed join@1
487 Warnings:
488 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t1` = `test`.`t1`.`t1`) and (`test`.`t3`.`t2` = `test`.`t2`.`t2`))
489 select count(*)
490 from t1_innodb as t1
491 straight_join t1 as t2
492 straight_join t1 as t3 on t3.t2 = t2.t2 and t3.t1 = t1.t1;
493 count(*)
494 1
495 explain select count(*)
496 from t1_innodb as t1
497 straight_join t1 as t2
498 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t2.t2 and t3.t3 = t1.t3;
499 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
500 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
501 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`t1` is not null) and (`test`.`t2`.`t2` is not null)); Using MRR
502 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 15 test.t2.t1,test.t2.t2,test.t1.t3 2 100.00 Child of 't2' in pushed join@1
503 Warnings:
504 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t3` = `test`.`t1`.`t3`) and (`test`.`t3`.`t2` = `test`.`t2`.`t2`) and (`test`.`t3`.`t1` = `test`.`t2`.`t1`))
505 select count(*)
506 from t1_innodb as t1
507 straight_join t1 as t2
508 straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t2.t2 and t3.t3 = t1.t3;
509 count(*)
510 1
511 alter table t1 drop index ix;
512 create index ix on t1(dt1,dt2,dt3);
513 explain select count(*)
514 from t1_innodb as t1
515 straight_join t1 as t2
516 straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t1.dt2;
517 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
518 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
519 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 6 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dt1` is not null); Using MRR
520 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t2.dt1,test.t1.dt2 2 100.00 Child of 't2' in pushed join@1
521 Warnings:
522 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dt2` = `test`.`t1`.`dt2`) and (`test`.`t3`.`dt1` = `test`.`t2`.`dt1`))
523 select count(*)
524 from t1_innodb as t1
525 straight_join t1 as t2
526 straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t1.dt2;
527 count(*)
528 1
529 explain select count(*)
530 from t1_innodb as t1
531 straight_join t1 as t2
532 straight_join t1 as t3 on t3.dt2 = t2.dt2 and t3.dt1 = t1.dt1;
533 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
534 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
535 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dt2` is not null)
536 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t1.dt1,test.t2.dt2 2 100.00 Child of 't2' in pushed join@1
537 Warnings:
538 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dt1` = `test`.`t1`.`dt1`) and (`test`.`t3`.`dt2` = `test`.`t2`.`dt2`))
539 select count(*)
540 from t1_innodb as t1
541 straight_join t1 as t2
542 straight_join t1 as t3 on t3.dt2 = t2.dt2 and t3.dt1 = t1.dt1;
543 count(*)
544 1
545 explain select count(*)
546 from t1_innodb as t1
547 straight_join t1 as t2
548 straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t2.dt2 and t3.dt3 = t1.dt3;
549 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
550 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
551 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 6 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`dt1` is not null) and (`test`.`t2`.`dt2` is not null)); Using MRR
552 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 21 test.t2.dt1,test.t2.dt2,test.t1.dt3 2 100.00 Child of 't2' in pushed join@1
553 Warnings:
554 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dt3` = `test`.`t1`.`dt3`) and (`test`.`t3`.`dt2` = `test`.`t2`.`dt2`) and (`test`.`t3`.`dt1` = `test`.`t2`.`dt1`))
555 select count(*)
556 from t1_innodb as t1
557 straight_join t1 as t2
558 straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t2.dt2 and t3.dt3 = t1.dt3;
559 count(*)
560 1
561 alter table t1 drop index ix;
562 create index ix on t1(ts1,ts2,ts3);
563 explain select count(*)
564 from t1_innodb as t1
565 straight_join t1 as t2
566 straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t1.ts2;
567 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
568 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
569 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`ts1` is not null); Using MRR
570 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t2.ts1,test.t1.ts2 2 100.00 Child of 't2' in pushed join@1
571 Warnings:
572 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`ts2` = `test`.`t1`.`ts2`) and (`test`.`t3`.`ts1` = `test`.`t2`.`ts1`))
573 select count(*)
574 from t1_innodb as t1
575 straight_join t1 as t2
576 straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t1.ts2;
577 count(*)
578 1
579 explain select count(*)
580 from t1_innodb as t1
581 straight_join t1 as t2
582 straight_join t1 as t3 on t3.ts2 = t2.ts2 and t3.ts1 = t1.ts1;
583 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
584 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
585 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`ts2` is not null)
586 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t1.ts1,test.t2.ts2 2 100.00 Child of 't2' in pushed join@1
587 Warnings:
588 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`ts1` = `test`.`t1`.`ts1`) and (`test`.`t3`.`ts2` = `test`.`t2`.`ts2`))
589 select count(*)
590 from t1_innodb as t1
591 straight_join t1 as t2
592 straight_join t1 as t3 on t3.ts2 = t2.ts2 and t3.ts1 = t1.ts1;
593 count(*)
594 1
595 explain select count(*)
596 from t1_innodb as t1
597 straight_join t1 as t2
598 straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t2.ts2 and t3.ts3 = t1.ts3;
599 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
600 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
601 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`ts1` is not null) and (`test`.`t2`.`ts2` is not null)); Using MRR
602 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 18 test.t2.ts1,test.t2.ts2,test.t1.ts3 2 100.00 Child of 't2' in pushed join@1
603 Warnings:
604 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`ts3` = `test`.`t1`.`ts3`) and (`test`.`t3`.`ts2` = `test`.`t2`.`ts2`) and (`test`.`t3`.`ts1` = `test`.`t2`.`ts1`))
605 select count(*)
606 from t1_innodb as t1
607 straight_join t1 as t2
608 straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t2.ts2 and t3.ts3 = t1.ts3;
609 count(*)
610 1
611 alter table t1 drop index ix;
612 drop table t1;
613 drop table t1_innodb;
614 set @@optimizer_switch='block_nested_loop=default';