Debian Codegraph @

mysql-test/suite/maria/icp.result

raw
1 set @save_storage_engine= @@default_storage_engine;
2 set default_storage_engine=Maria;
3 set @maria_icp_tmp=@@optimizer_switch;
4 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
5 #
6 # Bug#36981 - "innodb crash when selecting for update"
7 #
8 CREATE TABLE t1 (
9 c1 CHAR(1),
10 c2 CHAR(10),
11 KEY (c1)
12 );
13 INSERT INTO t1 VALUES ('3', null);
14 SELECT * FROM t1 WHERE c1='3' FOR UPDATE;
15 c1 c2
16 3 NULL
17 DROP TABLE t1;
18 CREATE TABLE t1 (a INT);
19 INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
20 CREATE TABLE t2 (a INT);
21 INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C;
22 CREATE TABLE t3 (
23 c1 CHAR(10) NOT NULL,
24 c2 CHAR(10) NOT NULL,
25 c3 CHAR(200) NOT NULL,
26 KEY (c1)
27 );
28 INSERT INTO t3
29 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler'
30 FROM t2;
31 INSERT INTO t3
32 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1'
33 FROM t2;
34 INSERT INTO t3
35 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2'
36 FROM t2;
37 SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE;
38 c1 c3
39 c-1994=w filler
40 c-1994=w filler-1
41 c-1994=w filler-2
42 c-1995=w filler
43 c-1995=w filler-1
44 c-1995=w filler-2
45 c-1997=w filler
46 c-1997=w filler-1
47 c-1997=w filler-2
48 c-1998=w filler
49 c-1998=w filler-1
50 c-1998=w filler-2
51 c-1999=w filler
52 c-1999=w filler-1
53 c-1999=w filler-2
54 DROP TABLE t1,t2,t3;
55 #
56 # Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
57 # null-safe operator <=> NULL
58 #
59 CREATE TABLE t1(
60 c1 DATE NOT NULL,
61 c2 DATE NULL,
62 c3 DATETIME,
63 c4 TIMESTAMP,
64 PRIMARY KEY(c1),
65 UNIQUE(c2)
66 );
67
68 INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
69 INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
70 INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03');
71 INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29');
72 INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
73
74 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
75 c1 c2 c3 c4
76 2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
77 2008-01-17 NULL NULL 2009-01-29 00:00:00
78
79 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
80 c1 c2 c3 c4
81 2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
82 2008-01-17 NULL NULL 2009-01-29 00:00:00
83
84 DROP TABLE t1;
85 #
86 # Bug#43617 - Innodb returns wrong results with timestamp's range value
87 # in IN clause
88 # (Note: Fixed by patch for BUG#42580)
89 #
90 CREATE TABLE t1(
91 c1 TIMESTAMP NOT NULL,
92 c2 TIMESTAMP NULL,
93 c3 DATE,
94 c4 DATETIME,
95 PRIMARY KEY(c1),
96 UNIQUE INDEX(c2)
97 );
98 INSERT INTO t1 VALUES
99 ('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'),
100 ('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'),
101 ('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL),
102 ('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'),
103 ('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'),
104 ('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'),
105 ('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'),
106 ('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00');
107
108 SELECT *
109 FROM t1
110 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
111 ORDER BY c2;
112 c1 c2 c3 c4
113 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
114
115 SELECT *
116 FROM t1
117 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
118 ORDER BY c2 LIMIT 2;
119 c1 c2 c3 c4
120 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
121
122 SELECT *
123 FROM t1
124 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
125 ORDER BY c2 DESC;
126 c1 c2 c3 c4
127 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
128
129 SELECT *
130 FROM t1
131 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07')
132 ORDER BY c2 DESC LIMIT 2;
133 c1 c2 c3 c4
134 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00
135
136 DROP TABLE t1;
137 #
138 # BUG#43618: MyISAM&Maria returns wrong results with 'between'
139 # on timestamp
140 #
141 CREATE TABLE t1(
142 ts TIMESTAMP NOT NULL,
143 c char NULL,
144 PRIMARY KEY(ts)
145 );
146 INSERT INTO t1 VALUES
147 ('1971-01-01','a'),
148 ('2007-05-25','b'),
149 ('2008-01-01','c'),
150 ('2038-01-09','d');
151
152 # Execute select with invalid timestamp, desc ordering
153 SELECT *
154 FROM t1 FORCE INDEX(PRIMARY)
155 WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00'
156 ORDER BY ts DESC
157 LIMIT 2;
158 ts c
159 2008-01-01 00:00:00 c
160 2007-05-25 00:00:00 b
161
162 # Should use index condition
163 EXPLAIN
164 SELECT *
165 FROM t1 FORCE INDEX(PRIMARY)
166 WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00'
167 ORDER BY ts DESC
168 LIMIT 2;
169 id select_type table type possible_keys key key_len ref rows Extra
170 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where
171
172 DROP TABLE t1;
173 #
174 # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc
175 # (Note: Fixed by patch for LP BUG#625841)
176 #
177 CREATE TABLE t1 (
178 f1 VARCHAR(1024),
179 f2 VARCHAR(10),
180 INDEX test_idx USING BTREE (f2,f1(5))
181 );
182 INSERT INTO t1 VALUES ('a','c'), ('b','d');
183 SELECT f1
184 FROM t1
185 WHERE f2 LIKE 'd'
186 ORDER BY f1;
187 f1
188 b
189 DROP TABLE t1;
190 #
191 # Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
192 # an index containing TEXT"
193 #
194 CREATE TABLE t1 (a INT);
195 INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
196 CREATE TABLE t2 (a INT);
197 INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
198 CREATE TABLE t3 (
199 c1 TINYTEXT NOT NULL,
200 i1 INT NOT NULL,
201 KEY (c1(6),i1)
202 ) CHARSET=latin1;
203 INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
204 EXPLAIN
205 SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w';
206 id select_type table type possible_keys key key_len ref rows Extra
207 1 SIMPLE t3 range c1 c1 8 NULL 3 Using where
208 SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w';
209 c1
210 c-1004=w
211 c-1005=w
212 c-1006=w
213 EXPLAIN
214 SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
215 id select_type table type possible_keys key key_len ref rows Extra
216 1 SIMPLE t3 range c1 c1 12 NULL 2 Using where
217 SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
218 c1
219 EXPLAIN
220 SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2;
221 id select_type table type possible_keys key key_len ref rows Extra
222 1 SIMPLE t3 ALL c1 NULL NULL NULL 100 Using where
223 SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2;
224 c1
225 c-1004=w
226 c-1005=w
227 c-1006=w
228 DROP TABLE t1, t2, t3;
229 #
230 # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
231 #
232 CREATE TABLE t (
233 dummy INT PRIMARY KEY,
234 a INT UNIQUE,
235 b INT
236 );
237 INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
238 SELECT * FROM t WHERE a > 2 FOR UPDATE;
239 dummy a b
240 3 3 3
241 5 5 5
242 DROP TABLE t;
243 #
244 # Bug#35080 - Innodb crash at mem_block_get_len line 72
245 #
246 CREATE TABLE t1 (
247 t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
248 uuid VARCHAR(36) DEFAULT NULL,
249 PRIMARY KEY (t1_autoinc),
250 KEY k (uuid)
251 );
252 CREATE TABLE t2 (
253 t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
254 uuid VARCHAR(36) DEFAULT NULL,
255 date DATETIME DEFAULT NULL,
256 PRIMARY KEY (t2_autoinc),
257 KEY k (uuid)
258 );
259 CREATE VIEW v1 AS
260 SELECT t1_autoinc, uuid
261 FROM t1
262 WHERE (ISNULL(uuid) OR (uuid like '%-%'));
263 CREATE VIEW v2 AS
264 SELECT t2_autoinc, uuid, date
265 FROM t2
266 WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36));
267 CREATE PROCEDURE delete_multi (IN uuid CHAR(36))
268 DELETE v1, v2 FROM v1 INNER JOIN v2
269 ON v1.uuid = v2.uuid
270 WHERE v1.uuid = @uuid;
271 SET @uuid = UUID();
272 INSERT INTO v1 (uuid) VALUES (@uuid);
273 INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09');
274 CALL delete_multi(@uuid);
275 DROP procedure delete_multi;
276 DROP table t1,t2;
277 DROP view v1,v2;
278 #
279 # Bug#41996 - multi-table delete crashes server (InnoDB table)
280 #
281 CREATE TABLE t1 (
282 b BIGINT,
283 i INT,
284 KEY (b)
285 );
286 INSERT INTO t1 VALUES (2, 2);
287 DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
288 DROP TABLE t1;
289 #
290 # Bug#43448 - Server crashes on multi table delete with Innodb
291 #
292 CREATE TABLE t1 (
293 id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
294 t CHAR(12)
295 );
296 CREATE TABLE t2 (
297 id2 INT NOT NULL,
298 t CHAR(12)
299 );
300 CREATE TABLE t3(
301 id3 INT NOT NULL,
302 t CHAR(12),
303 INDEX(id3)
304 );
305 CREATE PROCEDURE insert_data ()
306 BEGIN
307 DECLARE i1 INT DEFAULT 20;
308 DECLARE i2 INT;
309 DECLARE i3 INT;
310 WHILE (i1 > 0) DO
311 INSERT INTO t1(t) VALUES (i1);
312 SET i2 = 2;
313 WHILE (i2 > 0) DO
314 INSERT INTO t2(id2, t) VALUES (i1, i2);
315 SET i3 = 2;
316 WHILE (i3 > 0) DO
317 INSERT INTO t3(id3, t) VALUES (i1, i2);
318 SET i3 = i3 -1;
319 END WHILE;
320 SET i2 = i2 -1;
321 END WHILE;
322 SET i1 = i1 - 1;
323 END WHILE;
324 END |
325 CALL insert_data();
326 SELECT COUNT(*) FROM t1 WHERE id1 > 10;
327 COUNT(*)
328 10
329 SELECT COUNT(*) FROM t2 WHERE id2 > 10;
330 COUNT(*)
331 20
332 SELECT COUNT(*) FROM t3 WHERE id3 > 10;
333 COUNT(*)
334 40
335 DELETE t1, t2, t3
336 FROM t1, t2, t3
337 WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
338 SELECT COUNT(*) FROM t1;
339 COUNT(*)
340 3
341 SELECT COUNT(*) FROM t2;
342 COUNT(*)
343 6
344 SELECT COUNT(*) FROM t3;
345 COUNT(*)
346 12
347 DROP PROCEDURE insert_data;
348 DROP TABLE t1, t2, t3;
349 #
350 # Bug#57372 "Multi-table updates and deletes fail when running with ICP
351 # against InnoDB"
352 #
353 CREATE TABLE t1 (
354 a INT KEY,
355 b INT
356 );
357 CREATE TABLE t2 (
358 a INT KEY,
359 b INT
360 );
361 INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
362 INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
363 UPDATE t1, t2
364 SET t1.a = t1.a + 100, t2.b = t1.a + 10
365 WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
366 SELECT * FROM t1;
367 a b
368 1 101
369 102 102
370 103 103
371 104 104
372 5 105
373 SELECT * FROM t2;
374 a b
375 1 1
376 2 12
377 3 13
378 4 14
379 5 5
380 DROP TABLE t1, t2;
381 #
382 # Bug#52605 - "Adding LIMIT 1 clause to query with complex range
383 # predicate causes wrong results"
384 #
385 CREATE TABLE t1 (
386 pk INT NOT NULL,
387 c1 INT,
388 PRIMARY KEY (pk),
389 KEY k1 (c1)
390 );
391 INSERT INTO t1 VALUES (1,NULL);
392 INSERT INTO t1 VALUES (2,6);
393 INSERT INTO t1 VALUES (3,NULL);
394 INSERT INTO t1 VALUES (4,6);
395 INSERT INTO t1 VALUES (5,NULL);
396 INSERT INTO t1 VALUES (6,NULL);
397 INSERT INTO t1 VALUES (7,9);
398 INSERT INTO t1 VALUES (8,0);
399 SELECT pk, c1
400 FROM t1
401 WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
402 ORDER BY c1
403 LIMIT 1;
404 pk c1
405 4 6
406 EXPLAIN SELECT pk, c1
407 FROM t1
408 WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
409 ORDER BY c1
410 LIMIT 1;
411 id select_type table type possible_keys key key_len ref rows Extra
412 1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using index condition; Using where
413 DROP TABLE t1;
414 #
415 #
416 # Bug#59259 "Incorrect rows returned for a correlated subquery
417 # when ICP is on"
418 #
419 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
420 INSERT INTO t1 VALUES (11,0);
421 INSERT INTO t1 VALUES (12,5);
422 INSERT INTO t1 VALUES (15,0);
423 CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
424 INSERT INTO t2 VALUES (11,1);
425 INSERT INTO t2 VALUES (12,2);
426 INSERT INTO t2 VALUES (15,4);
427 analyze table t1,t2 persistent for all;
428 Table Op Msg_type Msg_text
429 test.t1 analyze status Engine-independent statistics collected
430 test.t1 analyze status OK
431 test.t2 analyze status Engine-independent statistics collected
432 test.t2 analyze status OK
433 set @save_optimizer_switch= @@optimizer_switch;
434 set optimizer_switch='semijoin=off';
435 EXPLAIN
436 SELECT * FROM t1
437 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
438 id select_type table type possible_keys key key_len ref rows Extra
439 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
440 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func # Using index condition
441 2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL # Using index; Using join buffer (flat, BNL join)
442 SELECT * FROM t1
443 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
444 pk i
445 12 5
446 set optimizer_switch=@save_optimizer_switch;
447 DROP TABLE t1, t2;
448 #
449 # Bug #58816 "Extra temporary duplicate rows in result set when
450 # switching ICP off"
451 #
452 set @save_optimizer_switch= @@optimizer_switch;
453 CREATE TABLE t1 (
454 pk INT NOT NULL,
455 c1 INT NOT NULL,
456 PRIMARY KEY (pk)
457 );
458 INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
459 insert into t1 select seq,seq from seq_100_to_110;
460 EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
461 id select_type table type possible_keys key key_len ref rows Extra
462 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 16 Using index condition; Rowid-ordered scan
463 SET SESSION optimizer_switch='index_condition_pushdown=off';
464 SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
465 pk c1
466 1 9
467 2 7
468 4 3
469 5 1
470 100 100
471 101 101
472 102 102
473 103 103
474 104 104
475 105 105
476 106 106
477 107 107
478 108 108
479 109 109
480 110 110
481 DROP TABLE t1;
482 set optimizer_switch= @save_optimizer_switch;
483 #
484 # Bug#58837: ICP crash or valgrind error due to uninitialized
485 # value in innobase_index_cond
486 #
487 CREATE TABLE t1 (
488 t1_int INT,
489 t1_time TIME
490 );
491 CREATE TABLE t2 (
492 t2_int int PRIMARY KEY,
493 t2_int2 INT
494 );
495 INSERT IGNORE INTO t2 VALUES ();
496 INSERT INTO t1 VALUES ();
497 SELECT * FROM t1 AS t1a
498 WHERE NOT EXISTS (SELECT * FROM t1 AS t1b
499 WHERE t1b.t1_int NOT IN
500 (SELECT t2.t2_int FROM t2
501 WHERE t1b.t1_time LIKE t1b.t1_int
502 OR t1b.t1_time <> t2.t2_int2
503 AND 6=7));
504 t1_int t1_time
505 DROP TABLE t1,t2;
506 #
507 # Bug#59186: Wrong results of join when ICP is enabled
508 # (fixed by the patch for LP bug #694092)
509 #
510 CREATE TABLE t1 (
511 pk INTEGER NOT NULL,
512 c1 VARCHAR(3) NOT NULL,
513 PRIMARY KEY (pk)
514 );
515 INSERT INTO t1 VALUES (1,'y'),(0,'or');
516 CREATE TABLE t2 (
517 pk INTEGER NOT NULL,
518 c1 VARCHAR(3) NOT NULL,
519 c2 VARCHAR(6) NOT NULL,
520 PRIMARY KEY (pk)
521 );
522 INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
523 EXPLAIN
524 SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
525 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
526 (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
527 id select_type table type possible_keys key key_len ref rows Extra
528 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
529 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
530 SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
531 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
532 (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
533 c2
534 DROP TABLE t1, t2;
535 #
536 # Bug#58838: "Wrong results with HAVING + LIMIT without GROUP BY when
537 # ICP is enabled".
538 # (Fixed by the patches for LP bugs #668644, #702322)
539 #
540 CREATE TABLE t1 (
541 pk INT NOT NULL,
542 c1 INT,
543 PRIMARY KEY (pk),
544 KEY col_int_key (c1)
545 );
546 INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55);
547 SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0;
548 pk
549 SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1;
550 pk
551 3
552 SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2;
553 pk
554 3
555 SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5;
556 pk
557 3
558 DROP TABLE t1;
559 #
560 # Bug#59483 "Crash on INSERT/REPLACE in
561 # rec_convert_dtuple_to_rec_comp with ICP on"
562 #
563 CREATE TABLE t1 (
564 pk INTEGER AUTO_INCREMENT PRIMARY KEY,
565 i1 INTEGER,
566 c1 CHAR(6),
567 i2 INTEGER NOT NULL,
568 KEY (i2)
569 );
570 INSERT INTO t1 VALUES
571 (NULL, 4, 'that', 8),
572 (NULL, 1, 'she', 6),
573 (NULL, 6, 'tell', 2);
574 SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE;
575 pk i1 c1 i2
576 2 1 she 6
577 INSERT INTO t1 (i2) VALUES (1);
578 DROP TABLE t1;
579 #
580 # Bug #11766678 - 59843:
581 # USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY
582 #
583 CREATE TABLE t1 (
584 col999 FLOAT NOT NULL,
585 COL1000 VARBINARY(179) NOT NULL,
586 col1003 DATE DEFAULT NULL,
587 KEY idx4267 (col1000, col1003)
588 );
589 INSERT IGNORE INTO t1 VALUES (),();
590 Warnings:
591 Warning 1364 Field 'col999' doesn't have a default value
592 Warning 1364 Field 'COL1000' doesn't have a default value
593 SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate();
594 col999
595 DROP TABLE t1;
596 #
597 # BUG#12822678 - ICP WITH STRAIGHT_JOIN
598 #
599 CREATE TABLE t1 (
600 i1 INTEGER NOT NULL,
601 d1 DOUBLE,
602 KEY k1 (d1)
603 );
604 INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
605 CREATE TABLE t2 (
606 pk INTEGER NOT NULL,
607 i1 INTEGER NOT NULL,
608 PRIMARY KEY (pk)
609 );
610 INSERT INTO t2 VALUES (4,1);
611 ANALYZE TABLE t1,t2;
612 Table Op Msg_type Msg_text
613 test.t1 analyze status Engine-independent statistics collected
614 test.t1 analyze status OK
615 test.t2 analyze status Engine-independent statistics collected
616 test.t2 analyze status OK
617 EXPLAIN
618 SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
619 WHERE t2.pk <> t1.d1 AND t2.pk = 4;
620 id select_type table type possible_keys key key_len ref rows Extra
621 1 SIMPLE t1 index NULL k1 9 NULL 3 Using index
622 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
623 SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
624 WHERE t2.pk <> t1.d1 AND t2.pk = 4;
625 d1 pk i1
626 1 4 1
627 EXPLAIN
628 SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
629 WHERE t1.d1 <> t2.pk AND t2.pk = 4;
630 id select_type table type possible_keys key key_len ref rows Extra
631 1 SIMPLE t1 index NULL k1 9 NULL 3 Using index
632 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
633 SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
634 WHERE t1.d1 <> t2.pk AND t2.pk = 4;
635 d1 pk i1
636 1 4 1
637 DROP TABLE t1, t2;
638 #
639 # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
640 #
641 CREATE TABLE t1 ( f11 int) ;
642 INSERT IGNORE INTO t1 VALUES (0);
643 CREATE TABLE t2 ( f10 int) ;
644 INSERT IGNORE INTO t2 VALUES (0);
645 CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
646 INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
647 CREATE TABLE t4 ( f11 int) ;
648 INSERT IGNORE INTO t4 VALUES
649 (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
650 (0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
651 set @tmp_778434=@@optimizer_switch;
652 SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
653 SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
654 WHERE (6, 234) IN (
655 SELECT t3.f1, t3.f1
656 FROM t3 JOIN t4 ON t4.f11 = t3.f10
657 );
658 f11 f10
659 DROP TABLE t1,t2,t3,t4;
660 set optimizer_switch= @tmp_778434;
661 #
662 # Bug#885168: ICP for one index + potential ORDER BY for another
663 #
664 CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) CHARSET=latin1;
665 INSERT INTO t1 VALUES
666 ('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'),
667 ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei');
668 SET SESSION optimizer_switch='index_condition_pushdown=off';
669 EXPLAIN
670 SELECT * FROM t1
671 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
672 ORDER BY a;
673 id select_type table type possible_keys key key_len ref rows Extra
674 1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort
675 SELECT * FROM t1
676 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
677 ORDER BY a;
678 a b
679 d xdmbdkpjda
680 SET SESSION optimizer_switch='index_condition_pushdown=on';
681 EXPLAIN
682 SELECT * FROM t1
683 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
684 ORDER BY a;
685 id select_type table type possible_keys key key_len ref rows Extra
686 1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort
687 SELECT * FROM t1
688 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
689 ORDER BY a;
690 a b
691 d xdmbdkpjda
692 DROP TABLE t1;
693 #
694 # Bug#886145: join with ICP + ORDER BY
695 #
696 CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)) CHARSET=latin1;
697 INSERT INTO t1 VALUES (1,4,'Ill');
698 CREATE TABLE t2 (a varchar(1024), KEY (a(512))) CHARSET=latin1;
699 INSERT INTO t2 VALUES
700 ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w');
701 insert into t2 select seq from seq_1_to_100;
702 analyze table t1,t2 persistent for all;
703 Table Op Msg_type Msg_text
704 test.t1 analyze status Engine-independent statistics collected
705 test.t1 analyze status OK
706 test.t2 analyze status Engine-independent statistics collected
707 test.t2 analyze status OK
708 SET SESSION optimizer_switch='index_condition_pushdown=off';
709 EXPLAIN
710 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
711 HAVING t1.c != 5 ORDER BY t1.c;
712 id select_type table type possible_keys key key_len ref rows Extra
713 1 SIMPLE t1 system PRIMARY NULL NULL NULL #
714 1 SIMPLE t2 ref a a 515 const # Using where
715 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
716 HAVING t1.c != 5 ORDER BY t1.c;
717 b c
718 1 4
719 SET SESSION optimizer_switch='index_condition_pushdown=on';
720 EXPLAIN
721 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
722 HAVING t1.c != 5 ORDER BY t1.c;
723 id select_type table type possible_keys key key_len ref rows Extra
724 1 SIMPLE t1 system PRIMARY NULL NULL NULL #
725 1 SIMPLE t2 ref a a 515 const # Using where
726 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
727 HAVING t1.c != 5 ORDER BY t1.c;
728 b c
729 1 4
730 DROP TABLE t1,t2;
731 #
732 # Bug#879871: InnoDB: possible ICP + GROUP BY primary index
733 #
734 CREATE TABLE t1 (
735 a int NOT NULL, b int, c varchar(1), d varchar(1),
736 PRIMARY KEY (a), KEY c (c,b)
737 );
738 INSERT INTO t1 VALUES (10,8,'g','g');
739 SET SESSION optimizer_switch='index_condition_pushdown=off';
740 SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
741 a
742 SET SESSION optimizer_switch='index_condition_pushdown=on';
743 SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
744 a
745 DROP TABLE t1;
746 #
747 # Bug#904832: an attempt to perform an index condition pushdown
748 # of a condition containing a subquery
749 #
750 CREATE TABLE t1 (
751 a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
752 b INT, c INT, d DATE NOT NULL, e VARCHAR(1),
753 KEY (c), KEY (d), KEY k2(b), KEY k3(b), KEY k4(b)
754 );
755 Warnings:
756 Note 1831 Duplicate index `k3`. This is deprecated and will be disallowed in a future release
757 Note 1831 Duplicate index `k4`. This is deprecated and will be disallowed in a future release
758 INSERT INTO t1 (b,c,d,e) VALUES
759 (6,5,'2006-05-25','y'),(1,5,'2008-01-23','t'),
760 (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
761 (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
762 (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
763 (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
764 (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
765 (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
766 (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
767 (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
768 (1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
769 (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
770 (4,5,'2006-03-09','b'),(4,5,'2001-06-05','f'),
771 (4,5,'2001-06-05','x'),(8,5,'1900-01-01','m'),
772 (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
773 (1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
774 (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
775 (4,5,'2006-03-09','b'),(6,5,'2007-06-18','d'),
776 (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
777 (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
778 (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
779 (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
780 (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
781 (1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
782 (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
783 (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
784 (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
785 (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
786 (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
787 (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
788 (6,5,'2007-06-18','d'),(4,1,'1900-01-01','r'),
789 (8,8,'1900-01-01','m'),(4,1,'2006-03-09','b'),
790 (4,1,'2001-06-05','x'),(7,1,'2006-05-28','g');
791 CREATE TABLE t2 (
792 f INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
793 g INT,
794 h VARCHAR(1),
795 KEY (g)
796 );
797 INSERT INTO t2 (g,h) VALUES
798 (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
799 (5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),
800 (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),
801 (6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),
802 (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
803 (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
804 (7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
805 (9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
806 (0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
807 (1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
808 (6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),(0,'p'),
809 (0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
810 (1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
811 (8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
812 (5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
813 (1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),
814 (0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
815 (1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(7,'d'),
816 (7,'f'),(5,'j'),(3,'e'),(1,'u'),(0,'f'),(0,'p'),
817 (7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
818 (9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
819 (0,'f'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
820 (5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
821 (1,'x'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),
822 (4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),
823 (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
824 (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
825 (5,'l'),(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),
826 (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
827 (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
828 (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u');
829 ANALYZE TABLE t1,t2;
830 Table Op Msg_type Msg_text
831 test.t1 analyze status Engine-independent statistics collected
832 test.t1 analyze status OK
833 test.t2 analyze status Engine-independent statistics collected
834 test.t2 analyze status Table is already up to date
835 SET @save_optimize_switch=@@optimizer_switch;
836 SET optimizer_switch='materialization=on';
837 set @save_optimizer_where_cost=@@optimizer_where_cost;
838 set @@optimizer_where_cost=1;
839 EXPLAIN
840 SELECT COUNT(*) FROM t1 AS t, t2
841 WHERE c = g
842 AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
843 OR a = 0 AND h < 'z' );
844 id select_type table type possible_keys key key_len ref rows Extra
845 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where
846 1 PRIMARY t2 ref g g 5 test.t.c 18 Using where
847 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index
848 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
849 SELECT COUNT(*) FROM t1 AS t, t2
850 WHERE c = g
851 AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
852 OR a = 0 AND h < 'z' );
853 COUNT(*)
854 1478
855 SELECT COUNT(*) FROM t1 AS t, t2
856 WHERE c = g
857 AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
858 OR a = 0 AND h < 'z' );
859 COUNT(*)
860 1478
861 SET optimizer_switch=@save_optimizer_switch;
862 set @@optimizer_where_cost=@save_optimizer_where_cost;
863 DROP TABLE t1,t2;
864 # check "Handler_pushed" status varuiables
865 CREATE TABLE t1 (
866 c1 CHAR(1),
867 c2 CHAR(1),
868 KEY (c1)
869 );
870 INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5');
871 flush status;
872 show status like "Handler_icp%";
873 Variable_name Value
874 Handler_icp_attempts 0
875 Handler_icp_match 0
876 SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ;
877 c1 c2
878 4 4
879 show status like "Handler_icp%";
880 Variable_name Value
881 Handler_icp_attempts 2
882 Handler_icp_match 1
883 SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ;
884 c1 c2
885 4 4
886 show status like "Handler_icp%";
887 Variable_name Value
888 Handler_icp_attempts 2
889 Handler_icp_match 1
890 DROP TABLE t1;
891 create table t1 (a int,b char(5),primary key (a), key (b(1)));
892 insert ignore into t1 values ('a','b');
893 Warnings:
894 Warning 1366 Incorrect integer value: 'a' for column `test`.`t1`.`a` at row 1
895 select 1 from t1 where a and b >= 'aa';
896 1
897 drop table t1;
898 #
899 # Bug#58015 "Assert in row_sel_field_store_in_mysql_format
900 # when running innodb_mrr_icp test"
901 #
902 create table t1 (a char(2) charset utf8,b double, primary key (a(1)),key (b));
903 insert into t1 values ('',1);
904 select 1 from t1 where b <= 1 and a <> '';
905 1
906 drop table t1;
907 #
908 # MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column
909 #
910 CREATE TABLE t1 (
911 c1 TEXT ,
912 c2 VARCHAR(2) ,
913 INDEX idx1 (c2,c1(2)),
914 INDEX idx2 (c2,c1(1))
915 ) CHARSET=latin1;
916 INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y');
917 SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
918 c1 c2
919 aa x
920 a y
921 EXPLAIN
922 SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
923 id select_type table type possible_keys key key_len ref rows Extra
924 1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where
925 SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
926 c1 c2
927 aa x
928 a y
929 SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
930 c1 c2
931 aa x
932 a y
933 DROP TABLE t1;
934 set default_storage_engine= @save_storage_engine;
935 set optimizer_switch=@maria_icp_tmp;