merge changes

mysql-test/r/insert_select.result:
  Auto merged
mysql-test/t/insert_select.test:
  Auto merged
sql/sql_select.cc:
  Auto merged

22 files changed, 190 insertions, 61 deletions

diff --git a/mysql-test/r/convert.result b/mysql-test/r/convert.result
new file mode 100644
index 00000000000..f8dad8c69ba
--- /dev/null
+++ b/mysql-test/r/convert.result
@@ -0,0 +1,17 @@
+select @@convert_character_set;
+@@convert_character_set
+
+select @@global.convert_character_set;
+@@global.convert_character_set
+
+show variables like "%convert_character_set%";
+Variable_name	Value
+convert_character_set	
+SET CHARACTER SET cp1251_koi8;
+select @@convert_character_set;
+@@convert_character_set
+cp1251_koi8
+SET CHARACTER SET DEFAULT;
+select @@convert_character_set;
+@@convert_character_set
+
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 5f4f7cced1e..53a20eeea0b 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -161,7 +161,7 @@ SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
 UserId
 drop table t1;
 CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
-INSERT INTO t1 VALUES (1,1),(2,1);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
 CREATE TABLE t2 (a int(10) unsigned not null, key (A));
 INSERT INTO t2 VALUES (1),(2);
 CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
@@ -189,7 +189,7 @@ insert into t4 select * from t3;
 insert into t3 select * from t4;
 explain select distinct t1.a from t1,t3 where t1.a=t3.a;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index; Using temporary
+t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index; Using temporary
 t3	ref	a	a	5	t1.a	10	Using where; Using index; Distinct
 select distinct t1.a from t1,t3 where t1.a=t3.a;
 a
@@ -200,16 +200,16 @@ select distinct 1 from t1,t3 where t1.a=t3.a;
 1
 explain SELECT distinct t1.a from t1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	PRIMARY	4	NULL	2	Using index
+t1	index	NULL	PRIMARY	4	NULL	4	Using index
 explain SELECT distinct t1.a from t1 order by a desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	PRIMARY	4	NULL	2	Using index
+t1	index	NULL	PRIMARY	4	NULL	4	Using index
 explain SELECT t1.a from t1 group by a order by a desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	PRIMARY	4	NULL	2	Using index
+t1	index	NULL	PRIMARY	4	NULL	4	Using index
 explain SELECT distinct t1.a from t1 order by a desc limit 1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	PRIMARY	4	NULL	2	Using index
+t1	index	NULL	PRIMARY	4	NULL	4	Using index
 explain SELECT distinct a from t3 order by a desc limit 2;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t3	index	NULL	a	5	NULL	204	Using index
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 94e1ce59585..67e6bec09f6 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -534,11 +534,11 @@ a	b
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
-t2	ALL	a	NULL	NULL	NULL	4	Using where
+t2	ALL	a	NULL	NULL	NULL	3	Using where
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary
-t2	ALL	a	NULL	NULL	NULL	4	Using where
+t2	ALL	a	NULL	NULL	NULL	3	Using where
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index d8905085e34..a04ddf3f302 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -166,7 +166,7 @@ alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop
 update t1 set new_col=btn;
 explain select * from t1 where btn="a";
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ALL	btn	NULL	NULL	NULL	14	Using where
+t1	ALL	btn	NULL	NULL	NULL	11	Using where
 explain select * from t1 where btn="a" and new_col="a";
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	btn	btn	11	const,const	10	Using where
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index d019cfcbfa9..ebcb7721822 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -65,7 +65,7 @@ insert into t1 values (1), (NULL), (2);
 select * from t1;
 id
 1
-8
+0
 2
 drop table t1;
 drop database if exists foo;
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 5f5a3fb1172..dff5d0ff3ca 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -21,8 +21,7 @@ payoutID
 20
 22
 drop table t1,t2;
