set @save_storage_engine= @@default_storage_engine; set default_storage_engine=InnoDB; set @innodb_stats_persistent_save= @@innodb_stats_persistent; set @innodb_stats_persistent_sample_pages_save= @@innodb_stats_persistent_sample_pages; set global innodb_stats_persistent= 1; set global innodb_stats_persistent_sample_pages=100; set @innodb_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 index condition; 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 PRIMARY,k1 k1 5 NULL 4 Using where; Using index 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 where 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 range PRIMARY PRIMARY 4 NULL 4 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 where 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; 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 range PRIMARY PRIMARY 4 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 range PRIMARY PRIMARY 4 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 OK 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 d 3 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 index condition; 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; # # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 # CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES (7),(7); CREATE TABLE t2 ( b VARCHAR(1) ); INSERT INTO t2 VALUES ('j'),('v'); CREATE TABLE t3 ( c INT, d VARCHAR(1), e VARCHAR(1), KEY (d,c) ) ENGINE=InnoDB; INSERT INTO t3 VALUES (6,'w','w'), (1,'v','v'),(7,'s','s'),(4,'l','l'), (7,'y','y'),(0,'c','c'),(2,'i','i'), (9,'h','h'),(4,'q','q'),(0,'a','a'), (9,'v','v'),(1,'u','u'),(3,'s','s'), (8,'z','z'),(1,'h','h'),(8,'p','p'), (6,'e','e'),(3,'i','i'),(6,'y','y'); SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 ON d = b WHERE NOT EXISTS ( SELECT * FROM t3 ) OR a = c ORDER BY e; a b c d e DROP TABLE t1,t2,t3; # # MDEV-5337: Wrong result in mariadb 5.5.32 with ORDER BY + LIMIT when index_condition_pushdown=on # MDEV-5512: Wrong result (WHERE clause ignored) with multiple clauses using Percona-XtraDB engine # create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (pk int primary key, key1 char(32), key2 char(32), key(key1), key(key2) ) engine=innodb; insert into t2 select A.a+10*B.a+100*C.a, concat('rare-', A.a+10*B.a), concat('rare-', A.a+10*B.a) from t1 A, t1 B, t1 C; update t2 set key1='frequent-val' where pk between 100 and 350; select * from t2 ignore key(PRIMARY) where key1='frequent-val' and key2 between 'rare-400' and 'rare-450' order by pk limit 2; pk key1 key2 141 frequent-val rare-41 142 frequent-val rare-42 drop table t1, t2; set optimizer_switch=@innodb_icp_tmp; set default_storage_engine= @save_storage_engine; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save;