bzr commit into mysql-6.0 branch (azundris:2637) WL#4421

[Tatiana A. Nurnberg]

#At file:///home/tnurnberg/forest/WL4421/

 2637 Tatiana A. Nurnberg	2008-06-27
      WL#4421 Add hints on join buffer usage for join queries
      
      Adds USE/IGNORE/FORCE JOIN_BUFFER[(<size>[,<level>])]
modified:
  mysql-test/r/group_by.result
  mysql-test/r/join_cache.result
  mysql-test/t/group_by.test
  mysql-test/t/join_cache.test
  sql/lex.h
  sql/sql_lex.h
  sql/sql_select.cc
  sql/sql_select.h
  sql/sql_yacc.yy
  sql/table.h

per-file messages:
  mysql-test/r/group_by.result
    Test neglected to drop a table, confusing follow-up
    tests if they were run in a particular order.
  mysql-test/r/join_cache.result
    add tests for the JOIN_BUFFER hints syntax.
  mysql-test/t/group_by.test
    Test neglected to drop a table, confusing follow-up
    tests if they were run in a particular order.
  mysql-test/t/join_cache.test
    add tests for the JOIN_BUFFER hints syntax.
  sql/lex.h
    new keyword JOIN_BUFFER
  sql/sql_lex.h
    getters/setters for JOIN_BUFFER-related hints on
    st_select_lex
  sql/sql_select.cc
    If a size-hint is given for JOIN_BUFFER, use it for
    the cache instead of sysvar. Likewise for cache-level
    hint and IGNORE/FORCE/USE.  If we cannot get buffering
    at the requested level for a given table, decrease
    level until we succeed (or reach 0).
  sql/sql_select.h
    Set on cache what level it is (the level we actually
    got, not the one the user asked for).
  sql/sql_yacc.yy
    parse JOIN_BUFFER hints
  sql/table.h
    define JOIN_BUFFER hints. set them on table.
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2008-02-17 13:54:05 +0000
+++ b/mysql-test/r/group_by.result	2008-06-27 08:58:13 +0000
@@ -1478,7 +1478,7 @@ b
 NULL
 1
 2
-DROP TABLE t1;
+DROP TABLE t1,t2;
 CREATE TABLE t1 ( a INT, b INT );
 SELECT b c, (SELECT a FROM t1 WHERE b = c)
 FROM t1;

