set @myisam_icp_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; # # Bug#36981 - "innodb crash when selecting for update" # CREATE TABLE t1 ( c1 CHAR(1), c2 CHAR(10), KEY (c1) ); INSERT INTO t1 VALUES ('3', null); SELECT * FROM t1 WHERE c1='3' FOR UPDATE; c1 c2 3 NULL DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t2 (a INT); INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C; CREATE TABLE t3 ( c1 CHAR(10) NOT NULL, c2 CHAR(10) NOT NULL, c3 CHAR(200) NOT NULL, KEY (c1) ); INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler' FROM t2; INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1' FROM t2; INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2' FROM t2; SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE; c1 c3 c-1994=w filler c-1994=w filler-1 c-1994=w filler-2 c-1995=w filler c-1995=w filler-1 c-1995=w filler-2 c-1997=w filler c-1997=w filler-1 c-1997=w filler-2 c-1998=w filler c-1998=w filler-1 c-1998=w filler-2 c-1999=w filler c-1999=w filler-1 c-1999=w filler-2 DROP TABLE t1,t2,t3; # # Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for # null-safe operator <=> NULL # CREATE TABLE t1( c1 DATE NOT NULL, c2 DATE NULL, c3 DATETIME, c4 TIMESTAMP, PRIMARY KEY(c1), UNIQUE(c2) ); INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05'); INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26'); INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03'); INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29'); INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29'); SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2; c1 c2 c3 c4 2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00 2008-01-17 NULL NULL 2009-01-29 00:00:00 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00 2008-01-17 NULL NULL 2009-01-29 00:00:00 DROP TABLE t1; # # Bug#43617 - Innodb returns wrong results with timestamp's range value # in IN clause # (Note: Fixed by patch for BUG#42580) # CREATE TABLE t1( c1 TIMESTAMP NOT NULL, c2 TIMESTAMP NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1), UNIQUE INDEX(c2) ); INSERT INTO t1 VALUES ('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'), ('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'), ('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL), ('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'), ('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'), ('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'), ('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'), ('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00'); SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2; c1 c2 c3 c4 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 LIMIT 2; c1 c2 c3 c4 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 DESC; c1 c2 c3 c4 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 DESC LIMIT 2; c1 c2 c3 c4 2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 DROP TABLE t1; # # BUG#43618: MyISAM&Maria returns wrong results with 'between' # on timestamp # CREATE TABLE t1( ts TIMESTAMP NOT NULL, c char NULL, PRIMARY KEY(ts) ); INSERT INTO t1 VALUES ('1971-01-01','a'), ('2007-05-25','b'), ('2008-01-01','c'), ('2038-01-09','d'); # Execute select with invalid timestamp, desc ordering SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; ts c 2008-01-01 00:00:00 c 2007-05-25 00:00:00 b # Should use index condition EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where DROP TABLE t1; # # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc # (Note: Fixed by patch for LP BUG#625841) # CREATE TABLE t1 ( f1 VARCHAR(1024), f2 VARCHAR(10), INDEX test_idx USING BTREE (f2,f1(5)) ); INSERT INTO t1 VALUES ('a','c'), ('b','d'); SELECT f1 FROM t1 WHERE f2 LIKE 'd' ORDER BY f1; f1 b DROP TABLE t1; # # Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on # an index containing TEXT" # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t2 (a INT); INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; CREATE TABLE t3 ( c1 TINYTEXT NOT NULL, i1 INT NOT NULL, KEY (c1(6),i1) ); INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c1 c1 8 NULL 3 Using where SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; c1 c-1004=w c-1005=w c-1006=w EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c1 c1 12 NULL 2 Using where SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; c1 EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL c1 NULL NULL NULL 100 Using where SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; c1 c-1004=w c-1005=w c-1006=w DROP TABLE t1, t2, t3; # # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on # CREATE TABLE t ( dummy INT PRIMARY KEY, a INT UNIQUE, b INT ); INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5); SELECT * FROM t WHERE a > 2 FOR UPDATE; dummy a b 3 3 3 5 5 5 DROP TABLE t; # # Bug#35080 - Innodb crash at mem_block_get_len line 72 # CREATE TABLE t1 ( t1_autoinc INT(11) NOT NULL AUTO_INCREMENT, uuid VARCHAR(36) DEFAULT NULL, PRIMARY KEY (t1_autoinc), KEY k (uuid) ); CREATE TABLE t2 ( t2_autoinc INT(11) NOT NULL AUTO_INCREMENT, uuid VARCHAR(36) DEFAULT NULL, date DATETIME DEFAULT NULL, PRIMARY KEY (t2_autoinc), KEY k (uuid) ); CREATE VIEW v1 AS SELECT t1_autoinc, uuid FROM t1 WHERE (ISNULL(uuid) OR (uuid like '%-%')); CREATE VIEW v2 AS SELECT t2_autoinc, uuid, date FROM t2 WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36)); CREATE PROCEDURE delete_multi (IN uuid CHAR(36)) DELETE v1, v2 FROM v1 INNER JOIN v2 ON v1.uuid = v2.uuid WHERE v1.uuid = @uuid; SET @uuid = UUID(); INSERT INTO v1 (uuid) VALUES (@uuid); INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09'); CALL delete_multi(@uuid); DROP procedure delete_multi; DROP table t1,t2; DROP view v1,v2; # # Bug#41996 - multi-table delete crashes server (InnoDB table) # CREATE TABLE t1 ( b BIGINT, i INT, KEY (b) ); INSERT INTO t1 VALUES (2, 2); DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b; DROP TABLE t1; # # Bug#43448 - Server crashes on multi table delete with Innodb # CREATE TABLE t1 ( id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, t CHAR(12) ); CREATE TABLE t2 ( id2 INT NOT NULL, t CHAR(12) ); CREATE TABLE t3( id3 INT NOT NULL, t CHAR(12), INDEX(id3) ); CREATE PROCEDURE insert_data () BEGIN DECLARE i1 INT DEFAULT 20; DECLARE i2 INT; DECLARE i3 INT; WHILE (i1 > 0) DO INSERT INTO t1(t) VALUES (i1); SET i2 = 2; WHILE (i2 > 0) DO INSERT INTO t2(id2, t) VALUES (i1, i2); SET i3 = 2; WHILE (i3 > 0) DO INSERT INTO t3(id3, t) VALUES (i1, i2); SET i3 = i3 -1; END WHILE; SET i2 = i2 -1; END WHILE; SET i1 = i1 - 1; END WHILE; END | CALL insert_data(); SELECT COUNT(*) FROM t1 WHERE id1 > 10; COUNT(*) 10 SELECT COUNT(*) FROM t2 WHERE id2 > 10; COUNT(*) 20 SELECT COUNT(*) FROM t3 WHERE id3 > 10; COUNT(*) 40 DELETE t1, t2, t3 FROM t1, t2, t3 WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3; SELECT COUNT(*) FROM t1; COUNT(*) 3 SELECT COUNT(*) FROM t2; COUNT(*) 6 SELECT COUNT(*) FROM t3; COUNT(*) 12 DROP PROCEDURE insert_data; DROP TABLE t1, t2, t3; # # Bug#57372 "Multi-table updates and deletes fail when running with ICP # against InnoDB" # CREATE TABLE t1 ( a INT KEY, b INT ); CREATE TABLE t2 ( a INT KEY, b INT ); INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10 WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; SELECT * FROM t1; a b 1 101 102 102 103 103 104 104 5 105 SELECT * FROM t2; a b 1 1 2 12 3 13 4 14 5 5 DROP TABLE t1, t2; # # Bug#52605 - "Adding LIMIT 1 clause to query with complex range # predicate causes wrong results" # CREATE TABLE t1 ( pk INT NOT NULL, c1 INT, PRIMARY KEY (pk), KEY k1 (c1) ); INSERT INTO t1 VALUES (1,NULL); INSERT INTO t1 VALUES (2,6); INSERT INTO t1 VALUES (3,NULL); INSERT INTO t1 VALUES (4,6); INSERT INTO t1 VALUES (5,NULL); INSERT INTO t1 VALUES (6,NULL); INSERT INTO t1 VALUES (7,9); INSERT INTO t1 VALUES (8,0); SELECT pk, c1 FROM t1 WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; pk c1 4 6 EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter DROP TABLE t1; # # # Bug#59259 "Incorrect rows returned for a correlated subquery # when ICP is on" # CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL); INSERT INTO t1 VALUES (11,0); INSERT INTO t1 VALUES (12,5); INSERT INTO t1 VALUES (15,0); CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL); INSERT INTO t2 VALUES (11,1); INSERT INTO t2 VALUES (12,2); INSERT INTO t2 VALUES (15,4); analyze table t1,t2 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK set @save_optimizer_switch= @@optimizer_switch; set optimizer_switch='semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func # Using index condition 2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL # Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i 12 5 set optimizer_switch=@save_optimizer_switch; DROP TABLE t1, t2; # # Bug #58816 "Extra temporary duplicate rows in result set when # switching ICP off" # set @save_optimizer_switch= @@optimizer_switch; CREATE TABLE t1 ( pk INT NOT NULL, c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 4 3 5 1 DROP TABLE t1; set optimizer_switch= @save_optimizer_switch; # # Bug#58837: ICP crash or valgrind error due to uninitialized # value in innobase_index_cond # CREATE TABLE t1 ( t1_int INT, t1_time TIME ); CREATE TABLE t2 ( t2_int int PRIMARY KEY, t2_int2 INT ); INSERT IGNORE INTO t2 VALUES (); INSERT INTO t1 VALUES (); SELECT * FROM t1 AS t1a WHERE NOT EXISTS (SELECT * FROM t1 AS t1b WHERE t1b.t1_int NOT IN (SELECT t2.t2_int FROM t2 WHERE t1b.t1_time LIKE t1b.t1_int OR t1b.t1_time <> t2.t2_int2 AND 6=7)); t1_int t1_time DROP TABLE t1,t2; # # Bug#59186: Wrong results of join when ICP is enabled # (fixed by the patch for LP bug #694092) # CREATE TABLE t1 ( pk INTEGER NOT NULL, c1 VARCHAR(3) NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,'y'),(0,'or'); CREATE TABLE t2 ( pk INTEGER NOT NULL, c1 VARCHAR(3) NOT NULL, c2 VARCHAR(6) NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); c2 DROP TABLE t1, t2; # # Bug#58838: "Wrong results with HAVING + LIMIT without GROUP BY when # ICP is enabled". # (Fixed by the patches for LP bugs #668644, #702322) # CREATE TABLE t1 ( pk INT NOT NULL, c1 INT, PRIMARY KEY (pk), KEY col_int_key (c1) ); INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; pk SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; pk 3 SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; pk 3 SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; pk 3 DROP TABLE t1; # # Bug#59483 "Crash on INSERT/REPLACE in # rec_convert_dtuple_to_rec_comp with ICP on" # CREATE TABLE t1 ( pk INTEGER AUTO_INCREMENT PRIMARY KEY, i1 INTEGER, c1 CHAR(6), i2 INTEGER NOT NULL, KEY (i2) ); INSERT INTO t1 VALUES (NULL, 4, 'that', 8), (NULL, 1, 'she', 6), (NULL, 6, 'tell', 2); SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; pk i1 c1 i2 2 1 she 6 INSERT INTO t1 (i2) VALUES (1); DROP TABLE t1; # # Bug #11766678 - 59843: # USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY # CREATE TABLE t1 ( col999 FLOAT NOT NULL, COL1000 VARBINARY(179) NOT NULL, col1003 DATE DEFAULT NULL, KEY idx4267 (col1000, col1003) ); INSERT IGNORE INTO t1 VALUES (),(); Warnings: Warning 1364 Field 'col999' doesn't have a default value Warning 1364 Field 'COL1000' doesn't have a default value SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); col999 DROP TABLE t1; # # BUG#12822678 - ICP WITH STRAIGHT_JOIN # CREATE TABLE t1 ( i1 INTEGER NOT NULL, d1 DOUBLE, KEY k1 (d1) ); INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); CREATE TABLE t2 ( pk INTEGER NOT NULL, i1 INTEGER NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t2 VALUES (4,1); ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK EXPLAIN SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t2.pk <> t1.d1 AND t2.pk = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL k1 9 NULL 3 Using index 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t2.pk <> t1.d1 AND t2.pk = 4; d1 pk i1 1 4 1 EXPLAIN SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t1.d1 <> t2.pk AND t2.pk = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL k1 9 NULL 3 Using index 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t1.d1 <> t2.pk AND t2.pk = 4; d1 pk i1 1 4 1 DROP TABLE t1, t2; # # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89 # CREATE TABLE t1 ( f11 int) ; INSERT IGNORE INTO t1 VALUES (0); CREATE TABLE t2 ( f10 int) ; INSERT IGNORE INTO t2 VALUES (0); CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ; INSERT IGNORE INTO t3 VALUES (6,0),(10,0); CREATE TABLE t4 ( f11 int) ; INSERT IGNORE INTO t4 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL), (0),(0),(0),(0),(0),(0),(0),(0),(0),(0); set @tmp_778434=@@optimizer_switch; SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off'; SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11 WHERE (6, 234) IN ( SELECT t3.f1, t3.f1 FROM t3 JOIN t4 ON t4.f11 = t3.f10 ); f11 f10 DROP TABLE t1,t2,t3,t4; set optimizer_switch= @tmp_778434; # # Bug#885168: ICP for one index + potential ORDER BY for another # CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ; INSERT INTO t1 VALUES ('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'), ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; a b d xdmbdkpjda SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; a b d xdmbdkpjda DROP TABLE t1; # # Bug#886145: join with ICP + ORDER BY # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); insert into t2 select seq from seq_1_to_100; analyze table t1,t2 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 DROP TABLE t1,t2; # # Bug#879871: InnoDB: possible ICP + GROUP BY primary index # CREATE TABLE t1 ( a int NOT NULL, b int, c varchar(1), d varchar(1), PRIMARY KEY (a), KEY c (c,b) ); INSERT INTO t1 VALUES (10,8,'g','g'); SET SESSION optimizer_switch='index_condition_pushdown=off'; SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1; a SET SESSION optimizer_switch='index_condition_pushdown=on'; SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1; a DROP TABLE t1; # # Bug#904832: an attempt to perform an index condition pushdown # of a condition containing a subquery # CREATE TABLE t1 ( a INT PRIMARY KEY NOT NULL AUTO_INCREMENT, b INT, c INT, d DATE NOT NULL, e VARCHAR(1), KEY (c), KEY (d), KEY k2(b), KEY k3(b), KEY k4(b) ); Warnings: Note 1831 Duplicate index `k3`. This is deprecated and will be disallowed in a future release Note 1831 Duplicate index `k4`. This is deprecated and will be disallowed in a future release INSERT INTO t1 (b,c,d,e) VALUES (6,5,'2006-05-25','y'),(1,5,'2008-01-23','t'), (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'), (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'), (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'), (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'), (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'), (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'), (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'), (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'), (1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'), (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'), (4,5,'2006-03-09','b'),(4,5,'2001-06-05','f'), (4,5,'2001-06-05','x'),(8,5,'1900-01-01','m'), (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'), (1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'), (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'), (4,5,'2006-03-09','b'),(6,5,'2007-06-18','d'), (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'), (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'), (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'), (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'), (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'), (1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'), (4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'), (4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'), (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'), (6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'), (8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'), (4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'), (6,5,'2007-06-18','d'),(4,1,'1900-01-01','r'), (8,8,'1900-01-01','m'),(4,1,'2006-03-09','b'), (4,1,'2001-06-05','x'),(7,1,'2006-05-28','g'); CREATE TABLE t2 ( f INT PRIMARY KEY NOT NULL AUTO_INCREMENT, g INT, h VARCHAR(1), KEY (g) ); INSERT INTO t2 (g,h) VALUES (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'), (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'), (6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'), (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'), (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'), (9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'), (0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'), (1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'), (6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),(0,'p'), (0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'), (1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'), (8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'), (5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'), (1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'), (0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'), (1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(7,'d'), (7,'f'),(5,'j'),(3,'e'),(1,'u'),(0,'f'),(0,'p'), (7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'), (9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'), (0,'f'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'), (5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'), (1,'x'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'), (4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'), (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'), (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (5,'l'),(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'), (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'), (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'); ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) OR a = 0 AND h < 'z' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 18 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) OR a = 0 AND h < 'z' ); COUNT(*) 1478 SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) OR a = 0 AND h < 'z' ); COUNT(*) 1478 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; # check "Handler_pushed" status varuiables CREATE TABLE t1 ( c1 CHAR(1), c2 CHAR(1), KEY (c1) ); INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5'); flush status; show status like "Handler_icp%"; Variable_name Value Handler_icp_attempts 0 Handler_icp_match 0 SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ; c1 c2 4 4 show status like "Handler_icp%"; Variable_name Value Handler_icp_attempts 2 Handler_icp_match 1 SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ; c1 c2 4 4 show status like "Handler_icp%"; Variable_name Value Handler_icp_attempts 2 Handler_icp_match 1 DROP TABLE t1; create table t1 (a int,b char(5),primary key (a), key (b(1))); insert ignore into t1 values ('a','b'); Warnings: Warning 1366 Incorrect integer value: 'a' for column `test`.`t1`.`a` at row 1 select 1 from t1 where a and b >= 'aa'; 1 drop table t1; # # Bug#58015 "Assert in row_sel_field_store_in_mysql_format # when running innodb_mrr_icp test" # create table t1 (a char(2) charset utf8,b double, primary key (a(1)),key (b)); insert into t1 values ('',1); select 1 from t1 where b <= 1 and a <> ''; 1 drop table t1; # # MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column # CREATE TABLE t1 ( c1 TEXT , c2 VARCHAR(2) , INDEX idx1 (c2,c1(2)), INDEX idx2 (c2,c1(1)) ); INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); c1 c2 aa x a y EXPLAIN SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); c1 c2 aa x a y SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); c1 c2 aa x a y DROP TABLE t1; drop table if exists t0, t1, t1i, t1m; # # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed # CREATE TABLE t1 ( a int, b varchar(1024), c int, KEY (c), KEY (c,a)) ; INSERT INTO t1 VALUES (NULL,'x','-678428672'), (NULL,'ok',NULL), (796262400,'byluovkgwoukfxedyeffsedajyqkyhpaqqpozn', NULL), (7,'STQUF',146014208), (955711488,'WWVOR','-1515388928'); SELECT b FROM t1 WHERE a != 1 AND c IS NULL ORDER BY 1; b byluovkgwoukfxedyeffsedajyqkyhpaqqpozn DROP TABLE t1; # # Bug#870046: ICP for a GROUP BY query # CREATE TABLE t1 (a int, b varchar(1), c varchar(1), INDEX idx(b)); INSERT INTO t1 VALUES (2,'x','x'), (5,'x','y'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx idx 4 const 2 Using where; Using temporary; Using filesort SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; a MIN(c) 5 y SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx idx 4 const 2 Using index condition; Using where; Using temporary; Using filesort SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; a MIN(c) 5 y DROP TABLE t1; # # BUG#887026: Wrong result with ICP, outer join, subquery in maria-5.3-icp # CREATE TABLE t1 (c varchar(1)); INSERT INTO t1 VALUES ('c'), ('c'); CREATE TABLE t2 (c varchar(1), b int); INSERT INTO t2 VALUES ('d', NULL),('d', NULL); CREATE TABLE t3 (c varchar(1)); INSERT INTO t3 VALUES ('c'); INSERT INTO t3 VALUES ('c'); CREATE TABLE t4 ( b int, c varchar(1), KEY (b)); INSERT INTO t4 VALUES (7,'c'); INSERT INTO t4 VALUES (7,'c'); SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; # Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition explain SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b WHERE t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t4 range b b 5 NULL 1 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b WHERE t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); c c b c NULL NULL c NULL NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'c' Warning 1292 Truncated incorrect DECIMAL value: 'c' Warning 1292 Truncated incorrect DECIMAL value: 'c' Warning 1292 Truncated incorrect DECIMAL value: 'c' SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3,t4; # # BUG#1000051: Query with simple join and ORDER BY takes thousands times longer when run with ICP # create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, col int, filler char(100), key(a, col)); insert into t1 select A.a + 10*B.a, 1234, 'filler' from t0 A, t0 B, t0 C; set @tmp_10000051= @@optimizer_switch; set optimizer_switch='mrr=off'; # Must not use ICP: explain select * from t1 where a between 5 and 8 order by a desc, col desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 40 Using where set optimizer_switch= @tmp_10000051; # Must not use ICP: explain select * from t1 where a=3 and col > 500 order by a desc, col desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 10 NULL 10 Using where drop table t0, t1; # # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE # create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int); insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t1 (key1 int not null, filler char(100)); insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B; alter table t1 add key(key1); explain select * from t1 where key1 < 3 or key1 > 99999; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 4 NULL 22 Using index condition; Rowid-ordered scan select * from t1 where key1 < 3 or key1 > 99999; key1 filler 0 filler-data 1 filler-data 2 filler-data drop table ten,one_k,t1; set optimizer_switch=@myisam_icp_tmp;