5.3 merge

18 files changed, 506 insertions, 15 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 176d39c50a9..86b95e94a81 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2097,6 +2097,24 @@ a	b
 drop table t1;
 set optimizer_switch=@save978847_optimizer_switch;
 #
+# LP bug998516 Server hangs on INSERT .. SELECT with derived_merge,
+# FROM subquery, UNION 
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2;
+select * from t1;
+a
+1
+2
+1
+2
+3
+4
+drop table t1,t2;
+#
 # end of 5.3 tests
 #
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result
index 0f50f913ab3..c067ff02574 100644
--- a/mysql-test/r/fulltext.result
+++ b/mysql-test/r/fulltext.result
@@ -692,8 +692,10 @@ PREPARE stmt FROM
  WHERE t1.f1 GROUP BY t1.f1))';
 EXECUTE stmt;
 1
+1
 EXECUTE stmt;
 1
+1
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 End of 5.1 tests
diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result
index 71f1c6faef0..00baa5f990a 100644
--- a/mysql-test/r/func_group_innodb.result
+++ b/mysql-test/r/func_group_innodb.result
@@ -146,6 +146,19 @@ count(*)	min(7)	max(7)
 0	NULL	NULL
 drop table t1m, t1i, t2m, t2i;
 #
+# Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN
+#
+CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT,
+KEY(b, c, a(765))) ENGINE=INNODB;
+INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
+SELECT MIN(c) FROM t1 GROUP BY b;
+MIN(c)
+0
+EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	b	263	NULL	4	Using index for group-by
+DROP TABLE t1;
+#
 # Bug #57954: BIT_AND function returns incorrect results when 
 #   semijoin=on
 CREATE TABLE c (
diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result
index 3d5ca1805b7..c7f548ae2bc 100644
--- a/mysql-test/r/func_if.result
+++ b/mysql-test/r/func_if.result
@@ -217,3 +217,20 @@ foo
 Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'a'
 Warning	1292	Truncated incorrect DOUBLE value: 'a'
+#
+# LP bug#998321 Simple query with IF expression causes an
+# assertion abort (see also mysql Bug#12620084)
+#
+SELECT if(0, (SELECT min('hello')), NULL);
+if(0, (SELECT min('hello')), NULL)
+NULL
+SELECT if(1, (SELECT min('hello')), NULL);
+if(1, (SELECT min('hello')), NULL)
+hello
+SELECT if(0, NULL, (SELECT min('hello')));
+if(0, NULL, (SELECT min('hello')))
+hello
+SELECT if(1, NULL, (SELECT min('hello')));
+if(1, NULL, (SELECT min('hello')))
+NULL
+End of 5.2 tests
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 74e9cd3caaa..222977e5106 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2021,8 +2021,88 @@ zzz
 3
 4
 8
-#TODO: in merge with 5.3 add original test suite
+SET @save_optimizer_switch967242=@@optimizer_switch;
+SET optimizer_switch = 'in_to_exists=on';
+SELECT t2_1.b
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+( SELECT COUNT(*) FROM t2 ) IS NOT NULL
+OR a = t2_1.c
+GROUP BY t2_1.b;
+b
+0
+3
+4
+8
+SET optimizer_switch=@save_optimizer_switch967242;
 drop table t1, t2;
+#
+# Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY 
+#               ROWS WHEN GROUP IS OPTIMIZED AWAY
+#
+CREATE TABLE t1 (col1 int, col2 int) ;
+INSERT INTO t1 VALUES (10,1),(11,7);
+CREATE TABLE t2 (col1 int, col2 int) ;
+INSERT INTO t2 VALUES (10,8);
+
+EXPLAIN SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+col2
+8
+
+EXPLAIN SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+col2
+8
+
+DROP TABLE t1,t2;
+#
+# BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A
+#               DIFFERENT QUERY OUTPUT
+#
+CREATE TABLE t1 (
+a int,
+b varchar(1),
+KEY (b,a)
+);
+INSERT INTO t1 VALUES (1,NULL),(0,'a');
+
+EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	b	b	9	NULL	2	Using where; Using index for group-by; Using temporary
+
+SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
+MIN(a)	b
+0	a
+
+EXPLAIN SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	b	b	9	NULL	2	Using where; Using index for group-by
+
+SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
+MIN(a)	b
+0	a
+
+DROP TABLE t1;
+#
+# LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0);
+SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0);
+1
+1
+SELECT max(1) FROM t1 WHERE a!=0;
+max(1)
+NULL
+drop table t1;
 # End of 5.2 tests
 #
 # lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index ebe8255fd8b..e5c788b0cc8 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1855,6 +1855,30 @@ f1	f1	f2
 DROP TABLE t1,t2;
 End of 5.1 tests
 #
+# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery
+# predicate in WHERE condition.
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+c	b
+3	NULL
+SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7);
+c	b
+3	NULL
+drop table t1,t2,t3;
+End of 5.2 tests
+#
 # LP bug #813447: LEFT JOIN with single-row inner table and
 #                 a subquery in ON expression
 #
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 3d0e4cbfc79..d93b16b5c4c 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1866,6 +1866,30 @@ f1	f1	f2
 DROP TABLE t1,t2;
 End of 5.1 tests
 #
+# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery
+# predicate in WHERE condition.
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+c	b
+3	NULL
+SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7);
+c	b
+3	NULL
+drop table t1,t2,t3;
+End of 5.2 tests
+#
 # LP bug #813447: LEFT JOIN with single-row inner table and
 #                 a subquery in ON expression
 #
diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result
index f438a3884eb..1a6f8534cc2 100644
--- a/mysql-test/r/log_state.result
+++ b/mysql-test/r/log_state.result
@@ -320,22 +320,22 @@ TRUNCATE TABLE mysql.slow_log;
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (b INT, PRIMARY KEY (b));
 INSERT INTO t2 VALUES (3),(4);
-INSERT INTO t1 VALUES (1+sleep(.01)),(2);
-INSERT INTO t1 SELECT b+sleep(.01) from t2;
-UPDATE t1 SET a=a+sleep(.01) WHERE a>2;
-UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC;
-UPDATE t2 set b=b+sleep(.01) limit 1;
-UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2);
-DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2;
+INSERT INTO t1 VALUES (1+sleep(.02)),(2);
+INSERT INTO t1 SELECT b+sleep(.02) from t2;
+UPDATE t1 SET a=a+sleep(.02) WHERE a>2;
+UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC;
+UPDATE t2 set b=b+sleep(.02) limit 1;
+UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2);
+DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2;
 SELECT rows_examined,sql_text FROM mysql.slow_log;
 rows_examined	sql_text
-0	INSERT INTO t1 VALUES (1+sleep(.01)),(2)
-2	INSERT INTO t1 SELECT b+sleep(.01) from t2
-4	UPDATE t1 SET a=a+sleep(.01) WHERE a>2
-8	UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC
-1	UPDATE t2 set b=b+sleep(.01) limit 1
-4	UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2)
-6	DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2
+0	INSERT INTO t1 VALUES (1+sleep(.02)),(2)
+2	INSERT INTO t1 SELECT b+sleep(.02) from t2
+4	UPDATE t1 SET a=a+sleep(.02) WHERE a>2
+8	UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC
+1	UPDATE t2 set b=b+sleep(.02) limit 1
+4	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
+6	DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2
 DROP TABLE t1,t2;
 TRUNCATE TABLE mysql.slow_log;
 # end of bug#49756
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index ce0926c6bc1..27131438d16 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3940,3 +3940,107 @@ Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 deallocate prepare st;
 drop table t1;
