Fixed LP bug #925985.

If the flag 'optimize_join_buffer_size' is set to 'off' and the value
of the system variable 'join_buffer_size' is greater than the value of
the system variable 'join_buffer_space_limit' than no join cache can
be employed to join tables of the executed query.
A bug in the function JOIN_CACHE::alloc_buffer allowed to use join
buffer even in this case while another bug in the function 
revise_cache_usage could cause a crash of the server in this case if the
chosen execution plan for the query contained outer join or semi-join
operation.  

1 files changed, 35 insertions, 0 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 11114a032f9..dda0f4c8f66 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5534,4 +5534,39 @@ we
 set join_cache_level = default;
 set optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3;
+#
+# Bug #925985: LEFT JOIN with optimize_join_buffer_size=off +
+#              join_buffer_size > join_buffer_space_limit
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (5), (3);
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+(3,30), (1,10), (7,70), (2,20),
+(3,31), (1,11), (7,71), (2,21),
+(3,32), (1,12), (7,72), (2,22);
+CREATE TABLE t3 (b int, c int);
+INSERT INTO t3 VALUES (32, 302), (42,400), (30,300);
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='optimize_join_buffer_size=off';
+set join_buffer_space_limit=4096;
+set join_buffer_size=4096*2;
+set join_cache_level=2;
+set optimizer_switch='outer_join_with_cache=on';
+EXPLAIN
+SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a;
+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	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (incremental, BNL join)
+SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a;
+a	a	b	b	c
+3	3	30	30	300
+3	3	31	NULL	NULL
+3	3	32	32	302
+set join_buffer_space_limit=default;
+set join_buffer_size=default;
+set join_cache_level=default;
+set optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;