5 files changed, 299 insertions, 1 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 227b34f3691..057e0308313 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -190,7 +190,7 @@ insert into t3 select * from t4;
 explain select distinct t1.a from t1,t3 where t1.a=t3.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index; Using temporary
-1	SIMPLE	t3	ref	a	a	5	t1.a	10	Using index; Distinct
+1	SIMPLE	t3	ref	a	a	5	t1.a	10	where used; Using index; Distinct
 select distinct t1.a from t1,t3 where t1.a=t3.a;
 a
 1
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 0a708ff298c..c6aed00b6c5 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -188,4 +188,10 @@ id	uniq_id
 4	2
 7	3
 8	4
+order_id	product_id	product_type
+order_id	product_id	product_type
+3d7ce39b5d4b3e3d22aaafe9b633de51	1206029	3
+3d7ce39b5d4b3e3d22aaafe9b633de51	5880836	3
+id	id
+id	id
 DROP table t1,t2;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index fc918012d1f..925f754f203 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3266,3 +3266,205 @@ select wss_type from t1 where wss_type =102935229216544093;
 wss_type
 102935229216544093
 drop table t1;
+create table t1 (a int not null auto_increment primary key);
+insert into t1 values ();
+insert into t1 values ();
+insert into t1 values ();
+select * from (t1 as t2 left join t1 as t3 using (a)), t1;
+a	a	a
+1	1	1
+2	2	1
+3	3	1
+1	1	2
+2	2	2
+3	3	2
+1	1	3
+2	2	3
+3	3	3
+select * from t1, (t1 as t2 left join t1 as t3 using (a));
+a	a	a
+1	1	1
+2	1	1
+3	1	1
+1	2	2
+2	2	2
+3	2	2
+1	3	3
+2	3	3
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
+a	a	a
+1	1	1
+2	2	1
+3	3	1
+1	1	2
+2	2	2
+3	3	2
+1	1	3
+2	2	3
+3	3	3
+select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
+a	a	a
+1	1	1
+2	1	1
+3	1	1
+1	2	2
+2	2	2
+3	2	2
+1	3	3
+2	3	3
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
+a	a	a
+1	1	2
+2	2	2
+3	3	2
+1	1	3
+2	2	3
+3	3	3
+select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+a	a	a
+1	1	NULL
+2	1	1
+3	1	1
+1	2	NULL
+2	2	2
+3	2	2
+1	3	NULL
+2	3	3
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
+a	a	a
+1	1	1
+2	2	2
+3	3	3
+select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
+a	a	a
+1	1	1
+2	1	NULL
+3	1	NULL
+1	2	NULL
+2	2	2
+3	2	NULL
+1	3	NULL
+2	3	NULL
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
+a	a	a
+1	1	2
+1	1	3
+2	2	2
+2	2	3
+3	3	2
+3	3	3
+select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+a	a	a
+1	1	NULL
+2	1	1
+3	1	1
+1	2	NULL
+2	2	2
+3	2	2
+1	3	NULL
+2	3	3
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
+a	a	a
+1	1	1
+2	2	2
+3	3	3
+select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
+a	a	a
+1	1	1
+2	1	NULL
+3	1	NULL
+1	2	NULL
+2	2	2
+3	2	NULL
+1	3	NULL
+2	3	NULL
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
+a	a	a
+1	1	1
+2	2	2
+3	3	3
+select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
+a	a	a
+1	1	1
+2	1	NULL
+3	1	NULL
+1	2	NULL
+2	2	2
+3	2	NULL
+1	3	NULL
+2	3	NULL
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
+a	a	a
+1	NULL	1
+2	NULL	1
+3	NULL	1
+1	1	2
+2	2	2
+3	3	2
+1	1	3
+2	2	3
+3	3	3
+select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+a	a	a
+2	1	1
+3	1	1
+2	2	2
+3	2	2
+2	3	3
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
+a	a	a
+1	1	1
+2	NULL	1
+3	NULL	1
+1	NULL	2
+2	2	2
+3	NULL	2
+1	NULL	3
+2	NULL	3
+3	3	3
+select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
+a	a	a
+1	1	1
+2	2	2
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
+a	a	a
+1	1	1
+2	NULL	1
+3	NULL	1
+1	NULL	2
+2	2	2
+3	NULL	2
+1	NULL	3
+2	NULL	3
+3	3	3
+select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
+a	a	a
+1	1	1
+2	2	2
+3	3	3
+select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
+a	a	a
+1	1	1
+2	1	NULL
+3	1	NULL
+1	2	NULL
+2	2	2
+3	2	NULL
+1	3	NULL
+2	3	NULL
+3	3	3
+select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
+a	a	a
+1	1	1
+2	2	2
+3	3	3
+drop table t1;
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index e5d8fc59e4f..3ab8b993f43 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -91,3 +91,47 @@ DELETE FROM t2 WHERE uniq_id IS NULL;
 SELECT * FROM t1 ORDER BY uniq_id, id;
 SELECT * FROM t2 ORDER BY uniq_id, id;
 DROP table t1,t2;