+#
+# LP bug#993459 Execution of PS for a query with GROUP BY
+# returns wrong result (see also mysql bug#13805127)
+#
+PREPARE s1 FROM 
+"
+SELECT c1, t2.c2, count(c3)
+FROM
+  (
+  SELECT 3 as c2 FROM dual WHERE @x = 1
+  UNION
+  SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
+  ) AS t1,
+  (
+  SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+  UNION
+  SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
+  UNION
+  SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
+  ) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2
+";
+
+SET @x = 1;
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE @x = 1
+UNION
+SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
+UNION
+SELECT '2012-03-01 01:99345900:00',       2,       1       FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+c1	c2	count(c3)
+2012-03-01 01:00:00	3	1
+2012-03-01 01:99345900:00	2	1
+2012-03-01 02:00:00	3	1
+
+EXECUTE s1;
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+2012-03-01 01:00:00	3	1
+2012-03-01 02:00:00	3	1
+
+SET @x = 2;
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE @x = 1
+UNION
+SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
+UNION
+SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+
+EXECUTE s1;
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+
+SET @x = 1;
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE @x = 1
+UNION
+SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
+UNION
+SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2;
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+2012-03-01 01:00:00	3	1
+2012-03-01 02:00:00	3	1
+
+EXECUTE s1;
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+2012-03-01 01:00:00	3	1
+2012-03-01 02:00:00	3	1
+DEALLOCATE PREPARE s1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 3533001ccec..30d5ca00bc0 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5291,3 +5291,15 @@ DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
 SET optimizer_switch=@save_optimizer_switch;
+#
+# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed
+# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK
+#
+CREATE TABLE t1 ( a INT(6) ZEROFILL );
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 ( b INT PRIMARY KEY );
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1, t2 WHERE a=3 AND a=b;
+a	b
+drop table t1,t2;
+End of 5.3 tests
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index a5416eb6718..a9c74afdd9c 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5302,6 +5302,18 @@ DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
 SET optimizer_switch=@save_optimizer_switch;
+#
+# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed
+# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK
+#
+CREATE TABLE t1 ( a INT(6) ZEROFILL );
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 ( b INT PRIMARY KEY );
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1, t2 WHERE a=3 AND a=b;
+a	b
+drop table t1,t2;
+End of 5.3 tests
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 3533001ccec..30d5ca00bc0 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5291,3 +5291,15 @@ DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
 SET optimizer_switch=@save_optimizer_switch;
+#
+# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed
+# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK
+#
+CREATE TABLE t1 ( a INT(6) ZEROFILL );
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 ( b INT PRIMARY KEY );
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1, t2 WHERE a=3 AND a=b;
+a	b
+drop table t1,t2;
+End of 5.3 tests
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index d52318c6c5b..05f9000bb33 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -7807,3 +7807,45 @@ Warnings:
 Error	1424	Recursive stored functions and triggers are not allowed.
 Error	1305	FUNCTION test.f1 does not exist
 DROP FUNCTION f1;
+# ------------------------------------------------------------------
+# -- End of 5.1 tests
+# ------------------------------------------------------------------
+#
+# LP bug#993459 Execution of PS for a query with GROUP BY
+# returns wrong result (see also mysql bug#13805127)
+#
+CREATE PROCEDURE p1(x INT UNSIGNED)
+BEGIN
+SELECT c1, t2.c2, count(c3)
+FROM
+(
+SELECT 3 as c2 FROM dual WHERE x = 1
+UNION
+SELECT 2       FROM dual WHERE x = 1 OR x = 2
+) AS t1,
+(
+SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+UNION
+SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
+UNION
+SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
+) AS t2
+WHERE t2.c2 = t1.c2
+GROUP BY c1, c2
+;
+END|
+
+CALL p1(1);
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+2012-03-01 01:00:00	3	1
+2012-03-01 02:00:00	3	1
+CALL p1(2);
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+CALL p1(1);
+c1	c2	count(c3)
+2012-03-01 01:00:00	2	1
+2012-03-01 01:00:00	3	1
+2012-03-01 02:00:00	3	1
+DROP PROCEDURE p1;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 86442df1e8b..da2a76626dc 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1986,6 +1986,19 @@ y
 z
 DROP VIEW v2;
 DROP TABLE t1, t2;