-DROP TABLE IF EXISTS crash1,crash2;
-CREATE TABLE `crash1` (
+CREATE TABLE `t1` (
 `numeropost` bigint(20) unsigned NOT NULL default '0',
 `icone` tinyint(4) unsigned NOT NULL default '0',
 `numreponse` bigint(20) unsigned NOT NULL auto_increment,
@@ -37,7 +36,7 @@ KEY `date` (`date`),
 KEY `pseudo` (`pseudo`),
 KEY `numreponse` (`numreponse`)
 ) TYPE=MyISAM;
-CREATE TABLE `crash2` (
+CREATE TABLE `t2` (
 `numeropost` bigint(20) unsigned NOT NULL default '0',
 `icone` tinyint(4) unsigned NOT NULL default '0',
 `numreponse` bigint(20) unsigned NOT NULL auto_increment,
@@ -52,22 +51,20 @@ KEY `date` (`date`),
 KEY `pseudo` (`pseudo`),
 KEY `numreponse` (`numreponse`)
 ) TYPE=MyISAM;
-INSERT INTO crash2
+INSERT INTO t2
 (numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
 (9,1,56,'test','joce','2001-07-25 13:50:53'
 ,3649052399,0);
-INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip)
-SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2
+INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
+SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
 WHERE numeropost=9 ORDER BY numreponse ASC;
 show variables like '%bulk%';
 Variable_name	Value
 bulk_insert_buffer_size	8388608
-INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip)
-SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2
+INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
+SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
 WHERE numeropost=9 ORDER BY numreponse ASC;
-DROP TABLE IF EXISTS crash1,crash2;
-drop table if exists t1;
-drop table if exists t2;
+DROP TABLE t1,t2;
 create table t1(a int, unique(a));
 insert into t1 values(2);
 create table t2(a int);
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index e063b5c3e02..039b6e1cba3 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -104,9 +104,7 @@ KEY category (category,county,state)
 INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
 INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
 INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
-select a.id, b.category as catid, b.state as stateid, b.county as
-countyid from t1 a, t2 b where (a.token =
-'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
+select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
 id	catid	stateid	countyid
 27	2	12	11
 28	2	12	11
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 81266f6562e..8f3f82201c3 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -659,3 +659,11 @@ i	i	i
 1	NULL	NULL
 2	2	2
 drop table t1,t2,t3;
+create table t1 (f1 integer,f2 integer,f3 integer);
+create table t2 (f2 integer,f4 integer);
+create table t3 (f3 integer,f5 integer);
+select * from t1
+left outer join t2 using (f2)
+left outer join t3 using (f3);
+Unknown column 'test.t2.f3' in 'on clause'
+drop table t1,t2,t3;
diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result
index 4eaccc696f9..2d4bc19474f 100644
--- a/mysql-test/r/key_diff.result
+++ b/mysql-test/r/key_diff.result
@@ -36,7 +36,7 @@ a	a	a	a
 explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	a	NULL	NULL	NULL	5	
-t2	ALL	b	NULL	NULL	NULL	5	Using where
+t2	ALL	b	NULL	NULL	NULL	4	Using where
 select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a;
 a	b	a	b
 A	B	a	a
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 9a653aff99e..c4368384bf8 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -335,12 +335,12 @@ t1	1	c_2	1	c	A	5	NULL	NULL	YES	BTREE
 t1	1	c_2	2	a	A	5	NULL	NULL		BTREE	
 explain select * from t1,t2 where t1.a=t2.a;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ALL	a	NULL	NULL	NULL	5	
-t2	ALL	a	NULL	NULL	NULL	2	Using where
+t2	ALL	a	NULL	NULL	NULL	2	
+t1	ALL	a	NULL	NULL	NULL	4	Using where
 explain select * from t1,t2 force index(a) where t1.a=t2.a;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t2	ALL	a	NULL	NULL	NULL	2	
-t1	ALL	a	NULL	NULL	NULL	5	Using where
+t1	ALL	a	NULL	NULL	NULL	4	Using where
 explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t2	ALL	a	NULL	NULL	NULL	2	
@@ -351,8 +351,8 @@ t2	ALL	b	NULL	NULL	NULL	2
 t1	ref	b	b	5	t2.b	1	Using where
 explain select * from t1,t2 force index(c) where t1.a=t2.a;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ALL	a	NULL	NULL	NULL	5	
-t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+t2	ALL	NULL	NULL	NULL	NULL	2	
+t1	ALL	a	NULL	NULL	NULL	4	Using where
 explain select * from t1 where a=0 or a=2;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	a	NULL	NULL	NULL	5	Using where
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index ff2dd93311c..64fac8af872 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -307,17 +307,17 @@ table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	a	a	9	NULL	8	Using where; Using index
 explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	9	NULL	5	Using where; Using index
+t1	range	a	a	9	NULL	4	Using where; Using index
 explain select * from t1 where a = 2 and b is null order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	a	a	9	const,const	1	Using where; Using index; Using filesort
 explain select * from t1 where a = 2 and (b is null or b > 0) order by a
 desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	9	NULL	6	Using where; Using index
+t1	range	a	a	9	NULL	5	Using where; Using index
 explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	9	NULL	5	Using where; Using index
+t1	range	a	a	9	NULL	4	Using where; Using index
 explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	a	a	9	NULL	2	Using where; Using index
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index a4c2533ec1a..811c396ea67 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2569,16 +2569,46 @@ fld1	fld1
 250503	250505
 250504	250505
 250505	250505
+insert into t2 (fld1, companynr) values (999999,99);
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 companynr	companyname
+99	NULL
+select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
+count(*)
+1199
 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t2	ALL	NULL	NULL	NULL	NULL	1199	
+t2	ALL	NULL	NULL	NULL	NULL	1200	
 t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t4	ALL	NULL	NULL	NULL	NULL	12	
-t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Not exists
+t2	ALL	NULL	NULL	NULL	NULL	1200	Using where; Not exists
+delete from t2 where fld1=999999;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t4	ALL	NULL	NULL	NULL	NULL	12	
+t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t4	ALL	PRIMARY	NULL	NULL	NULL	12	
+t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t4	ALL	NULL	NULL	NULL	NULL	12	
+t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result
index 3303f19d9c7..ca5c03bdb50 100644
--- a/mysql-test/r/select_safe.result
+++ b/mysql-test/r/select_safe.result
@@ -13,7 +13,7 @@ a	b
 1	test
 2	test2
 update t1 set b="a" where a=1;
-select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
+select 1 from t1,t1 as t2,t1 as t3;
 1
 1
 1
@@ -35,18 +35,19 @@ update t1 set b="a" limit 1;
 update t1 set b="a" where b="b" limit 2;
 delete from t1 where b="test" limit 1;
 delete from t1 where a+0=1 limit 2;
+alter table t1 add key b (b);
 SET MAX_JOIN_SIZE=2;
 SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
 @@max_join_size	@@sql_big_selects
 2	0
 insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
-SELECT * from t1;
+SELECT * from t1 order by a;
 The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok
 SET SQL_BIG_SELECTS=1;
-SELECT * from t1;
+SELECT * from t1 order by a;
 a	b
-3	a
 2	test2
+3	a
 4	a
 5	a
 SET MAX_JOIN_SIZE=2;
@@ -55,9 +56,26 @@ The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use
 SET MAX_JOIN_SIZE=DEFAULT;
 SELECT * from t1;
 a	b
-3	a
 2	test2
+3	a
 4	a
 5	a
+SELECT @@MAX_SEEKS_FOR_KEY;
+@@max_seeks_for_key
+4294967295
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	b	NULL	NULL	NULL	21	
+t2	ALL	b	NULL	NULL	NULL	16	Using where
+set MAX_SEEKS_FOR_KEY=1;
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	b	NULL	NULL	NULL	21	
+t2	ref	b	b	21	t1.b	6	Using where
+SET MAX_SEEKS_FOR_KEY=DEFAULT;
 drop table t1;
 SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 5e9f3a720c2..67e55acb29b 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -30,3 +30,15 @@ explain select * from t1 where i=@vv1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	i	i	4	const	1	Using where
 drop table t1,t2;
+select @a:=10,   @b:=1,   @a > @b, @a < @b;
+@a:=10	@b:=1	@a > @b	@a < @b
+10	1	1	0
+select @a:="10", @b:="1", @a > @b, @a < @b;
+@a:="10"	@b:="1"	@a > @b	@a < @b
+10	1	1	0
+select @a:=10,   @b:=2,   @a > @b, @a < @b;
+@a:=10	@b:=2	@a > @b	@a < @b
+10	2	1	0
+select @a:="10", @b:="2", @a > @b, @a < @b;
+@a:="10"	@b:="2"	@a > @b	@a < @b
+10	2	0	1
diff --git a/mysql-test/t/convert.test b/mysql-test/t/convert.test
new file mode 100644
index 00000000000..f26ef3a8c72
--- /dev/null
+++ b/mysql-test/t/convert.test
@@ -0,0 +1,11 @@
+# Test of character set conversions
+
+# Test that SET DEFAULT works
+
+select @@convert_character_set;
+select @@global.convert_character_set;
+show variables like "%convert_character_set%";
+SET CHARACTER SET cp1251_koi8;
+select @@convert_character_set;
+SET CHARACTER SET DEFAULT;
+select @@convert_character_set;
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 7f75b6b1687..ecce2409571 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -60,7 +60,7 @@ drop table t1;
 #
 
 CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
-INSERT INTO t1 VALUES (1,1),(2,1);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
 CREATE TABLE t2 (a int(10) unsigned not null, key (A));
 INSERT INTO t2 VALUES (1),(2);
 CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 2bebd4e86dd..5c7fc57262b 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -12,13 +12,13 @@ insert into t2 (payoutID) SELECT payoutID+10 FROM t1;
 insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1;
 select * from t2;
 drop table t1,t2;
+
 #
 # bug in bulk insert optimization
 # test case by Fournier Jocelyn <joc@presence-pc.com>
 #
 
-DROP TABLE IF EXISTS crash1,crash2;
-CREATE TABLE `crash1` (
+CREATE TABLE `t1` (
   `numeropost` bigint(20) unsigned NOT NULL default '0',
   `icone` tinyint(4) unsigned NOT NULL default '0',
   `numreponse` bigint(20) unsigned NOT NULL auto_increment,
@@ -34,7 +34,7 @@ CREATE TABLE `crash1` (
   KEY `numreponse` (`numreponse`)
 ) TYPE=MyISAM;
 
-CREATE TABLE `crash2` (
+CREATE TABLE `t2` (
   `numeropost` bigint(20) unsigned NOT NULL default '0',
   `icone` tinyint(4) unsigned NOT NULL default '0',
   `numreponse` bigint(20) unsigned NOT NULL auto_increment,
@@ -50,30 +50,27 @@ CREATE TABLE `crash2` (
   KEY `numreponse` (`numreponse`)
 ) TYPE=MyISAM;
 
-INSERT INTO crash2
+INSERT INTO t2
 (numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
 (9,1,56,'test','joce','2001-07-25 13:50:53'
 ,3649052399,0);
 
 
-INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip)
-SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2
+INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
+SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
 WHERE numeropost=9 ORDER BY numreponse ASC;
 
 show variables like '%bulk%';
 
-INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip)
-SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2
+INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
+SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
 WHERE numeropost=9 ORDER BY numreponse ASC;
 
-DROP TABLE IF EXISTS crash1,crash2;
-
+DROP TABLE t1,t2;
 
-# Addendum by Guilhem:
 # Check if a partly-completed INSERT SELECT in a MyISAM table goes
 # into the binlog
-drop table if exists t1;
-drop table if exists t2;
+
 create table t1(a int, unique(a));
 insert into t1 values(2);
 create table t2(a int);
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 08cc5731723..19e04d2aa7e 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -98,10 +98,7 @@ CREATE TABLE t2 (
 INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
 INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
 INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
-
-select a.id, b.category as catid, b.state as stateid, b.county as
-countyid from t1 a, t2 b where (a.token =
-'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
+select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
 select a.id, b.category as catid, b.state as stateid, b.county as
 countyid from t1 a, t2 b where (a.token =
 'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index e172d54194e..ee7d55d2a4e 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -425,3 +425,15 @@ insert into t2 values(2),(3);
 insert into t3 values(2),(4);
 select * from t1 natural left join t2 natural left join t3;
 drop table t1,t2,t3;
+
+#
+# Test of USING
+#
+create table t1 (f1 integer,f2 integer,f3 integer);
+create table t2 (f2 integer,f4 integer);
+create table t3 (f3 integer,f5 integer);
+--error 1054
+select * from t1
+         left outer join t2 using (f2)
+         left outer join t3 using (f3);
+drop table t1,t2,t3;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index de90eeec2e8..c2e451ea7f3 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1527,10 +1527,24 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25
 #
 # Test of left join.
 #
+insert into t2 (fld1, companynr) values (999999,99);
 
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
+delete from t2 where fld1=999999;
+
+#
+# Test left join optimization
+
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+# Following can't be optimized
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
 
 #
 # Joins with forms.
diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test
index a085cfee29d..206f911d028 100644
--- a/mysql-test/t/select_safe.test
+++ b/mysql-test/t/select_safe.test
@@ -12,7 +12,7 @@ delete from t1 where a=1;
 insert into t1 values(1,"test"),(2,"test2");
 SELECT SQL_BUFFER_RESULT * from t1;
 update t1 set b="a" where a=1;
-select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
+select 1 from t1,t1 as t2,t1 as t3;
 
 # The following should give errors:
 --error 1175
@@ -36,19 +36,31 @@ delete from t1 where a+0=1 limit 2;
 
 # Test SQL_BIG_SELECTS
 
+alter table t1 add key b (b);
 SET MAX_JOIN_SIZE=2;
 SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
 insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
 --error 1104
-SELECT * from t1;
+SELECT * from t1 order by a;
 SET SQL_BIG_SELECTS=1;
-SELECT * from t1;
+SELECT * from t1 order by a;
 SET MAX_JOIN_SIZE=2;
 --error 1104
 SELECT * from t1;
 SET MAX_JOIN_SIZE=DEFAULT;
 SELECT * from t1;
 
+#
+# Test MAX_SEEKS_FOR_KEY
+#
+SELECT @@MAX_SEEKS_FOR_KEY;
+analyze table t1;
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+set MAX_SEEKS_FOR_KEY=1;
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+SET MAX_SEEKS_FOR_KEY=DEFAULT;
+
 drop table t1;
 
 SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index bb1a9b59611..f5f91a8a680 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -17,3 +17,9 @@ explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
 explain select @vv1:=i from t1 where i=@vv1;
 explain select * from t1 where i=@vv1;
 drop table t1,t2;
+
+# Check types of variables
+select @a:=10,   @b:=1,   @a > @b, @a < @b;
+select @a:="10", @b:="1", @a > @b, @a < @b;
+select @a:=10,   @b:=2,   @a > @b, @a < @b;
+select @a:="10", @b:="2", @a > @b, @a < @b;