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 
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 
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);
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);
insert into t1 select seq,seq from seq_100_to_110;
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	16	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
100	100
101	101
102	102
103	103
104	104
105	105
106	106
107	107
108	108
109	109
110	110
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');
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;
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	system	PRIMARY	NULL	NULL	NULL	#	
1	SIMPLE	t2	ref	a	a	515	const	#	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	system	PRIMARY	NULL	NULL	NULL	#	
1	SIMPLE	t2	ref	a	a	515	const	#	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';
set @save_optimizer_where_cost=@@optimizer_where_cost;
set @@optimizer_where_cost=1;
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;
set @@optimizer_where_cost=@save_optimizer_where_cost;
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'), (6,'a','b'), (7,'a','b');
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;