+#
+# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0
+#
+CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('b','b'),('e','e');
+CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i');
+SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') );
+a1	a2	b1	b2
+b	b	v	v
+b	b	s	s
+b	b	y	y
+DROP TABLE t1,t2;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index f8ebe0d07a9..1ceeb541a85 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2711,4 +2711,46 @@ DROP VIEW v1;
 DROP TABLE t1;
 set @@join_cache_level= @tmp_jcl_978479;
 set @@optimizer_switch= @tmp_os_978479;
+#
+# BUG#998236: Assertion failure or valgrind errors at best_access_path ...
+#
+CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'),
+('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'),
+('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'),
+('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'),
+('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'),
+('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'),
+('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'),
+('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'),
+('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'),
+('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'),
+('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'),
+('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'),
+('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'),
+('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'),
+('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'),
+('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'),
+('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'),
+('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'),
+('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'),
+('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'),
+('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'),
+('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'),
+('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'),
+('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'),
+('USA','Inglewood'),('USA','Livonia');
+CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English');
+CREATE TABLE t2 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+SELECT alias1.a1 AS field1
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 )
+AND alias1.a1 IS NULL
+AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 )
+GROUP BY field1;
+field1
+DROP TABLE t1,t3,t2;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index c0a9287483d..31400c95959 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2725,6 +2725,48 @@ DROP VIEW v1;
 DROP TABLE t1;
 set @@join_cache_level= @tmp_jcl_978479;
 set @@optimizer_switch= @tmp_os_978479;
+#
+# BUG#998236: Assertion failure or valgrind errors at best_access_path ...
+#
+CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'),
+('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'),
+('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'),
+('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'),
+('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'),
+('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'),
+('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'),
+('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'),
+('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'),
+('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'),
+('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'),
+('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'),
+('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'),
+('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'),
+('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'),
+('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'),
+('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'),
+('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'),
+('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'),
+('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'),
+('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'),
+('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'),
+('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'),
+('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'),
+('USA','Inglewood'),('USA','Livonia');
+CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English');
+CREATE TABLE t2 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+SELECT alias1.a1 AS field1
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 )
+AND alias1.a1 IS NULL
+AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 )
+GROUP BY field1;
+field1
+DROP TABLE t1,t3,t2;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index dc1d9b407fd..da18870bbeb 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2026,6 +2026,19 @@ y
 z
 DROP VIEW v2;
 DROP TABLE t1, t2;
+#
+# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0
+#
+CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('b','b'),('e','e');
+CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i');
+SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') );
+a1	a2	b1	b2
+b	b	v	v
+b	b	s	s
+b	b	y	y
+DROP TABLE t1,t2;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 69cd9792299..63b3304641b 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -570,6 +570,27 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 #    as that violates the "no interleaving of outer join nests" rule.
 DROP TABLE t1,t2,t3,t4,t5;
 #
+# BUG#997747: Assertion `join->best_read < ((double)1.79..5e+308L)' 
+#             failed in greedy_search with LEFT JOINs and unique keys  
+#
+CREATE TABLE t1 (a1 INT);
+CREATE TABLE t2 (b1 INT);
+CREATE TABLE t3 (c1 INT, UNIQUE KEY(c1));
+CREATE TABLE t4 (d1 INT, UNIQUE KEY(d1));
+CREATE TABLE t5 (e1 INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (2),(3);
+INSERT INTO t3 VALUES (3),(4);
+INSERT INTO t4 VALUES (4),(5);
+INSERT INTO t5 VALUES (5),(6);
+SELECT a1 FROM t1 LEFT JOIN t2 LEFT JOIN t3 LEFT JOIN t4
+ON c1 = d1 ON d1 = b1 ON a1 = b1
+LEFT JOIN t5 ON a1 = e1 ;
+a1
+1
+2
+DROP TABLE t1,t2,t3,t4,t5;
+#
 # BUG#884184: Wrong result with RIGHT JOIN + derived_merge
 #
 CREATE TABLE t1 (a int(11), b varchar(1)) ;