Debian Codegraph @

mysql-test/suite/ndb_opt/condition_pushdown.result

raw
1 CREATE TABLE t1 (
2 auto int(5) unsigned NOT NULL auto_increment,
3 string char(10),
4 vstring varchar(10),
5 bin binary(2),
6 vbin varbinary(7),
7 tiny tinyint(4) DEFAULT '0' NOT NULL ,
8 short smallint(6) DEFAULT '1' NOT NULL ,
9 medium mediumint(8) DEFAULT '0' NOT NULL,
10 long_int int(11) DEFAULT '0' NOT NULL,
11 longlong bigint(13) DEFAULT '0' NOT NULL,
12 real_float float(13,1) DEFAULT 0.0 NOT NULL,
13 real_double double(16,4),
14 real_decimal decimal(16,4),
15 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
16 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
17 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
18 ulong int(11) unsigned DEFAULT '0' NOT NULL,
19 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
20 bits bit(3),
21 options enum('zero','one','two','three','four') not null,
22 flags set('zero','one','two','three','four') not null,
23 date_field date,
24 year_field year,
25 time_field time,
26 date_time datetime,
27 time_stamp timestamp,
28 PRIMARY KEY (auto)
29 ) engine=ndb
30 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
31 Warnings:
32 Warning 1681 Integer display width is deprecated and will be removed in a future release.
33 Warning 1681 Integer display width is deprecated and will be removed in a future release.
34 Warning 1681 Integer display width is deprecated and will be removed in a future release.
35 Warning 1681 Integer display width is deprecated and will be removed in a future release.
36 Warning 1681 Integer display width is deprecated and will be removed in a future release.
37 Warning 1681 Integer display width is deprecated and will be removed in a future release.
38 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
39 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
40 Warning 1681 Integer display width is deprecated and will be removed in a future release.
41 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
42 Warning 1681 Integer display width is deprecated and will be removed in a future release.
43 Warning 1681 Integer display width is deprecated and will be removed in a future release.
44 Warning 1681 Integer display width is deprecated and will be removed in a future release.
45 Warning 1681 Integer display width is deprecated and will be removed in a future release.
46 insert into t1 values
47 (NULL,"aaaa","aaaa",0xAAAA,0xAAAA,-1,-1,-1,-1,-1,1.1,1.1,1.1,1,1,1,1,1,
48 b'001','one','one',
49 '1901-01-01','1901',
50 '01:01:01','1901-01-01 01:01:01',NULL),
51 (NULL,"bbbb","bbbb",0xBBBB,0xBBBB,-2,-2,-2,-2,-2,2.2,2.2,2.2,2,2,2,2,2,
52 b'010','two','one,two',
53 '1902-02-02','1902',
54 '02:02:02','1902-02-02 02:02:02',NULL),
55 (NULL,"cccc","cccc",0xCCCC,0xCCCC,-3,-3,-3,-3,-3,3.3,3.3,3.3,3,3,3,3,3,
56 b'011','three','one,two,three',
57 '1903-03-03','1903',
58 '03:03:03','1903-03-03 03:03:03',NULL),
59 (NULL,"dddd","dddd",0xDDDD,0xDDDD,-4,-4,-4,-4,-4,4.4,4.4,4.4,4,4,4,4,4,
60 b'100','four','one,two,three,four',
61 '1904-04-04','1904',
62 '04:04:04','1904-04-04 04:04:04',NULL);
63 CREATE TABLE t2 (pk1 int unsigned NOT NULL PRIMARY KEY, attr1 int unsigned NOT NULL, attr2 int unsigned, attr3 VARCHAR(10) )
64 ENGINE=ndbcluster
65 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
66 insert into t2 values (0,0,0, "a"),(1,1,1,"b"),(2,2,NULL,NULL),(3,3,3,"d"),(4,4,4,"e"),(5,5,5,"f");
67 CREATE TABLE t3 (pk1 int unsigned NOT NULL PRIMARY KEY, attr1 int unsigned NOT NULL, attr2 bigint unsigned, attr3 tinyint unsigned, attr4 VARCHAR(10) )
68 ENGINE=ndbcluster
69 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
70 insert into t3 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");
71 CREATE TABLE t4 (pk1 int unsigned NOT NULL PRIMARY KEY, attr1 int unsigned NOT NULL, attr2 bigint unsigned, attr3 tinyint unsigned, attr4 VARCHAR(10) , KEY (attr1)) ENGINE=ndbcluster
72 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
73 insert into t4 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");
74 set @old_ecpd = @@session.optimizer_switch;
75 set @@optimizer_switch = 'engine_condition_pushdown=off';
76 select auto from t1 where
77 string = "aaaa" and
78 vstring = "aaaa" and
79 bin = 0xAAAA and
80 vbin = 0xAAAA and
81 tiny = -1 and
82 short = -1 and
83 medium = -1 and
84 long_int = -1 and
85 longlong = -1 and
86 real_float > 1.0 and real_float < 2.0 and
87 real_double > 1.0 and real_double < 2.0 and
88 real_decimal > 1.0 and real_decimal < 2.0 and
89 utiny = 1 and
90 ushort = 1 and
91 umedium = 1 and
92 ulong = 1 and
93 ulonglong = 1 and
94 bits = b'001' and
95 options = 'one' and
96 flags = 'one' and
97 date_field = '1901-01-01' and
98 year_field = '1901' and
99 time_field = '01:01:01' and
100 date_time = '1901-01-01 01:01:01'
101 order by auto;
102 auto
103 1
104 select auto from t1 where
105 string != "aaaa" and
106 vstring != "aaaa" and
107 bin != 0xAAAA and
108 vbin != 0xAAAA and
109 tiny != -1 and
110 short != -1 and
111 medium != -1 and
112 long_int != -1 and
113 longlong != -1 and
114 (real_float < 1.0 or real_float > 2.0) and
115 (real_double < 1.0 or real_double > 2.0) and
116 (real_decimal < 1.0 or real_decimal > 2.0) and
117 utiny != 1 and
118 ushort != 1 and
119 umedium != 1 and
120 ulong != 1 and
121 ulonglong != 1 and
122 bits != b'001' and
123 options != 'one' and
124 flags != 'one' and
125 date_field != '1901-01-01' and
126 year_field != '1901' and
127 time_field != '01:01:01' and
128 date_time != '1901-01-01 01:01:01'
129 order by auto;
130 auto
131 2
132 3
133 4
134 select auto from t1 where
135 string > "aaaa" and
136 vstring > "aaaa" and
137 bin > 0xAAAA and
138 vbin > 0xAAAA and
139 tiny < -1 and
140 short < -1 and
141 medium < -1 and
142 long_int < -1 and
143 longlong < -1 and
144 real_float > 1.1 and
145 real_double > 1.1 and
146 real_decimal > 1.1 and
147 utiny > 1 and
148 ushort > 1 and
149 umedium > 1 and
150 ulong > 1 and
151 ulonglong > 1 and
152 bits > b'001' and
153 (options = 'two' or options = 'three' or options = 'four') and
154 (flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
155 date_field > '1901-01-01' and
156 year_field > '1901' and
157 time_field > '01:01:01' and
158 date_time > '1901-01-01 01:01:01'
159 order by auto;
160 auto
161 2
162 3
163 4
164 select auto from t1 where
165 string >= "aaaa" and
166 vstring >= "aaaa" and
167 bin >= 0xAAAA and
168 vbin >= 0xAAAA and
169 tiny <= -1 and
170 short <= -1 and
171 medium <= -1 and
172 long_int <= -1 and
173 longlong <= -1 and
174 real_float >= 1.0 and
175 real_double >= 1.0 and
176 real_decimal >= 1.0 and
177 utiny >= 1 and
178 ushort >= 1 and
179 umedium >= 1 and
180 ulong >= 1 and
181 ulonglong >= 1 and
182 bits >= b'001' and
183 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
184 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
185 date_field >= '1901-01-01' and
186 year_field >= '1901' and
187 time_field >= '01:01:01' and
188 date_time >= '1901-01-01 01:01:01'
189 order by auto;
190 auto
191 1
192 2
193 3
194 4
195 select auto from t1 where
196 string < "dddd" and
197 vstring < "dddd" and
198 bin < 0xDDDD and
199 vbin < 0xDDDD and
200 tiny > -4 and
201 short > -4 and
202 medium > -4 and
203 long_int > -4 and
204 longlong > -4 and
205 real_float < 4.4 and
206 real_double < 4.4 and
207 real_decimal < 4.4 and
208 utiny < 4 and
209 ushort < 4 and
210 umedium < 4 and
211 ulong < 4 and
212 ulonglong < 4 and
213 bits < b'100' and
214 (options = 'one' or options = 'two' or options = 'three') and
215 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
216 date_field < '1904-01-01' and
217 year_field < '1904' and
218 time_field < '04:04:04' and
219 date_time < '1904-04-04 04:04:04'
220 order by auto;
221 auto
222 1
223 2
224 3
225 select auto from t1 where
226 string <= "dddd" and
227 vstring <= "dddd" and
228 bin <= 0xDDDD and
229 vbin <= 0xDDDD and
230 tiny >= -4 and
231 short >= -4 and
232 medium >= -4 and
233 long_int >= -4 and
234 longlong >= -4 and
235 real_float <= 4.5 and
236 real_double <= 4.5 and
237 real_decimal <= 4.5 and
238 utiny <= 4 and
239 ushort <= 4 and
240 umedium <= 4 and
241 ulong <= 4 and
242 ulonglong <= 4 and
243 bits <= b'100' and
244 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
245 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
246 date_field <= '1904-04-04' and
247 year_field <= '1904' and
248 time_field <= '04:04:04' and
249 date_time <= '1904-04-04 04:04:04'
250 order by auto;
251 auto
252 1
253 2
254 3
255 4
256 select auto from t1 where
257 string like "b%" and
258 vstring like "b%" and
259 bin like concat(0xBB, '%') and
260 vbin like concat(0xBB, '%')
261 order by auto;
262 auto
263 2
264 select auto from t1 where
265 string not like "b%" and
266 vstring not like "b%" and
267 bin not like concat(0xBB, '%') and
268 vbin not like concat(0xBB, '%')
269 order by auto;
270 auto
271 1
272 3
273 4
274 select auto from t1 where
275 (string between "aaaa" and "cccc") and
276 (vstring between "aaaa" and "cccc") and
277 (bin between 0xAAAA and 0xCCCC) and
278 (vbin between 0xAAAA and 0xCCCC) and
279 (tiny between -3 and -1) and
280 (short between -3 and -1) and
281 (medium between -3 and -1) and
282 (long_int between -3 and -1) and
283 (longlong between -3 and -1) and
284 (utiny between 1 and 3) and
285 (ushort between 1 and 3) and
286 (umedium between 1 and 3) and
287 (ulong between 1 and 3) and
288 (ulonglong between 1 and 3) and
289 (bits between b'001' and b'011') and
290 (options between 'one' and 'three') and
291 (flags between 'one' and 'one,two,three') and
292 (date_field between '1901-01-01' and '1903-03-03') and
293 (year_field between '1901' and '1903') and
294 (time_field between '01:01:01' and '03:03:03') and
295 (date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
296 order by auto;
297 auto
298 1
299 3
300 select auto from t1 where
301 ("aaaa" between string and string) and
302 ("aaaa" between vstring and vstring) and
303 (0xAAAA between bin and bin) and
304 (0xAAAA between vbin and vbin) and
305 (-1 between tiny and tiny) and
306 (-1 between short and short) and
307 (-1 between medium and medium) and
308 (-1 between long_int and long_int) and
309 (-1 between longlong and longlong) and
310 (1 between utiny and utiny) and
311 (1 between ushort and ushort) and
312 (1 between umedium and umedium) and
313 (1 between ulong and ulong) and
314 (1 between ulonglong and ulonglong) and
315 (b'001' between bits and bits) and
316 ('one' between options and options) and
317 ('one' between flags and flags) and
318 ('1901-01-01' between date_field and date_field) and
319 ('1901' between year_field and year_field) and
320 ('01:01:01' between time_field and time_field) and
321 ('1901-01-01 01:01:01' between date_time and date_time)
322 order by auto;
323 auto
324 1
325 select auto from t1 where
326 (string not between "aaaa" and "cccc") and
327 (vstring not between "aaaa" and "cccc") and
328 (bin not between 0xAAAA and 0xCCCC) and
329 (vbin not between 0xAAAA and 0xCCCC) and
330 (tiny not between -3 and -1) and
331 (short not between -3 and -1) and
332 (medium not between -3 and -1) and
333 (long_int not between -3 and -1) and
334 (longlong not between -3 and -1) and
335 (utiny not between 1 and 3) and
336 (ushort not between 1 and 3) and
337 (umedium not between 1 and 3) and
338 (ulong not between 1 and 3) and
339 (ulonglong not between 1 and 3) and
340 (bits not between b'001' and b'011') and
341 (options not between 'one' and 'three') and
342 (flags not between 'one' and 'one,two,three') and
343 (date_field not between '1901-01-01' and '1903-03-03') and
344 (year_field not between '1901' and '1903') and
345 (time_field not between '01:01:01' and '03:03:03') and
346 (date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
347 order by auto;
348 auto
349 4
350 select auto from t1 where
351 ("aaaa" not between string and string) and
352 ("aaaa" not between vstring and vstring) and
353 (0xAAAA not between bin and bin) and
354 (0xAAAA not between vbin and vbin) and
355 (-1 not between tiny and tiny) and
356 (-1 not between short and short) and
357 (-1 not between medium and medium) and
358 (-1 not between long_int and long_int) and
359 (-1 not between longlong and longlong) and
360 (1 not between utiny and utiny) and
361 (1 not between ushort and ushort) and
362 (1 not between umedium and umedium) and
363 (1 not between ulong and ulong) and
364 (1 not between ulonglong and ulonglong) and
365 (b'001' not between bits and bits) and
366 ('one' not between options and options) and
367 ('one' not between flags and flags) and
368 ('1901-01-01' not between date_field and date_field) and
369 ('1901' not between year_field and year_field) and
370 ('01:01:01' not between time_field and time_field) and
371 ('1901-01-01 01:01:01' not between date_time and date_time)
372 order by auto;
373 auto
374 2
375 3
376 4
377 select auto from t1 where
378 string in("aaaa","cccc") and
379 vstring in("aaaa","cccc") and
380 bin in(0xAAAA,0xCCCC) and
381 vbin in(0xAAAA,0xCCCC) and
382 tiny in(-1,-3) and
383 short in(-1,-3) and
384 medium in(-1,-3) and
385 long_int in(-1,-3) and
386 longlong in(-1,-3) and
387 utiny in(1,3) and
388 ushort in(1,3) and
389 umedium in(1,3) and
390 ulong in(1,3) and
391 ulonglong in(1,3) and
392 bits in(b'001',b'011') and
393 options in('one','three') and
394 flags in('one','one,two,three') and
395 date_field in('1901-01-01','1903-03-03') and
396 year_field in('1901','1903') and
397 time_field in('01:01:01','03:03:03') and
398 date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')
399 order by auto;
400 auto
401 1
402 3
403 select auto from t1 where
404 "aaaa" in(string) and
405 "aaaa" in(vstring) and
406 0xAAAA in(bin) and
407 0xAAAA in(vbin) and
408 (-1 in(tiny)) and
409 (-1 in(short)) and
410 (-1 in(medium)) and
411 (-1 in(long_int)) and
412 (-1 in(longlong)) and
413 1 in(utiny) and
414 1 in(ushort) and
415 1 in(umedium) and
416 1 in(ulong) and
417 1 in(ulonglong) and
418 b'001' in(bits) and
419 'one' in(options) and
420 'one' in(flags) and
421 '1901-01-01' in(date_field) and
422 '1901' in(year_field) and
423 '01:01:01' in(time_field) and
424 '1901-01-01 01:01:01' in(date_time)
425 order by auto;
426 auto
427 1
428 select auto from t1 where
429 string not in("aaaa","cccc") and
430 vstring not in("aaaa","cccc") and
431 bin not in(0xAAAA,0xCCCC) and
432 vbin not in(0xAAAA,0xCCCC) and
433 tiny not in(-1,-3) and
434 short not in(-1,-3) and
435 medium not in(-1,-3) and
436 long_int not in(-1,-3) and
437 longlong not in(-1,-3) and
438 utiny not in(1,3) and
439 ushort not in(1,3) and
440 umedium not in(1,3) and
441 ulong not in(1,3) and
442 ulonglong not in(1,3) and
443 bits not in(b'001',b'011') and
444 options not in('one','three') and
445 flags not in('one','one,two,three') and
446 date_field not in('1901-01-01','1903-03-03') and
447 year_field not in('1901','1903') and
448 time_field not in('01:01:01','03:03:03') and
449 date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')
450 order by auto;
451 auto
452 2
453 4
454 select auto from t1 where
455 "aaaa" not in(string) and
456 "aaaa" not in(vstring) and
457 0xAAAA not in(bin) and
458 0xAAAA not in(vbin) and
459 (-1 not in(tiny)) and
460 (-1 not in(short)) and
461 (-1 not in(medium)) and
462 (-1 not in(long_int)) and
463 (-1 not in(longlong)) and
464 1 not in(utiny) and
465 1 not in(ushort) and
466 1 not in(umedium) and
467 1 not in(ulong) and
468 1 not in(ulonglong) and
469 b'001' not in(bits) and
470 'one' not in(options) and
471 'one' not in(flags) and
472 '1901-01-01' not in(date_field) and
473 '1901' not in(year_field) and
474 '01:01:01' not in(time_field) and
475 '1901-01-01 01:01:01' not in(date_time)
476 order by auto;
477 auto
478 2
479 3
480 4
481 select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
482 pk1 attr1 attr2 attr3
483 2 2 NULL NULL
484 3 3 3 d
485 select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
486 pk1 attr1 attr2 attr3
487 3 3 3 d
488 4 4 4 e
489 5 5 5 f
490 select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
491 pk1 attr1 attr2 attr3 attr4
492 2 2 9223372036854775804 2 c
493 4 4 9223372036854775806 4 e
494 5 5 9223372036854775807 5 f
495 select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
496 pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4
497 0 0 0 a 0 0 0 0 a
498 select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
499 pk1 attr1 attr2 attr3 attr4
500 2 2 9223372036854775804 2 c
501 4 4 9223372036854775806 4 e
502 select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
503 pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4
504 2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c
505 3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d
506 4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e
507 set @@optimizer_switch = 'engine_condition_pushdown=on';
508 explain
509 select auto from t1 where
510 string = "aaaa" and
511 vstring = "aaaa" and
512 bin = 0xAAAA and
513 vbin = 0xAAAA and
514 tiny = -1 and
515 short = -1 and
516 medium = -1 and
517 long_int = -1 and
518 longlong = -1 and
519 real_float > 1.0 and real_float < 2.0 and
520 real_double > 1.0 and real_double < 2.0 and
521 real_decimal > 1.0 and real_decimal < 2.0 and
522 utiny = 1 and
523 ushort = 1 and
524 umedium = 1 and
525 ulong = 1 and
526 ulonglong = 1 and
527 /* bits = b'001' and */
528 options = 'one' and
529 flags = 'one' and
530 date_field = '1901-01-01' and
531 year_field = '1901' and
532 time_field = '01:01:01' and
533 date_time = '1901-01-01 01:01:01'
534 order by auto;
535 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
536 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`date_time` = TIMESTAMP'1901-01-01 01:01:01') and (`test`.`t1`.`year_field` = 1901) and (`test`.`t1`.`date_field` = DATE'1901-01-01') and (`test`.`t1`.`flags` = 'one') and (`test`.`t1`.`options` = 'one') and (`test`.`t1`.`ulonglong` = 1) and (`test`.`t1`.`ulong` = 1) and (`test`.`t1`.`umedium` = 1) and (`test`.`t1`.`ushort` = 1) and (`test`.`t1`.`utiny` = 1) and (`test`.`t1`.`longlong` = -1) and (`test`.`t1`.`long_int` = <cache>(-(1))) and (`test`.`t1`.`medium` = <cache>(-(1))) and (`test`.`t1`.`short` = <cache>(-(1))) and (`test`.`t1`.`tiny` = <cache>(-(1))) and (`test`.`t1`.`vbin` = 0xaaaa) and (`test`.`t1`.`bin` = 0xaaaa) and (`test`.`t1`.`vstring` = 'aaaa') and (`test`.`t1`.`string` = 'aaaa') and (`test`.`t1`.`real_float` > 1) and (`test`.`t1`.`real_float` < 2) and (`test`.`t1`.`real_double` > 1) and (`test`.`t1`.`real_double` < 2) and (`test`.`t1`.`real_decimal` > 1.0000) and (`test`.`t1`.`real_decimal` < 2.0000) and (`test`.`t1`.`time_field` = TIME'01:01:01'))
537 Warnings:
538 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`date_time` = TIMESTAMP'1901-01-01 01:01:01') and (`test`.`t1`.`year_field` = 1901) and (`test`.`t1`.`date_field` = DATE'1901-01-01') and (`test`.`t1`.`flags` = 'one') and (`test`.`t1`.`options` = 'one') and (`test`.`t1`.`ulonglong` = 1) and (`test`.`t1`.`ulong` = 1) and (`test`.`t1`.`umedium` = 1) and (`test`.`t1`.`ushort` = 1) and (`test`.`t1`.`utiny` = 1) and (`test`.`t1`.`longlong` = -1) and (`test`.`t1`.`long_int` = <cache>(-(1))) and (`test`.`t1`.`medium` = <cache>(-(1))) and (`test`.`t1`.`short` = <cache>(-(1))) and (`test`.`t1`.`tiny` = <cache>(-(1))) and (`test`.`t1`.`vbin` = 0xaaaa) and (`test`.`t1`.`bin` = 0xaaaa) and (`test`.`t1`.`vstring` = 'aaaa') and (`test`.`t1`.`string` = 'aaaa') and (`test`.`t1`.`real_float` > 1) and (`test`.`t1`.`real_float` < 2) and (`test`.`t1`.`real_double` > 1) and (`test`.`t1`.`real_double` < 2) and (`test`.`t1`.`real_decimal` > 1.0000) and (`test`.`t1`.`real_decimal` < 2.0000) and (`test`.`t1`.`time_field` = TIME'01:01:01')) order by `test`.`t1`.`auto`
539 select auto from t1 where
540 string = "aaaa" and
541 vstring = "aaaa" and
542 bin = 0xAAAA and
543 vbin = 0xAAAA and
544 tiny = -1 and
545 short = -1 and
546 medium = -1 and
547 long_int = -1 and
548 longlong = -1 and
549 real_float > 1.0 and real_float < 2.0 and
550 real_double > 1.0 and real_double < 2.0 and
551 real_decimal > 1.0 and real_decimal < 2.0 and
552 utiny = 1 and
553 ushort = 1 and
554 umedium = 1 and
555 ulong = 1 and
556 ulonglong = 1 and
557 /* bits = b'001' and */
558 options = 'one' and
559 flags = 'one' and
560 date_field = '1901-01-01' and
561 year_field = '1901' and
562 time_field = '01:01:01' and
563 date_time = '1901-01-01 01:01:01'
564 order by auto;
565 auto
566 1
567 explain
568 select auto from t1 where
569 string != "aaaa" and
570 vstring != "aaaa" and
571 bin != 0xAAAA and
572 vbin != 0xAAAA and
573 tiny != -1 and
574 short != -1 and
575 medium != -1 and
576 long_int != -1 and
577 longlong != -1 and
578 (real_float < 1.0 or real_float > 2.0) and
579 (real_double < 1.0 or real_double > 2.0) and
580 (real_decimal < 1.0 or real_decimal > 2.0) and
581 utiny != 1 and
582 ushort != 1 and
583 umedium != 1 and
584 ulong != 1 and
585 ulonglong != 1 and
586 /* bits != b'001' and */
587 options != 'one' and
588 flags != 'one' and
589 date_field != '1901-01-01' and
590 year_field != '1901' and
591 time_field != '01:01:01' and
592 date_time != '1901-01-01 01:01:01'
593 order by auto;
594 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
595 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` <> 'aaaa') and (`test`.`t1`.`vstring` <> 'aaaa') and (`test`.`t1`.`bin` <> 0xaaaa) and (`test`.`t1`.`vbin` <> 0xaaaa) and (`test`.`t1`.`tiny` <> <cache>(-(1))) and (`test`.`t1`.`short` <> <cache>(-(1))) and (`test`.`t1`.`medium` <> <cache>(-(1))) and (`test`.`t1`.`long_int` <> <cache>(-(1))) and (`test`.`t1`.`longlong` <> -1) and ((`test`.`t1`.`real_float` < 1) or (`test`.`t1`.`real_float` > 2)) and ((`test`.`t1`.`real_double` < 1) or (`test`.`t1`.`real_double` > 2)) and ((`test`.`t1`.`real_decimal` < 1.0000) or (`test`.`t1`.`real_decimal` > 2.0000)) and (`test`.`t1`.`utiny` <> 1) and (`test`.`t1`.`ushort` <> 1) and (`test`.`t1`.`umedium` <> 1) and (`test`.`t1`.`ulong` <> 1) and (`test`.`t1`.`ulonglong` <> 1) and (`test`.`t1`.`options` <> 'one') and (`test`.`t1`.`flags` <> 'one') and (`test`.`t1`.`date_field` <> DATE'1901-01-01') and (`test`.`t1`.`year_field` <> 1901) and (`test`.`t1`.`time_field` <> TIME'01:01:01') and (`test`.`t1`.`date_time` <> TIMESTAMP'1901-01-01 01:01:01'))
596 Warnings:
597 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` <> 'aaaa') and (`test`.`t1`.`vstring` <> 'aaaa') and (`test`.`t1`.`bin` <> 0xaaaa) and (`test`.`t1`.`vbin` <> 0xaaaa) and (`test`.`t1`.`tiny` <> <cache>(-(1))) and (`test`.`t1`.`short` <> <cache>(-(1))) and (`test`.`t1`.`medium` <> <cache>(-(1))) and (`test`.`t1`.`long_int` <> <cache>(-(1))) and (`test`.`t1`.`longlong` <> -1) and ((`test`.`t1`.`real_float` < 1) or (`test`.`t1`.`real_float` > 2)) and ((`test`.`t1`.`real_double` < 1) or (`test`.`t1`.`real_double` > 2)) and ((`test`.`t1`.`real_decimal` < 1.0000) or (`test`.`t1`.`real_decimal` > 2.0000)) and (`test`.`t1`.`utiny` <> 1) and (`test`.`t1`.`ushort` <> 1) and (`test`.`t1`.`umedium` <> 1) and (`test`.`t1`.`ulong` <> 1) and (`test`.`t1`.`ulonglong` <> 1) and (`test`.`t1`.`options` <> 'one') and (`test`.`t1`.`flags` <> 'one') and (`test`.`t1`.`date_field` <> DATE'1901-01-01') and (`test`.`t1`.`year_field` <> 1901) and (`test`.`t1`.`time_field` <> TIME'01:01:01') and (`test`.`t1`.`date_time` <> TIMESTAMP'1901-01-01 01:01:01')) order by `test`.`t1`.`auto`
598 select auto from t1 where
599 string != "aaaa" and
600 vstring != "aaaa" and
601 bin != 0xAAAA and
602 vbin != 0xAAAA and
603 tiny != -1 and
604 short != -1 and
605 medium != -1 and
606 long_int != -1 and
607 longlong != -1 and
608 (real_float < 1.0 or real_float > 2.0) and
609 (real_double < 1.0 or real_double > 2.0) and
610 (real_decimal < 1.0 or real_decimal > 2.0) and
611 utiny != 1 and
612 ushort != 1 and
613 umedium != 1 and
614 ulong != 1 and
615 ulonglong != 1 and
616 /* bits != b'001' and */
617 options != 'one' and
618 flags != 'one' and
619 date_field != '1901-01-01' and
620 year_field != '1901' and
621 time_field != '01:01:01' and
622 date_time != '1901-01-01 01:01:01'
623 order by auto;
624 auto
625 2
626 3
627 4
628 explain
629 select auto from t1 where
630 string > "aaaa" and
631 vstring > "aaaa" and
632 bin > 0xAAAA and
633 vbin > 0xAAAA and
634 tiny < -1 and
635 short < -1 and
636 medium < -1 and
637 long_int < -1 and
638 longlong < -1 and
639 real_float > 1.1 and
640 real_double > 1.1 and
641 real_decimal > 1.1 and
642 utiny > 1 and
643 ushort > 1 and
644 umedium > 1 and
645 ulong > 1 and
646 ulonglong > 1 and
647 /* bits > b'001' and */
648 (options = 'two' or options = 'three' or options = 'four') and
649 (flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
650 date_field > '1901-01-01' and
651 year_field > '1901' and
652 time_field > '01:01:01' and
653 date_time > '1901-01-01 01:01:01'
654 order by auto;
655 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
656 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` > 'aaaa') and (`test`.`t1`.`vstring` > 'aaaa') and (`test`.`t1`.`bin` > 0xaaaa) and (`test`.`t1`.`vbin` > 0xaaaa) and (`test`.`t1`.`tiny` < <cache>(-(1))) and (`test`.`t1`.`short` < <cache>(-(1))) and (`test`.`t1`.`medium` < <cache>(-(1))) and (`test`.`t1`.`long_int` < <cache>(-(1))) and (`test`.`t1`.`longlong` < -1) and (`test`.`t1`.`real_float` > 1.100000023841858) and (`test`.`t1`.`real_double` > 1.1) and (`test`.`t1`.`real_decimal` > 1.1000) and (`test`.`t1`.`utiny` > 1) and (`test`.`t1`.`ushort` > 1) and (`test`.`t1`.`umedium` > 1) and (`test`.`t1`.`ulong` > 1) and (`test`.`t1`.`ulonglong` > 1) and ((`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` > DATE'1901-01-01') and (`test`.`t1`.`year_field` > 1901) and (`test`.`t1`.`time_field` > TIME'01:01:01') and (`test`.`t1`.`date_time` > TIMESTAMP'1901-01-01 01:01:01'))
657 Warnings:
658 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` > 'aaaa') and (`test`.`t1`.`vstring` > 'aaaa') and (`test`.`t1`.`bin` > 0xaaaa) and (`test`.`t1`.`vbin` > 0xaaaa) and (`test`.`t1`.`tiny` < <cache>(-(1))) and (`test`.`t1`.`short` < <cache>(-(1))) and (`test`.`t1`.`medium` < <cache>(-(1))) and (`test`.`t1`.`long_int` < <cache>(-(1))) and (`test`.`t1`.`longlong` < -1) and (`test`.`t1`.`real_float` > 1.100000023841858) and (`test`.`t1`.`real_double` > 1.1) and (`test`.`t1`.`real_decimal` > 1.1000) and (`test`.`t1`.`utiny` > 1) and (`test`.`t1`.`ushort` > 1) and (`test`.`t1`.`umedium` > 1) and (`test`.`t1`.`ulong` > 1) and (`test`.`t1`.`ulonglong` > 1) and ((`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` > DATE'1901-01-01') and (`test`.`t1`.`year_field` > 1901) and (`test`.`t1`.`time_field` > TIME'01:01:01') and (`test`.`t1`.`date_time` > TIMESTAMP'1901-01-01 01:01:01')) order by `test`.`t1`.`auto`
659 select auto from t1 where
660 string > "aaaa" and
661 vstring > "aaaa" and
662 bin > 0xAAAA and
663 vbin > 0xAAAA and
664 tiny < -1 and
665 short < -1 and
666 medium < -1 and
667 long_int < -1 and
668 longlong < -1 and
669 real_float > 1.1 and
670 real_double > 1.1 and
671 real_decimal > 1.1 and
672 utiny > 1 and
673 ushort > 1 and
674 umedium > 1 and
675 ulong > 1 and
676 ulonglong > 1 and
677 /* bits > b'001' and */
678 (options = 'two' or options = 'three' or options = 'four') and
679 (flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
680 date_field > '1901-01-01' and
681 year_field > '1901' and
682 time_field > '01:01:01' and
683 date_time > '1901-01-01 01:01:01'
684 order by auto;
685 auto
686 2
687 3
688 4
689 explain
690 select auto from t1 where
691 string >= "aaaa" and
692 vstring >= "aaaa" and
693 bin >= 0xAAAA and
694 vbin >= 0xAAAA and
695 tiny <= -1 and
696 short <= -1 and
697 medium <= -1 and
698 long_int <= -1 and
699 longlong <= -1 and
700 real_float >= 1.0 and
701 real_double >= 1.0 and
702 real_decimal >= 1.0 and
703 utiny >= 1 and
704 ushort >= 1 and
705 umedium >= 1 and
706 ulong >= 1 and
707 ulonglong >= 1 and
708 /* bits >= b'001' and */
709 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
710 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
711 date_field >= '1901-01-01' and
712 year_field >= '1901' and
713 time_field >= '01:01:01' and
714 date_time >= '1901-01-01 01:01:01'
715 order by auto;
716 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
717 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` >= 'aaaa') and (`test`.`t1`.`vstring` >= 'aaaa') and (`test`.`t1`.`bin` >= 0xaaaa) and (`test`.`t1`.`vbin` >= 0xaaaa) and (`test`.`t1`.`tiny` <= <cache>(-(1))) and (`test`.`t1`.`short` <= <cache>(-(1))) and (`test`.`t1`.`medium` <= <cache>(-(1))) and (`test`.`t1`.`long_int` <= <cache>(-(1))) and (`test`.`t1`.`longlong` <= -1) and (`test`.`t1`.`real_float` >= 1) and (`test`.`t1`.`real_double` >= 1) and (`test`.`t1`.`real_decimal` >= 1.0000) and (`test`.`t1`.`utiny` >= 1) and (`test`.`t1`.`ushort` >= 1) and (`test`.`t1`.`umedium` >= 1) and (`test`.`t1`.`ulong` >= 1) and (`test`.`t1`.`ulonglong` >= 1) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` >= DATE'1901-01-01') and (`test`.`t1`.`year_field` >= 1901) and (`test`.`t1`.`time_field` >= TIME'01:01:01') and (`test`.`t1`.`date_time` >= TIMESTAMP'1901-01-01 01:01:01'))
718 Warnings:
719 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` >= 'aaaa') and (`test`.`t1`.`vstring` >= 'aaaa') and (`test`.`t1`.`bin` >= 0xaaaa) and (`test`.`t1`.`vbin` >= 0xaaaa) and (`test`.`t1`.`tiny` <= <cache>(-(1))) and (`test`.`t1`.`short` <= <cache>(-(1))) and (`test`.`t1`.`medium` <= <cache>(-(1))) and (`test`.`t1`.`long_int` <= <cache>(-(1))) and (`test`.`t1`.`longlong` <= -1) and (`test`.`t1`.`real_float` >= 1) and (`test`.`t1`.`real_double` >= 1) and (`test`.`t1`.`real_decimal` >= 1.0000) and (`test`.`t1`.`utiny` >= 1) and (`test`.`t1`.`ushort` >= 1) and (`test`.`t1`.`umedium` >= 1) and (`test`.`t1`.`ulong` >= 1) and (`test`.`t1`.`ulonglong` >= 1) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` >= DATE'1901-01-01') and (`test`.`t1`.`year_field` >= 1901) and (`test`.`t1`.`time_field` >= TIME'01:01:01') and (`test`.`t1`.`date_time` >= TIMESTAMP'1901-01-01 01:01:01')) order by `test`.`t1`.`auto`
720 select auto from t1 where
721 string >= "aaaa" and
722 vstring >= "aaaa" and
723 bin >= 0xAAAA and
724 vbin >= 0xAAAA and
725 tiny <= -1 and
726 short <= -1 and
727 medium <= -1 and
728 long_int <= -1 and
729 longlong <= -1 and
730 real_float >= 1.0 and
731 real_double >= 1.0 and
732 real_decimal >= 1.0 and
733 utiny >= 1 and
734 ushort >= 1 and
735 umedium >= 1 and
736 ulong >= 1 and
737 ulonglong >= 1 and
738 /* bits >= b'001' and */
739 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
740 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
741 date_field >= '1901-01-01' and
742 year_field >= '1901' and
743 time_field >= '01:01:01' and
744 date_time >= '1901-01-01 01:01:01'
745 order by auto;
746 auto
747 1
748 2
749 3
750 4
751 explain
752 select auto from t1 where
753 string < "dddd" and
754 vstring < "dddd" and
755 bin < 0xDDDD and
756 vbin < 0xDDDD and
757 tiny > -4 and
758 short > -4 and
759 medium > -4 and
760 long_int > -4 and
761 longlong > -4 and
762 real_float < 4.4 and
763 real_double < 4.4 and
764 real_decimal < 4.4 and
765 utiny < 4 and
766 ushort < 4 and
767 umedium < 4 and
768 ulong < 4 and
769 ulonglong < 4 and
770 /* bits < b'100' and */
771 (options = 'one' or options = 'two' or options = 'three') and
772 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
773 date_field < '1904-01-01' and
774 year_field < '1904' and
775 time_field < '04:04:04' and
776 date_time < '1904-04-04 04:04:04'
777 order by auto;
778 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
779 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` < 'dddd') and (`test`.`t1`.`vstring` < 'dddd') and (`test`.`t1`.`bin` < 0xdddd) and (`test`.`t1`.`vbin` < 0xdddd) and (`test`.`t1`.`tiny` > <cache>(-(4))) and (`test`.`t1`.`short` > <cache>(-(4))) and (`test`.`t1`.`medium` > <cache>(-(4))) and (`test`.`t1`.`long_int` > <cache>(-(4))) and (`test`.`t1`.`longlong` > -4) and (`test`.`t1`.`real_float` < 4.400000095367432) and (`test`.`t1`.`real_double` < 4.4) and (`test`.`t1`.`real_decimal` < 4.4000) and (`test`.`t1`.`utiny` < 4) and (`test`.`t1`.`ushort` < 4) and (`test`.`t1`.`umedium` < 4) and (`test`.`t1`.`ulong` < 4) and (`test`.`t1`.`ulonglong` < 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three')) and (`test`.`t1`.`date_field` < DATE'1904-01-01') and (`test`.`t1`.`year_field` < 1904) and (`test`.`t1`.`time_field` < TIME'04:04:04') and (`test`.`t1`.`date_time` < TIMESTAMP'1904-04-04 04:04:04'))
780 Warnings:
781 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` < 'dddd') and (`test`.`t1`.`vstring` < 'dddd') and (`test`.`t1`.`bin` < 0xdddd) and (`test`.`t1`.`vbin` < 0xdddd) and (`test`.`t1`.`tiny` > <cache>(-(4))) and (`test`.`t1`.`short` > <cache>(-(4))) and (`test`.`t1`.`medium` > <cache>(-(4))) and (`test`.`t1`.`long_int` > <cache>(-(4))) and (`test`.`t1`.`longlong` > -4) and (`test`.`t1`.`real_float` < 4.400000095367432) and (`test`.`t1`.`real_double` < 4.4) and (`test`.`t1`.`real_decimal` < 4.4000) and (`test`.`t1`.`utiny` < 4) and (`test`.`t1`.`ushort` < 4) and (`test`.`t1`.`umedium` < 4) and (`test`.`t1`.`ulong` < 4) and (`test`.`t1`.`ulonglong` < 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three')) and (`test`.`t1`.`date_field` < DATE'1904-01-01') and (`test`.`t1`.`year_field` < 1904) and (`test`.`t1`.`time_field` < TIME'04:04:04') and (`test`.`t1`.`date_time` < TIMESTAMP'1904-04-04 04:04:04')) order by `test`.`t1`.`auto`
782 select auto from t1 where
783 string < "dddd" and
784 vstring < "dddd" and
785 bin < 0xDDDD and
786 vbin < 0xDDDD and
787 tiny > -4 and
788 short > -4 and
789 medium > -4 and
790 long_int > -4 and
791 longlong > -4 and
792 real_float < 4.4 and
793 real_double < 4.4 and
794 real_decimal < 4.4 and
795 utiny < 4 and
796 ushort < 4 and
797 umedium < 4 and
798 ulong < 4 and
799 ulonglong < 4 and
800 /* bits < b'100' and */
801 (options = 'one' or options = 'two' or options = 'three') and
802 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
803 date_field < '1904-01-01' and
804 year_field < '1904' and
805 time_field < '04:04:04' and
806 date_time < '1904-04-04 04:04:04'
807 order by auto;
808 auto
809 1
810 2
811 3
812 explain
813 select auto from t1 where
814 string <= "dddd" and
815 vstring <= "dddd" and
816 bin <= 0xDDDD and
817 vbin <= 0xDDDD and
818 tiny >= -4 and
819 short >= -4 and
820 medium >= -4 and
821 long_int >= -4 and
822 longlong >= -4 and
823 real_float <= 4.5 and
824 real_double <= 4.5 and
825 real_decimal <= 4.5 and
826 utiny <= 4 - 1 + 1 and /* Checking function composition */
827 ushort <= 4 and
828 umedium <= 4 and
829 ulong <= 4 and
830 ulonglong <= 4 and
831 /* bits <= b'100' and */
832 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
833 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
834 date_field <= '1904-04-04' and
835 year_field <= '1904' and
836 time_field <= '04:04:04' and
837 date_time <= '1904-04-04 04:04:04'
838 order by auto;
839 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
840 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` <= 'dddd') and (`test`.`t1`.`vstring` <= 'dddd') and (`test`.`t1`.`bin` <= 0xdddd) and (`test`.`t1`.`vbin` <= 0xdddd) and (`test`.`t1`.`tiny` >= <cache>(-(4))) and (`test`.`t1`.`short` >= <cache>(-(4))) and (`test`.`t1`.`medium` >= <cache>(-(4))) and (`test`.`t1`.`long_int` >= <cache>(-(4))) and (`test`.`t1`.`longlong` >= -4) and (`test`.`t1`.`real_float` <= 4.5) and (`test`.`t1`.`real_double` <= 4.5) and (`test`.`t1`.`real_decimal` <= 4.5000) and (`test`.`t1`.`utiny` <= <cache>(((4 - 1) + 1))) and (`test`.`t1`.`ushort` <= 4) and (`test`.`t1`.`umedium` <= 4) and (`test`.`t1`.`ulong` <= 4) and (`test`.`t1`.`ulonglong` <= 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` <= DATE'1904-04-04') and (`test`.`t1`.`year_field` <= 1904) and (`test`.`t1`.`time_field` <= TIME'04:04:04') and (`test`.`t1`.`date_time` <= TIMESTAMP'1904-04-04 04:04:04'))
841 Warnings:
842 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` <= 'dddd') and (`test`.`t1`.`vstring` <= 'dddd') and (`test`.`t1`.`bin` <= 0xdddd) and (`test`.`t1`.`vbin` <= 0xdddd) and (`test`.`t1`.`tiny` >= <cache>(-(4))) and (`test`.`t1`.`short` >= <cache>(-(4))) and (`test`.`t1`.`medium` >= <cache>(-(4))) and (`test`.`t1`.`long_int` >= <cache>(-(4))) and (`test`.`t1`.`longlong` >= -4) and (`test`.`t1`.`real_float` <= 4.5) and (`test`.`t1`.`real_double` <= 4.5) and (`test`.`t1`.`real_decimal` <= 4.5000) and (`test`.`t1`.`utiny` <= <cache>(((4 - 1) + 1))) and (`test`.`t1`.`ushort` <= 4) and (`test`.`t1`.`umedium` <= 4) and (`test`.`t1`.`ulong` <= 4) and (`test`.`t1`.`ulonglong` <= 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` <= DATE'1904-04-04') and (`test`.`t1`.`year_field` <= 1904) and (`test`.`t1`.`time_field` <= TIME'04:04:04') and (`test`.`t1`.`date_time` <= TIMESTAMP'1904-04-04 04:04:04')) order by `test`.`t1`.`auto`
843 select auto from t1 where
844 string <= "dddd" and
845 vstring <= "dddd" and
846 bin <= 0xDDDD and
847 vbin <= 0xDDDD and
848 tiny >= -4 and
849 short >= -4 and
850 medium >= -4 and
851 long_int >= -4 and
852 longlong >= -4 and
853 real_float <= 4.5 and
854 real_double <= 4.5 and
855 real_decimal <= 4.5 and
856 utiny <= 4 - 1 + 1 and /* Checking function composition */
857 ushort <= 4 and
858 umedium <= 4 and
859 ulong <= 4 and
860 ulonglong <= 4 and
861 /* bits <= b'100' and */
862 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
863 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
864 date_field <= '1904-04-04' and
865 year_field <= '1904' and
866 time_field <= '04:04:04' and
867 date_time <= '1904-04-04 04:04:04'
868 order by auto;
869 auto
870 1
871 2
872 3
873 4
874 create index medium_index on t1(medium);
875 explain
876 select auto from t1 where
877 string = "aaaa" and
878 vstring = "aaaa" and
879 bin = 0xAAAA and
880 vbin = 0xAAAA and
881 tiny = -1 and
882 short = -1 and
883 medium = -1 and
884 long_int = -1 and
885 longlong = -1 and
886 real_float > 1.0 and real_float < 2.0 and
887 real_double > 1.0 and real_double < 2.0 and
888 real_decimal > 1.0 and real_decimal < 2.0 and
889 utiny = 1 and
890 ushort = 1 and
891 umedium = 1 and
892 ulong = 1 and
893 ulonglong = 1 and
894 /* bits = b'001' and */
895 options = 'one' and
896 flags = 'one' and
897 date_field = '1901-01-01' and
898 year_field = '1901' and
899 time_field = '01:01:01' and
900 date_time = '1901-01-01 01:01:01'
901 order by auto;
902 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
903 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref medium_index medium_index 3 const # # Using pushed condition ((`test`.`t1`.`date_time` = TIMESTAMP'1901-01-01 01:01:01') and (`test`.`t1`.`year_field` = 1901) and (`test`.`t1`.`date_field` = DATE'1901-01-01') and (`test`.`t1`.`flags` = 'one') and (`test`.`t1`.`options` = 'one') and (`test`.`t1`.`ulonglong` = 1) and (`test`.`t1`.`ulong` = 1) and (`test`.`t1`.`umedium` = 1) and (`test`.`t1`.`ushort` = 1) and (`test`.`t1`.`utiny` = 1) and (`test`.`t1`.`longlong` = -1) and (`test`.`t1`.`long_int` = <cache>(-(1))) and (`test`.`t1`.`short` = <cache>(-(1))) and (`test`.`t1`.`tiny` = <cache>(-(1))) and (`test`.`t1`.`vbin` = 0xaaaa) and (`test`.`t1`.`bin` = 0xaaaa) and (`test`.`t1`.`vstring` = 'aaaa') and (`test`.`t1`.`string` = 'aaaa') and (`test`.`t1`.`real_float` > 1) and (`test`.`t1`.`real_float` < 2) and (`test`.`t1`.`real_double` > 1) and (`test`.`t1`.`real_double` < 2) and (`test`.`t1`.`real_decimal` > 1.0000) and (`test`.`t1`.`real_decimal` < 2.0000) and (`test`.`t1`.`time_field` = TIME'01:01:01')); Using filesort
904 Warnings:
905 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`date_time` = TIMESTAMP'1901-01-01 01:01:01') and (`test`.`t1`.`year_field` = 1901) and (`test`.`t1`.`date_field` = DATE'1901-01-01') and (`test`.`t1`.`flags` = 'one') and (`test`.`t1`.`options` = 'one') and (`test`.`t1`.`ulonglong` = 1) and (`test`.`t1`.`ulong` = 1) and (`test`.`t1`.`umedium` = 1) and (`test`.`t1`.`ushort` = 1) and (`test`.`t1`.`utiny` = 1) and (`test`.`t1`.`longlong` = -1) and (`test`.`t1`.`long_int` = <cache>(-(1))) and (`test`.`t1`.`medium` = -(1)) and (`test`.`t1`.`short` = <cache>(-(1))) and (`test`.`t1`.`tiny` = <cache>(-(1))) and (`test`.`t1`.`vbin` = 0xaaaa) and (`test`.`t1`.`bin` = 0xaaaa) and (`test`.`t1`.`vstring` = 'aaaa') and (`test`.`t1`.`string` = 'aaaa') and (`test`.`t1`.`real_float` > 1) and (`test`.`t1`.`real_float` < 2) and (`test`.`t1`.`real_double` > 1) and (`test`.`t1`.`real_double` < 2) and (`test`.`t1`.`real_decimal` > 1.0000) and (`test`.`t1`.`real_decimal` < 2.0000) and (`test`.`t1`.`time_field` = TIME'01:01:01')) order by `test`.`t1`.`auto`
906 select auto from t1 where
907 string = "aaaa" and
908 vstring = "aaaa" and
909 bin = 0xAAAA and
910 vbin = 0xAAAA and
911 tiny = -1 and
912 short = -1 and
913 medium = -1 and
914 long_int = -1 and
915 longlong = -1 and
916 real_float > 1.0 and real_float < 2.0 and
917 real_double > 1.0 and real_double < 2.0 and
918 real_decimal > 1.0 and real_decimal < 2.0 and
919 utiny = 1 and
920 ushort = 1 and
921 umedium = 1 and
922 ulong = 1 and
923 ulonglong = 1 and
924 /* bits = b'001' and */
925 options = 'one' and
926 flags = 'one' and
927 date_field = '1901-01-01' and
928 year_field = '1901' and
929 time_field = '01:01:01' and
930 date_time = '1901-01-01 01:01:01'
931 order by auto;
932 auto
933 1
934 explain
935 select auto from t1 where
936 string != "aaaa" and
937 vstring != "aaaa" and
938 bin != 0xAAAA and
939 vbin != 0xAAAA and
940 tiny != -1 and
941 short != -1 and
942 medium != -1 and
943 long_int != -1 and
944 longlong != -1 and
945 (real_float < 1.0 or real_float > 2.0) and
946 (real_double < 1.0 or real_double > 2.0) and
947 (real_decimal < 1.0 or real_decimal > 2.0) and
948 utiny != 1 and
949 ushort != 1 and
950 umedium != 1 and
951 ulong != 1 and
952 ulonglong != 1 and
953 /* bits != b'001' and */
954 options != 'one' and
955 flags != 'one' and
956 date_field != '1901-01-01' and
957 year_field != '1901' and
958 time_field != '01:01:01' and
959 date_time != '1901-01-01 01:01:01'
960 order by auto;
961 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
962 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` <> 'aaaa') and (`test`.`t1`.`vstring` <> 'aaaa') and (`test`.`t1`.`bin` <> 0xaaaa) and (`test`.`t1`.`vbin` <> 0xaaaa) and (`test`.`t1`.`tiny` <> <cache>(-(1))) and (`test`.`t1`.`short` <> <cache>(-(1))) and (`test`.`t1`.`medium` <> <cache>(-(1))) and (`test`.`t1`.`long_int` <> <cache>(-(1))) and (`test`.`t1`.`longlong` <> -1) and ((`test`.`t1`.`real_float` < 1) or (`test`.`t1`.`real_float` > 2)) and ((`test`.`t1`.`real_double` < 1) or (`test`.`t1`.`real_double` > 2)) and ((`test`.`t1`.`real_decimal` < 1.0000) or (`test`.`t1`.`real_decimal` > 2.0000)) and (`test`.`t1`.`utiny` <> 1) and (`test`.`t1`.`ushort` <> 1) and (`test`.`t1`.`umedium` <> 1) and (`test`.`t1`.`ulong` <> 1) and (`test`.`t1`.`ulonglong` <> 1) and (`test`.`t1`.`options` <> 'one') and (`test`.`t1`.`flags` <> 'one') and (`test`.`t1`.`date_field` <> DATE'1901-01-01') and (`test`.`t1`.`year_field` <> 1901) and (`test`.`t1`.`time_field` <> TIME'01:01:01') and (`test`.`t1`.`date_time` <> TIMESTAMP'1901-01-01 01:01:01'))
963 Warnings:
964 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` <> 'aaaa') and (`test`.`t1`.`vstring` <> 'aaaa') and (`test`.`t1`.`bin` <> 0xaaaa) and (`test`.`t1`.`vbin` <> 0xaaaa) and (`test`.`t1`.`tiny` <> <cache>(-(1))) and (`test`.`t1`.`short` <> <cache>(-(1))) and (`test`.`t1`.`medium` <> <cache>(-(1))) and (`test`.`t1`.`long_int` <> <cache>(-(1))) and (`test`.`t1`.`longlong` <> -1) and ((`test`.`t1`.`real_float` < 1) or (`test`.`t1`.`real_float` > 2)) and ((`test`.`t1`.`real_double` < 1) or (`test`.`t1`.`real_double` > 2)) and ((`test`.`t1`.`real_decimal` < 1.0000) or (`test`.`t1`.`real_decimal` > 2.0000)) and (`test`.`t1`.`utiny` <> 1) and (`test`.`t1`.`ushort` <> 1) and (`test`.`t1`.`umedium` <> 1) and (`test`.`t1`.`ulong` <> 1) and (`test`.`t1`.`ulonglong` <> 1) and (`test`.`t1`.`options` <> 'one') and (`test`.`t1`.`flags` <> 'one') and (`test`.`t1`.`date_field` <> DATE'1901-01-01') and (`test`.`t1`.`year_field` <> 1901) and (`test`.`t1`.`time_field` <> TIME'01:01:01') and (`test`.`t1`.`date_time` <> TIMESTAMP'1901-01-01 01:01:01')) order by `test`.`t1`.`auto`
965 select auto from t1 where
966 string != "aaaa" and
967 vstring != "aaaa" and
968 bin != 0xAAAA and
969 vbin != 0xAAAA and
970 tiny != -1 and
971 short != -1 and
972 medium != -1 and
973 long_int != -1 and
974 longlong != -1 and
975 (real_float < 1.0 or real_float > 2.0) and
976 (real_double < 1.0 or real_double > 2.0) and
977 (real_decimal < 1.0 or real_decimal > 2.0) and
978 utiny != 1 and
979 ushort != 1 and
980 umedium != 1 and
981 ulong != 1 and
982 ulonglong != 1 and
983 /* bits != b'001' and */
984 options != 'one' and
985 flags != 'one' and
986 date_field != '1901-01-01' and
987 year_field != '1901' and
988 time_field != '01:01:01' and
989 date_time != '1901-01-01 01:01:01'
990 order by auto;
991 auto
992 2
993 3
994 4
995 explain
996 select auto from t1 where
997 string > "aaaa" and
998 vstring > "aaaa" and
999 bin > 0xAAAA and
1000 vbin > 0xAAAA and
1001 tiny < -1 and
1002 short < -1 and
1003 medium < -1 and
1004 long_int < -1 and
1005 longlong < -1 and
1006 real_float > 1.1 and
1007 real_double > 1.1 and
1008 real_decimal > 1.1 and
1009 utiny > 1 and
1010 ushort > 1 and
1011 umedium > 1 and
1012 ulong > 1 and
1013 ulonglong > 1 and
1014 /* bits > b'001' and */
1015 (options = 'two' or options = 'three' or options = 'four') and
1016 (flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
1017 date_field > '1901-01-01' and
1018 year_field > '1901' and
1019 time_field > '01:01:01' and
1020 date_time > '1901-01-01 01:01:01'
1021 order by auto;
1022 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1023 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` > 'aaaa') and (`test`.`t1`.`vstring` > 'aaaa') and (`test`.`t1`.`bin` > 0xaaaa) and (`test`.`t1`.`vbin` > 0xaaaa) and (`test`.`t1`.`tiny` < <cache>(-(1))) and (`test`.`t1`.`short` < <cache>(-(1))) and (`test`.`t1`.`medium` < <cache>(-(1))) and (`test`.`t1`.`long_int` < <cache>(-(1))) and (`test`.`t1`.`longlong` < -1) and (`test`.`t1`.`real_float` > 1.100000023841858) and (`test`.`t1`.`real_double` > 1.1) and (`test`.`t1`.`real_decimal` > 1.1000) and (`test`.`t1`.`utiny` > 1) and (`test`.`t1`.`ushort` > 1) and (`test`.`t1`.`umedium` > 1) and (`test`.`t1`.`ulong` > 1) and (`test`.`t1`.`ulonglong` > 1) and ((`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` > DATE'1901-01-01') and (`test`.`t1`.`year_field` > 1901) and (`test`.`t1`.`time_field` > TIME'01:01:01') and (`test`.`t1`.`date_time` > TIMESTAMP'1901-01-01 01:01:01'))
1024 Warnings:
1025 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` > 'aaaa') and (`test`.`t1`.`vstring` > 'aaaa') and (`test`.`t1`.`bin` > 0xaaaa) and (`test`.`t1`.`vbin` > 0xaaaa) and (`test`.`t1`.`tiny` < <cache>(-(1))) and (`test`.`t1`.`short` < <cache>(-(1))) and (`test`.`t1`.`medium` < <cache>(-(1))) and (`test`.`t1`.`long_int` < <cache>(-(1))) and (`test`.`t1`.`longlong` < -1) and (`test`.`t1`.`real_float` > 1.100000023841858) and (`test`.`t1`.`real_double` > 1.1) and (`test`.`t1`.`real_decimal` > 1.1000) and (`test`.`t1`.`utiny` > 1) and (`test`.`t1`.`ushort` > 1) and (`test`.`t1`.`umedium` > 1) and (`test`.`t1`.`ulong` > 1) and (`test`.`t1`.`ulonglong` > 1) and ((`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` > DATE'1901-01-01') and (`test`.`t1`.`year_field` > 1901) and (`test`.`t1`.`time_field` > TIME'01:01:01') and (`test`.`t1`.`date_time` > TIMESTAMP'1901-01-01 01:01:01')) order by `test`.`t1`.`auto`
1026 select auto from t1 where
1027 string > "aaaa" and
1028 vstring > "aaaa" and
1029 bin > 0xAAAA and
1030 vbin > 0xAAAA and
1031 tiny < -1 and
1032 short < -1 and
1033 medium < -1 and
1034 long_int < -1 and
1035 longlong < -1 and
1036 real_float > 1.1 and
1037 real_double > 1.1 and
1038 real_decimal > 1.1 and
1039 utiny > 1 and
1040 ushort > 1 and
1041 umedium > 1 and
1042 ulong > 1 and
1043 ulonglong > 1 and
1044 /* bits > b'001' and */
1045 (options = 'two' or options = 'three' or options = 'four') and
1046 (flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
1047 date_field > '1901-01-01' and
1048 year_field > '1901' and
1049 time_field > '01:01:01' and
1050 date_time > '1901-01-01 01:01:01'
1051 order by auto;
1052 auto
1053 2
1054 3
1055 4
1056 explain
1057 select auto from t1 where
1058 string >= "aaaa" and
1059 vstring >= "aaaa" and
1060 bin >= 0xAAAA and
1061 vbin >= 0xAAAA and
1062 tiny <= -1 and
1063 short <= -1 and
1064 medium <= -1 and
1065 long_int <= -1 and
1066 longlong <= -1 and
1067 real_float >= 1.0 and
1068 real_double >= 1.0 and
1069 real_decimal >= 1.0 and
1070 utiny >= 1 and
1071 ushort >= 1 and
1072 umedium >= 1 and
1073 ulong >= 1 and
1074 ulonglong >= 1 and
1075 /* bits >= b'001' and */
1076 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
1077 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
1078 date_field >= '1901-01-01' and
1079 year_field >= '1901' and
1080 time_field >= '01:01:01' and
1081 date_time >= '1901-01-01 01:01:01'
1082 order by auto;
1083 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1084 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` >= 'aaaa') and (`test`.`t1`.`vstring` >= 'aaaa') and (`test`.`t1`.`bin` >= 0xaaaa) and (`test`.`t1`.`vbin` >= 0xaaaa) and (`test`.`t1`.`tiny` <= <cache>(-(1))) and (`test`.`t1`.`short` <= <cache>(-(1))) and (`test`.`t1`.`medium` <= <cache>(-(1))) and (`test`.`t1`.`long_int` <= <cache>(-(1))) and (`test`.`t1`.`longlong` <= -1) and (`test`.`t1`.`real_float` >= 1) and (`test`.`t1`.`real_double` >= 1) and (`test`.`t1`.`real_decimal` >= 1.0000) and (`test`.`t1`.`utiny` >= 1) and (`test`.`t1`.`ushort` >= 1) and (`test`.`t1`.`umedium` >= 1) and (`test`.`t1`.`ulong` >= 1) and (`test`.`t1`.`ulonglong` >= 1) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` >= DATE'1901-01-01') and (`test`.`t1`.`year_field` >= 1901) and (`test`.`t1`.`time_field` >= TIME'01:01:01') and (`test`.`t1`.`date_time` >= TIMESTAMP'1901-01-01 01:01:01'))
1085 Warnings:
1086 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` >= 'aaaa') and (`test`.`t1`.`vstring` >= 'aaaa') and (`test`.`t1`.`bin` >= 0xaaaa) and (`test`.`t1`.`vbin` >= 0xaaaa) and (`test`.`t1`.`tiny` <= <cache>(-(1))) and (`test`.`t1`.`short` <= <cache>(-(1))) and (`test`.`t1`.`medium` <= <cache>(-(1))) and (`test`.`t1`.`long_int` <= <cache>(-(1))) and (`test`.`t1`.`longlong` <= -1) and (`test`.`t1`.`real_float` >= 1) and (`test`.`t1`.`real_double` >= 1) and (`test`.`t1`.`real_decimal` >= 1.0000) and (`test`.`t1`.`utiny` >= 1) and (`test`.`t1`.`ushort` >= 1) and (`test`.`t1`.`umedium` >= 1) and (`test`.`t1`.`ulong` >= 1) and (`test`.`t1`.`ulonglong` >= 1) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` >= DATE'1901-01-01') and (`test`.`t1`.`year_field` >= 1901) and (`test`.`t1`.`time_field` >= TIME'01:01:01') and (`test`.`t1`.`date_time` >= TIMESTAMP'1901-01-01 01:01:01')) order by `test`.`t1`.`auto`
1087 select auto from t1 where
1088 string >= "aaaa" and
1089 vstring >= "aaaa" and
1090 bin >= 0xAAAA and
1091 vbin >= 0xAAAA and
1092 tiny <= -1 and
1093 short <= -1 and
1094 medium <= -1 and
1095 long_int <= -1 and
1096 longlong <= -1 and
1097 real_float >= 1.0 and
1098 real_double >= 1.0 and
1099 real_decimal >= 1.0 and
1100 utiny >= 1 and
1101 ushort >= 1 and
1102 umedium >= 1 and
1103 ulong >= 1 and
1104 ulonglong >= 1 and
1105 /* bits >= b'001' and */
1106 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
1107 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
1108 date_field >= '1901-01-01' and
1109 year_field >= '1901' and
1110 time_field >= '01:01:01' and
1111 date_time >= '1901-01-01 01:01:01'
1112 order by auto;
1113 auto
1114 1
1115 2
1116 3
1117 4
1118 explain
1119 select auto from t1 where
1120 string < "dddd" and
1121 vstring < "dddd" and
1122 bin < 0xDDDD and
1123 vbin < 0xDDDD and
1124 tiny > -4 and
1125 short > -4 and
1126 medium > -4 and
1127 long_int > -4 and
1128 longlong > -4 and
1129 real_float < 4.4 and
1130 real_double < 4.4 and
1131 real_decimal < 4.4 and
1132 utiny < 4 and
1133 ushort < 4 and
1134 umedium < 4 and
1135 ulong < 4 and
1136 ulonglong < 4 and
1137 /* bits < b'100' and */
1138 (options = 'one' or options = 'two' or options = 'three') and
1139 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
1140 date_field < '1904-01-01' and
1141 year_field < '1904' and
1142 time_field < '04:04:04' and
1143 date_time < '1904-04-04 04:04:04'
1144 order by auto;
1145 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1146 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` < 'dddd') and (`test`.`t1`.`vstring` < 'dddd') and (`test`.`t1`.`bin` < 0xdddd) and (`test`.`t1`.`vbin` < 0xdddd) and (`test`.`t1`.`tiny` > <cache>(-(4))) and (`test`.`t1`.`short` > <cache>(-(4))) and (`test`.`t1`.`medium` > <cache>(-(4))) and (`test`.`t1`.`long_int` > <cache>(-(4))) and (`test`.`t1`.`longlong` > -4) and (`test`.`t1`.`real_float` < 4.400000095367432) and (`test`.`t1`.`real_double` < 4.4) and (`test`.`t1`.`real_decimal` < 4.4000) and (`test`.`t1`.`utiny` < 4) and (`test`.`t1`.`ushort` < 4) and (`test`.`t1`.`umedium` < 4) and (`test`.`t1`.`ulong` < 4) and (`test`.`t1`.`ulonglong` < 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three')) and (`test`.`t1`.`date_field` < DATE'1904-01-01') and (`test`.`t1`.`year_field` < 1904) and (`test`.`t1`.`time_field` < TIME'04:04:04') and (`test`.`t1`.`date_time` < TIMESTAMP'1904-04-04 04:04:04'))
1147 Warnings:
1148 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` < 'dddd') and (`test`.`t1`.`vstring` < 'dddd') and (`test`.`t1`.`bin` < 0xdddd) and (`test`.`t1`.`vbin` < 0xdddd) and (`test`.`t1`.`tiny` > <cache>(-(4))) and (`test`.`t1`.`short` > <cache>(-(4))) and (`test`.`t1`.`medium` > <cache>(-(4))) and (`test`.`t1`.`long_int` > <cache>(-(4))) and (`test`.`t1`.`longlong` > -4) and (`test`.`t1`.`real_float` < 4.400000095367432) and (`test`.`t1`.`real_double` < 4.4) and (`test`.`t1`.`real_decimal` < 4.4000) and (`test`.`t1`.`utiny` < 4) and (`test`.`t1`.`ushort` < 4) and (`test`.`t1`.`umedium` < 4) and (`test`.`t1`.`ulong` < 4) and (`test`.`t1`.`ulonglong` < 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three')) and (`test`.`t1`.`date_field` < DATE'1904-01-01') and (`test`.`t1`.`year_field` < 1904) and (`test`.`t1`.`time_field` < TIME'04:04:04') and (`test`.`t1`.`date_time` < TIMESTAMP'1904-04-04 04:04:04')) order by `test`.`t1`.`auto`
1149 select auto from t1 where
1150 string < "dddd" and
1151 vstring < "dddd" and
1152 bin < 0xDDDD and
1153 vbin < 0xDDDD and
1154 tiny > -4 and
1155 short > -4 and
1156 medium > -4 and
1157 long_int > -4 and
1158 longlong > -4 and
1159 real_float < 4.4 and
1160 real_double < 4.4 and
1161 real_decimal < 4.4 and
1162 utiny < 4 and
1163 ushort < 4 and
1164 umedium < 4 and
1165 ulong < 4 and
1166 ulonglong < 4 and
1167 /* bits < b'100' and */
1168 (options = 'one' or options = 'two' or options = 'three') and
1169 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
1170 date_field < '1904-01-01' and
1171 year_field < '1904' and
1172 time_field < '04:04:04' and
1173 date_time < '1904-04-04 04:04:04'
1174 order by auto;
1175 auto
1176 1
1177 2
1178 3
1179 explain
1180 select auto from t1 where
1181 string <= "dddd" and
1182 vstring <= "dddd" and
1183 bin <= 0xDDDD and
1184 vbin <= 0xDDDD and
1185 tiny >= -4 and
1186 short >= -4 and
1187 medium >= -4 and
1188 long_int >= -4 and
1189 longlong >= -4 and
1190 real_float <= 4.5 and
1191 real_double <= 4.5 and
1192 real_decimal <= 4.5 and
1193 utiny <= 4 - 1 + 1 and /* Checking function composition */
1194 ushort <= 4 and
1195 umedium <= 4 and
1196 ulong <= 4 and
1197 ulonglong <= 4 and
1198 /* bits <= b'100' and */
1199 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
1200 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
1201 date_field <= '1904-04-04' and
1202 year_field <= '1904' and
1203 time_field <= '04:04:04' and
1204 date_time <= '1904-04-04 04:04:04'
1205 order by auto;
1206 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1207 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` <= 'dddd') and (`test`.`t1`.`vstring` <= 'dddd') and (`test`.`t1`.`bin` <= 0xdddd) and (`test`.`t1`.`vbin` <= 0xdddd) and (`test`.`t1`.`tiny` >= <cache>(-(4))) and (`test`.`t1`.`short` >= <cache>(-(4))) and (`test`.`t1`.`medium` >= <cache>(-(4))) and (`test`.`t1`.`long_int` >= <cache>(-(4))) and (`test`.`t1`.`longlong` >= -4) and (`test`.`t1`.`real_float` <= 4.5) and (`test`.`t1`.`real_double` <= 4.5) and (`test`.`t1`.`real_decimal` <= 4.5000) and (`test`.`t1`.`utiny` <= <cache>(((4 - 1) + 1))) and (`test`.`t1`.`ushort` <= 4) and (`test`.`t1`.`umedium` <= 4) and (`test`.`t1`.`ulong` <= 4) and (`test`.`t1`.`ulonglong` <= 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` <= DATE'1904-04-04') and (`test`.`t1`.`year_field` <= 1904) and (`test`.`t1`.`time_field` <= TIME'04:04:04') and (`test`.`t1`.`date_time` <= TIMESTAMP'1904-04-04 04:04:04'))
1208 Warnings:
1209 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` <= 'dddd') and (`test`.`t1`.`vstring` <= 'dddd') and (`test`.`t1`.`bin` <= 0xdddd) and (`test`.`t1`.`vbin` <= 0xdddd) and (`test`.`t1`.`tiny` >= <cache>(-(4))) and (`test`.`t1`.`short` >= <cache>(-(4))) and (`test`.`t1`.`medium` >= <cache>(-(4))) and (`test`.`t1`.`long_int` >= <cache>(-(4))) and (`test`.`t1`.`longlong` >= -4) and (`test`.`t1`.`real_float` <= 4.5) and (`test`.`t1`.`real_double` <= 4.5) and (`test`.`t1`.`real_decimal` <= 4.5000) and (`test`.`t1`.`utiny` <= <cache>(((4 - 1) + 1))) and (`test`.`t1`.`ushort` <= 4) and (`test`.`t1`.`umedium` <= 4) and (`test`.`t1`.`ulong` <= 4) and (`test`.`t1`.`ulonglong` <= 4) and ((`test`.`t1`.`options` = 'one') or (`test`.`t1`.`options` = 'two') or (`test`.`t1`.`options` = 'three') or (`test`.`t1`.`options` = 'four')) and ((`test`.`t1`.`flags` = 'one') or (`test`.`t1`.`flags` = 'one,two') or (`test`.`t1`.`flags` = 'one,two,three') or (`test`.`t1`.`flags` = 'one,two,three,four')) and (`test`.`t1`.`date_field` <= DATE'1904-04-04') and (`test`.`t1`.`year_field` <= 1904) and (`test`.`t1`.`time_field` <= TIME'04:04:04') and (`test`.`t1`.`date_time` <= TIMESTAMP'1904-04-04 04:04:04')) order by `test`.`t1`.`auto`
1210 select auto from t1 where
1211 string <= "dddd" and
1212 vstring <= "dddd" and
1213 bin <= 0xDDDD and
1214 vbin <= 0xDDDD and
1215 tiny >= -4 and
1216 short >= -4 and
1217 medium >= -4 and
1218 long_int >= -4 and
1219 longlong >= -4 and
1220 real_float <= 4.5 and
1221 real_double <= 4.5 and
1222 real_decimal <= 4.5 and
1223 utiny <= 4 - 1 + 1 and /* Checking function composition */
1224 ushort <= 4 and
1225 umedium <= 4 and
1226 ulong <= 4 and
1227 ulonglong <= 4 and
1228 /* bits <= b'100' and */
1229 (options = 'one' or options = 'two' or options = 'three' or options = 'four') and
1230 (flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
1231 date_field <= '1904-04-04' and
1232 year_field <= '1904' and
1233 time_field <= '04:04:04' and
1234 date_time <= '1904-04-04 04:04:04'
1235 order by auto;
1236 auto
1237 1
1238 2
1239 3
1240 4
1241 explain
1242 select auto from t1 where
1243 string like "b%" and
1244 vstring like "b%" and
1245 bin like concat(0xBB, '%') and
1246 vbin like concat(0xBB, '%')
1247 order by auto;
1248 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1249 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` like 'b%') and (`test`.`t1`.`vstring` like 'b%') and (`test`.`t1`.`bin` like <cache>(concat(0xbb,'%'))) and (`test`.`t1`.`vbin` like <cache>(concat(0xbb,'%'))))
1250 Warnings:
1251 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` like 'b%') and (`test`.`t1`.`vstring` like 'b%') and (`test`.`t1`.`bin` like <cache>(concat(0xbb,'%'))) and (`test`.`t1`.`vbin` like <cache>(concat(0xbb,'%')))) order by `test`.`t1`.`auto`
1252 select auto from t1 where
1253 string like "b%" and
1254 vstring like "b%" and
1255 bin like concat(0xBB, '%') and
1256 vbin like concat(0xBB, '%')
1257 order by auto;
1258 auto
1259 2
1260 explain
1261 select auto from t1 where
1262 string not like "b%" and
1263 vstring not like "b%" and
1264 bin not like concat(0xBB, '%') and
1265 vbin not like concat(0xBB, '%')
1266 order by auto;
1267 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1268 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((not((`test`.`t1`.`string` like 'b%'))) and (not((`test`.`t1`.`vstring` like 'b%'))) and (not((`test`.`t1`.`bin` like <cache>(concat(0xbb,'%'))))) and (not((`test`.`t1`.`vbin` like <cache>(concat(0xbb,'%'))))))
1269 Warnings:
1270 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((not((`test`.`t1`.`string` like 'b%'))) and (not((`test`.`t1`.`vstring` like 'b%'))) and (not((`test`.`t1`.`bin` like <cache>(concat(0xbb,'%'))))) and (not((`test`.`t1`.`vbin` like <cache>(concat(0xbb,'%')))))) order by `test`.`t1`.`auto`
1271 select auto from t1 where
1272 string not like "b%" and
1273 vstring not like "b%" and
1274 bin not like concat(0xBB, '%') and
1275 vbin not like concat(0xBB, '%')
1276 order by auto;
1277 auto
1278 1
1279 3
1280 4
1281 explain
1282 select auto from t1 where
1283 (string between "aaaa" and "cccc") and
1284 (vstring between "aaaa" and "cccc") and
1285 (bin between 0xAAAA and 0xCCCC) and
1286 (vbin between 0xAAAA and 0xCCCC) and
1287 (tiny between -3 and -1) and
1288 (short between -3 and -1) and
1289 (medium between -3 and -1) and
1290 (long_int between -3 and -1) and
1291 (longlong between -3 and -1) and
1292 (utiny between 1 and 3) and
1293 (ushort between 1 and 3) and
1294 (umedium between 1 and 3) and
1295 (ulong between 1 and 3) and
1296 (ulonglong between 1 and 3) and
1297 /* (bits between b'001' and b'011') and */
1298 (options between 'one' and 'three') and
1299 (flags between 'one' and 'one,two,three') and
1300 (date_field between '1901-01-01' and '1903-03-03') and
1301 (year_field between '1901' and '1903') and
1302 (time_field between '01:01:01' and '03:03:03') and
1303 (date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
1304 order by auto;
1305 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1306 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using where; Using pushed condition ((`test`.`t1`.`string` between 'aaaa' and 'cccc') and (`test`.`t1`.`vstring` between 'aaaa' and 'cccc') and (`test`.`t1`.`bin` between 0xaaaa and 0xcccc) and (`test`.`t1`.`vbin` between 0xaaaa and 0xcccc) and (`test`.`t1`.`tiny` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`short` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`medium` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`long_int` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`longlong` between -3 and -1) and (`test`.`t1`.`utiny` between 1 and 3) and (`test`.`t1`.`ushort` between 1 and 3) and (`test`.`t1`.`umedium` between 1 and 3) and (`test`.`t1`.`ulong` between 1 and 3) and (`test`.`t1`.`ulonglong` between 1 and 3) and (`test`.`t1`.`flags` between 'one' and 'one,two,three') and (`test`.`t1`.`date_field` between '1901-01-01' and '1903-03-03') and (`test`.`t1`.`year_field` between 1901 and 1903) and (`test`.`t1`.`time_field` between '01:01:01' and '03:03:03') and (`test`.`t1`.`date_time` between '1901-01-01 01:01:01' and '1903-03-03 03:03:03'))
1307 Warnings:
1308 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` between 'aaaa' and 'cccc') and (`test`.`t1`.`vstring` between 'aaaa' and 'cccc') and (`test`.`t1`.`bin` between 0xaaaa and 0xcccc) and (`test`.`t1`.`vbin` between 0xaaaa and 0xcccc) and (`test`.`t1`.`tiny` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`short` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`medium` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`long_int` between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`longlong` between -3 and -1) and (`test`.`t1`.`utiny` between 1 and 3) and (`test`.`t1`.`ushort` between 1 and 3) and (`test`.`t1`.`umedium` between 1 and 3) and (`test`.`t1`.`ulong` between 1 and 3) and (`test`.`t1`.`ulonglong` between 1 and 3) and (`test`.`t1`.`options` between 'one' and 'three') and (`test`.`t1`.`flags` between 'one' and 'one,two,three') and (`test`.`t1`.`date_field` between '1901-01-01' and '1903-03-03') and (`test`.`t1`.`year_field` between 1901 and 1903) and (`test`.`t1`.`time_field` between '01:01:01' and '03:03:03') and (`test`.`t1`.`date_time` between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')) order by `test`.`t1`.`auto`
1309 select auto from t1 where
1310 (string between "aaaa" and "cccc") and
1311 (vstring between "aaaa" and "cccc") and
1312 (bin between 0xAAAA and 0xCCCC) and
1313 (vbin between 0xAAAA and 0xCCCC) and
1314 (tiny between -3 and -1) and
1315 (short between -3 and -1) and
1316 (medium between -3 and -1) and
1317 (long_int between -3 and -1) and
1318 (longlong between -3 and -1) and
1319 (utiny between 1 and 3) and
1320 (ushort between 1 and 3) and
1321 (umedium between 1 and 3) and
1322 (ulong between 1 and 3) and
1323 (ulonglong between 1 and 3) and
1324 /* (bits between b'001' and b'011') and */
1325 (options between 'one' and 'three') and
1326 (flags between 'one' and 'one,two,three') and
1327 (date_field between '1901-01-01' and '1903-03-03') and
1328 (year_field between '1901' and '1903') and
1329 (time_field between '01:01:01' and '03:03:03') and
1330 (date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
1331 order by auto;
1332 auto
1333 1
1334 3
1335 explain
1336 select auto from t1 where
1337 ("aaaa" between string and string) and
1338 ("aaaa" between vstring and vstring) and
1339 (0xAAAA between bin and bin) and
1340 (0xAAAA between vbin and vbin) and
1341 (-1 between tiny and tiny) and
1342 (-1 between short and short) and
1343 (-1 between medium and medium) and
1344 (-1 between long_int and long_int) and
1345 (-1 between longlong and longlong) and
1346 (1 between utiny and utiny) and
1347 (1 between ushort and ushort) and
1348 (1 between umedium and umedium) and
1349 (1 between ulong and ulong) and
1350 (1 between ulonglong and ulonglong) and
1351 /* (b'001' between bits and bits) and */
1352 ('one' between options and options) and
1353 ('one' between flags and flags) and
1354 ('1901-01-01' between date_field and date_field) and
1355 ('1901' between year_field and year_field) and
1356 ('01:01:01' between time_field and time_field) and
1357 ('1901-01-01 01:01:01' between date_time and date_time)
1358 order by auto;
1359 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1360 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref medium_index medium_index 3 const # # Using pushed condition (('aaaa' between `test`.`t1`.`string` and `test`.`t1`.`string`) and ('aaaa' between `test`.`t1`.`vstring` and `test`.`t1`.`vstring`) and (0xaaaa between `test`.`t1`.`bin` and `test`.`t1`.`bin`) and (0xaaaa between `test`.`t1`.`vbin` and `test`.`t1`.`vbin`) and (<cache>(-(1)) between `test`.`t1`.`tiny` and `test`.`t1`.`tiny`) and (<cache>(-(1)) between `test`.`t1`.`short` and `test`.`t1`.`short`) and (<cache>(-(1)) between `test`.`t1`.`medium` and `test`.`t1`.`medium`) and (<cache>(-(1)) between `test`.`t1`.`long_int` and `test`.`t1`.`long_int`) and (<cache>(-(1)) between `test`.`t1`.`longlong` and `test`.`t1`.`longlong`) and (1 between `test`.`t1`.`utiny` and `test`.`t1`.`utiny`) and (1 between `test`.`t1`.`ushort` and `test`.`t1`.`ushort`) and (1 between `test`.`t1`.`umedium` and `test`.`t1`.`umedium`) and (1 between `test`.`t1`.`ulong` and `test`.`t1`.`ulong`) and (1 between `test`.`t1`.`ulonglong` and `test`.`t1`.`ulonglong`) and ('one' between `test`.`t1`.`options` and `test`.`t1`.`options`) and ('one' between `test`.`t1`.`flags` and `test`.`t1`.`flags`) and ('1901-01-01' between `test`.`t1`.`date_field` and `test`.`t1`.`date_field`) and ('1901' between `test`.`t1`.`year_field` and `test`.`t1`.`year_field`) and ('01:01:01' between `test`.`t1`.`time_field` and `test`.`t1`.`time_field`) and ('1901-01-01 01:01:01' between `test`.`t1`.`date_time` and `test`.`t1`.`date_time`)); Using filesort
1361 Warnings:
1362 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where (('aaaa' between `test`.`t1`.`string` and `test`.`t1`.`string`) and ('aaaa' between `test`.`t1`.`vstring` and `test`.`t1`.`vstring`) and (0xaaaa between `test`.`t1`.`bin` and `test`.`t1`.`bin`) and (0xaaaa between `test`.`t1`.`vbin` and `test`.`t1`.`vbin`) and (<cache>(-(1)) between `test`.`t1`.`tiny` and `test`.`t1`.`tiny`) and (<cache>(-(1)) between `test`.`t1`.`short` and `test`.`t1`.`short`) and (<cache>(-(1)) between `test`.`t1`.`medium` and `test`.`t1`.`medium`) and (<cache>(-(1)) between `test`.`t1`.`long_int` and `test`.`t1`.`long_int`) and (<cache>(-(1)) between `test`.`t1`.`longlong` and `test`.`t1`.`longlong`) and (1 between `test`.`t1`.`utiny` and `test`.`t1`.`utiny`) and (1 between `test`.`t1`.`ushort` and `test`.`t1`.`ushort`) and (1 between `test`.`t1`.`umedium` and `test`.`t1`.`umedium`) and (1 between `test`.`t1`.`ulong` and `test`.`t1`.`ulong`) and (1 between `test`.`t1`.`ulonglong` and `test`.`t1`.`ulonglong`) and ('one' between `test`.`t1`.`options` and `test`.`t1`.`options`) and ('one' between `test`.`t1`.`flags` and `test`.`t1`.`flags`) and ('1901-01-01' between `test`.`t1`.`date_field` and `test`.`t1`.`date_field`) and ('1901' between `test`.`t1`.`year_field` and `test`.`t1`.`year_field`) and ('01:01:01' between `test`.`t1`.`time_field` and `test`.`t1`.`time_field`) and ('1901-01-01 01:01:01' between `test`.`t1`.`date_time` and `test`.`t1`.`date_time`)) order by `test`.`t1`.`auto`
1363 select auto from t1 where
1364 ("aaaa" between string and string) and
1365 ("aaaa" between vstring and vstring) and
1366 (0xAAAA between bin and bin) and
1367 (0xAAAA between vbin and vbin) and
1368 (-1 between tiny and tiny) and
1369 (-1 between short and short) and
1370 (-1 between medium and medium) and
1371 (-1 between long_int and long_int) and
1372 (-1 between longlong and longlong) and
1373 (1 between utiny and utiny) and
1374 (1 between ushort and ushort) and
1375 (1 between umedium and umedium) and
1376 (1 between ulong and ulong) and
1377 (1 between ulonglong and ulonglong) and
1378 /* (b'001' between bits and bits) and */
1379 ('one' between options and options) and
1380 ('one' between flags and flags) and
1381 ('1901-01-01' between date_field and date_field) and
1382 ('1901' between year_field and year_field) and
1383 ('01:01:01' between time_field and time_field) and
1384 ('1901-01-01 01:01:01' between date_time and date_time)
1385 order by auto;
1386 auto
1387 1
1388 explain
1389 select auto from t1 where
1390 (string not between "aaaa" and "cccc") and
1391 (vstring not between "aaaa" and "cccc") and
1392 (bin not between 0xAAAA and 0xCCCC) and
1393 (vbin not between 0xAAAA and 0xCCCC) and
1394 (tiny not between -3 and -1) and
1395 (short not between -3 and -1) and
1396 (medium not between -3 and -1) and
1397 (long_int not between -3 and -1) and
1398 (longlong not between -3 and -1) and
1399 (utiny not between 1 and 3) and
1400 (ushort not between 1 and 3) and
1401 (umedium not between 1 and 3) and
1402 (ulong not between 1 and 3) and
1403 (ulonglong not between 1 and 3) and
1404 /* (bits not between b'001' and b'011') and */
1405 (options not between 'one' and 'three') and
1406 (flags not between 'one' and 'one,two,three') and
1407 (date_field not between '1901-01-01' and '1903-03-03') and
1408 (year_field not between '1901' and '1903') and
1409 (time_field not between '01:01:01' and '03:03:03') and
1410 (date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
1411 order by auto;
1412 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1413 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using where; Using pushed condition ((`test`.`t1`.`string` not between 'aaaa' and 'cccc') and (`test`.`t1`.`vstring` not between 'aaaa' and 'cccc') and (`test`.`t1`.`bin` not between 0xaaaa and 0xcccc) and (`test`.`t1`.`vbin` not between 0xaaaa and 0xcccc) and (`test`.`t1`.`tiny` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`short` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`medium` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`long_int` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`longlong` not between -3 and -1) and (`test`.`t1`.`utiny` not between 1 and 3) and (`test`.`t1`.`ushort` not between 1 and 3) and (`test`.`t1`.`umedium` not between 1 and 3) and (`test`.`t1`.`ulong` not between 1 and 3) and (`test`.`t1`.`ulonglong` not between 1 and 3) and (`test`.`t1`.`flags` not between 'one' and 'one,two,three') and (`test`.`t1`.`date_field` not between '1901-01-01' and '1903-03-03') and (`test`.`t1`.`year_field` not between 1901 and 1903) and (`test`.`t1`.`time_field` not between '01:01:01' and '03:03:03') and (`test`.`t1`.`date_time` not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03'))
1414 Warnings:
1415 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` not between 'aaaa' and 'cccc') and (`test`.`t1`.`vstring` not between 'aaaa' and 'cccc') and (`test`.`t1`.`bin` not between 0xaaaa and 0xcccc) and (`test`.`t1`.`vbin` not between 0xaaaa and 0xcccc) and (`test`.`t1`.`tiny` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`short` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`medium` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`long_int` not between <cache>(-(3)) and <cache>(-(1))) and (`test`.`t1`.`longlong` not between -3 and -1) and (`test`.`t1`.`utiny` not between 1 and 3) and (`test`.`t1`.`ushort` not between 1 and 3) and (`test`.`t1`.`umedium` not between 1 and 3) and (`test`.`t1`.`ulong` not between 1 and 3) and (`test`.`t1`.`ulonglong` not between 1 and 3) and (`test`.`t1`.`options` not between 'one' and 'three') and (`test`.`t1`.`flags` not between 'one' and 'one,two,three') and (`test`.`t1`.`date_field` not between '1901-01-01' and '1903-03-03') and (`test`.`t1`.`year_field` not between 1901 and 1903) and (`test`.`t1`.`time_field` not between '01:01:01' and '03:03:03') and (`test`.`t1`.`date_time` not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')) order by `test`.`t1`.`auto`
1416 select auto from t1 where
1417 (string not between "aaaa" and "cccc") and
1418 (vstring not between "aaaa" and "cccc") and
1419 (bin not between 0xAAAA and 0xCCCC) and
1420 (vbin not between 0xAAAA and 0xCCCC) and
1421 (tiny not between -3 and -1) and
1422 (short not between -3 and -1) and
1423 (medium not between -3 and -1) and
1424 (long_int not between -3 and -1) and
1425 (longlong not between -3 and -1) and
1426 (utiny not between 1 and 3) and
1427 (ushort not between 1 and 3) and
1428 (umedium not between 1 and 3) and
1429 (ulong not between 1 and 3) and
1430 (ulonglong not between 1 and 3) and
1431 /* (bits not between b'001' and b'011') and */
1432 (options not between 'one' and 'three') and
1433 (flags not between 'one' and 'one,two,three') and
1434 (date_field not between '1901-01-01' and '1903-03-03') and
1435 (year_field not between '1901' and '1903') and
1436 (time_field not between '01:01:01' and '03:03:03') and
1437 (date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
1438 order by auto;
1439 auto
1440 4
1441 explain
1442 select auto from t1 where
1443 ("aaaa" not between string and string) and
1444 ("aaaa" not between vstring and vstring) and
1445 (0xAAAA not between bin and bin) and
1446 (0xAAAA not between vbin and vbin) and
1447 (-1 not between tiny and tiny) and
1448 (-1 not between short and short) and
1449 (-1 not between medium and medium) and
1450 (-1 not between long_int and long_int) and
1451 (-1 not between longlong and longlong) and
1452 (1 not between utiny and utiny) and
1453 (1 not between ushort and ushort) and
1454 (1 not between umedium and umedium) and
1455 (1 not between ulong and ulong) and
1456 (1 not between ulonglong and ulonglong) and
1457 /* (b'001' not between bits and bits) and */
1458 ('one' not between options and options) and
1459 ('one' not between flags and flags) and
1460 ('1901-01-01' not between date_field and date_field) and
1461 ('1901' not between year_field and year_field) and
1462 ('01:01:01' not between time_field and time_field) and
1463 ('1901-01-01 01:01:01' not between date_time and date_time)
1464 order by auto;
1465 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1466 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition (('aaaa' not between `test`.`t1`.`string` and `test`.`t1`.`string`) and ('aaaa' not between `test`.`t1`.`vstring` and `test`.`t1`.`vstring`) and (0xaaaa not between `test`.`t1`.`bin` and `test`.`t1`.`bin`) and (0xaaaa not between `test`.`t1`.`vbin` and `test`.`t1`.`vbin`) and (<cache>(-(1)) not between `test`.`t1`.`tiny` and `test`.`t1`.`tiny`) and (<cache>(-(1)) not between `test`.`t1`.`short` and `test`.`t1`.`short`) and (<cache>(-(1)) not between `test`.`t1`.`medium` and `test`.`t1`.`medium`) and (<cache>(-(1)) not between `test`.`t1`.`long_int` and `test`.`t1`.`long_int`) and (<cache>(-(1)) not between `test`.`t1`.`longlong` and `test`.`t1`.`longlong`) and (1 not between `test`.`t1`.`utiny` and `test`.`t1`.`utiny`) and (1 not between `test`.`t1`.`ushort` and `test`.`t1`.`ushort`) and (1 not between `test`.`t1`.`umedium` and `test`.`t1`.`umedium`) and (1 not between `test`.`t1`.`ulong` and `test`.`t1`.`ulong`) and (1 not between `test`.`t1`.`ulonglong` and `test`.`t1`.`ulonglong`) and ('one' not between `test`.`t1`.`options` and `test`.`t1`.`options`) and ('one' not between `test`.`t1`.`flags` and `test`.`t1`.`flags`) and ('1901-01-01' not between `test`.`t1`.`date_field` and `test`.`t1`.`date_field`) and ('1901' not between `test`.`t1`.`year_field` and `test`.`t1`.`year_field`) and ('01:01:01' not between `test`.`t1`.`time_field` and `test`.`t1`.`time_field`) and ('1901-01-01 01:01:01' not between `test`.`t1`.`date_time` and `test`.`t1`.`date_time`))
1467 Warnings:
1468 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where (('aaaa' not between `test`.`t1`.`string` and `test`.`t1`.`string`) and ('aaaa' not between `test`.`t1`.`vstring` and `test`.`t1`.`vstring`) and (0xaaaa not between `test`.`t1`.`bin` and `test`.`t1`.`bin`) and (0xaaaa not between `test`.`t1`.`vbin` and `test`.`t1`.`vbin`) and (<cache>(-(1)) not between `test`.`t1`.`tiny` and `test`.`t1`.`tiny`) and (<cache>(-(1)) not between `test`.`t1`.`short` and `test`.`t1`.`short`) and (<cache>(-(1)) not between `test`.`t1`.`medium` and `test`.`t1`.`medium`) and (<cache>(-(1)) not between `test`.`t1`.`long_int` and `test`.`t1`.`long_int`) and (<cache>(-(1)) not between `test`.`t1`.`longlong` and `test`.`t1`.`longlong`) and (1 not between `test`.`t1`.`utiny` and `test`.`t1`.`utiny`) and (1 not between `test`.`t1`.`ushort` and `test`.`t1`.`ushort`) and (1 not between `test`.`t1`.`umedium` and `test`.`t1`.`umedium`) and (1 not between `test`.`t1`.`ulong` and `test`.`t1`.`ulong`) and (1 not between `test`.`t1`.`ulonglong` and `test`.`t1`.`ulonglong`) and ('one' not between `test`.`t1`.`options` and `test`.`t1`.`options`) and ('one' not between `test`.`t1`.`flags` and `test`.`t1`.`flags`) and ('1901-01-01' not between `test`.`t1`.`date_field` and `test`.`t1`.`date_field`) and ('1901' not between `test`.`t1`.`year_field` and `test`.`t1`.`year_field`) and ('01:01:01' not between `test`.`t1`.`time_field` and `test`.`t1`.`time_field`) and ('1901-01-01 01:01:01' not between `test`.`t1`.`date_time` and `test`.`t1`.`date_time`)) order by `test`.`t1`.`auto`
1469 select auto from t1 where
1470 ("aaaa" not between string and string) and
1471 ("aaaa" not between vstring and vstring) and
1472 (0xAAAA not between bin and bin) and
1473 (0xAAAA not between vbin and vbin) and
1474 (-1 not between tiny and tiny) and
1475 (-1 not between short and short) and
1476 (-1 not between medium and medium) and
1477 (-1 not between long_int and long_int) and
1478 (-1 not between longlong and longlong) and
1479 (1 not between utiny and utiny) and
1480 (1 not between ushort and ushort) and
1481 (1 not between umedium and umedium) and
1482 (1 not between ulong and ulong) and
1483 (1 not between ulonglong and ulonglong) and
1484 /* (b'001' not between bits and bits) and */
1485 ('one' not between options and options) and
1486 ('one' not between flags and flags) and
1487 ('1901-01-01' not between date_field and date_field) and
1488 ('1901' not between year_field and year_field) and
1489 ('01:01:01' not between time_field and time_field) and
1490 ('1901-01-01 01:01:01' not between date_time and date_time)
1491 order by auto;
1492 auto
1493 2
1494 3
1495 4
1496 explain
1497 select auto from t1 where
1498 string in("aaaa","cccc") and
1499 vstring in("aaaa","cccc") and
1500 bin in(0xAAAA,0xCCCC) and
1501 vbin in(0xAAAA,0xCCCC) and
1502 tiny in(-1,-3) and
1503 short in(-1,-3) and
1504 medium in(-1,-3) and
1505 long_int in(-1,-3) and
1506 longlong in(-1,-3) and
1507 utiny in(1,3) and
1508 ushort in(1,3) and
1509 umedium in(1,3) and
1510 ulong in(1,3) and
1511 ulonglong in(1,3) and
1512 /* bits in(b'001',b'011') and */
1513 options in('one','three') and
1514 flags in('one','one,two,three') and
1515 date_field in('1901-01-01','1903-03-03') and
1516 year_field in('1901','1903') and
1517 time_field in('01:01:01','03:03:03') and
1518 date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')
1519 order by auto;
1520 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1521 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` in ('aaaa','cccc')) and (`test`.`t1`.`vstring` in ('aaaa','cccc')) and (`test`.`t1`.`bin` in (0xaaaa,0xcccc)) and (`test`.`t1`.`vbin` in (0xaaaa,0xcccc)) and (`test`.`t1`.`tiny` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`short` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`medium` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`long_int` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`longlong` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`utiny` in (1,3)) and (`test`.`t1`.`ushort` in (1,3)) and (`test`.`t1`.`umedium` in (1,3)) and (`test`.`t1`.`ulong` in (1,3)) and (`test`.`t1`.`ulonglong` in (1,3)) and (`test`.`t1`.`options` in ('one','three')) and (`test`.`t1`.`flags` in ('one','one,two,three')) and (`test`.`t1`.`date_field` in ('1901-01-01','1903-03-03')) and (`test`.`t1`.`year_field` in (1901,1903)) and (`test`.`t1`.`time_field` in ('01:01:01','03:03:03')) and (`test`.`t1`.`date_time` in ('1901-01-01 01:01:01','1903-03-03 03:03:03')))
1522 Warnings:
1523 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` in ('aaaa','cccc')) and (`test`.`t1`.`vstring` in ('aaaa','cccc')) and (`test`.`t1`.`bin` in (0xaaaa,0xcccc)) and (`test`.`t1`.`vbin` in (0xaaaa,0xcccc)) and (`test`.`t1`.`tiny` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`short` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`medium` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`long_int` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`longlong` in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`utiny` in (1,3)) and (`test`.`t1`.`ushort` in (1,3)) and (`test`.`t1`.`umedium` in (1,3)) and (`test`.`t1`.`ulong` in (1,3)) and (`test`.`t1`.`ulonglong` in (1,3)) and (`test`.`t1`.`options` in ('one','three')) and (`test`.`t1`.`flags` in ('one','one,two,three')) and (`test`.`t1`.`date_field` in ('1901-01-01','1903-03-03')) and (`test`.`t1`.`year_field` in (1901,1903)) and (`test`.`t1`.`time_field` in ('01:01:01','03:03:03')) and (`test`.`t1`.`date_time` in ('1901-01-01 01:01:01','1903-03-03 03:03:03'))) order by `test`.`t1`.`auto`
1524 select auto from t1 where
1525 string in("aaaa","cccc") and
1526 vstring in("aaaa","cccc") and
1527 bin in(0xAAAA,0xCCCC) and
1528 vbin in(0xAAAA,0xCCCC) and
1529 tiny in(-1,-3) and
1530 short in(-1,-3) and
1531 medium in(-1,-3) and
1532 long_int in(-1,-3) and
1533 longlong in(-1,-3) and
1534 utiny in(1,3) and
1535 ushort in(1,3) and
1536 umedium in(1,3) and
1537 ulong in(1,3) and
1538 ulonglong in(1,3) and
1539 /* bits in(b'001',b'011') and */
1540 options in('one','three') and
1541 flags in('one','one,two,three') and
1542 date_field in('1901-01-01','1903-03-03') and
1543 year_field in('1901','1903') and
1544 time_field in('01:01:01','03:03:03') and
1545 date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')
1546 order by auto;
1547 auto
1548 1
1549 3
1550 explain
1551 select auto from t1 where
1552 "aaaa" in(string) and
1553 "aaaa" in(vstring) and
1554 0xAAAA in(bin) and
1555 0xAAAA in(vbin) and
1556 (-1 in(tiny)) and
1557 (-1 in (short)) and
1558 (-1 in(medium)) and
1559 (-1 in(long_int)) and
1560 (-1 in(longlong)) and
1561 1 in(utiny) and
1562 1 in(ushort) and
1563 1 in(umedium) and
1564 1 in(ulong) and
1565 1 in(ulonglong) and
1566 /* b'001' in(bits) and */
1567 'one' in(options) and
1568 'one' in(flags) and
1569 '1901-01-01' in(date_field) and
1570 '1901' in(year_field) and
1571 '01:01:01' in(time_field) and
1572 '1901-01-01 01:01:01' in(date_time)
1573 order by auto;
1574 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1575 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref medium_index medium_index 3 const # # Using pushed condition ((`test`.`t1`.`date_time` = TIMESTAMP'1901-01-01 01:01:01') and (`test`.`t1`.`year_field` = 1901) and (`test`.`t1`.`date_field` = DATE'1901-01-01') and (`test`.`t1`.`flags` = 'one') and (`test`.`t1`.`options` = 'one') and (`test`.`t1`.`ulonglong` = 1) and (`test`.`t1`.`ulong` = 1) and (`test`.`t1`.`umedium` = 1) and (`test`.`t1`.`ushort` = 1) and (`test`.`t1`.`utiny` = 1) and (`test`.`t1`.`longlong` = -1) and (`test`.`t1`.`long_int` = <cache>(-(1))) and (`test`.`t1`.`short` = <cache>(-(1))) and (`test`.`t1`.`tiny` = <cache>(-(1))) and (`test`.`t1`.`vbin` = 0xaaaa) and (`test`.`t1`.`bin` = 0xaaaa) and (`test`.`t1`.`vstring` = 'aaaa') and (`test`.`t1`.`string` = 'aaaa') and (TIME'01:01:01' = `test`.`t1`.`time_field`)); Using filesort
1576 Warnings:
1577 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`date_time` = TIMESTAMP'1901-01-01 01:01:01') and (`test`.`t1`.`year_field` = 1901) and (`test`.`t1`.`date_field` = DATE'1901-01-01') and (`test`.`t1`.`flags` = 'one') and (`test`.`t1`.`options` = 'one') and (`test`.`t1`.`ulonglong` = 1) and (`test`.`t1`.`ulong` = 1) and (`test`.`t1`.`umedium` = 1) and (`test`.`t1`.`ushort` = 1) and (`test`.`t1`.`utiny` = 1) and (`test`.`t1`.`longlong` = -1) and (`test`.`t1`.`long_int` = <cache>(-(1))) and (`test`.`t1`.`medium` = -(1)) and (`test`.`t1`.`short` = <cache>(-(1))) and (`test`.`t1`.`tiny` = <cache>(-(1))) and (`test`.`t1`.`vbin` = 0xaaaa) and (`test`.`t1`.`bin` = 0xaaaa) and (`test`.`t1`.`vstring` = 'aaaa') and (`test`.`t1`.`string` = 'aaaa') and (TIME'01:01:01' = `test`.`t1`.`time_field`)) order by `test`.`t1`.`auto`
1578 select auto from t1 where
1579 "aaaa" in(string) and
1580 "aaaa" in(vstring) and
1581 0xAAAA in(bin) and
1582 0xAAAA in(vbin) and
1583 (-1 in(tiny)) and
1584 (-1 in (short)) and
1585 (-1 in(medium)) and
1586 (-1 in(long_int)) and
1587 (-1 in(longlong)) and
1588 1 in(utiny) and
1589 1 in(ushort) and
1590 1 in(umedium) and
1591 1 in(ulong) and
1592 1 in(ulonglong) and
1593 /* b'001' in(bits) and */
1594 'one' in(options) and
1595 'one' in(flags) and
1596 '1901-01-01' in(date_field) and
1597 '1901' in(year_field) and
1598 '01:01:01' in(time_field) and
1599 '1901-01-01 01:01:01' in(date_time)
1600 order by auto;
1601 auto
1602 1
1603 explain
1604 select auto from t1 where
1605 string not in("aaaa","cccc") and
1606 vstring not in("aaaa","cccc") and
1607 bin not in(0xAAAA,0xCCCC) and
1608 vbin not in(0xAAAA,0xCCCC) and
1609 tiny not in(-1,-3) and
1610 short not in(-1,-3) and
1611 medium not in(-1,-3) and
1612 long_int not in(-1,-3) and
1613 longlong not in(-1,-3) and
1614 utiny not in(1,3) and
1615 ushort not in(1,3) and
1616 umedium not in(1,3) and
1617 ulong not in(1,3) and
1618 ulonglong not in(1,3) and
1619 /* bits not in(b'001',b'011') and */
1620 options not in('one','three') and
1621 flags not in('one','one,two,three') and
1622 date_field not in('1901-01-01','1903-03-03') and
1623 year_field not in('1901','1903') and
1624 time_field not in('01:01:01','03:03:03') and
1625 date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')
1626 order by auto;
1627 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1628 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index medium_index PRIMARY 4 NULL # # Using pushed condition ((`test`.`t1`.`string` not in ('aaaa','cccc')) and (`test`.`t1`.`vstring` not in ('aaaa','cccc')) and (`test`.`t1`.`bin` not in (0xaaaa,0xcccc)) and (`test`.`t1`.`vbin` not in (0xaaaa,0xcccc)) and (`test`.`t1`.`tiny` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`short` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`medium` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`long_int` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`longlong` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`utiny` not in (1,3)) and (`test`.`t1`.`ushort` not in (1,3)) and (`test`.`t1`.`umedium` not in (1,3)) and (`test`.`t1`.`ulong` not in (1,3)) and (`test`.`t1`.`ulonglong` not in (1,3)) and (`test`.`t1`.`options` not in ('one','three')) and (`test`.`t1`.`flags` not in ('one','one,two,three')) and (`test`.`t1`.`date_field` not in ('1901-01-01','1903-03-03')) and (`test`.`t1`.`year_field` not in (1901,1903)) and (`test`.`t1`.`time_field` not in ('01:01:01','03:03:03')) and (`test`.`t1`.`date_time` not in ('1901-01-01 01:01:01','1903-03-03 03:03:03')))
1629 Warnings:
1630 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where ((`test`.`t1`.`string` not in ('aaaa','cccc')) and (`test`.`t1`.`vstring` not in ('aaaa','cccc')) and (`test`.`t1`.`bin` not in (0xaaaa,0xcccc)) and (`test`.`t1`.`vbin` not in (0xaaaa,0xcccc)) and (`test`.`t1`.`tiny` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`short` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`medium` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`long_int` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`longlong` not in (<cache>(-(1)),<cache>(-(3)))) and (`test`.`t1`.`utiny` not in (1,3)) and (`test`.`t1`.`ushort` not in (1,3)) and (`test`.`t1`.`umedium` not in (1,3)) and (`test`.`t1`.`ulong` not in (1,3)) and (`test`.`t1`.`ulonglong` not in (1,3)) and (`test`.`t1`.`options` not in ('one','three')) and (`test`.`t1`.`flags` not in ('one','one,two,three')) and (`test`.`t1`.`date_field` not in ('1901-01-01','1903-03-03')) and (`test`.`t1`.`year_field` not in (1901,1903)) and (`test`.`t1`.`time_field` not in ('01:01:01','03:03:03')) and (`test`.`t1`.`date_time` not in ('1901-01-01 01:01:01','1903-03-03 03:03:03'))) order by `test`.`t1`.`auto`
1631 select auto from t1 where
1632 string not in("aaaa","cccc") and
1633 vstring not in("aaaa","cccc") and
1634 bin not in(0xAAAA,0xCCCC) and
1635 vbin not in(0xAAAA,0xCCCC) and
1636 tiny not in(-1,-3) and
1637 short not in(-1,-3) and
1638 medium not in(-1,-3) and
1639 long_int not in(-1,-3) and
1640 longlong not in(-1,-3) and
1641 utiny not in(1,3) and
1642 ushort not in(1,3) and
1643 umedium not in(1,3) and
1644 ulong not in(1,3) and
1645 ulonglong not in(1,3) and
1646 /* bits not in(b'001',b'011') and */
1647 options not in('one','three') and
1648 flags not in('one','one,two,three') and
1649 date_field not in('1901-01-01','1903-03-03') and
1650 year_field not in('1901','1903') and
1651 time_field not in('01:01:01','03:03:03') and
1652 date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')
1653 order by auto;
1654 auto
1655 2
1656 4
1657 explain
1658 select auto from t1 where
1659 "aaaa" not in(string) and
1660 "aaaa" not in(vstring) and
1661 0xAAAA not in(bin) and
1662 0xAAAA not in(vbin) and
1663 (-1 not in(tiny)) and
1664 (-1 not in(short)) and
1665 (-1 not in(medium)) and
1666 (-1 not in(long_int)) and
1667 (-1 not in(longlong)) and
1668 1 not in(utiny) and
1669 1 not in(ushort) and
1670 1 not in(umedium) and
1671 1 not in(ulong) and
1672 1 not in(ulonglong) and
1673 /* b'001' not in(bits) and */
1674 'one' not in(options) and
1675 'one' not in(flags) and
1676 '1901-01-01' not in(date_field) and
1677 '1901' not in(year_field) and
1678 '01:01:01' not in(time_field) and
1679 '1901-01-01 01:01:01' not in(date_time)
1680 order by auto;
1681 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1682 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition (('aaaa' <> `test`.`t1`.`string`) and ('aaaa' <> `test`.`t1`.`vstring`) and (0xaaaa <> `test`.`t1`.`bin`) and (0xaaaa <> `test`.`t1`.`vbin`) and (<cache>(-(1)) <> `test`.`t1`.`tiny`) and (<cache>(-(1)) <> `test`.`t1`.`short`) and (<cache>(-(1)) <> `test`.`t1`.`medium`) and (<cache>(-(1)) <> `test`.`t1`.`long_int`) and (-1 <> `test`.`t1`.`longlong`) and (1 <> `test`.`t1`.`utiny`) and (1 <> `test`.`t1`.`ushort`) and (1 <> `test`.`t1`.`umedium`) and (1 <> `test`.`t1`.`ulong`) and (1 <> `test`.`t1`.`ulonglong`) and ('one' <> `test`.`t1`.`options`) and ('one' <> `test`.`t1`.`flags`) and (DATE'1901-01-01' <> `test`.`t1`.`date_field`) and (1901 <> `test`.`t1`.`year_field`) and (TIME'01:01:01' <> `test`.`t1`.`time_field`) and (TIMESTAMP'1901-01-01 01:01:01' <> `test`.`t1`.`date_time`))
1683 Warnings:
1684 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where (('aaaa' <> `test`.`t1`.`string`) and ('aaaa' <> `test`.`t1`.`vstring`) and (0xaaaa <> `test`.`t1`.`bin`) and (0xaaaa <> `test`.`t1`.`vbin`) and (<cache>(-(1)) <> `test`.`t1`.`tiny`) and (<cache>(-(1)) <> `test`.`t1`.`short`) and (<cache>(-(1)) <> `test`.`t1`.`medium`) and (<cache>(-(1)) <> `test`.`t1`.`long_int`) and (-1 <> `test`.`t1`.`longlong`) and (1 <> `test`.`t1`.`utiny`) and (1 <> `test`.`t1`.`ushort`) and (1 <> `test`.`t1`.`umedium`) and (1 <> `test`.`t1`.`ulong`) and (1 <> `test`.`t1`.`ulonglong`) and ('one' <> `test`.`t1`.`options`) and ('one' <> `test`.`t1`.`flags`) and (DATE'1901-01-01' <> `test`.`t1`.`date_field`) and (1901 <> `test`.`t1`.`year_field`) and (TIME'01:01:01' <> `test`.`t1`.`time_field`) and (TIMESTAMP'1901-01-01 01:01:01' <> `test`.`t1`.`date_time`)) order by `test`.`t1`.`auto`
1685 select auto from t1 where
1686 "aaaa" not in(string) and
1687 "aaaa" not in(vstring) and
1688 0xAAAA not in(bin) and
1689 0xAAAA not in(vbin) and
1690 (-1 not in(tiny)) and
1691 (-1 not in(short)) and
1692 (-1 not in(medium)) and
1693 (-1 not in(long_int)) and
1694 (-1 not in(longlong)) and
1695 1 not in(utiny) and
1696 1 not in(ushort) and
1697 1 not in(umedium) and
1698 1 not in(ulong) and
1699 1 not in(ulonglong) and
1700 /* b'001' not in(bits) and */
1701 'one' not in(options) and
1702 'one' not in(flags) and
1703 '1901-01-01' not in(date_field) and
1704 '1901' not in(year_field) and
1705 '01:01:01' not in(time_field) and
1706 '1901-01-01 01:01:01' not in(date_time)
1707 order by auto;
1708 auto
1709 2
1710 3
1711 4
1712 update t1
1713 set medium = 17
1714 where
1715 string = "aaaa" and
1716 vstring = "aaaa" and
1717 bin = 0xAAAA and
1718 vbin = 0xAAAA and
1719 tiny = -1 and
1720 short = -1 and
1721 medium = -1 and
1722 long_int = -1 and
1723 longlong = -1 and
1724 real_float > 1.0 and real_float < 2.0 and
1725 real_double > 1.0 and real_double < 2.0 and
1726 real_decimal > 1.0 and real_decimal < 2.0 and
1727 utiny = 1 and
1728 ushort = 1 and
1729 umedium = 1 and
1730 ulong = 1 and
1731 ulonglong = 1 and
1732 /* bits = b'001' and */
1733 options = 'one' and
1734 flags = 'one' and
1735 date_field = '1901-01-01' and
1736 year_field = '1901' and
1737 time_field = '01:01:01' and
1738 date_time = '1901-01-01 01:01:01';
1739 delete from t1
1740 where
1741 string = "aaaa" and
1742 vstring = "aaaa" and
1743 bin = 0xAAAA and
1744 vbin = 0xAAAA and
1745 tiny = -1 and
1746 short = -1 and
1747 medium = 17 and
1748 long_int = -1 and
1749 longlong = -1 and
1750 real_float > 1.0 and real_float < 2.0 and
1751 real_double > 1.0 and real_double < 2.0 and
1752 real_decimal > 1.0 and real_decimal < 2.0 and
1753 utiny = 1 and
1754 ushort = 1 and
1755 umedium = 1 and
1756 ulong = 1 and
1757 ulonglong = 1 and
1758 /* bits = b'001' and */
1759 options = 'one' and
1760 flags = 'one' and
1761 date_field = '1901-01-01' and
1762 year_field = '1901' and
1763 time_field = '01:01:01' and
1764 date_time = '1901-01-01 01:01:01';
1765 select count(*) from t1;
1766 count(*)
1767 3
1768 explain
1769 select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
1770 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1771 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 index PRIMARY PRIMARY 4 NULL # # Using pushed condition ((`test`.`t2`.`attr3` is null) or ((`test`.`t2`.`pk1` = 3) and (`test`.`t2`.`attr1` > 2)))
1772 Warnings:
1773 Note 1003 /* select#1 */ select `test`.`t2`.`pk1` AS `pk1`,`test`.`t2`.`attr1` AS `attr1`,`test`.`t2`.`attr2` AS `attr2`,`test`.`t2`.`attr3` AS `attr3` from `test`.`t2` where ((`test`.`t2`.`attr3` is null) or ((`test`.`t2`.`pk1` = 3) and (`test`.`t2`.`attr1` > 2))) order by `test`.`t2`.`pk1`
1774 select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
1775 pk1 attr1 attr2 attr3
1776 2 2 NULL NULL
1777 3 3 3 d
1778 explain
1779 select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
1780 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1781 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t2`.`attr3` is not null) and (`test`.`t2`.`attr1` > 2))
1782 Warnings:
1783 Note 1003 /* select#1 */ select `test`.`t2`.`pk1` AS `pk1`,`test`.`t2`.`attr1` AS `attr1`,`test`.`t2`.`attr2` AS `attr2`,`test`.`t2`.`attr3` AS `attr3` from `test`.`t2` where ((`test`.`t2`.`attr3` is not null) and (`test`.`t2`.`attr1` > 2)) order by `test`.`t2`.`pk1`
1784 select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
1785 pk1 attr1 attr2 attr3
1786 3 3 3 d
1787 4 4 4 e
1788 5 5 5 f
1789 explain
1790 select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
1791 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1792 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition ((`test`.`t3`.`attr2` > 9223372036854775803) and (`test`.`t3`.`attr3` <> 3))
1793 Warnings:
1794 Note 1003 /* select#1 */ select `test`.`t3`.`pk1` AS `pk1`,`test`.`t3`.`attr1` AS `attr1`,`test`.`t3`.`attr2` AS `attr2`,`test`.`t3`.`attr3` AS `attr3`,`test`.`t3`.`attr4` AS `attr4` from `test`.`t3` where ((`test`.`t3`.`attr2` > 9223372036854775803) and (`test`.`t3`.`attr3` <> 3)) order by `test`.`t3`.`pk1`
1795 select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
1796 pk1 attr1 attr2 attr3 attr4
1797 2 2 9223372036854775804 2 c
1798 4 4 9223372036854775806 4 e
1799 5 5 9223372036854775807 5 f
1800 explain
1801 select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
1802 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1803 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t2`.`attr1` < 1); Using temporary; Using filesort
1804 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t3`.`attr1` < 5); Using join buffer (hash join)
1805 Warnings:
1806 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
1807 Note 1003 /* select#1 */ select `test`.`t2`.`pk1` AS `pk1`,`test`.`t2`.`attr1` AS `attr1`,`test`.`t2`.`attr2` AS `attr2`,`test`.`t2`.`attr3` AS `attr3`,`test`.`t3`.`pk1` AS `pk1`,`test`.`t3`.`attr1` AS `attr1`,`test`.`t3`.`attr2` AS `attr2`,`test`.`t3`.`attr3` AS `attr3`,`test`.`t3`.`attr4` AS `attr4` from `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`attr1` < 1) and (`test`.`t2`.`attr2` = `test`.`t3`.`attr2`) and (`test`.`t3`.`attr1` < 5)) order by `test`.`t2`.`pk1`
1808 select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
1809 pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4
1810 0 0 0 a 0 0 0 0 a
1811 explain
1812 select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
1813 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1814 1 SIMPLE t4 p0,p1,p2,p3,p4,p5,p6,p7 range attr1 attr1 4 NULL # # Using pushed condition ((`test`.`t4`.`attr1` < 5) and (`test`.`t4`.`attr2` > 9223372036854775803) and (`test`.`t4`.`attr3` <> 3)); Using MRR; Using filesort
1815 Warnings:
1816 Note 1003 /* select#1 */ select `test`.`t4`.`pk1` AS `pk1`,`test`.`t4`.`attr1` AS `attr1`,`test`.`t4`.`attr2` AS `attr2`,`test`.`t4`.`attr3` AS `attr3`,`test`.`t4`.`attr4` AS `attr4` from `test`.`t4` where ((`test`.`t4`.`attr1` < 5) and (`test`.`t4`.`attr2` > 9223372036854775803) and (`test`.`t4`.`attr3` <> 3)) order by `test`.`t4`.`pk1`
1817 select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
1818 pk1 attr1 attr2 attr3 attr4
1819 2 2 9223372036854775804 2 c
1820 4 4 9223372036854775806 4 e
1821 explain
1822 select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
1823 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1824 1 SIMPLE t4 p0,p1,p2,p3,p4,p5,p6,p7 range attr1 attr1 4 NULL # # Using pushed condition ((`test`.`t4`.`attr1` > 1) and (`test`.`t4`.`attr3` < 5)); Using MRR; Using temporary; Using filesort
1825 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
1826 Warnings:
1827 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
1828 Note 1003 /* select#1 */ select `test`.`t3`.`pk1` AS `pk1`,`test`.`t3`.`attr1` AS `attr1`,`test`.`t3`.`attr2` AS `attr2`,`test`.`t3`.`attr3` AS `attr3`,`test`.`t3`.`attr4` AS `attr4`,`test`.`t4`.`pk1` AS `pk1`,`test`.`t4`.`attr1` AS `attr1`,`test`.`t4`.`attr2` AS `attr2`,`test`.`t4`.`attr3` AS `attr3`,`test`.`t4`.`attr4` AS `attr4` from `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`attr2` = `test`.`t4`.`attr2`) and (`test`.`t4`.`attr1` > 1) and (`test`.`t4`.`attr3` < 5)) order by `test`.`t4`.`pk1`
1829 select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
1830 pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4
1831 2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c
1832 3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d
1833 4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e
1834 explain
1835 select auto from t1 where string = "aaaa" collate utf8mb4_general_ci order by auto;
1836 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1837 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using where
1838 Warnings:
1839 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where (`test`.`t1`.`string` = <cache>(('aaaa' collate utf8mb4_general_ci))) order by `test`.`t1`.`auto`
1840 explain
1841 select * from t2 where (attr1 < 2) = (attr2 < 2) order by pk1;
1842 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1843 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using where
1844 Warnings:
1845 Note 1003 /* select#1 */ select `test`.`t2`.`pk1` AS `pk1`,`test`.`t2`.`attr1` AS `attr1`,`test`.`t2`.`attr2` AS `attr2`,`test`.`t2`.`attr3` AS `attr3` from `test`.`t2` where ((`test`.`t2`.`attr1` < 2) = (`test`.`t2`.`attr2` < 2)) order by `test`.`t2`.`pk1`
1846 explain
1847 select * from t3 left join t4 on t4.attr2 = t3.attr2 where t4.attr1 > 1 and t4.attr3 < 5 or t4.attr1 is null order by t4.pk1;
1848 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1849 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using temporary; Using filesort
1850 1 SIMPLE t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
1851 Warnings:
1852 Note 1003 Can't push table 't4' as child, 'type' must be a 'ref' access
1853 Note 1003 /* select#1 */ select `test`.`t3`.`pk1` AS `pk1`,`test`.`t3`.`attr1` AS `attr1`,`test`.`t3`.`attr2` AS `attr2`,`test`.`t3`.`attr3` AS `attr3`,`test`.`t3`.`attr4` AS `attr4`,`test`.`t4`.`pk1` AS `pk1`,`test`.`t4`.`attr1` AS `attr1`,`test`.`t4`.`attr2` AS `attr2`,`test`.`t4`.`attr3` AS `attr3`,`test`.`t4`.`attr4` AS `attr4` from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`attr2` = `test`.`t3`.`attr2`)) where (((`test`.`t4`.`attr1` > 1) and (`test`.`t4`.`attr3` < 5)) or (`test`.`t4`.`attr1` is null)) order by `test`.`t4`.`pk1`
1854 create table t5 (a int primary key auto_increment, b tinytext not null)
1855 engine = ndb
1856 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1857 insert into t5 (b) values ('jonas'), ('jensing'), ('johan');
1858 set @@optimizer_switch='engine_condition_pushdown=off';
1859 select * from t5 where b like '%jo%' order by a;
1860 a b
1861 1 jonas
1862 3 johan
1863 set @@optimizer_switch = 'engine_condition_pushdown=on';
1864 explain select * from t5 where b like '%jo%';
1865 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1866 1 SIMPLE t5 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
1867 Warnings:
1868 Note 1003 /* select#1 */ select `test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t5` where (`test`.`t5`.`b` like '%jo%')
1869 select * from t5 where b like '%jo%' order by a;
1870 a b
1871 1 jonas
1872 3 johan
1873 set @@optimizer_switch='engine_condition_pushdown=off';
1874 select auto from t1 where date_time like '1902-02-02 %' order by auto;
1875 auto
1876 2
1877 select auto from t1 where date_time not like '1902-02-02 %' order by auto;
1878 auto
1879 3
1880 4
1881 set @@optimizer_switch = 'engine_condition_pushdown=on';
1882 explain select auto from t1 where date_time like '1902-02-02 %';
1883 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1884 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
1885 Warnings:
1886 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where (`test`.`t1`.`date_time` like '1902-02-02 %')
1887 select auto from t1 where date_time like '1902-02-02 %' order by auto;
1888 auto
1889 2
1890 explain select auto from t1 where date_time not like '1902-02-02 %';
1891 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1892 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
1893 Warnings:
1894 Note 1003 /* select#1 */ select `test`.`t1`.`auto` AS `auto` from `test`.`t1` where (not((`test`.`t1`.`date_time` like '1902-02-02 %')))
1895 select auto from t1 where date_time not like '1902-02-02 %' order by auto;
1896 auto
1897 3
1898 4
1899 drop table t1;
1900 create table t1 (a int, b varchar(3), primary key using hash(a))
1901 engine=ndb
1902 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1903 insert into t1 values (1,'a'), (2,'ab'), (3,'abc');
1904 set @@optimizer_switch='engine_condition_pushdown=off';
1905 select * from t1 where b like 'ab';
1906 a b
1907 2 ab
1908 select * from t1 where b like 'ab' or b like 'ab';
1909 a b
1910 2 ab
1911 select * from t1 where b like 'abc';
1912 a b
1913 3 abc
1914 select * from t1 where b like 'abc' or b like 'abc';
1915 a b
1916 3 abc
1917 set @@optimizer_switch = 'engine_condition_pushdown=on';
1918 select * from t1 where b like 'ab';
1919 a b
1920 2 ab
1921 select * from t1 where b like 'ab' or b like 'ab';
1922 a b
1923 2 ab
1924 select * from t1 where b like 'abc';
1925 a b
1926 3 abc
1927 select * from t1 where b like 'abc' or b like 'abc';
1928 a b
1929 3 abc
1930 drop table t1;
1931 create table t1 (a int, b char(3), primary key using hash(a))
1932 engine=ndb;
1933 insert into t1 values (1,'a'), (2,'ab'), (3,'abc');
1934 set @@optimizer_switch='engine_condition_pushdown=off';
1935 select * from t1 where b like 'ab';
1936 a b
1937 2 ab
1938 select * from t1 where b like 'ab' or b like 'ab';
1939 a b
1940 2 ab
1941 select * from t1 where b like 'abc';
1942 a b
1943 3 abc
1944 select * from t1 where b like 'abc' or b like 'abc';
1945 a b
1946 3 abc
1947 set @@optimizer_switch = 'engine_condition_pushdown=on';
1948 select * from t1 where b like 'ab';
1949 a b
1950 2 ab
1951 select * from t1 where b like 'ab' or b like 'ab';
1952 a b
1953 2 ab
1954 select * from t1 where b like 'abc';
1955 a b
1956 3 abc
1957 select * from t1 where b like 'abc' or b like 'abc';
1958 a b
1959 3 abc
1960 drop table t1;
1961 create table t1 ( fname varchar(255), lname varchar(255) )
1962 engine=ndbcluster;
1963 insert into t1 values ("Young","Foo");
1964 set @@optimizer_switch = 'engine_condition_pushdown=off';
1965 SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
1966 fname lname
1967 Young Foo
1968 set @@optimizer_switch = 'engine_condition_pushdown=on';
1969 SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
1970 fname lname
1971 Young Foo
1972 insert into t1 values ("aaa", "aaa");
1973 insert into t1 values ("bbb", "bbb");
1974 insert into t1 values ("ccc", "ccc");
1975 insert into t1 values ("ddd", "ddd");
1976 set @@optimizer_switch = 'engine_condition_pushdown=off';
1977 SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
1978 fname lname
1979 Young Foo
1980 set @@optimizer_switch = 'engine_condition_pushdown=on';
1981 SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
1982 fname lname
1983 Young Foo
1984 drop table t1;
1985 CREATE TABLE NodeAlias (
1986 id int(10) unsigned NOT NULL AUTO_INCREMENT,
1987 nodeId int(10) unsigned NOT NULL,
1988 displayName varchar(45) DEFAULT NULL,
1989 aliasKey varchar(45) DEFAULT NULL,
1990 objectVersion int(10) unsigned NOT NULL DEFAULT '0',
1991 changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1992 PRIMARY KEY (id),
1993 KEY NodeAlias_identifies_1_Node (nodeId),
1994 KEY NodeAlias_KeyIndex (aliasKey)
1995 ) engine=ndb
1996 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1997 Warnings:
1998 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1999 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2000 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2001 insert into NodeAlias VALUES(null, 2 , '49', '49', 0,'2008-03-07 14:54:59');
2002 insert into NodeAlias VALUES(null, 3 , '49' , '49' , 0 , '2008-03-07 14:55:24');
2003 insert into NodeAlias VALUES(null, 4 , '49' , '49' , 0 , '2008-03-07 14:55:51');
2004 insert into NodeAlias VALUES(null, 5 , '150' , '150' , 0 , '2008-03-10 10:48:30');
2005 insert into NodeAlias VALUES(null, 6 , '154' , '154' , 0 , '2008-03-10 10:48:43');
2006 insert into NodeAlias VALUES(null, 7 , '158' , '158' , 0 , '2008-03-10 10:48:57');
2007 insert into NodeAlias VALUES(null, 8 , '491803%' , '491803%' , 0 , '2008-03-10
2008 12:22:26');
2009 Warnings:
2010 Warning 4095 Delimiter '\n' in position 10 in datetime value '2008-03-10
2011 12:22:26' at row 1 is deprecated. Prefer the standard ' '.
2012 explain select * from nodealias where (aliasKey LIKE '491803%');
2013 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2014 1 SIMPLE nodealias p0,p1,p2,p3,p4,p5,p6,p7 range nodealias_KeyIndex nodealias_KeyIndex 183 NULL # # Using pushed condition (`test`.`nodealias`.`aliasKey` like '491803%'); Using MRR
2015 Warnings:
2016 Note 1003 /* select#1 */ select `test`.`nodealias`.`id` AS `id`,`test`.`nodealias`.`nodeId` AS `nodeId`,`test`.`nodealias`.`displayName` AS `displayName`,`test`.`nodealias`.`aliasKey` AS `aliasKey`,`test`.`nodealias`.`objectVersion` AS `objectVersion`,`test`.`nodealias`.`changed` AS `changed` from `test`.`nodealias` where (`test`.`nodealias`.`aliasKey` like '491803%')
2017 select * from NodeAlias where (aliasKey LIKE '491803%') order by id;
2018 id nodeId displayName aliasKey objectVersion changed
2019 7 8 491803% 491803% 0 2008-03-10 12:22:26
2020 explain select * from nodealias where ('4918031215220' LIKE aliasKey OR aliasKey LIKE '4918031215220');
2021 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2022 1 SIMPLE nodealias p0,p1,p2,p3,p4,p5,p6,p7 ALL nodealias_KeyIndex NULL NULL NULL # # Using where
2023 Warnings:
2024 Note 1003 /* select#1 */ select `test`.`nodealias`.`id` AS `id`,`test`.`nodealias`.`nodeId` AS `nodeId`,`test`.`nodealias`.`displayName` AS `displayName`,`test`.`nodealias`.`aliasKey` AS `aliasKey`,`test`.`nodealias`.`objectVersion` AS `objectVersion`,`test`.`nodealias`.`changed` AS `changed` from `test`.`nodealias` where (('4918031215220' like `test`.`nodealias`.`aliasKey`) or (`test`.`nodealias`.`aliasKey` like '4918031215220'))
2025 select * from NodeAlias where ('4918031215220' LIKE aliasKey OR aliasKey LIKE '4918031215220') order by id;
2026 id nodeId displayName aliasKey objectVersion changed
2027 7 8 491803% 491803% 0 2008-03-10 12:22:26
2028 drop table NodeAlias;
2029 create table t1 (a int, b int, c int, d int, primary key using hash(a))
2030 engine=ndbcluster;
2031 insert into t1 values (10,1,100,0+0x1111);
2032 insert into t1 values (20,2,200,0+0x2222);
2033 insert into t1 values (30,3,300,0+0x3333);
2034 insert into t1 values (40,4,400,0+0x4444);
2035 insert into t1 values (50,5,500,0+0x5555);
2036 set @@optimizer_switch = 'engine_condition_pushdown=on';
2037 select a,b,d from t1
2038 where b in (0,1,2,5)
2039 order by b;
2040 a b d
2041 10 1 4369
2042 20 2 8738
2043 50 5 21845
2044 -- big filter just below limit
2045 a b d
2046 10 1 4369
2047 20 2 8738
2048 50 5 21845
2049 -- big filter just above limit
2050 a b d
2051 10 1 4369
2052 20 2 8738
2053 50 5 21845
2054 Warnings:
2055 Warning 4294 Scan filter is too large, discarded
2056 drop table t1;
2057 create table t1 (a int primary key, b varchar(5000) character set latin1)
2058 engine=ndb
2059 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2060 insert into t1 values (0, 'I just cant beg you, any-more');
2061 select * from t1 where b="value";
2062 a b
2063 drop table t1;
2064 create table t1 (a int primary key, b varchar(5000) character set latin1) engine=ndb;
2065 insert into t1 values(0, 'Edinburgh'),(1, 'Glasgow'),(2,'Aberdeen');
2066 select * from t1 where b in ('0', '1', '2','3','4','5','6','7','8','9',
2067 '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', 'Aberdeen');
2068 a b
2069 2 Aberdeen
2070 show warnings;
2071 Level Code Message
2072 create table balerno (
2073 a int primary key,
2074 b varchar(2000),
2075 c varchar(2000)
2076 ) engine=ndb character set latin1;
2077 alter table balerno add index (b);
2078 show warnings;
2079 Level Code Message
2080 insert into balerno values (1, repeat('BA', 1000), repeat('CA', 1000));
2081 insert into balerno values (2, repeat('BB', 1000), repeat('CB', 1000));
2082 insert into balerno values (3, repeat('BC', 1000), repeat('CC', 1000));
2083 insert into balerno values (4, repeat('BD', 1000), repeat('CD', 1000));
2084 insert into balerno values (5, repeat('BE', 1000), repeat('CE', 1000));
2085 set @@optimizer_switch = 'engine_condition_pushdown=on';
2086 select a from balerno where b in (
2087 repeat('10', 1000),
2088 repeat('11', 1000),
2089 repeat('12', 1000),
2090 repeat('13', 1000),
2091 repeat('14', 1000),
2092 repeat('15', 1000),
2093 repeat('16', 1000),
2094 repeat('17', 1000),
2095 repeat('18', 1000),
2096 repeat('19', 1000),
2097 repeat('20', 1000),
2098 repeat('21', 1000),
2099 repeat('22', 1000),
2100 repeat('23', 1000),
2101 repeat('24', 1000),
2102 repeat('25', 1000),
2103 repeat('26', 1000),
2104 repeat('27', 1000),
2105 repeat('28', 1000),
2106 repeat('29', 1000),
2107 repeat('30', 1000),
2108 repeat('31', 1000),
2109 repeat('32', 1000),
2110 repeat('33', 1000),
2111 repeat('34', 1000),
2112 repeat('35', 1000),
2113 repeat('36', 1000),
2114 repeat('37', 1000),
2115 repeat('38', 1000),
2116 repeat('BA', 1000));
2117 a
2118 1
2119 show warnings;
2120 Level Code Message
2121 select a from balerno ignore index(b) where b in (
2122 repeat('10', 1000),
2123 repeat('11', 1000),
2124 repeat('12', 1000),
2125 repeat('13', 1000),
2126 repeat('14', 1000),
2127 repeat('15', 1000),
2128 repeat('16', 1000),
2129 repeat('17', 1000),
2130 repeat('18', 1000),
2131 repeat('19', 1000),
2132 repeat('20', 1000),
2133 repeat('21', 1000),
2134 repeat('22', 1000),
2135 repeat('23', 1000),
2136 repeat('24', 1000),
2137 repeat('25', 1000),
2138 repeat('26', 1000),
2139 repeat('27', 1000),
2140 repeat('28', 1000),
2141 repeat('29', 1000),
2142 repeat('30', 1000),
2143 repeat('31', 1000),
2144 repeat('32', 1000),
2145 repeat('33', 1000),
2146 repeat('34', 1000),
2147 repeat('35', 1000),
2148 repeat('36', 1000),
2149 repeat('37', 1000),
2150 repeat('38', 1000),
2151 repeat('BA', 1000));
2152 a
2153 1
2154 show warnings;
2155 Level Code Message
2156 set @@optimizer_switch='engine_condition_pushdown=off';
2157 select a from balerno where b in (
2158 repeat('10', 1000),
2159 repeat('11', 1000),
2160 repeat('12', 1000),
2161 repeat('13', 1000),
2162 repeat('14', 1000),
2163 repeat('15', 1000),
2164 repeat('16', 1000),
2165 repeat('17', 1000),
2166 repeat('18', 1000),
2167 repeat('19', 1000),
2168 repeat('20', 1000),
2169 repeat('21', 1000),
2170 repeat('22', 1000),
2171 repeat('23', 1000),
2172 repeat('24', 1000),
2173 repeat('25', 1000),
2174 repeat('26', 1000),
2175 repeat('27', 1000),
2176 repeat('28', 1000),
2177 repeat('29', 1000),
2178 repeat('30', 1000),
2179 repeat('31', 1000),
2180 repeat('32', 1000),
2181 repeat('33', 1000),
2182 repeat('34', 1000),
2183 repeat('35', 1000),
2184 repeat('36', 1000),
2185 repeat('37', 1000),
2186 repeat('38', 1000),
2187 repeat('BA', 1000));
2188 a
2189 1
2190 show warnings;
2191 Level Code Message
2192 drop table balerno;
2193 create table t (pk int primary key, x varchar(1)) engine = ndb
2194 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2195 insert into t values (0,"a");
2196 set @@optimizer_switch='engine_condition_pushdown=off';
2197 select * from t where x <> "aa";
2198 pk x
2199 0 a
2200 select * from t where "aa" <> x;
2201 pk x
2202 0 a
2203 select * from t where x between "" and "bb";
2204 pk x
2205 0 a
2206 select * from t where x not between "" and "bb";
2207 pk x
2208 select * from t where x in ("","aa","b");
2209 pk x
2210 select * from t where x not in ("","aa","b");
2211 pk x
2212 0 a
2213 select * from t where x like "aa?";
2214 pk x
2215 set @@optimizer_switch = 'engine_condition_pushdown=on';
2216 explain select * from t where x <> "aa";
2217 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2218 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2219 Warnings:
2220 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` <> 'aa')
2221 select * from t where x <> "aa";
2222 pk x
2223 0 a
2224 explain select * from t where "aa" <> x;
2225 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2226 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2227 Warnings:
2228 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where ('aa' <> `test`.`t`.`x`)
2229 select * from t where "aa" <> x;
2230 pk x
2231 0 a
2232 explain select * from t where x between "" and "bb";
2233 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2234 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2235 Warnings:
2236 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` between '' and 'bb')
2237 select * from t where x between "" and "bb";
2238 pk x
2239 0 a
2240 explain select * from t where x not between "" and "bb";
2241 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2242 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2243 Warnings:
2244 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` not between '' and 'bb')
2245 select * from t where x not between "" and "bb";
2246 pk x
2247 explain select * from t where x in ("","aa","b");
2248 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2249 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2250 Warnings:
2251 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` in ('','aa','b'))
2252 select * from t where x in ("","aa","b");
2253 pk x
2254 explain select * from t where x not in ("","aa","b");
2255 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2256 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2257 Warnings:
2258 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` not in ('','aa','b'))
2259 select * from t where x not in ("","aa","b");
2260 pk x
2261 0 a
2262 explain select * from t where x like "aa?";
2263 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2264 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t`.`x` like 'aa?')
2265 Warnings:
2266 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` like 'aa?')
2267 select * from t where x like "aa?";
2268 pk x
2269 explain select * from t where x not like "aa?";
2270 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2271 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (not((`test`.`t`.`x` like 'aa?')))
2272 Warnings:
2273 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (not((`test`.`t`.`x` like 'aa?')))
2274 select * from t where x not like "aa?";
2275 pk x
2276 0 a
2277 select * from t where x like "%a%";
2278 pk x
2279 0 a
2280 select * from t where x not like "%b%";
2281 pk x
2282 0 a
2283 select * from t where x like replace(concat("%", "b%"),"b","a");
2284 pk x
2285 0 a
2286 select * from t where x not like replace(concat("%", "a%"),"a","b");
2287 pk x
2288 0 a
2289 select * from t where x like concat("%", replace("b%","b","a"));
2290 pk x
2291 0 a
2292 select * from t where x not like concat("%", replace("a%","a","b"));
2293 pk x
2294 0 a
2295 drop table t;
2296 create table t (pk int primary key, x int) engine = ndb
2297 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2298 insert into t values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5);
2299 set @@optimizer_switch = 'engine_condition_pushdown=on';
2300 explain select * from t where 3 between 1+1 and x order by pk;
2301 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2302 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition (3 between <cache>((1 + 1)) and `test`.`t`.`x`)
2303 Warnings:
2304 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (3 between <cache>((1 + 1)) and `test`.`t`.`x`) order by `test`.`t`.`pk`
2305 select * from t where 3 between 1+1 and x order by pk;
2306 pk x
2307 3 3
2308 4 4
2309 5 5
2310 explain select * from t where 3 between -1 and x order by pk;
2311 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2312 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL # # Using pushed condition (3 between <cache>(-(1)) and `test`.`t`.`x`)
2313 Warnings:
2314 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`x` AS `x` from `test`.`t` where (3 between <cache>(-(1)) and `test`.`t`.`x`) order by `test`.`t`.`pk`
2315 select * from t where 3 between -1 and x order by pk;
2316 pk x
2317 3 3
2318 4 4
2319 5 5
2320 drop table t;
2321 set @@optimizer_switch = 'engine_condition_pushdown=on';
2322 create table t (x enum ('yes','yep','no')) engine = ndb
2323 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2324 insert into t values ('yes'),('yep'),('no');
2325 explain select * from t where x like 'yes' order by x;
2326 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2327 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2328 Warnings:
2329 Note 1003 /* select#1 */ select `test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` like 'yes') order by `test`.`t`.`x`
2330 select * from t where x like 'yes' order by x;
2331 x
2332 yes
2333 explain select * from t where x like 'ye%' order by x;
2334 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2335 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2336 Warnings:
2337 Note 1003 /* select#1 */ select `test`.`t`.`x` AS `x` from `test`.`t` where (`test`.`t`.`x` like 'ye%') order by `test`.`t`.`x`
2338 select * from t where x like 'ye%' order by x;
2339 x
2340 yes
2341 yep
2342 explain select * from t where x not like 'ye%' order by x;
2343 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2344 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2345 Warnings:
2346 Note 1003 /* select#1 */ select `test`.`t`.`x` AS `x` from `test`.`t` where (not((`test`.`t`.`x` like 'ye%'))) order by `test`.`t`.`x`
2347 select * from t where x not like 'ye%' order by x;
2348 x
2349 no
2350 drop table t;
2351 create table tx (
2352 a int not null,
2353 b int not null,
2354 c int not null,
2355 d int not null,
2356 primary key (`a`,`b`)
2357 ) engine = ndb
2358 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2359 explain select * from tx join tx as t2 on tx.c=1 where t2.c=1;
2360 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2361 1 SIMPLE tx p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`tx`.`c` = 1)
2362 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t2`.`c` = 1); Using join buffer (hash join)
2363 Warnings:
2364 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
2365 Note 1003 /* select#1 */ select `test`.`tx`.`a` AS `a`,`test`.`tx`.`b` AS `b`,`test`.`tx`.`c` AS `c`,`test`.`tx`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`c` = 1) and (`test`.`t2`.`c` = 1))
2366 explain
2367 select straight_join *
2368 from tx
2369 join tx as t2 on t2.a = tx.a and t2.b = tx.b
2370 join tx as t3 on t3.a = tx.c and t3.b = tx.d
2371 join tx as t4 on t4.a = t3.b and t4.b = t2.c;
2372 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2373 1 SIMPLE tx p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL # # Parent of 4 pushed join@1
2374 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 8 test.tx.a,test.tx.b # # Child of 'tx' in pushed join@1
2375 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 8 test.tx.c,test.tx.d # # Child of 'tx' in pushed join@1
2376 1 SIMPLE t4 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 8 test.tx.d,test.t2.c # # Child of 't2' in pushed join@1
2377 Warnings:
2378 Note 1003 /* select#1 */ select straight_join `test`.`tx`.`a` AS `a`,`test`.`tx`.`b` AS `b`,`test`.`tx`.`c` AS `c`,`test`.`tx`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`tx` join `test`.`tx` `t2` join `test`.`tx` `t3` join `test`.`tx` `t4` where ((`test`.`t2`.`b` = `test`.`tx`.`b`) and (`test`.`t2`.`a` = `test`.`tx`.`a`) and (`test`.`t3`.`a` = `test`.`tx`.`c`) and (`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t3`.`b` = `test`.`tx`.`d`) and (`test`.`t4`.`a` = `test`.`tx`.`d`))
2379 explain
2380 select t2.c, count(distinct t2.a)
2381 from tx
2382 join tx as t2 on tx.a = t2.c and tx.b = t2.d
2383 where t2.a = 4
2384 group by t2.c;
2385 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2386 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref PRIMARY PRIMARY 4 const # # Using filesort
2387 1 SIMPLE tx p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d # # NULL
2388 Warnings:
2389 Note 1003 Can't push table 'tx' as child of 't2', it is in a sorted-branch which can't be referred.
2390 Note 1003 /* select#1 */ select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`b` = `test`.`t2`.`d`) and (`test`.`tx`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c`
2391 explain select * from tx join tx as t2 on tx.c=1 where t2.c=1;
2392 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2393 1 SIMPLE tx p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`tx`.`c` = 1)
2394 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t2`.`c` = 1); Using join buffer (hash join)
2395 Warnings:
2396 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
2397 Note 1003 /* select#1 */ select `test`.`tx`.`a` AS `a`,`test`.`tx`.`b` AS `b`,`test`.`tx`.`c` AS `c`,`test`.`tx`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`c` = 1) and (`test`.`t2`.`c` = 1))
2398 explain
2399 select t2.c, count(distinct t2.a)
2400 from tx
2401 join tx as t2 on tx.a = t2.c and tx.b = t2.d
2402 where t2.a = 4
2403 group by t2.c;
2404 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2405 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref PRIMARY PRIMARY 4 const # # Using filesort
2406 1 SIMPLE tx p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d # # NULL
2407 Warnings:
2408 Note 1003 Can't push table 'tx' as child of 't2', it is in a sorted-branch which can't be referred.
2409 Note 1003 /* select#1 */ select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`b` = `test`.`t2`.`d`) and (`test`.`tx`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c`
2410 drop table tx;
2411 set @@optimizer_switch = 'engine_condition_pushdown=on';
2412 create table t (pk int, i int) engine = ndb
2413 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2414 insert into t values (1,3), (3,6), (6,9), (9,1);
2415 create table subq (pk int, i int) engine = ndb
2416 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2417 insert into subq values (1,3), (3,6), (6,9), (9,1);
2418 explain
2419 select * from t where exists
2420 (select * from t as subq where subq.i=3 and t.i=3);
2421 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2422 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t`.`i` = 3)
2423 1 SIMPLE subq p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`subq`.`i` = 3); FirstMatch(t); Using join buffer (hash join)
2424 Warnings:
2425 Note 1276 Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1
2426 Note 1003 Can't push table 'subq' as child, 'type' must be a 'ref' access
2427 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` semi join (`test`.`t` `subq`) where ((`test`.`t`.`i` = 3) and (`test`.`subq`.`i` = 3))
2428 explain
2429 select * from t where exists
2430 (select * from subq where subq.i=3 and t.i=3);
2431 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2432 1 SIMPLE t p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t`.`i` = 3)
2433 1 SIMPLE subq p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`subq`.`i` = 3); FirstMatch(t); Using join buffer (hash join)
2434 Warnings:
2435 Note 1276 Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1
2436 Note 1003 Can't push table 'subq' as child, 'type' must be a 'ref' access
2437 Note 1003 /* select#1 */ select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` semi join (`test`.`subq`) where ((`test`.`t`.`i` = 3) and (`test`.`subq`.`i` = 3))
2438 select * from t where exists
2439 (select * from t as subq where subq.i=3 and t.i=3);
2440 pk i
2441 1 3
2442 select * from t where exists
2443 (select * from subq where subq.i=3 and t.i=3);
2444 pk i
2445 1 3
2446 drop table t,subq;
2447 create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
2448 insert into t values (1,0), (2,0), (3,0), (4,0);
2449 set @@optimizer_switch='engine_condition_pushdown=on';
2450 select table1.pk1, table2.pk1, table1.pk2, table2.pk2
2451 from t as table1, t as table2
2452 where table2.pk1 in (0,3) and
2453 (table1.pk1 = 7 or table2.pk1 = 3);
2454 pk1 pk1 pk2 pk2
2455 1 3 0 0
2456 2 3 0 0
2457 3 3 0 0
2458 4 3 0 0
2459 drop table t;
2460 create table mytable(i int, s varchar(255) ) engine = ndb;
2461 insert into mytable values(0,"Text Hej"),(1, "xText aaja");
2462 set @@optimizer_switch = 'engine_condition_pushdown=on';
2463 select * from mytable where s like concat("%Text","%") or s like concat("%Text","%") order by i;
2464 i s
2465 0 Text Hej
2466 1 xText aaja
2467 select * from mytable where s like concat("%Text","%") or s like "%Text%" order by i;
2468 i s
2469 0 Text Hej
2470 1 xText aaja
2471 select * from mytable where s like concat("%Text","%") or s like concat("%Text1","%") order by i;
2472 i s
2473 0 Text Hej
2474 1 xText aaja
2475 select * from mytable where s like concat("%Text","%") or s like "%Text1%" order by i;
2476 i s
2477 0 Text Hej
2478 1 xText aaja
2479 select * from mytable where s not like concat("%Text","%") or s not like concat("%Text","%") order by i;
2480 i s
2481 select * from mytable where s not like concat("%Text1","%") or s not like concat("%Text","%") order by i;
2482 i s
2483 0 Text Hej
2484 1 xText aaja
2485 select * from mytable where s like concat("%Text","%") or s not like "%Text%" order by i;
2486 i s
2487 0 Text Hej
2488 1 xText aaja
2489 select * from mytable where s like concat("%Text1","%") or s not like "%Text%" order by i;
2490 i s
2491 select * from mytable where s like concat("%Text","%") and s like concat("%Text","%") order by i;
2492 i s
2493 0 Text Hej
2494 1 xText aaja
2495 select * from mytable where s like concat("%Text","%") and s like "%Text%" order by i;
2496 i s
2497 0 Text Hej
2498 1 xText aaja
2499 select * from mytable where s like concat("%Text","%") and s like concat("%Text1","%") order by i;
2500 i s
2501 select * from mytable where s like concat("%Text","%") and s like "%Text1%" order by i;
2502 i s
2503 select * from mytable where s not like concat("%Text","%") and s not like concat("%Text","%") order by i;
2504 i s
2505 select * from mytable where s not like concat("%Text","%") and s not like concat("%Text1","%") order by i;
2506 i s
2507 select * from mytable where s like concat("%Text","%") and s not like "%Text%" order by i;
2508 i s
2509 select * from mytable where s like concat("%Text","%") and s not like "%Text1%" order by i;
2510 i s
2511 0 Text Hej
2512 1 xText aaja
2513 select * from mytable where s like replace(concat("%Xext","%"),"X", "T") order by i;
2514 i s
2515 0 Text Hej
2516 1 xText aaja
2517 select * from mytable where s not like replace(concat("%Text","%"),"T", "X") order by i;
2518 i s
2519 0 Text Hej
2520 1 xText aaja
2521 select * from mytable where s like concat(replace("%Xext","X", "T"),"%") order by i;
2522 i s
2523 0 Text Hej
2524 1 xText aaja
2525 select * from mytable where s not like concat(replace("%Text","T", "X"),"%") order by i;
2526 i s
2527 0 Text Hej
2528 1 xText aaja
2529 drop table mytable;
2530 create table mytab(a char(20),b char(20),
2531 key a (a)) engine=ndbcluster default charset=latin1;
2532 insert into mytab values (null,null),(-1,1);
2533 select b from mytab where a like -1 having `b` like 1;
2534 b
2535 1
2536 drop table mytab;
2537 create table t(a bigint unsigned not null primary key auto_increment, b varchar(100)) character set utf8 engine ndb;
2538 Warnings:
2539 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2540 insert into t (b) values('abc'),('aaa'),('bbb'),('ccc');
2541 select * from t where b like 'a%';
2542 a b
2543 1 abc
2544 2 aaa
2545 select * from t where b not like 'a%';
2546 a b
2547 3 bbb
2548 4 ccc
2549 drop table t;
2550 create table escapetest ( emailaddress varchar(255) default null, id int not
2551 null default '0') engine=ndbcluster
2552 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2553 insert into escapetest values('[email protected]', 1);
2554 explain select * from escapetest where emailaddress like "test_%";
2555 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2556 1 SIMPLE escapetest p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`escapetest`.`emailaddress` like 'test_%')
2557 Warnings:
2558 Note 1003 /* select#1 */ select `test`.`escapetest`.`emailaddress` AS `emailaddress`,`test`.`escapetest`.`id` AS `id` from `test`.`escapetest` where (`test`.`escapetest`.`emailaddress` like 'test_%')
2559 select * from escapetest where emailaddress like "test_%";
2560 emailaddress id
2561 [email protected] 1
2562 explain select * from escapetest where emailaddress like "test|_%" escape '|';
2563 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2564 1 SIMPLE escapetest p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2565 Warnings:
2566 Note 1003 /* select#1 */ select `test`.`escapetest`.`emailaddress` AS `emailaddress`,`test`.`escapetest`.`id` AS `id` from `test`.`escapetest` where (`test`.`escapetest`.`emailaddress` like 'test|_%' escape '|')
2567 select * from escapetest where emailaddress like "test|_%" escape '|';
2568 emailaddress id
2569 [email protected] 1
2570 drop table escapetest;
2571 create table a(a varchar(7)) engine=ndb charset=utf8
2572 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2573 Warnings:
2574 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2575 insert into a values ('abcdefg');
2576 select * from a;
2577 a
2578 abcdefg
2579 explain select * from a where a like 'abcdefg%';
2580 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2581 1 SIMPLE a p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`a`.`a` like 'abcdefg%')
2582 Warnings:
2583 Note 1003 /* select#1 */ select `test`.`a`.`a` AS `a` from `test`.`a` where (`test`.`a`.`a` like 'abcdefg%')
2584 select * from a where a like 'abcdefg%';
2585 a
2586 abcdefg
2587 drop table a;
2588 set @@session.optimizer_switch = @old_ecpd;
2589 DROP TABLE t1,t2,t3,t4,t5;
2590 #
2591 # Bug#14106592 DEBUG BUILD CRASH IN NDB_SERIALIZE_COND()
2592 #
2593 create table t1(a time) engine=ndbcluster;
2594 insert into t1 values ('00:00:00'),('01:01:01');
2595 select 1 from t1 where a >= NULL;
2596 1
2597 drop table t1;
2598 #
2599 # BUG#14798022 "CHAR (0)" DATATYPE CAUSING ERROR 1296
2600 # Verify there is no push down support for char(0)
2601 #
2602 create table t1(a int, b char(0)) engine=ndbcluster
2603 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2604 insert into t1 values(1,''), (2, NULL);
2605 explain select * from t1 where b = '';
2606 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2607 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where
2608 Warnings:
2609 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` = '')
2610 select * from t1 where b = '';
2611 a b
2612 1
2613 drop table t1;
2614 #
2615 # Bug#28610217 RESULT ROWS ARE MISSING WHEN ENUM COMPARISON PREDICATES ARE PUSHED
2616 #
2617 # Enum values should be compared by their textual values,
2618 # not by their internal 'index' value.
2619 # As the storage engine only know the index value of the
2620 # enum values, we cant compare them by '>' and '<' in the SE.
2621 #
2622 create table t1 (
2623 options enum('one','two','three','four','five') not null
2624 ) engine=ndb;
2625 insert into t1(options) values ('one'),('two'),('three'),('four'),('five');
2626 Enum comparison by '<' / '>' should not be pushed
2627 explain select * from t1 where options between 'one' and 'three' order by options;
2628 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2629 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2630 Warnings:
2631 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` between 'one' and 'three') order by `test`.`t1`.`options`
2632 explain select * from t1 where options >= 'one' order by options;
2633 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2634 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2635 Warnings:
2636 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` >= 'one') order by `test`.`t1`.`options`
2637 explain select * from t1 where options <= 'three' order by options;
2638 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2639 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2640 Warnings:
2641 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` <= 'three') order by `test`.`t1`.`options`
2642 explain select * from t1 where options > 'one' order by options;
2643 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2644 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2645 Warnings:
2646 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` > 'one') order by `test`.`t1`.`options`
2647 explain select * from t1 where options < 'three' order by options;
2648 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2649 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Using filesort
2650 Warnings:
2651 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` < 'three') order by `test`.`t1`.`options`
2652 select * from t1 where options between 'one' and 'three' order by options;
2653 options
2654 one
2655 three
2656 select * from t1 where options >= 'one' order by options;
2657 options
2658 one
2659 two
2660 three
2661 select * from t1 where options <= 'three' order by options;
2662 options
2663 one
2664 three
2665 four
2666 five
2667 select * from t1 where options > 'one' order by options;
2668 options
2669 two
2670 three
2671 select * from t1 where options < 'three' order by options;
2672 options
2673 one
2674 four
2675 five
2676 Equalities and IN-lists should be pushed
2677 explain select * from t1 where options = 'one' order by options;
2678 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2679 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t1`.`options` = 'one')
2680 Warnings:
2681 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` = 'one') order by `test`.`t1`.`options`
2682 explain select * from t1 where options <> 'one' order by options;
2683 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2684 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t1`.`options` <> 'one'); Using filesort
2685 Warnings:
2686 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` <> 'one') order by `test`.`t1`.`options`
2687 explain select * from t1 where options IN ('one','four','two','zero') order by options;
2688 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2689 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t1`.`options` in ('one','four','two','zero')); Using filesort
2690 Warnings:
2691 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` in ('one','four','two','zero')) order by `test`.`t1`.`options`
2692 explain select * from t1 where options NOT IN ('one','four','two','zero') order by options;
2693 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2694 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using pushed condition (`test`.`t1`.`options` not in ('one','four','two','zero')); Using filesort
2695 Warnings:
2696 Note 1003 /* select#1 */ select `test`.`t1`.`options` AS `options` from `test`.`t1` where (`test`.`t1`.`options` not in ('one','four','two','zero')) order by `test`.`t1`.`options`
2697 select * from t1 where options = 'one' order by options;
2698 options
2699 one
2700 select * from t1 where options <> 'one' order by options;
2701 options
2702 two
2703 three
2704 four
2705 five
2706 select * from t1 where options IN ('one','four','two','zero') order by options;
2707 options
2708 one
2709 two
2710 four
2711 select * from t1 where options NOT IN ('one','four','two','zero') order by options;
2712 options
2713 three
2714 five
2715 drop table t1;
2716 #
2717 # Bug#28643463 NULL VALUES NOT FILTERED OUT BY PUSHED CONDITIONS
2718 #
2719 Testing: '= 'five''
2720 result_rows filtered_rows
2721 1 1
2722 Testing: '<> 'five''
2723 result_rows filtered_rows
2724 4 4
2725 Testing: '< 'five''
2726 result_rows filtered_rows
2727 0 0
2728 Testing: '> 'five''
2729 result_rows filtered_rows
2730 4 4
2731 Testing: '<= 'five''
2732 result_rows filtered_rows
2733 1 1
2734 Testing: '>= 'five''
2735 result_rows filtered_rows
2736 5 5
2737 Testing: 'like 'five''
2738 result_rows filtered_rows
2739 1 1
2740 Testing: 'not like 'five''
2741 result_rows filtered_rows
2742 4 4
2743 Testing: 'not <column> like 'five''
2744 result_rows filtered_rows
2745 4 4
2746 Testing: 'not <column> not like 'five''
2747 result_rows filtered_rows
2748 1 1
2749 #
2750 # WL#14476 Introduce NdbScanFilter option for NULL-compare according to SQL semantics
2751 #
2752 # Test case for checking the pre-WL behaviour. Disable NULL semantics added
2753 # by WL -> force old behaviour of still needing explicit NULL check.
2754 #
2755 # Test cases are the same as added for Bug#28643463 above.
2756 #
2757 Testing: '= 'five''
2758 result_rows filtered_rows
2759 1 1
2760 Testing: '<> 'five''
2761 result_rows filtered_rows
2762 4 4
2763 Testing: '< 'five''
2764 result_rows filtered_rows
2765 0 0
2766 Testing: '> 'five''
2767 result_rows filtered_rows
2768 4 4
2769 Testing: '<= 'five''
2770 result_rows filtered_rows
2771 1 1
2772 Testing: '>= 'five''
2773 result_rows filtered_rows
2774 5 5
2775 Testing: 'like 'five''
2776 result_rows filtered_rows
2777 1 1
2778 Testing: 'not like 'five''
2779 result_rows filtered_rows
2780 4 4
2781 Testing: 'not <column> like 'five''
2782 result_rows filtered_rows
2783 4 4
2784 Testing: 'not <column> not like 'five''
2785 result_rows filtered_rows
2786 1 1
2787 #
2788 # Bug#29231709 NULL VALUES (still) NOT FILTERED OUT BY PUSHED CONDITIONS
2789 #
2790 Testing: ''five' ='
2791 result_rows filtered_rows
2792 1 1
2793 Testing: ''five' <>'
2794 result_rows filtered_rows
2795 4 4
2796 Testing: ''five' >'
2797 result_rows filtered_rows
2798 0 0
2799 Testing: ''five' <'
2800 result_rows filtered_rows
2801 4 4
2802 Testing: ''five' >='
2803 result_rows filtered_rows
2804 1 1
2805 Testing: ''five' <='
2806 result_rows filtered_rows
2807 5 5
2808 #
2809 # Bug#29232744 NULL VALUES NOT FILTERED OUT BY PUSHED
2810 # 'NOT IN/BETWEEN' PREDICATES
2811 #
2812 Testing: '<field> between'
2813 result_rows filtered_rows
2814 3 3
2815 Testing: '<field> not between'
2816 result_rows filtered_rows
2817 2 2
2818 Testing: 'not <field> between'
2819 result_rows filtered_rows
2820 2 2
2821 Testing: '<field> in (...)'
2822 result_rows filtered_rows
2823 3 3
2824 Testing: '<field> not in (...)'
2825 result_rows filtered_rows
2826 2 2
2827 Testing: 'not <field> in (...)'
2828 result_rows filtered_rows
2829 2 2
2830 #
2831 # Bug#29699347 ITEM_FLOAT C'TOR DOES NOT SET MAX_LENGTH MEMBER
2832 #
2833 Creating baseline, returning all 8 rows
2834 result_rows filtered_rows
2835 8 8
2836 Testing filtering of data type 'real', returning 6 rows.
2837 result_rows filtered_rows
2838 6 6
2839 Testing filtering of data type 'double', returning 6 rows.
2840 result_rows filtered_rows
2841 6 6
2842 #
2843 # Bug#29054626 TRUNCATION OF OUT OF RANGE VALUES IN PUSHED CONDITION -> INCORRECT RESULT
2844 #
2845 CREATE TABLE t1 (
2846 ushort smallint unsigned NOT NULL,
2847 date_field date
2848 ) engine=ndb;
2849 insert into t1 values
2850 (1, '1901-01-01'),
2851 (2, '1902-02-02'),
2852 (3, '1903-03-03'),
2853 (4, '1904-04-04');
2854 update t1 set ushort=65535;
2855 # save_in_field() will truncate 999999 to MAX_UINT(65535)
2856 # The truncated warning is ignored, and we effectively compared
2857 # against 65535 below.
2858 explain
2859 select * from t1 where ushort <= 999999;
2860 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2861 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
2862 Warnings:
2863 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where true
2864 select * from t1 where ushort <= 999999;
2865 ushort date_field
2866 65535 1901-01-01
2867 65535 1902-02-02
2868 65535 1903-03-03
2869 65535 1904-04-04
2870 explain
2871 select * from t1 where ushort < 999999;
2872 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2873 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
2874 Warnings:
2875 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where true
2876 select * from t1 where ushort < 999999;
2877 ushort date_field
2878 65535 1901-01-01
2879 65535 1902-02-02
2880 65535 1903-03-03
2881 65535 1904-04-04
2882 explain
2883 select * from t1 where ushort >= 999999;
2884 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2885 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2886 Warnings:
2887 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where false
2888 select * from t1 where ushort >= 999999;
2889 ushort date_field
2890 explain
2891 select * from t1 where ushort = 999999;
2892 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2893 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2894 Warnings:
2895 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where false
2896 select * from t1 where ushort = 999999;
2897 ushort date_field
2898 # Similar for other data types. Range for Date is '1000-01-01' to '9999-12-31'.
2899 # Seems to be unpredictable which value is actually stored for an
2900 # out of range value. Anyway the result was not as expected.
2901 # This was made obsolete when we started rejecting invalid dates
2902 set @@optimizer_switch = 'engine_condition_pushdown=off';
2903 explain
2904 select * from t1 where date_field < '9999-99-99';
2905 ERROR HY000: Incorrect DATE value: '9999-99-99'
2906 select * from t1 where date_field < '9999-99-99';
2907 ERROR HY000: Incorrect DATE value: '9999-99-99'
2908 explain
2909 select * from t1 where date_field > '9999-99-99';
2910 ERROR HY000: Incorrect DATE value: '9999-99-99'
2911 select * from t1 where date_field > '9999-99-99';
2912 ERROR HY000: Incorrect DATE value: '9999-99-99'
2913 set @@optimizer_switch = 'engine_condition_pushdown=on';
2914 explain
2915 select * from t1 where date_field < '9999-99-99';
2916 ERROR HY000: Incorrect DATE value: '9999-99-99'
2917 select * from t1 where date_field < '9999-99-99';
2918 ERROR HY000: Incorrect DATE value: '9999-99-99'
2919 explain
2920 select * from t1 where date_field > '9999-99-99';
2921 ERROR HY000: Incorrect DATE value: '9999-99-99'
2922 select * from t1 where date_field > '9999-99-99';
2923 ERROR HY000: Incorrect DATE value: '9999-99-99'
2924 #
2925 # Bug#29058732 CONDITION PUSHDOWN INCORRECTLY REJECTED FOR TEMPORAL DATA TYPES
2926 # Condition pushdown incorrectly depended on whether the date_field was
2927 # specified before or after the string constant.
2928 #
2929 explain
2930 select * from t1 where
2931 '1999-01-01' >= date_field ;
2932 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2933 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 33.33 Using pushed condition (DATE'1999-01-01' >= `test`.`t1`.`date_field`)
2934 Warnings:
2935 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where (DATE'1999-01-01' >= `test`.`t1`.`date_field`)
2936 explain
2937 select * from t1 where
2938 date_field <= '1999-01-01';
2939 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2940 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 33.33 Using pushed condition (`test`.`t1`.`date_field` <= DATE'1999-01-01')
2941 Warnings:
2942 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where (`test`.`t1`.`date_field` <= DATE'1999-01-01')
2943 explain
2944 select * from t1 where
2945 date '1999-01-01' >= date_field ;
2946 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2947 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 33.33 Using pushed condition (DATE'1999-01-01' >= `test`.`t1`.`date_field`)
2948 Warnings:
2949 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where (DATE'1999-01-01' >= `test`.`t1`.`date_field`)
2950 explain
2951 select * from t1 where
2952 date_field <= date '1999-01-01';
2953 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2954 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 33.33 Using pushed condition (`test`.`t1`.`date_field` <= DATE'1999-01-01')
2955 Warnings:
2956 Note 1003 /* select#1 */ select `test`.`t1`.`ushort` AS `ushort`,`test`.`t1`.`date_field` AS `date_field` from `test`.`t1` where (`test`.`t1`.`date_field` <= DATE'1999-01-01')
2957 drop table t1;
2958 #########################################################
2959 # WL#12686: Enable condition pushdown of field references
2960 # to previous tables in the query plan.
2961 #########################################################
2962 create table t1 (
2963 a int,
2964 b int,
2965 c int,
2966 d int
2967 ) engine=ndbcluster;
2968 insert into t1 values
2969 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
2970 (1,2,5,1), (1,3,1,2), (1,4,2,3),
2971 (2,1,3,4), (2,3,4,5), (2,4,5,1),
2972 (3,1,1,2), (3,2,2,3), (3,4,3,4),
2973 (4,1,4,5), (4,2,5,1), (4,3,1,2);
2974 set optimizer_switch='block_nested_loop=off';
2975 explain
2976 select *
2977 from t1 straight_join t1 as t2
2978 on t2.d = t1.d;
2979 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2980 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
2981 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 10.00 Using pushed condition (`test`.`t2`.`d` = `test`.`t1`.`d`)
2982 explain
2983 select *
2984 from t1 straight_join t1 as t2
2985 on t2.a = t1.c and t2.d = t1.d;
2986 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2987 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
2988 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 6.25 Using pushed condition ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`a` = `test`.`t1`.`c`))
2989 explain
2990 select *
2991 from t1 straight_join t1 as t2
2992 on t2.a = t1.c or t2.d = t1.d;
2993 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2994 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
2995 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 19.00 Using pushed condition ((`test`.`t2`.`a` = `test`.`t1`.`c`) or (`test`.`t2`.`d` = `test`.`t1`.`d`))
2996 explain
2997 select * from t1
2998 straight_join t1 as t2 on t2.d = t1.d
2999 straight_join t1 as t3 on t3.a = t2.c and t3.d = t1.c;
3000 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3001 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3002 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 10.00 Using pushed condition (`test`.`t2`.`d` = `test`.`t1`.`d`)
3003 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 6.25 Using pushed condition ((`test`.`t3`.`d` = `test`.`t1`.`c`) and (`test`.`t3`.`a` = `test`.`t2`.`c`))
3004 explain
3005 select * from t1
3006 straight_join t1 as t2 on t2.d = t1.d
3007 straight_join t1 as t3 on t3.a = t2.c or t3.d = t1.c;
3008 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3009 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3010 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 10.00 Using pushed condition (`test`.`t2`.`d` = `test`.`t1`.`d`)
3011 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 19.00 Using pushed condition ((`test`.`t3`.`a` = `test`.`t2`.`c`) or (`test`.`t3`.`d` = `test`.`t1`.`c`))
3012 explain
3013 select *
3014 from t1 straight_join t1 as t2
3015 on (t2.d = 1 or t1.d = t2.d);
3016 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3017 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3018 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 19.00 Using pushed condition ((`test`.`t2`.`d` = 1) or (`test`.`t2`.`d` = `test`.`t1`.`d`))
3019 explain
3020 select *
3021 from t1 straight_join t1 as t2
3022 on (t2.d = 1 or t1.d = 1);
3023 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3024 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3025 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition ((`test`.`t2`.`d` = 1) or (`test`.`t1`.`d` = 1))
3026 explain
3027 select *
3028 from t1 straight_join t1 as t2
3029 on (t2.d = 1 or 1 = t1.d);
3030 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3031 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3032 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition ((`test`.`t2`.`d` = 1) or (`test`.`t1`.`d` = 1))
3033 explain
3034 select *
3035 from t1 straight_join t1 as t2
3036 on (t1.d = 1 or 1 = t2.d);
3037 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3038 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3039 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition ((`test`.`t1`.`d` = 1) or (`test`.`t2`.`d` = 1))
3040 explain
3041 select *
3042 from t1 straight_join t1 as t2
3043 on (1 = t1.d or t2.d = 1);
3044 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3045 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3046 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition ((`test`.`t1`.`d` = 1) or (`test`.`t2`.`d` = 1))
3047 explain
3048 select *
3049 from t1 straight_join t1 as t2
3050 on (t2.d = 1 or t1.c = t1.b);
3051 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3052 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3053 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition ((`test`.`t2`.`d` = 1) or (`test`.`t1`.`b` = `test`.`t1`.`c`))
3054 explain
3055 select *
3056 from t1 straight_join t1 as t2
3057 on (t2.d = t1.d or t1.c = t1.b);
3058 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3059 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3060 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition ((`test`.`t2`.`d` = `test`.`t1`.`d`) or (`test`.`t1`.`b` = `test`.`t1`.`c`))
3061 explain
3062 select *
3063 from t1 straight_join t1 as t2
3064 on (t2.d = t2.c);
3065 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3066 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3067 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 Using pushed condition (`test`.`t2`.`c` = `test`.`t2`.`d`)
3068 explain
3069 select *
3070 from t1 straight_join t1 as t2
3071 on (t1.a between t2.a and t2.b);
3072 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3073 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3074 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 11.11 Using pushed condition (`test`.`t1`.`a` between `test`.`t2`.`a` and `test`.`t2`.`b`)
3075 explain
3076 select *
3077 from t1 straight_join t1 as t2
3078 on (t2.a between t1.a and t1.b);
3079 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3080 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3081 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 11.11 Using pushed condition (`test`.`t2`.`a` between `test`.`t1`.`a` and `test`.`t1`.`b`)
3082 explain
3083 select *
3084 from t1 straight_join t1 as t2
3085 on (t2.a between t1.a and t2.b);
3086 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3087 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3088 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 11.11 Using pushed condition (`test`.`t2`.`a` between `test`.`t1`.`a` and `test`.`t2`.`b`)
3089 delete from t1;
3090 insert into t1 values (1,1,1,1), (2, 2, NULL, NULL);
3091 explain
3092 select *
3093 from t1 straight_join t1 as t2
3094 on t2.d = t1.d;
3095 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3096 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 NULL
3097 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 50.00 Using pushed condition (`test`.`t2`.`d` = `test`.`t1`.`d`)
3098 select *
3099 from t1 straight_join t1 as t2
3100 on t2.d = t1.d;
3101 a b c d a b c d
3102 1 1 1 1 1 1 1 1
3103 select *
3104 from t1 straight_join t1 as t2
3105 on t2.d = t1.d or t2.a = t1.a;
3106 a b c d a b c d
3107 1 1 1 1 1 1 1 1
3108 2 2 NULL NULL 2 2 NULL NULL
3109 select *
3110 from t1 straight_join t1 as t2
3111 on t2.a = t1.a or t2.d = t1.d;
3112 a b c d a b c d
3113 1 1 1 1 1 1 1 1
3114 2 2 NULL NULL 2 2 NULL NULL
3115 select *
3116 from t1 straight_join t1 as t2
3117 on t2.d <> t1.d;
3118 a b c d a b c d
3119 select *
3120 from t1 straight_join t1 as t2
3121 on t2.d < t1.d;
3122 a b c d a b c d
3123 drop table t1;
3124 create table t1(a int, b char(10), c char(20))
3125 engine=ndbcluster;
3126 insert into t1 values
3127 (1,'1','1'), (2,'2','2'),
3128 (3,'1%','0123456789abcdef'), (4,null,null), (null,'5','555555555555555'),
3129 (6,'x','x'), (7,'y','y'),
3130 (8, '0123456789', '01234567890123456789');
3131 explain
3132 select *
3133 from t1 straight_join t1 as t2
3134 on t2.b = t1.b;
3135 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3136 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3137 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t2`.`b` = `test`.`t1`.`b`)
3138 explain
3139 select *
3140 from t1 straight_join t1 as t2
3141 on t2.b = t1.c;
3142 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3143 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3144 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3145 explain
3146 select *
3147 from t1 straight_join t1 as t2
3148 on t2.a = t1.b;
3149 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3150 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3151 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3152 explain
3153 select *
3154 from t1 straight_join t1 as t2
3155 on t2.a = t1.c;
3156 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3157 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3158 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3159 explain
3160 select *
3161 from t1 straight_join t1 as t2
3162 on t2.b = t1.c;
3163 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3164 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3165 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3166 explain
3167 select *
3168 from t1 straight_join t1 as t2
3169 on t1.c = t2.b;
3170 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3171 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3172 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3173 explain
3174 select *
3175 from t1 straight_join t1 as t2
3176 on t2.c = t1.b;
3177 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3178 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3179 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t2`.`c` = `test`.`t1`.`b`)
3180 explain
3181 select *
3182 from t1 straight_join t1 as t2
3183 on t1.b = t2.c;
3184 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3185 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3186 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t1`.`b` = `test`.`t2`.`c`)
3187 alter table t1 add column d varchar(20);
3188 update t1 set d = c;
3189 explain
3190 select *
3191 from t1 straight_join t1 as t2
3192 on t2.c = t1.d;
3193 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3194 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3195 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t2`.`c` = `test`.`t1`.`d`)
3196 explain
3197 select *
3198 from t1 straight_join t1 as t2
3199 on t1.d = t2.c;
3200 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3201 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3202 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t1`.`d` = `test`.`t2`.`c`)
3203 alter table t1 drop column d;
3204 alter table t1 add column d varchar(30);
3205 update t1 set d = c;
3206 explain
3207 select *
3208 from t1 straight_join t1 as t2
3209 on t2.c = t1.d;
3210 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3211 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3212 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3213 explain
3214 select *
3215 from t1 straight_join t1 as t2
3216 on t1.d = t2.c;
3217 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3218 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3219 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3220 alter table t1 drop column d;
3221 alter table t1 add column d varchar(10);
3222 update t1 set d = substring(c,1,10);
3223 explain
3224 select *
3225 from t1 straight_join t1 as t2
3226 on t2.c = t1.d;
3227 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3228 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3229 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t2`.`c` = `test`.`t1`.`d`)
3230 explain
3231 select *
3232 from t1 straight_join t1 as t2
3233 on t1.d = t2.c;
3234 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3235 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3236 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using pushed condition (`test`.`t1`.`d` = `test`.`t2`.`c`)
3237 alter table t1 drop column d;
3238 alter table t1 add column d char(20) character set latin1 collate latin1_swedish_ci;
3239 update t1 set d = c;
3240 explain
3241 select *
3242 from t1 straight_join t1 as t2
3243 on t2.c = t1.d;
3244 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3245 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3246 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3247 explain
3248 select *
3249 from t1 straight_join t1 as t2
3250 on t1.d = t2.c;
3251 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3252 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 100.00 NULL
3253 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 8 12.50 Using where
3254 drop table t1;
3255 set optimizer_switch='block_nested_loop=default';
3256 #
3257 # Bug#29296615 FAILS TO DO PARTIAL PUSH OF PREDICATES INSIDE
3258 # AN OR'ED CONDITION
3259 #
3260 create table t1 (
3261 a3 int,
3262 b3 int,
3263 c3 int,
3264 d3 int)
3265 engine = ndb;
3266 insert into t1 values (0x1f, 0x2f, 1, 0x1f);
3267 insert into t1 values (0x2f, 0x3f, 2, 0x2f);
3268 insert into t1 values (0x3f, 0x1f, 3, 0x3f);
3269 insert into t1 values (0x40, 0, null, null);
3270 insert into t1 values (0x41, 0, null, null);
3271 insert into t1 values (0x42, 0, 4, null);
3272 insert into t1 values (0x43, 0, null, 0x43);
3273 explain select * from t1 where c3 > 1;
3274 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3275 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using pushed condition (`test`.`t1`.`c3` > 1)
3276 Warnings:
3277 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (`test`.`t1`.`c3` > 1)
3278 explain select * from t1 where c3+1 > 1;
3279 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3280 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3281 Warnings:
3282 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`c3` + 1) > 1)
3283 explain select * from t1 where c3 > 1 or c3 < 10;
3284 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3285 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using pushed condition ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10))
3286 Warnings:
3287 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10))
3288 explain select * from t1 where c3+1 > 1 or c3 < 10;
3289 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3290 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3291 Warnings:
3292 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` + 1) > 1) or (`test`.`t1`.`c3` < 10))
3293 explain select * from t1 where c3 > 1 or c3+1 < 10;
3294 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3295 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3296 Warnings:
3297 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`c3` > 1) or ((`test`.`t1`.`c3` + 1) < 10))
3298 explain select * from t1 where c3+1 > 1 or c3+1 < 10;
3299 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3300 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3301 Warnings:
3302 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` + 1) > 1) or ((`test`.`t1`.`c3` + 1) < 10))
3303 explain select * from t1 where (c3 > 1 or c3 < 10) and d3 >= 0;
3304 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3305 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 18.52 Using pushed condition (((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10)) and (`test`.`t1`.`d3` >= 0))
3306 Warnings:
3307 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10)) and (`test`.`t1`.`d3` >= 0))
3308 explain select * from t1 where (c3+1 > 1 or c3 < 10) and d3 >= 0;
3309 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3310 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`d3` >= 0)
3311 Warnings:
3312 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) or (`test`.`t1`.`c3` < 10)) and (`test`.`t1`.`d3` >= 0))
3313 explain select * from t1 where (c3 > 1 or c3+1 < 10) and d3 >= 0;
3314 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3315 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`d3` >= 0)
3316 Warnings:
3317 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) or ((`test`.`t1`.`c3` + 1) < 10)) and (`test`.`t1`.`d3` >= 0))
3318 explain select * from t1 where (c3+1 > 1 or c3+1 < 10) and d3 >= 0;
3319 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3320 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`d3` >= 0)
3321 Warnings:
3322 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) or ((`test`.`t1`.`c3` + 1) < 10)) and (`test`.`t1`.`d3` >= 0))
3323 explain select * from t1 where (c3 > 1 or c3 < 10) and d3+1 >= 0;
3324 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3325 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using where; Using pushed condition ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10))
3326 Warnings:
3327 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10)) and ((`test`.`t1`.`d3` + 1) >= 0))
3328 explain select * from t1 where (c3+1 > 1 or c3 < 10) and d3+1 >= 0;
3329 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3330 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3331 Warnings:
3332 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) or (`test`.`t1`.`c3` < 10)) and ((`test`.`t1`.`d3` + 1) >= 0))
3333 explain select * from t1 where (c3 > 1 or c3+1 < 10) and d3+1 >= 0;
3334 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3335 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3336 Warnings:
3337 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) or ((`test`.`t1`.`c3` + 1) < 10)) and ((`test`.`t1`.`d3` + 1) >= 0))
3338 explain select * from t1 where (c3+1 > 1 or c3+1 < 10) and d3+1 >= 0;
3339 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3340 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3341 Warnings:
3342 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) or ((`test`.`t1`.`c3` + 1) < 10)) and ((`test`.`t1`.`d3` + 1) >= 0))
3343 explain select * from t1 where d3 >= 0 and (c3 > 1 or c3 < 10);
3344 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3345 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 18.52 Using pushed condition ((`test`.`t1`.`d3` >= 0) and ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10)))
3346 Warnings:
3347 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) and ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10)))
3348 explain select * from t1 where d3 >= 0 and (c3+1 > 1 or c3 < 10);
3349 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3350 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`d3` >= 0)
3351 Warnings:
3352 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) and (((`test`.`t1`.`c3` + 1) > 1) or (`test`.`t1`.`c3` < 10)))
3353 explain select * from t1 where d3 >= 0 and (c3 > 1 or c3+1 < 10);
3354 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3355 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`d3` >= 0)
3356 Warnings:
3357 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) and ((`test`.`t1`.`c3` > 1) or ((`test`.`t1`.`c3` + 1) < 10)))
3358 explain select * from t1 where d3 >= 0 and (c3+1 > 1 or c3+1 < 10);
3359 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3360 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`d3` >= 0)
3361 Warnings:
3362 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) and (((`test`.`t1`.`c3` + 1) > 1) or ((`test`.`t1`.`c3` + 1) < 10)))
3363 explain select * from t1 where d3+1 >= 0 and (c3 > 1 or c3 < 10);
3364 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3365 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using where; Using pushed condition ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10))
3366 Warnings:
3367 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) and ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`c3` < 10)))
3368 explain select * from t1 where d3+1 >= 0 and (c3+1 > 1 or c3 < 10);
3369 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3370 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3371 Warnings:
3372 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) and (((`test`.`t1`.`c3` + 1) > 1) or (`test`.`t1`.`c3` < 10)))
3373 explain select * from t1 where d3+1 >= 0 and (c3 > 1 or c3+1 < 10);
3374 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3375 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3376 Warnings:
3377 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) and ((`test`.`t1`.`c3` > 1) or ((`test`.`t1`.`c3` + 1) < 10)))
3378 explain select * from t1 where d3+1 >= 0 and (c3+1 > 1 or c3+1 < 10);
3379 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3380 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3381 Warnings:
3382 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) and (((`test`.`t1`.`c3` + 1) > 1) or ((`test`.`t1`.`c3` + 1) < 10)))
3383 explain select * from t1 where c3 > 1 and c3 < 10;
3384 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3385 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 14.29 Using pushed condition ((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10))
3386 Warnings:
3387 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10))
3388 explain select * from t1 where c3+1 > 1 and c3 < 10;
3389 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3390 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`c3` < 10)
3391 Warnings:
3392 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` + 1) > 1) and (`test`.`t1`.`c3` < 10))
3393 explain select * from t1 where c3 > 1 and c3+1 < 10;
3394 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3395 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 33.33 Using where; Using pushed condition (`test`.`t1`.`c3` > 1)
3396 Warnings:
3397 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`c3` > 1) and ((`test`.`t1`.`c3` + 1) < 10))
3398 explain select * from t1 where c3+1 > 1 and c3+1 < 10;
3399 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3400 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3401 Warnings:
3402 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` + 1) > 1) and ((`test`.`t1`.`c3` + 1) < 10))
3403 explain select * from t1 where (c3 > 1 and c3 < 10) or d3 >= 0;
3404 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3405 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 40.74 Using pushed condition (((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10)) or (`test`.`t1`.`d3` >= 0))
3406 Warnings:
3407 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10)) or (`test`.`t1`.`d3` >= 0))
3408 explain select * from t1 where (c3+1 > 1 and c3 < 10) or d3 >= 0;
3409 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3410 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using where; Using pushed condition ((`test`.`t1`.`c3` < 10) or (`test`.`t1`.`d3` >= 0))
3411 Warnings:
3412 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) and (`test`.`t1`.`c3` < 10)) or (`test`.`t1`.`d3` >= 0))
3413 explain select * from t1 where (c3 > 1 and c3+1 < 10) or d3 >= 0;
3414 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3415 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using where; Using pushed condition ((`test`.`t1`.`c3` > 1) or (`test`.`t1`.`d3` >= 0))
3416 Warnings:
3417 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) and ((`test`.`t1`.`c3` + 1) < 10)) or (`test`.`t1`.`d3` >= 0))
3418 explain select * from t1 where (c3+1 > 1 and c3+1 < 10) or d3 >= 0;
3419 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3420 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3421 Warnings:
3422 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) and ((`test`.`t1`.`c3` + 1) < 10)) or (`test`.`t1`.`d3` >= 0))
3423 explain select * from t1 where (c3 > 1 and c3 < 10) or d3+1 >= 0;
3424 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3425 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3426 Warnings:
3427 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10)) or ((`test`.`t1`.`d3` + 1) >= 0))
3428 explain select * from t1 where (c3+1 > 1 and c3 < 10) or d3+1 >= 0;
3429 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3430 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3431 Warnings:
3432 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) and (`test`.`t1`.`c3` < 10)) or ((`test`.`t1`.`d3` + 1) >= 0))
3433 explain select * from t1 where (c3 > 1 and c3+1 < 10) or d3+1 >= 0;
3434 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3435 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3436 Warnings:
3437 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`c3` > 1) and ((`test`.`t1`.`c3` + 1) < 10)) or ((`test`.`t1`.`d3` + 1) >= 0))
3438 explain select * from t1 where (c3+1 > 1 and c3+1 < 10) or d3+1 >= 0;
3439 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3440 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3441 Warnings:
3442 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((((`test`.`t1`.`c3` + 1) > 1) and ((`test`.`t1`.`c3` + 1) < 10)) or ((`test`.`t1`.`d3` + 1) >= 0))
3443 explain select * from t1 where d3 >= 0 or (c3 > 1 and c3 < 10);
3444 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3445 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 40.74 Using pushed condition ((`test`.`t1`.`d3` >= 0) or ((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10)))
3446 Warnings:
3447 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) or ((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10)))
3448 explain select * from t1 where d3 >= 0 or (c3+1 > 1 and c3 < 10);
3449 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3450 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using where; Using pushed condition ((`test`.`t1`.`d3` >= 0) or (`test`.`t1`.`c3` < 10))
3451 Warnings:
3452 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) or (((`test`.`t1`.`c3` + 1) > 1) and (`test`.`t1`.`c3` < 10)))
3453 explain select * from t1 where d3 >= 0 or (c3 > 1 and c3+1 < 10);
3454 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3455 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 55.55 Using where; Using pushed condition ((`test`.`t1`.`d3` >= 0) or (`test`.`t1`.`c3` > 1))
3456 Warnings:
3457 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) or ((`test`.`t1`.`c3` > 1) and ((`test`.`t1`.`c3` + 1) < 10)))
3458 explain select * from t1 where d3 >= 0 or (c3+1 > 1 and c3+1 < 10);
3459 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3460 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3461 Warnings:
3462 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where ((`test`.`t1`.`d3` >= 0) or (((`test`.`t1`.`c3` + 1) > 1) and ((`test`.`t1`.`c3` + 1) < 10)))
3463 explain select * from t1 where d3+1 >= 0 or (c3 > 1 and c3 < 10);
3464 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3465 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3466 Warnings:
3467 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) or ((`test`.`t1`.`c3` > 1) and (`test`.`t1`.`c3` < 10)))
3468 explain select * from t1 where d3+1 >= 0 or (c3+1 > 1 and c3 < 10);
3469 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3470 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3471 Warnings:
3472 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) or (((`test`.`t1`.`c3` + 1) > 1) and (`test`.`t1`.`c3` < 10)))
3473 explain select * from t1 where d3+1 >= 0 or (c3 > 1 and c3+1 < 10);
3474 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3475 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3476 Warnings:
3477 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) or ((`test`.`t1`.`c3` > 1) and ((`test`.`t1`.`c3` + 1) < 10)))
3478 explain select * from t1 where d3+1 >= 0 or (c3+1 > 1 and c3+1 < 10);
3479 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3480 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 7 100.00 Using where
3481 Warnings:
3482 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3` from `test`.`t1` where (((`test`.`t1`.`d3` + 1) >= 0) or (((`test`.`t1`.`c3` + 1) > 1) and ((`test`.`t1`.`c3` + 1) < 10)))
3483 create index ix1 on t1(a3);
3484 explain select * from t1 straight_join t1 as t2
3485 where
3486 (
3487 t2.a3 = t1.a3
3488 and t1.b3 = 1
3489 and t1.c3 in (1,2,3)
3490 and t2.b3 between 1 and 5
3491 and t2.c3 > 1
3492 )
3493 or
3494 (
3495 t2.a3 = t1.a3
3496 and t1.b3 = 2
3497 and t1.c3 in (10,20,30)
3498 and t2.b3 between 1 and 10
3499 and t2.c3 > 2
3500 )
3501 or
3502 (
3503 t2.a3 = t1.a3
3504 and t1.b3 = 3
3505 and t1.c3 in (100,200,300)
3506 and t2.b3 between 1 and 15
3507 and t2.c3 > 3
3508 )
3509 ;
3510 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3511 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL ix1 NULL NULL NULL 7 17.27 Parent of 2 pushed join@1; Using pushed condition ((((`test`.`t1`.`b3` = 1) and (`test`.`t1`.`c3` in (1,2,3))) or ((`test`.`t1`.`b3` = 2) and (`test`.`t1`.`c3` in (10,20,30))) or ((`test`.`t1`.`b3` = 3) and (`test`.`t1`.`c3` in (100,200,300)))) and (`test`.`t1`.`a3` is not null))
3512 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref ix1 ix1 5 test.t1.a3 2 14.29 Child of 't1' in pushed join@1; Using where; Using pushed condition (((`test`.`t2`.`b3` between 1 and 5) and (`test`.`t2`.`c3` > 1)) or ((`test`.`t2`.`b3` between 1 and 10) and (`test`.`t2`.`c3` > 2)) or ((`test`.`t2`.`b3` between 1 and 15) and (`test`.`t2`.`c3` > 3)))
3513 Warnings:
3514 Note 1003 /* select#1 */ select `test`.`t1`.`a3` AS `a3`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`d3` AS `d3`,`test`.`t2`.`a3` AS `a3`,`test`.`t2`.`b3` AS `b3`,`test`.`t2`.`c3` AS `c3`,`test`.`t2`.`d3` AS `d3` from `test`.`t1` straight_join `test`.`t1` `t2` where (((`test`.`t1`.`b3` = 1) and (`test`.`t2`.`a3` = `test`.`t1`.`a3`) and (`test`.`t1`.`c3` in (1,2,3)) and (`test`.`t2`.`b3` between 1 and 5) and (`test`.`t2`.`c3` > 1)) or ((`test`.`t1`.`b3` = 2) and (`test`.`t2`.`a3` = `test`.`t1`.`a3`) and (`test`.`t1`.`c3` in (10,20,30)) and (`test`.`t2`.`b3` between 1 and 10) and (`test`.`t2`.`c3` > 2)) or ((`test`.`t1`.`b3` = 3) and (`test`.`t2`.`a3` = `test`.`t1`.`a3`) and (`test`.`t1`.`c3` in (100,200,300)) and (`test`.`t2`.`b3` between 1 and 15) and (`test`.`t2`.`c3` > 3)))
3515 drop table t1;
3516 #########################################################################
3517 #
3518 # Bug#34644930 NDB: Performance regression in TPC-H Q22
3519 #
3520 #########################################################################
3521 create table t1 (
3522 d decimal(6,2)
3523 ) engine = ndbcluster;
3524 insert into t1 values (1.00), (1.00), (3.00);
3525 explain format=tree
3526 select * from t1
3527 where d > (select avg(d) from t1);
3528 EXPLAIN
3529 -> Table scan on t1, with pushed condition: (t1.d > (select #2)) (cost=750 rows=1)
3530 -> Select #2 (subquery in pushed condition; run only once)
3531 -> Aggregate: avg(t1.d) (cost=751 rows=1)
3532 -> Table scan on t1 (cost=750 rows=3)
3533
3534 SELECT variable_value into @read_cnt0
3535 FROM performance_schema.global_status
3536 WHERE variable_name = 'Ndb_api_read_row_count';
3537 select * from t1
3538 where d > (select avg(d) from t1);
3539 d
3540 3.00
3541 # Expect 4 rows to have been read:
3542 # The 3 rows for calculating avg(d)
3543 # + A single row from t1 with the pushed condition 'd > avg()'
3544 SELECT variable_value-@read_cnt0
3545 FROM performance_schema.global_status
3546 WHERE variable_name = 'Ndb_api_read_row_count';
3547 variable_value-@read_cnt0
3548 4
3549 drop table t1;
3550 #
3551 # Bug#29460314 NDB: INCORRECT JOIN RESULT WHEN EQ_REF IS
3552 # COMBINED WITH PUSHED CONDITION
3553 #
3554 create table t1(a int not null, b int not null) engine=ndb;
3555 insert into t1 values (1,0), (1,1), (1,2);
3556 create table t2(pk int primary key, x int not null, y int not null) engine=ndb;
3557 insert into t2 values (0,0,0), (1,1,1), (2,2,2);
3558 create table t3(d int primary key) engine=ndb;
3559 insert into t3 values (0), (1), (2);
3560 Explain should verify that the condition 't2.x = t1.b' is pushed
3561 explain
3562 select straight_join * from t1
3563 join t2 on t2.pk = t1.a+0 and t2.x = t1.b
3564 join t3 on t3.d = t2.y;
3565 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3566 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 100.00 NULL
3567 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 func 1 33.33 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`x` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` = (`test`.`t1`.`a` + 0)))
3568 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 test.t2.y 1 100.00 Child of 't2' in pushed join@1
3569 Warnings:
3570 Note 1003 Can't push table 't2' as child, column 'pk' does neither 'ref' a column nor a constant
3571 Note 1003 Can't push table 't3' as child of 't1', column 't2.y' refers a table which was not pushed
3572 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`x` = `test`.`t1`.`b`) and (`test`.`t3`.`d` = `test`.`t2`.`y`) and (`test`.`t2`.`pk` = (`test`.`t1`.`a` + 0)))
3573 select straight_join * from t1
3574 join t2 on t2.pk = t1.a+0 and t2.x = t1.b
3575 join t3 on t3.d = t2.y;
3576 a b pk x y d
3577 1 1 1 1 1 1
3578 drop table t1,t2,t3;
3579 #
3580 # Bug#29590017 ADD EXPLAIN OF PUSHED JOINS AND PUSHED CONDITIONS TO 'FORMAT=TREE'
3581 #
3582 create table t1 (
3583 a int,
3584 b int,
3585 c int,
3586 d int,
3587 primary key(a,b)
3588 ) engine=ndbcluster;
3589 insert into t1 values
3590 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
3591 (1,2,5,1), (1,3,1,2), (1,4,2,3),
3592 (2,1,3,4), (2,3,4,5), (2,4,5,1),
3593 (3,1,1,2), (3,2,2,3), (3,4,3,4),
3594 (4,1,4,5), (4,2,5,1), (4,3,1,2);
3595 explain format=tree
3596 select * from t1 where b > 2;
3597 EXPLAIN
3598 -> Table scan on t1, with pushed condition: (t1.b > 2) (cost=4002 rows=5.33)
3599
3600 explain format=tree
3601 select * from t1 where a > 2;
3602 EXPLAIN
3603 -> Index range scan on t1 using PRIMARY over (2 < a), with pushed condition: (t1.a > 2) (cost=2.61 rows=3)
3604
3605 explain format=tree
3606 select * from t1 where a > 2 order by b;
3607 EXPLAIN
3608 -> Sort: t1.b (cost=2.61 rows=3)
3609 -> Index range scan on t1 using PRIMARY over (2 < a), with pushed condition: (t1.a > 2) (cost=2.61 rows=3)
3610
3611 explain format=tree
3612 select * from t1 where a = 2 and c > 0;
3613 EXPLAIN
3614 -> Index lookup on t1 using PRIMARY (a=2), with pushed condition: (t1.c > 0) (cost=2.1 rows=1)
3615
3616 explain format=tree
3617 select * from t1 where a = 2 and c > 0 order by d;
3618 EXPLAIN
3619 -> Sort: t1.d (cost=2.1 rows=3)
3620 -> Index lookup on t1 using PRIMARY (a=2), with pushed condition: (t1.c > 0) (cost=2.1 rows=3)
3621
3622 explain format=tree
3623 select * from t1 where b > 0 order by a;
3624 EXPLAIN
3625 -> Index scan on t1 using PRIMARY, with pushed condition: (t1.b > 0) (cost=4002 rows=5.33)
3626
3627 explain format=tree
3628 select * from t1 where b > 0 order by a desc;
3629 EXPLAIN
3630 -> Index scan on t1 using PRIMARY (reverse), with pushed condition: (t1.b > 0) (cost=4002 rows=5.33)
3631
3632 explain format=tree
3633 select * from t1 join t1 as t2 on t2.a = t1.b and t2.c > 3;
3634 EXPLAIN
3635 -> Nested loop inner join (cost=4011 rows=5.33)
3636 -> Table scan on t1, activating pushed join of 2 tables (cost=4002 rows=16)
3637 -> Index lookup on t2 using PRIMARY (a=t1.b), child of t1 in pushed join, with pushed condition: (t2.c > 3) (cost=0.502 rows=0.333)
3638
3639 drop table t1;
3640 #
3641 # Bug#29595346 INCORRECT RESULT FROM SPECIFYING ORDER BY ON
3642 # A QUERY WITH PUSHED CONDITIONS
3643 #
3644 CREATE TABLE t1 (
3645 col_int_unique INT DEFAULT NULL,
3646 col_varchar_256 VARCHAR(256) DEFAULT NULL,
3647 UNIQUE KEY ix1 (col_int_unique)
3648 ) ENGINE=ndbcluster;
3649 INSERT INTO t1
3650 VALUES (8,'wrong'), (27, 'got'), (12,'it'), (6,'right');
3651 SELECT t1.col_varchar_256 AS field1
3652 FROM t1 WHERE t1.col_int_unique <> 8
3653 ORDER BY field1;
3654 field1
3655 got
3656 it
3657 right
3658 EXPLAIN FORMAT=tree
3659 SELECT t1.col_varchar_256 AS field1
3660 FROM t1 WHERE t1.col_int_unique <> 8
3661 ORDER BY field1;
3662 EXPLAIN
3663 -> Sort: t1.col_varchar_256 (cost=5.21 rows=6)
3664 -> Index range scan on t1 using ix1 over (NULL < col_int_unique < 8) OR (8 < col_int_unique), with pushed condition: (t1.col_int_unique <> 8) (cost=5.21 rows=6)
3665
3666 DROP TABLE t1;
3667 #
3668 # WL#13120 & WL#13121
3669 #
3670 Testing all combinations of pushing '<col1> <compare op> <col2>' conditions
3671 CALL test_all("=");
3672 CALL test_all("<>");
3673 CALL test_all("<");
3674 CALL test_all("<=");
3675 CALL test_all(">");
3676 CALL test_all(">=");
3677 Test combinations using the 'variant' columns.
3678 Even if the base type is the same for these conditions,
3679 different precision, length, scale etc. should prevent pushing:
3680 CALL test_query("SELECT k INTO @res FROM t1 WHERE string1 = string3",false);
3681 CALL test_query("SELECT k INTO @res FROM t1 WHERE string1 = string4",false);
3682 CALL test_query("SELECT k INTO @res FROM t1 WHERE vstring1 = vstring3",false);
3683 CALL test_query("SELECT k INTO @res FROM t1 WHERE bin1 = bin3",false);
3684 CALL test_query("SELECT k INTO @res FROM t1 WHERE vbin1 = vbin3",false);
3685 CALL test_query("SELECT k INTO @res FROM t1 WHERE real_decimal1 = real_decimal3",false);
3686 CALL test_query("SELECT k INTO @res FROM t1 WHERE bits1 = bits3",false);
3687 CALL test_query("SELECT k INTO @res FROM t1 WHERE options1 = options3",false);
3688 CALL test_query("SELECT k INTO @res FROM t1 WHERE flags1 = flags3",false);
3689 CALL test_query("SELECT k INTO @res FROM t1 WHERE time_field1 = time_field3",false);
3690 CALL test_query("SELECT k INTO @res FROM t1 WHERE date_time1 = date_time3",false);
3691 CALL test_query("SELECT k INTO @res FROM t1 WHERE time_stamp1 = time_stamp3",false);
3692 Test that <col1> like <col2> is not pushed, - even if types are comparable
3693 CALL test_query("SELECT count(*) INTO @res FROM t1 WHERE string1 LIKE string1",false);
3694 CALL test_query("SELECT count(*) INTO @res FROM t1 WHERE string1 NOT LIKE string1",false);
3695 CALL test_query("SELECT count(*) INTO @res FROM t1 WHERE string1 LIKE string2",false);
3696 CALL test_query("SELECT count(*) INTO @res FROM t1 WHERE string1 NOT LIKE string2",false);
3697 CALL test_query("SELECT count(*) INTO @res FROM t1 WHERE string1 LIKE string3",false);
3698 CALL test_query("SELECT count(*) INTO @res FROM t1 WHERE string1 NOT LIKE string3",false);
3699 #
3700 # Bug#32924533 Conditions not pushed down to tables inside views and table-subqueries
3701 #
3702 create table t1 (
3703 a int not null,
3704 b int not null,
3705 c int not null,
3706 d int not null
3707 ) engine=ndbcluster;
3708 insert into t1 values
3709 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
3710 (1,2,5,1), (1,3,1,2), (1,4,2,3),
3711 (2,1,3,4), (2,3,4,5), (2,4,5,1),
3712 (3,1,1,2), (3,2,2,3), (3,4,3,4),
3713 (4,1,4,5), (4,2,5,1), (4,3,1,2);
3714 create view v1(a,b,c,d) as
3715 select a,b,c,d from t1;
3716 explain format=tree
3717 select * from v1
3718 where v1.d < 5;
3719 EXPLAIN
3720 -> Table scan on t1, with pushed condition: (t1.d < 5)
3721
3722 explain format=tree
3723 select * from v1
3724 where v1.d < v1.c;
3725 EXPLAIN
3726 -> Table scan on t1, with pushed condition: (t1.d < t1.c)
3727
3728 explain format=tree
3729 select * from t1,v1
3730 where v1.d < 5;
3731 EXPLAIN
3732 -> Inner hash join (no condition)
3733 -> Table scan on t1
3734 -> Hash
3735 -> Table scan on t1, with pushed condition: (t1.d < 5)
3736
3737 Warnings:
3738 Note 1003 Can't push table 't1' as child, 'type' must be a 'ref' access
3739 explain format=tree
3740 select * from
3741 (select a,b,c,d from t1) as v1(a,b,c,d)
3742 where v1.d < 5;
3743 EXPLAIN
3744 -> Table scan on t1, with pushed condition: (t1.d < 5)
3745
3746 explain format=tree
3747 select * from
3748 (select a,b,c,d from t1) as v1(a,b,c,d)
3749 where v1.d < v1.c;
3750 EXPLAIN
3751 -> Table scan on t1, with pushed condition: (t1.d < t1.c)
3752
3753 explain format=tree
3754 select * from t1,
3755 (select a,b,c,d from t1) as v1(a,b,c,d)
3756 where v1.d < 5;
3757 EXPLAIN
3758 -> Inner hash join (no condition)
3759 -> Table scan on t1
3760 -> Hash
3761 -> Table scan on t1, with pushed condition: (t1.d < 5)
3762
3763 Warnings:
3764 Note 1003 Can't push table 't1' as child, 'type' must be a 'ref' access
3765 drop view v1;
3766 drop table t1;
3767 #########################################################
3768 # WL#14388 Push condition referring fields from an
3769 SPJ ancestor table
3770 #########################################################
3771 create table t1 (
3772 a int,
3773 b int,
3774 c int,
3775 d int,
3776 primary key(a,b)
3777 ) engine=ndbcluster;
3778 insert into t1 values
3779 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
3780 (1,2,5,1), (1,3,1,2), (1,4,2,3),
3781 (2,1,3,4), (2,3,4,5), (2,4,5,1),
3782 (3,1,1,2), (3,2,2,3), (3,4,3,4),
3783 (4,1,4,5), (4,2,5,1), (4,3,1,2);
3784 set optimizer_switch='block_nested_loop=off';
3785 explain
3786 select *
3787 from t1
3788 straight_join t1 as t2 on t2.d = t1.d
3789 straight_join t1 as t3 on t3.a = t2.a
3790 where t3.c >= t1.c and t3.c <= t2.c;
3791 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3792 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 16 100.00 NULL
3793 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL 16 10.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`d` = `test`.`t1`.`d`)
3794 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref PRIMARY PRIMARY 4 test.t2.a 1 11.11 Child of 't2' in pushed join@1; Using pushed condition ((`test`.`t3`.`c` >= `test`.`t1`.`c`) and (`test`.`t3`.`c` <= `test`.`t2`.`c`))
3795 Warnings:
3796 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
3797 Note 1003 Can't push table 't3' as child of 't1', column 't2.a' refers a table which was not pushed
3798 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`c` >= `test`.`t1`.`c`) and (`test`.`t3`.`c` <= `test`.`t2`.`c`))
3799 drop table t1;