Merge.

1 files changed, 236 insertions, 0 deletions

diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 6df834168fa..32bbccb5e80 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -83,6 +83,150 @@ c1	c2	c3	c4
 
 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	2	Using index condition
+
+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 (
@@ -203,6 +347,98 @@ COUNT(*)
 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	3	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) ENGINE=InnoDB;
+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) ENGINE=InnoDB;
+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	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	PRIMARY	4	NULL	3	Using index
+2	DEPENDENT SUBQUERY	it	eq_ref	PRIMARY	PRIMARY	4	func	1	Using index condition
+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#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
 #
 CREATE TABLE t1 ( f11 int) ;