some bug fixes and tests for those.

I am pushing now, because :

* I have a fix for crashing bug
* Sanja will destroy my changes in sub-select test ... ;)


mysql-test/r/derived.result:
  tests for recent bugs
mysql-test/r/subselect.result:
  tests for recent bugs
mysql-test/t/derived.test:
  tests for recent bugs
mysql-test/t/subselect.test:
  tests for recent bugs
sql/sql_derived.cc:
  Luckily, we have `make test` so that my last changes could be re-checked

4 files changed, 35 insertions, 9 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 0dce2b50819..95640e69d8b 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -30,6 +30,29 @@ SELECT 1 FROM (SELECT 1) a WHERE a=2;
 Unknown column 'a' in 'where clause'
 SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1  HAVING a=1) as a;
 Unknown column 'a' in 'having clause'
+select * from t1 as x1, (select * from t1) as x2;
+a	b	a	b
+1	a	1	a
+2	b	1	a
+3	c	1	a
+3	c	1	a
+1	a	2	b
+2	b	2	b
+3	c	2	b
+3	c	2	b
+1	a	3	c
+2	b	3	c
+3	c	3	c
+3	c	3	c
+1	a	3	c
+2	b	3	c
+3	c	3	c
+3	c	3	c
+explain select * from t1 as x1, (select * from t1) as x2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	x1	ALL	NULL	NULL	NULL	NULL	4	
 drop table if exists  t1.t2,t3;
 select * from (select 1) as a;
 1
@@ -66,7 +89,8 @@ a	t
 20	20
 explain select count(*) from t1 as tt1, (select * from t1) as tt2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Select tables optimized away
+1	PRIMARY								Select tables optimized away
+2	DERIVED	tt1	index	NULL	a	4	NULL	10000	Using index
 drop table if exists  t1;
 SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
 (SELECT * FROM (SELECT 1 as a) as a )
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 65221ab3f4b..3617f8c7782 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -32,15 +32,15 @@ a
 1
 SELECT 1 FROM (SELECT (SELECT a) b) c;
 Unknown column 'a' in 'field list'
-SELECT * FROM (SELECT 1 as id) WHERE id IN (SELECT * FROM (SELECT 1 as id) ORDER BY id LIMIT 1);
+SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id LIMIT 1);
 id
 1
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT 1,1);
+SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
 Cardinality error (more/less than 1 columns)
 SELECT 1 IN (SELECT 1);
 1 IN (SELECT 1)
 1
-SELECT 1 FROM (SELECT 1 as a) WHERE 1 IN (SELECT (SELECT a));
+SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
@@ -524,7 +524,7 @@ x	y
 4	2
 2	1
 drop table t1, t2;
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *);
+SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
 No tables used
 drop table if exists t;
 CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
@@ -536,7 +536,7 @@ EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	ref	id	id	5	const	1	Using where; Using index
 Warnings:
-Note	1246	Select 2 was reduced during optimisation
+Note	1247	Select 2 was reduced during optimisation
 SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
 id
 1
@@ -548,7 +548,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	ref	id	id	5	const	1	Using where; Using index
 3	SUBSELECT								No tables used
 Warnings:
-Note	1246	Select 2 was reduced during optimisation
+Note	1247	Select 2 was reduced during optimisation
 EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	NULL	id	5	NULL	2	Using where; Using index
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 5c2d0157f14..8f187dd46ba 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -18,6 +18,8 @@ SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
 SELECT 1 FROM (SELECT 1) a WHERE a=2;
 --error 1054
 SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1  HAVING a=1) as a;
+select * from t1 as x1, (select * from t1) as x2;
+explain select * from t1 as x1, (select * from t1) as x2;
 drop table if exists  t1.t2,t3;
 select * from (select 1) as a;
 select a from (select 1 as a) as b;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3f997aa3d35..3e01dc5f5e9 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -17,7 +17,7 @@ SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
 SELECT 1 FROM (SELECT (SELECT a) b) c;
 SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id LIMIT 1);
 -- error 1239
-SELECT * FROM (SELECT 1)a  WHERE 1 IN (SELECT 1,1);
+SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
 SELECT 1 IN (SELECT 1);
 SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
@@ -313,7 +313,7 @@ select * from t1;
 drop table t1, t2;
 
 -- error 1096
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *);
+SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
 drop table if exists t;
 CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
 INSERT INTO t VALUES (1),(2);