+
+#
+# This crashed MySQL 3.23.47
+#
+
+CREATE TABLE `t1` (
+  `order_id` char(32) NOT NULL default '',
+  `product_id` char(32) NOT NULL default '',
+  `product_type` int(11) NOT NULL default '0',
+  PRIMARY KEY  (`order_id`,`product_id`,`product_type`)
+) TYPE=MyISAM;
+CREATE TABLE `t2` (
+  `order_id` char(32) NOT NULL default '',
+  `product_id` char(32) NOT NULL default '',
+  `product_type` int(11) NOT NULL default '0',
+  PRIMARY KEY  (`order_id`,`product_id`,`product_type`)
+) TYPE=MyISAM;
+INSERT INTO t1 (order_id, product_id, product_type) VALUES
+('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3),
+('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3),
+('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
+INSERT INTO t2 (order_id, product_id, product_type) VALUES
+('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
+
+select t1.* from t1
+left join t2 using(order_id, product_id, product_type)
+where t2.order_id=NULL;
+select t1.* from t1
+left join t2 using(order_id, product_id, product_type)
+where t2.order_id is NULL;
+drop table t1,t2;
+
+#
+# The last select returned wrong results in 3.23.52
+#
+
+create table t1 (id int);
+insert into t1 values (null), (0);
+create table t2 (id int);
+insert into t2 values (null);
+select * from t1, t2 where t1.id = t2.id;
+alter table t1 add key id (id);
+select * from t1, t2 where t1.id = t2.id;
+drop table t1,t2;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 94806d44e37..15d44bcd672 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1751,3 +1751,49 @@ select wss_type from t1 where wss_type ='102935229216544104';
 select wss_type from t1 where wss_type ='102935229216544093';
 select wss_type from t1 where wss_type =102935229216544093;
 drop table t1;
+
+#
+# Test of removing redundant braces in the FROM part
+# (We test each construct with the braced join to the left and right;
+#  the latter case used to cause a syntax errors.)
+#
+
+create table t1 (a int not null auto_increment primary key);
+insert into t1 values ();
+insert into t1 values ();
+insert into t1 values ();
+# ,
+select * from (t1 as t2 left join t1 as t3 using (a)), t1;
+select * from t1, (t1 as t2 left join t1 as t3 using (a));
+# stright_join
+select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
+select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
+# inner join on
+select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
+select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+# inner join using
+select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
+select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
+# left [outer] join on
+select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
+select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+# left join using
+select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
+select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
+# natural left join
+select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
+select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
+# right join on
+select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
+select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+# right [outer] joing using
+select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
+select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
+# natural right join
+select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
+select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
+# natural join
+select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
+select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
+
+drop table t1;