Fixed LP #bug 660963.

The condition that was supposed to check whether a join table
is an inner table of a nested outer join or semi-join was not
quite correct in the code of the function check_join_cache_usage.
That's why some queries with nested outer joins triggered 
an assertion failure.
Encapsulated this condition in the new method called
JOIN_TAB::is_nested_inner and provided a proper code for it.

Also corrected a bug in the code of check_join_cache_usage()
that caused a downgrade of not first join buffers of the
level 5 and 7 to level 4 and 6 correspondingly.

1 files changed, 38 insertions, 3 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index d838b5bf7a4..36152af7898 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -2104,7 +2104,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -3578,7 +3578,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -4661,7 +4661,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join)
 SELECT COUNT(*) FROM t1,t2,t3
 WHERE t1.a=t2.a AND t2.a=t3.a AND
 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -5969,4 +5969,39 @@ a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug #660963: nested outer join with join_cache_level set to 5
+#
+CREATE TABLE t1 (a1 int) ;
+INSERT INTO t1 VALUES (0),(0);
+CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ;
+INSERT INTO t2 VALUES (2,1);
+CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ;
+INSERT INTO t3 VALUES (2,1);
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 6;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join)
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+a1	a2	b2	a3	b3
+0	2	1	2	1
+0	2	1	2	1
+SET SESSION join_cache_level = 5;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BNLH join)
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+a1	a2	b2	a3	b3
+0	2	1	2	1
+0	2	1	2	1
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;