Debian Codegraph @

mysql-test/suite/ndb_opt/condition_pushdown.test

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