=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2008-06-06 22:03:14 +0000
+++ b/mysql-test/r/join_cache.result	2008-06-27 08:58:13 +0000
@@ -3185,3 +3185,353 @@ a1<>a2	a1	a2	b2	b3	c3	s1	s2
 0	6	6	73	73	738		
 0	6	6	74	74	749		
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int, b int default 0, c int default 1, d int default 4, primary key(a), key(b));
+INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 (a) SELECT a + 8 FROM t1;
+INSERT INTO t1 (a) SELECT a + 16 FROM t1;
+CREATE TABLE t2 (a int, b int default 0, c int default 1111);
+INSERT INTO t2 (a, b) VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t2 (a, b) SELECT a+4, a+4 FROM t2;
+INSERT INTO t2 (a, b) SELECT a+8, a+8 FROM t2;
+CREATE TABLE t3 (a int, b int default 0, primary key(a), key(b));
+INSERT INTO t3 (a, b) VALUES (1,1),(2,0),(3,7),(4,9);
+INSERT INTO t3 (a, b) SELECT a+4, a+4 FROM t3;
+INSERT INTO t3 (a, b) SELECT a+8, a+8 FROM t3;
+SELECT * FROM t1, t2, t3 FORCE  JOIN_BUFFER WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE  JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 USE    JOIN_BUFFER WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 USE    JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 IGNORE JOIN_BUFFER WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 IGNORE JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(1024) USE INDEX(PRIMARY) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(65536,1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(DEFAULT,1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(DEFAULT,-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER('BigFunkyCat') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''BigFunkyCat') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER() WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(4.5) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: Only integers allowed as number here near '4.5) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(DEFAULT) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(256 * 1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(4 * 64 * 1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(2 * @@GLOBAL.JOIN_BUFFER_SIZE) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(0) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+set join_cache_level=0;
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2 FORCE JOIN_BUFFER(222222)
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+c
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+c
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2 FORCE JOIN_BUFFER(222222)
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+c
+SELECT * FROM t1 FORCE JOIN_BUFFER(65536),
+t2 FORCE JOIN_BUFFER,
+t3 FORCE  JOIN_BUFFER
+WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+a	b	c	d	a	b	c	a	b
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]	10
[phone]	11
[phone]	12
[phone]	13
[phone]	14
[phone]	15
[phone]	16
+set join_cache_level=8;
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2 IGNORE JOIN_BUFFER
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+c
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+c
+set join_cache_level=default;
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1 FORCE JOIN_BUFFER(32000),t2 FORCE JOIN_BUFFER(222222)
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	32	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where; Using join buffer
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t2 FORCE JOIN_BUFFER(222222),t1 FORCE JOIN_BUFFER(32000)
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ref	b	b	5	test.t2.c	3	Using join buffer
+set join_cache_level=0;
+*** show that J/B is disallowed by JCL 0
+EXPLAIN SELECT * FROM t1,
+t2,
+t3
+WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	
+1	SIMPLE	t3	ref	b	b	5	test.t2.b	2	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where
+*** force use of J/B
+EXPLAIN SELECT * FROM t1 FORCE JOIN_BUFFER,
+t2 FORCE JOIN_BUFFER,
+t3 FORCE JOIN_BUFFER
+WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	
+1	SIMPLE	t3	ref	b	b	5	test.t2.b	2	Using join buffer
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where; Using join buffer
+*** allow server to use or not use J/B (it won't)
+EXPLAIN SELECT * FROM t1 USE   JOIN_BUFFER,
+t2 USE   JOIN_BUFFER,
+t3 USE   JOIN_BUFFER
+WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	
+1	SIMPLE	t3	ref	b	b	5	test.t2.b	2	Using join buffer
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where; Using join buffer
+*** show that J/B will not be used due to JCL 0
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	32	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where
+*** show USE will allow J/B, and in this case, it's actually chosen
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1 USE JOIN_BUFFER,t2 USE JOIN_BUFFER
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	32	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where; Using join buffer
+set join_cache_level=default;
+*** show that with default JCL, J/B will be chosen
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	32	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where; Using join buffer
+*** show that due to IGNORE, we will not choose J/B
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1 IGNORE JOIN_BUFFER,
+t2 IGNORE JOIN_BUFFER
+WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	32	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2008-02-17 13:54:05 +0000
+++ b/mysql-test/t/group_by.test	2008-06-27 08:58:13 +0000
@@ -945,7 +945,7 @@ SELECT a from t2 GROUP BY a;
 EXPLAIN SELECT b from t2 GROUP BY b;
 SELECT b from t2 GROUP BY b;
 
-DROP TABLE t1;
+DROP TABLE t1,t2;
 
 #
 # Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2008-06-03 01:49:09 +0000
+++ b/mysql-test/t/join_cache.test	2008-06-27 08:58:13 +0000
@@ -750,3 +750,155 @@ SELECT a1<>a2, a1, a2, b2, b3, c3,
 FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
  
 DROP TABLE t1,t2,t3;
+
+
+# WL4421 -- Add hints on join buffer usage for join queries
+
+CREATE TABLE t1 (a int, b int default 0, c int default 1, d int default 4, primary key(a), key(b));
+INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 (a) SELECT a + 8 FROM t1;
+INSERT INTO t1 (a) SELECT a + 16 FROM t1;
+
+CREATE TABLE t2 (a int, b int default 0, c int default 1111);
+INSERT INTO t2 (a, b) VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t2 (a, b) SELECT a+4, a+4 FROM t2;
+INSERT INTO t2 (a, b) SELECT a+8, a+8 FROM t2;
+
+CREATE TABLE t3 (a int, b int default 0, primary key(a), key(b));
+INSERT INTO t3 (a, b) VALUES (1,1),(2,0),(3,7),(4,9);
+INSERT INTO t3 (a, b) SELECT a+4, a+4 FROM t3;
+INSERT INTO t3 (a, b) SELECT a+8, a+8 FROM t3;
+
+### 1 - show parsing works as expected
+
+SELECT * FROM t1, t2, t3 FORCE  JOIN_BUFFER WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 FORCE  JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 USE    JOIN_BUFFER WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 USE    JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 IGNORE JOIN_BUFFER WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 IGNORE JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+## show that we can mix JOIN_BUFFER-hints with INDEX-hints any which way!
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(1024) USE INDEX(PRIMARY) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+## size + level
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(65536,1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(DEFAULT,1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+# we don't accept negative levels now, but it may come to mean 'auto' later!
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(DEFAULT,-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+## show that empty or rubbish size-options are not tolerated
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER('BigFunkyCat') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER() WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(4.5) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+# --error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(DEFAULT) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+## show that expressions on the other hand work!
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(256 * 1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(4 * 64 * 1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(2 * @@GLOBAL.JOIN_BUFFER_SIZE) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+## special case: very small join-buffer could trigger crash
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(0) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+### 2 - test some results in conjunction with JOIN_CACHE_LEVEL
+
+### FORCE
+
+set join_cache_level=0;
+
+# FORCE: needs to use J/B all the same for t2 (JCL doesn't allow, hints do)
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2 FORCE JOIN_BUFFER(222222)
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+# no hints, no J/B
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2 FORCE JOIN_BUFFER(222222)
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+# multi-FORCE
+SELECT * FROM t1 FORCE JOIN_BUFFER(65536),
+              t2 FORCE JOIN_BUFFER,
+              t3 FORCE  JOIN_BUFFER
+  WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+
+### IGNORE
+
+# 8 is max for now
+set join_cache_level=8;
+
+# needs to NOT use J/B for t2 (JCL allows, hints don't)
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2 IGNORE JOIN_BUFFER
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+# no hints, J/B
+SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+set join_cache_level=default;
+
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1 FORCE JOIN_BUFFER(32000),t2 FORCE JOIN_BUFFER(222222)
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t2 FORCE JOIN_BUFFER(222222),t1 FORCE JOIN_BUFFER(32000)
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+set join_cache_level=0;
+
+--echo *** show that J/B is disallowed by JCL 0
+
+EXPLAIN SELECT * FROM t1,
+                      t2,
+                      t3
+                      WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+
+
+--echo *** force use of J/B
+
+EXPLAIN SELECT * FROM t1 FORCE JOIN_BUFFER,
+                      t2 FORCE JOIN_BUFFER,
+                      t3 FORCE JOIN_BUFFER
+                      WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+
+--echo *** allow server to use or not use J/B (it won't)
+
+EXPLAIN SELECT * FROM t1 USE   JOIN_BUFFER,
+                      t2 USE   JOIN_BUFFER,
+                      t3 USE   JOIN_BUFFER
+                      WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5;
+
+--echo *** show that J/B will not be used due to JCL 0
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+--echo *** show USE will allow J/B, and in this case, it's actually chosen
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1 USE JOIN_BUFFER,t2 USE JOIN_BUFFER
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+set join_cache_level=default;
+
+--echo *** show that with default JCL, J/B will be chosen
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1,t2
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+--echo *** show that due to IGNORE, we will not choose J/B
+EXPLAIN SELECT STRAIGHT_JOIN t2.c FROM t1 IGNORE JOIN_BUFFER,
+                                       t2 IGNORE JOIN_BUFFER
+  WHERE t2.b=1 AND t1.b=t2.c ORDER BY t1.b, t1.c;
+
+# clean-up
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+## ends

=== modified file 'sql/lex.h'
--- a/sql/lex.h	2008-02-22 13:58:52 +0000
+++ b/sql/lex.h	2008-06-27 08:58:13 +0000
@@ -273,6 +273,7 @@ static SYMBOL symbols[] = {
   { "ITERATE",          SYM(ITERATE_SYM)},
   { "INVOKER",          SYM(INVOKER_SYM)},
   { "JOIN",		SYM(JOIN_SYM)},
+  { "JOIN_BUFFER",	SYM(JOIN_BUFFER_SYM)},
   { "KEY",		SYM(KEY_SYM)},
   { "KEYS",		SYM(KEYS)},
   { "KEY_BLOCK_SIZE",	SYM(KEY_BLOCK_SIZE)},

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2008-04-02 12:04:51 +0000
+++ b/sql/sql_lex.h	2008-06-27 08:58:13 +0000
@@ -811,12 +811,48 @@ public:
 
   void clear_index_hints(void) { index_hints= NULL; }
 
+  void set_join_buffer_hint_size(ulong size)
+  {
+    current_join_buffer_hint_size= size;
+    current_join_buffer_hint_type |= JOIN_BUFFER_HINT_SIZE;
+  }
+
+  ulong get_join_buffer_hint_size(void)
+  {
+    return current_join_buffer_hint_size;
+  }
+
+  void set_join_buffer_hint_type(enum join_buffer_hint_type type)
+  {
+    current_join_buffer_hint_type= type;
+  }
+
+  int get_join_buffer_hint_type(void)
+  {
+    return current_join_buffer_hint_type;
+  }
+
+  void set_join_buffer_hint_level(int level)
+  {
+    current_join_buffer_hint_level= level;
+    current_join_buffer_hint_type |= JOIN_BUFFER_HINT_LEVEL;
+  }
+
+  int get_join_buffer_hint_level(void)
+  {
+    return current_join_buffer_hint_level;
+  }
+
 private:  
   /* current index hint kind. used in filling up index_hints */
   enum index_hint_type current_index_hint_type;
   index_clause_map current_index_hint_clause;
   /* a list of USE/FORCE/IGNORE INDEX */
   List<Index_hint> *index_hints;
+
+  ulong current_join_buffer_hint_size;            // ulong, like the global
+  int current_join_buffer_hint_type;
+  int current_join_buffer_hint_level;
 };
 typedef class st_select_lex SELECT_LEX;
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-06-12 04:27:24 +0000
+++ b/sql/sql_select.cc	2008-06-27 08:58:13 +0000
@@ -5871,10 +5871,18 @@ best_access_path(JOIN      *join,
       }
       else
       {
-        /* We read the table as many times as join buffer becomes full. */
+        /*
+          We read the table as many times as join buffer becomes full.
+          Heed hint for join-buffer size or, missing that, use sysvar.
+        */
+        ulong join_buff_size=
+              (s->table->pos_in_table_list->join_buffer_hint_type &
+              JOIN_BUFFER_HINT_SIZE)
+              ?s->table->pos_in_table_list->join_buffer_hint_size
+              :thd->variables.join_buff_size;
         tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
                            record_count /
-                           (double) thd->variables.join_buff_size));
+                           (double) join_buff_size));
         /* 
             We don't make full cartesian product between rows in the scanned
            table and existing records because we skip all rows from the
@@ -8202,19 +8210,34 @@ void revise_cache_usage(JOIN_TAB *join_t
 }
 
 
+/**
+  Should we use join-buffering at given level for given table?
+
+  @param    tab             The table to make the decision for
+  @param    join            Join being processed
+  @param    options         Join's options (checking for SELECT_DESCRIBE,
+                            SELECT_NO_JOIN_CACHE)
+  @param    no_jbuf_after   Don't use join buffering after this table
+  @param    cache_level     The desired cache-level
+  @return   use_cache       t if we are to use join-buffer
+
+  @details
+    Check whether we can use join-buffering at the given level.
+    Called from check_join_cache_usage().
+ */
+
 static
-bool check_join_cache_usage(JOIN_TAB *tab,
+bool check_join_cache_level(JOIN_TAB *tab,
                             JOIN *join, ulonglong options,
-                            uint no_jbuf_after)
+                            uint no_jbuf_after, int cache_level)
 {
   uint flags;
   COST_VECT cost;
   uint bufsz= 4096;
   JOIN_CACHE *prev_cache=0;
-  uint cache_level= join->thd->variables.join_cache_level;
   bool force_unlinked_cache= test(cache_level & 1);
   uint i= tab-join->join_tab;
-  
+
   if (cache_level == 0)
     return FALSE;
   if (i == join->const_tables)
@@ -8281,6 +8304,73 @@ no_join_cache:
   return FALSE;          
 }
 
+
+/**
+  Find most appropriate cache-level for joining given table.
+
+  @param    tab             The table to make the decision for
+  @param    join            Join being processed
+  @param    options         Join's options (checking for SELECT_DESCRIBE,
+                            SELECT_NO_JOIN_CACHE)
+  @param    no_jbuf_after   Don't use join buffering after this table
+  @return   use_cache       t if we are to use join-buffer
+
+  @details
+    Apply all join-buffer hints except size-hints.
+    Then check whether we can use join-buffering at all, starting at the
+    level selected by the user and descending until we get a positive.
+    Called from make_join_readinfo().
+ */
+
+static
+bool check_join_cache_usage(JOIN_TAB *tab,
+                            JOIN *join, ulonglong options,
+                            uint no_jbuf_after)
+{
+  uint cache_level;
+  uint i= tab-join->join_tab;
+  uint hint= tab->table->pos_in_table_list->join_buffer_hint_type;
+
+  if (hint)
+  {
+    /*
+       join_buffer_hint_type can be one of FORCE/USE/IGNORE.
+       Since FORCE and USE are functionally equivalent for the time
+       being, we just need to bail here on IGNORE.
+    */
+    if (hint & JOIN_BUFFER_HINT_IGNORE)
+      return FALSE;
+
+    /*
+      If the user gave a level-hint, we'll start with that; otherwise,
+      we'll start with the maximum.
+    */
+    if (hint & JOIN_BUFFER_HINT_LEVEL)
+      cache_level= tab->table->pos_in_table_list->join_buffer_hint_level;
+    else
+      cache_level= 8;
+  }
+  else
+    /*
+       No hints given, use sysvar join-cache level.
+    */
+    cache_level= join->thd->variables.join_cache_level;
+
+  while (cache_level > 0)
+  {
+    if (check_join_cache_level(tab, join, options, no_jbuf_after, cache_level))
+    {
+      if (tab->cache)                           // NULL for SELECT_DESCRIBE
+        tab->cache->cache_level= cache_level;
+      return TRUE;
+    }
+    cache_level--;
+  }
+
+  return FALSE;
+}
+
+
 /*
   Plan refinement stage: do various set ups for the executioner
 
@@ -16911,7 +17001,19 @@ void JOIN_CACHE::set_constants()
   uint len= length + fields*sizeof(uint)+blobs*sizeof(uchar *) +
             (prev_cache ? prev_cache->get_size_of_rec_offset() : 0) +
             sizeof(ulong);
-  buff_size= max(join->thd->variables.join_buff_size, 2*len);
+
+  /*
+    If there is a valid size-hint for this join-buffer, it overrides
+    the system variable.
+  */
+
+  buff_size= (join_tab->table->pos_in_table_list->join_buffer_hint_type &
+              JOIN_BUFFER_HINT_SIZE)
+             ?join_tab->table->pos_in_table_list->join_buffer_hint_size
+             :join->thd->variables.join_buff_size;
+
+  buff_size= max(buff_size, 2*len);
+
   size_of_rec_ofs= offset_size(buff_size);
   size_of_rec_len= blobs ? size_of_rec_ofs : offset_size(len); 
   size_of_fld_ofs= size_of_rec_len;
@@ -18932,6 +19034,13 @@ int JOIN_CACHE_BKA_UNIQUE::init()
   if ((rc= JOIN_CACHE_BKA::init()))
     DBUG_RETURN (rc);
 
+  if (get_size_of_rec_offset() < 2)             // pathological case
+  {
+    my_free(buff, MYF(0));
+    buff= NULL;
+    DBUG_RETURN (1);
+  }
+
   key_length= ref->key_length;
 
   /* Take into account a reference to the next record in the key chain */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2008-06-11 15:57:58 +0000
+++ b/sql/sql_select.h	2008-06-27 08:58:13 +0000
@@ -657,6 +657,9 @@ public:
   /* Table to be joined with the partial join records from the cache */ 
   JOIN_TAB *join_tab;
 
+  /* This identifies the level we got rather than the one we asked for. */
+  int cache_level;
+
   /* Pointer to the previous join cache if there is any */
   JOIN_CACHE *prev_cache;
   /* Pointer to the next join cache if there is any */

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2008-04-02 12:04:51 +0000
+++ b/sql/sql_yacc.yy	2008-06-27 08:58:13 +0000
@@ -1188,6 +1188,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
 %token  YEAR_SYM                      /* SQL-2003-R */
 %token  ZEROFILL
 %token  BACKUP_TEST_SYM
+%token  JOIN_BUFFER_SYM               /* WL4421 */
 
 %left   JOIN_SYM INNER_SYM STRAIGHT_JOIN CROSS LEFT RIGHT
 /* A dummy token to force the priority of table_ref production in a join. */
@@ -1270,7 +1271,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
         literal text_literal insert_ident order_ident
         simple_ident expr opt_expr opt_else sum_expr in_sum_expr
         variable variable_aux bool_pri
-        predicate bit_expr
+        predicate bit_expr num_expr
         table_wild simple_expr udf_expr
         expr_or_default set_expr_or_default
         param_marker geometry_function
@@ -8090,6 +8091,7 @@ table_factor:
           {
             SELECT_LEX *sel= Select;
             sel->table_join_options= 0;
+            sel->set_join_buffer_hint_type(JOIN_BUFFER_HINT_DEFAULT);
           }
           table_ident opt_table_alias opt_key_definition
           {
@@ -8098,6 +8100,9 @@ table_factor:
                                                 Lex->lock_option,
                                                 Select->pop_index_hints())))
               MYSQL_YYABORT;
+            $$->join_buffer_hint_size = Select->get_join_buffer_hint_size();
+            $$->join_buffer_hint_level= Select->get_join_buffer_hint_level();
+            $$->join_buffer_hint_type = Select->get_join_buffer_hint_type();
             Select->add_joined_table($$);
           }
         | select_derived_init get_select_lex select_derived2
@@ -8321,6 +8326,56 @@ opt_outer:
         | OUTER {}
         ;
 
+num_expr:
+          num_expr '+' num_expr %prec '+'
+          { $$= new Item_func_plus($1,$3); }
+        | num_expr '*' num_expr %prec '*'
+          { $$= new Item_func_mul($1,$3); }
+        | num_expr '/' num_expr %prec '/'
+          { $$= new Item_func_div($1,$3); }
+        | num_expr '-' num_expr %prec '-'
+          { $$= new Item_func_minus($1,$3); }
+        | size_number
+          { $$= new Item_uint("size", $1, 16); }
+        | variable
+        ;
+
+join_buffer_hint_size:
+          num_expr
+          {
+            if ($1->fix_fields(YYTHD, (Item**)0) ||
+                ($1->result_type() != INT_RESULT &&
+                 $1->result_type() != REAL_RESULT) ||
+                ($1->val_int() < 0))
+            {
+              my_parse_error(ER(ER_ONLY_INTEGERS_ALLOWED));
+              MYSQL_YYABORT;
+            }
+            Select->set_join_buffer_hint_size($1->val_int());
+          }
+        ;
+
+join_buffer_hint_options:
+          join_buffer_hint_size
+        | join_buffer_hint_size ',' ulong_num
+          { Select->set_join_buffer_hint_level($3); }
+        | DEFAULT ',' ulong_num
+          { Select->set_join_buffer_hint_level($3); }
+        | DEFAULT
+        ;
+
+join_buffer_hint_type:
+          FORCE_SYM   { Select->set_join_buffer_hint_type(JOIN_BUFFER_HINT_FORCE); }
+        | USE_SYM     { Select->set_join_buffer_hint_type(JOIN_BUFFER_HINT_USE); }
+        ;
+
+join_buffer_hint_definition:
+          join_buffer_hint_type JOIN_BUFFER_SYM
+        | join_buffer_hint_type JOIN_BUFFER_SYM '(' join_buffer_hint_options ')'
+        | IGNORE_SYM JOIN_BUFFER_SYM
+          { Select->set_join_buffer_hint_type(JOIN_BUFFER_HINT_IGNORE); }
+        ;
+
 index_hint_clause:
           /* empty */
           {
@@ -8338,7 +8393,8 @@ index_hint_type:
         ;
 
 index_hint_definition:
-          index_hint_type key_or_index index_hint_clause
+          join_buffer_hint_definition
+        | index_hint_type key_or_index index_hint_clause
           {
             Select->set_index_hint_type($1, $3);
           }
@@ -8361,7 +8417,7 @@ opt_index_hints_list:
         ;
 
 opt_key_definition:
-          {  Select->clear_index_hints(); }
+          { Select->clear_index_hints(); }
           opt_index_hints_list
         ;
 

=== modified file 'sql/table.h'
--- a/sql/table.h	2008-04-02 12:04:51 +0000
+++ b/sql/table.h	2008-06-27 08:58:13 +0000
@@ -452,6 +452,16 @@ enum index_hint_type
   INDEX_HINT_FORCE
 };
 
+enum join_buffer_hint_type
+{
+  JOIN_BUFFER_HINT_DEFAULT=0,
+  JOIN_BUFFER_HINT_IGNORE=1,
+  JOIN_BUFFER_HINT_USE=2,
+  JOIN_BUFFER_HINT_FORCE=4,
+  JOIN_BUFFER_HINT_SIZE=32,
+  JOIN_BUFFER_HINT_LEVEL=64
+};
+
 struct st_table {
   st_table() {}                               /* Remove gcc warning */
 
@@ -1003,6 +1013,12 @@ struct TABLE_LIST
   List<Index_hint> *index_hints;
   TABLE        *table;                          /* opened table */
   uint          table_id; /* table id (from binlog) for opened table */
+
+  /* JOIN_BUFFER hints (USE/IGNORE/FORCE, SIZE) */
+  ulong join_buffer_hint_size;
+  int join_buffer_hint_level;
+  int join_buffer_hint_type;
+
   /*
     select_result for derived table to pass it from table creation to table
     filling procedure