Merge mysql.com:/home/pz/mysql/mysql-4.1-root

into mysql.com:/home/pz/mysql/mysql-4.1

12 files changed, 235 insertions, 152 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 86a8eb2dd22..ead9935f824 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1,3 +1,5 @@
+SELECT 1 FROM (SELECT 1) GROUP BY SUM(1);
+Invalid use of group function
 drop table if exists t1,t2,t3;
 CREATE TABLE t1 (
 spID int(10) unsigned,
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 154f8ac9667..5a8ff20aa33 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -17,7 +17,22 @@ SELECT (SELECT 1),MAX(1) FROM (SELECT 1);
 1	1
 SELECT (SELECT a) as a;
 Reference 'a' not supported (forward reference in item list)
-drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
+EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+3	DEPENDENT SUBSELECT								No tables used
+2	DERIVED								No tables used
+SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+1
+1
+SELECT (SELECT 1), a;
+Unknown column 'a' in 'field list'
+SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1;
+a
+1
+SELECT 1 FROM (SELECT (SELECT a));
+Unknown column 'a' in 'field list'
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 create table t1 (a int);
 create table t2 (a int, b int);
 create table t3 (a int);
@@ -140,11 +155,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
 Subselect returns more than 1 record
-create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq));
-create table clinic( uq int primary key, name char(25));
-insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
-insert into attend values (1,1),(1,2),(2,2),(1,3);
-select * from attend where exists (select * from clinic where uq = clinic_uq);
+create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
+create table t7( uq int primary key, name char(25));
+insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
+insert into t6 values (1,1),(1,2),(2,2),(1,3);
+select * from t6 where exists (select * from t7 where uq = clinic_uq);
 patient_uq	clinic_uq
 1	1
 1	2
@@ -167,117 +182,113 @@ W	1
 SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
 a	b
 W	a
-drop table if exists inscrit;
-CREATE TABLE `inscrit` (
+drop table if exists t8;
+CREATE TABLE `t8` (
 `pseudo` varchar(35) character set latin1 NOT NULL default '',
 `email` varchar(60) character set latin1 NOT NULL default '',
 PRIMARY KEY  (`pseudo`),
 UNIQUE KEY `email` (`email`)
 ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
-INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
-INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
-INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');
-EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
+INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
+INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
+INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
+EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	inscrit	const	PRIMARY	PRIMARY	35	const	1	
-4	SUBSELECT	inscrit	const	PRIMARY	PRIMARY	35	const	1	
-2	SUBSELECT	inscrit	const	PRIMARY	PRIMARY	35	const	1	
-3	SUBSELECT	inscrit	const	PRIMARY	PRIMARY	35	const	1	
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
-inscrit WHERE pseudo='joce');
+1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
+4	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
+2	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
+3	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
+t8 WHERE pseudo='joce');
 Subselect returns more than 1 field
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
 pseudo='joce');
 Subselect returns more than 1 field
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 pseudo
 joce
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%');
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
 Subselect returns more than 1 record
-drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
-drop table if exists searchconthardwarefr3;
-CREATE TABLE `searchconthardwarefr3` (
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
+CREATE TABLE `t1` (
 `topic` mediumint(8) unsigned NOT NULL default '0',
 `date` date NOT NULL default '0000-00-00',
 `pseudo` varchar(35) character set latin1 NOT NULL default '',
 PRIMARY KEY  (`pseudo`,`date`,`topic`),
 KEY `topic` (`topic`)
 ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
-INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES
+INSERT INTO t1 (topic,date,pseudo) VALUES
 ('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
-EXPLAIN SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03';
+EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	searchconthardwarefr3	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
-EXPLAIN SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
+1	SIMPLE	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
+EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY								No tables used
-2	SUBSELECT	searchconthardwarefr3	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
-SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03';
+2	SUBSELECT	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
+SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 date
 2002-08-03
-SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
-(SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03')
+SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
+(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
 2002-08-03
-SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
+SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
 1
 1
 1
 1
-SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
+SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
 Subselect returns more than 1 record
-EXPLAIN SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1);
+EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	searchconthardwarefr3	index	NULL	topic	3	NULL	2	Using index
+1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
 2	SUBSELECT								No tables used
 3	UNION								No tables used
-drop table searchconthardwarefr3;
-drop table if exists forumconthardwarefr7, searchconthardwarefr7;
-CREATE TABLE `forumconthardwarefr7` (
+drop table t1;
+CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
 `maxnumrep` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`numeropost`),
 UNIQUE KEY `maxnumrep` (`maxnumrep`)
 ) TYPE=MyISAM ROW_FORMAT=FIXED;
-INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
-CREATE TABLE `searchconthardwarefr7` (
+INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
+CREATE TABLE `t2` (
 `mot` varchar(30) NOT NULL default '',
 `topic` mediumint(8) unsigned NOT NULL default '0',
 `date` date NOT NULL default '0000-00-00',
 `pseudo` varchar(35) NOT NULL default '',
 PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
 ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
-INSERT INTO searchconthardwarefr7 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
-select numeropost as a FROM forumconthardwarefr7 GROUP BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
+INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
+select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
 a
 40143
-SELECT numeropost,maxnumrep FROM forumconthardwarefr7 WHERE exists (SELECT 1 FROM searchconthardwarefr7 WHERE (mot='joce') AND date >= '2002-10-21' AND forumconthardwarefr7.numeropost = searchconthardwarefr7.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
+SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
 numeropost	maxnumrep
 43506	2
 40143	1
-SELECT (SELECT 1) as a FROM (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
+SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
 Unknown column 'a' in 'having clause'
-drop table forumconthardwarefr7, searchconthardwarefr7;
-drop table if exists forumconthardwarefr7;
-CREATE TABLE `forumconthardwarefr7` (
+drop table t1, t2;
+drop table if exists t1;
+CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
 `maxnumrep` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`numeropost`),
 UNIQUE KEY `maxnumrep` (`maxnumrep`)
 ) TYPE=MyISAM ROW_FORMAT=FIXED;
-INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (1,0),(2,1);
-select numeropost as a FROM forumconthardwarefr7 GROUP BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
+INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
+select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
 Subselect returns more than 1 record
-select numeropost as a FROM forumconthardwarefr7 ORDER BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
+select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
 Subselect returns more than 1 record
-drop table if exists forumconthardwarefr7;
-drop table if exists iftest;
-CREATE TABLE iftest (field char(1) NOT NULL DEFAULT 'b');
-INSERT INTO iftest VALUES ();
-SELECT field FROM iftest WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
+drop table t1;
+CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
+INSERT INTO t1 VALUES ();
+SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
 Subselect returns more than 1 record
-drop table iftest;
-drop table if exists threadhardwarefr7;
-CREATE TABLE `threadhardwarefr7` (
+drop table t1;
+CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL default '0',
 `numreponse` int(10) unsigned NOT NULL auto_increment,
 `pseudo` varchar(35) NOT NULL default '',
@@ -285,20 +296,26 @@ PRIMARY KEY  (`numeropost`,`numreponse`),
 UNIQUE KEY `numreponse` (`numreponse`),
 KEY `pseudo` (`pseudo`,`numeropost`)
 ) TYPE=MyISAM;
-SELECT (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=a),numreponse FROM (SELECT * FROM threadhardwarefr7) as a;
+SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
 Reference 'numreponse' not supported (forward reference in item list)
-SELECT numreponse, (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=a) FROM (SELECT * FROM threadhardwarefr7) as a;
+SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
 Unknown column 'a' in 'having clause'
-SELECT numreponse, (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=1) FROM (SELECT * FROM threadhardwarefr7) as a;
-numreponse	(SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=1)
-INSERT INTO threadhardwarefr7 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
-EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM threadhardwarefr7 WHERE numeropost='1');
+SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
+numreponse	(SELECT numeropost FROM t1 HAVING numreponse=1)
+INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
+EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 Subselect returns more than 1 record
-EXPLAIN SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost='1';
+EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE								Select tables optimized away
-EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost='1');
+EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	threadhardwarefr7	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
 2	SUBSELECT								Select tables optimized away
-drop table if exists threadhardwarefrtest7;
+drop table t1;
+CREATE TABLE t1 (a int(1));
+INSERT INTO t1 VALUES (1);
+SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1;
+1
+1
+drop table t1;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 2f2f50c4085..0f30fbd4cc6 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1,3 +1,5 @@
+-- error 1111
+SELECT 1 FROM (SELECT 1) GROUP BY SUM(1);
 #
 # Test of group (Failed for Lars Hoss <lh@pbm.de>)
 #
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index f838a27e7ff..00f3a4f6e44 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -8,7 +8,14 @@ SELECT (SELECT 1 FROM (SELECT 1) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVI
 SELECT (SELECT 1),MAX(1) FROM (SELECT 1);
 -- error 1245
 SELECT (SELECT a) as a;
-drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
+EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+-- error 1054
+SELECT (SELECT 1), a;
+SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1;
+-- error 1054
+SELECT 1 FROM (SELECT (SELECT a));
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 create table t1 (a int);
 create table t2 (a int, b int);
 create table t3 (a int);
@@ -57,11 +64,11 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)
 explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
 -- error 1240
 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
-create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq));
-create table clinic( uq int primary key, name char(25));
-insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
-insert into attend values (1,1),(1,2),(2,2),(1,3);
-select * from attend where exists (select * from clinic where uq = clinic_uq);
+create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
+create table t7( uq int primary key, name char(25));
+insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
+insert into t6 values (1,1),(1,2),(2,2),(1,3);
+select * from t6 where exists (select * from t7 where uq = clinic_uq);
 
 # not unique fields
 -- error 1052
@@ -80,62 +87,62 @@ SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
 SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
 SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
 
-drop table if exists inscrit;
+drop table if exists t8;
 
-CREATE TABLE `inscrit` (
+CREATE TABLE `t8` (
   `pseudo` varchar(35) character set latin1 NOT NULL default '',
   `email` varchar(60) character set latin1 NOT NULL default '',
   PRIMARY KEY  (`pseudo`),
   UNIQUE KEY `email` (`email`)
 ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
-INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
-INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
-INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');
-EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
+INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
+INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
+INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
+EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 -- error 1239
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
-inscrit WHERE pseudo='joce');
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
+t8 WHERE pseudo='joce');
 -- error 1239
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
 pseudo='joce');
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 -- error 1240
-SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%');
+SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
 
-drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 
-drop table if exists searchconthardwarefr3;
-CREATE TABLE `searchconthardwarefr3` (
+#searchconthardwarefr3
+CREATE TABLE `t1` (
   `topic` mediumint(8) unsigned NOT NULL default '0',
   `date` date NOT NULL default '0000-00-00',
   `pseudo` varchar(35) character set latin1 NOT NULL default '',
   PRIMARY KEY  (`pseudo`,`date`,`topic`),
   KEY `topic` (`topic`)
 ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
-INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES
+INSERT INTO t1 (topic,date,pseudo) VALUES
 ('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
-EXPLAIN SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03';
-EXPLAIN SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
-SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03';
-SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
-SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
+EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
+EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
+SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
+SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
+SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
 -- error 1240
-SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
-EXPLAIN SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1);
-drop table searchconthardwarefr3;
+SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
+EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
+drop table t1;
 
-drop table if exists forumconthardwarefr7, searchconthardwarefr7;
-CREATE TABLE `forumconthardwarefr7` (
+#forumconthardwarefr7 searchconthardwarefr7
+CREATE TABLE `t1` (
   `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
   `maxnumrep` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY  (`numeropost`),
   UNIQUE KEY `maxnumrep` (`maxnumrep`)
 ) TYPE=MyISAM ROW_FORMAT=FIXED;
 
-INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
+INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
 
-CREATE TABLE `searchconthardwarefr7` (
+CREATE TABLE `t2` (
       `mot` varchar(30) NOT NULL default '',
       `topic` mediumint(8) unsigned NOT NULL default '0',
       `date` date NOT NULL default '0000-00-00',
@@ -143,37 +150,38 @@ CREATE TABLE `searchconthardwarefr7` (
        PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
     ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
 
-INSERT INTO searchconthardwarefr7 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
-select numeropost as a FROM forumconthardwarefr7 GROUP BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
-SELECT numeropost,maxnumrep FROM forumconthardwarefr7 WHERE exists (SELECT 1 FROM searchconthardwarefr7 WHERE (mot='joce') AND date >= '2002-10-21' AND forumconthardwarefr7.numeropost = searchconthardwarefr7.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
+INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
+select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
+SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
 -- error 1054
-SELECT (SELECT 1) as a FROM (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
-drop table forumconthardwarefr7, searchconthardwarefr7;
+SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
+drop table t1, t2;
 
-drop table if exists forumconthardwarefr7;
-CREATE TABLE `forumconthardwarefr7` (
+#forumconthardwarefr7
+drop table if exists t1;
+CREATE TABLE `t1` (
   `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
   `maxnumrep` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY  (`numeropost`),
   UNIQUE KEY `maxnumrep` (`maxnumrep`)
 ) TYPE=MyISAM ROW_FORMAT=FIXED;
 
-INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (1,0),(2,1);
+INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
 -- error 1240
-select numeropost as a FROM forumconthardwarefr7 GROUP BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
+select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
 -- error 1240
-select numeropost as a FROM forumconthardwarefr7 ORDER BY (SELECT 1 FROM forumconthardwarefr7 HAVING a=1);
-drop table if exists forumconthardwarefr7;
+select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
+drop table t1;
 
-drop table if exists iftest;
-CREATE TABLE iftest (field char(1) NOT NULL DEFAULT 'b');
-INSERT INTO iftest VALUES ();
+#iftest
+CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
+INSERT INTO t1 VALUES ();
 -- error 1240
-SELECT field FROM iftest WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
-drop table iftest;
+SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
+drop table t1;
 
-drop table if exists threadhardwarefr7;
-CREATE TABLE `threadhardwarefr7` (
+# threadhardwarefr7
+CREATE TABLE `t1` (
   `numeropost` mediumint(8) unsigned NOT NULL default '0',
   `numreponse` int(10) unsigned NOT NULL auto_increment,
   `pseudo` varchar(35) NOT NULL default '',
@@ -182,13 +190,17 @@ CREATE TABLE `threadhardwarefr7` (
   KEY `pseudo` (`pseudo`,`numeropost`)
 ) TYPE=MyISAM;
 -- error 1245
-SELECT (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=a),numreponse FROM (SELECT * FROM threadhardwarefr7) as a;
+SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
 -- error 1054
-SELECT numreponse, (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=a) FROM (SELECT * FROM threadhardwarefr7) as a;
-SELECT numreponse, (SELECT numeropost FROM threadhardwarefr7 HAVING numreponse=1) FROM (SELECT * FROM threadhardwarefr7) as a;
-INSERT INTO threadhardwarefr7 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
+SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
+SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
+INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
 -- error 1240
-EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM threadhardwarefr7 WHERE numeropost='1');
-EXPLAIN SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost='1';
-EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost='1');
-drop table if exists threadhardwarefrtest7;
+EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
+EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
+EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
+drop table t1;
+CREATE TABLE t1 (a int(1));
+INSERT INTO t1 VALUES (1);
+SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1;
+drop table t1;
\ No newline at end of file
diff --git a/sql/field.h b/sql/field.h
index 8c4c48968c4..9fc72cf56ec 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -126,10 +126,12 @@ public:
     Field *tmp= (Field*) memdup_root(root,(char*) this,size_of());
     if (tmp)
     {
-      tmp->table=new_table;
-      tmp->key_start=tmp->part_of_key=tmp->part_of_sortkey=0;
+      tmp->table= new_table;
+      tmp->key_start= tmp->part_of_key= tmp->part_of_sortkey= 0;
       tmp->unireg_check=Field::NONE;
-      tmp->flags&= (NOT_NULL_FLAG | BLOB_FLAG | UNSIGNED_FLAG | ZEROFILL_FLAG | ENUM_FLAG | SET_FLAG);
+      tmp->flags&= (NOT_NULL_FLAG | BLOB_FLAG | UNSIGNED_FLAG |
+		    ZEROFILL_FLAG | ENUM_FLAG | SET_FLAG);
+      tmp->table_name= new_table->table_name;
       tmp->reset_fields();
     }
     return tmp;
diff --git a/sql/item.cc b/sql/item.cc
index 3ec6e9a3aa9..df42b9185cc 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -468,11 +468,12 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 					 (last= sl)->get_table_list(),
 					 0)) != not_found_field)
 	    break;
-	  if((refer= find_item_in_list(this, (last= sl)->item_list,
+	  if ((refer= find_item_in_list(this, sl->item_list,
 				       REPORT_EXCEPT_NOT_FOUND)) !=
 	     (Item **)not_found_item)
 	    break;
-	  
+	  if (sl->linkage == DERIVED_TABLE_TYPE)
+	    break; // do not look over derived table
 	}
       if (!tmp)
 	return -1;
@@ -487,7 +488,7 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
       else if (refer != (Item **)not_found_item)
       {
 	Item_ref *r;
-	*ref= r= new Item_ref((char *)db_name, (char *)table_name,
+	*ref= r= new Item_ref(refer, (char *)table_name,
 			   (char *)field_name);
 	if (!r)
 	  return 1;
@@ -867,6 +868,7 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
 				  REPORT_ALL_ERRORS))) ==
 	(Item **)not_found_item)
     {
+      Field *tmp= (Field*) not_found_field;
       /*
 	We can't find table field in table list of current select,
 	consequently we have to find it in outer subselect(s).
@@ -878,16 +880,23 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
       */
       SELECT_LEX *last=0;
       for ( ; sl ; sl= sl->outer_select())
-	if((ref= find_item_in_list(this, (last= sl)->item_list,
+      {
+	if ((ref= find_item_in_list(this, (last= sl)->item_list,
 				   REPORT_EXCEPT_NOT_FOUND)) !=
 	   (Item **)not_found_item)
 	  break;
+	if ((tmp= find_field_in_tables(thd, this,
+				       sl->get_table_list(),
+				       0)) != not_found_field);
+	if (sl->linkage == DERIVED_TABLE_TYPE)
+	  break; // do not look over derived table
+      }
 
       if (!ref)
-      {
 	return 1;
-      }
-      else if (ref == (Item **)not_found_item)
+      else if (!tmp)
+	return -1;
+      else if (ref == (Item **)not_found_item && tmp == not_found_field)
       {
 	// Call to report error
 	find_item_in_list(this,
@@ -896,6 +905,16 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
         ref= 0;
 	return 1;
       }
+      else if (tmp != not_found_field)
+      {
+	ref= 0; // To prevent "delete *ref;" on ~Item_erf() of this item
+	Item_field* f;
+	if (!((*reference)= f= new Item_field(tmp)))
+	  return 1;
+	f->depended_from= last;
+	thd->lex.current_select->mark_as_dependent(last);
+	return 0;
+      }
       else
       {
 	depended_from= last;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 01ce5fad4a6..7650ecc01c0 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -81,6 +81,7 @@ void Item_subselect::make_field (Send_field *tmp_field)
 
 bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 {
+  char const *save_where= thd->where;
   int res= engine->prepare();
   if (!res)
   {
@@ -93,6 +94,7 @@ bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
     fix_length_and_dec();
   }
   fixed= 1;
+  thd->where= save_where;
   return res;
 }
 
@@ -312,11 +314,13 @@ void subselect_union_engine::fix_length_and_dec()
 int subselect_single_select_engine::exec()
 {
   DBUG_ENTER("subselect_single_select_engine::exec");
+  char const *save_where= join->thd->where;
   if (!optimized)
   {
     optimized=1;
     if (join->optimize())
     {
+      join->thd->where= save_where;
       executed= 1;
       DBUG_RETURN(join->error?join->error:1);
     }
@@ -324,7 +328,10 @@ int subselect_single_select_engine::exec()
   if (select_lex->dependent && executed)
   {
     if (join->reinit())
+    {
+      join->thd->where= save_where;
       DBUG_RETURN(1);
+    }
     item->assign_null();
     item->assigned((executed= 0));
   }
@@ -335,14 +342,19 @@ int subselect_single_select_engine::exec()
     join->exec();
     join->thd->lex.current_select= save_select;
     executed= 1;
+    join->thd->where= save_where;
     DBUG_RETURN(join->error||thd->fatal_error);
   }
+  join->thd->where= save_where;
   DBUG_RETURN(0);
 }
 
 int subselect_union_engine::exec()
 {
-  return unit->exec();
+  char const *save_where= unit->thd->where;
+  int res= unit->exec();
+  unit->thd->where= save_where;
+  return res;
 }
 
 uint subselect_single_select_engine::cols()
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index ce735495b9e..dab983353a0 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -464,7 +464,7 @@ bool table_is_used(TABLE *table, bool wait_for_name_lock);
 bool drop_locked_tables(THD *thd,const char *db, const char *table_name);
 void abort_locked_tables(THD *thd,const char *db, const char *table_name);
 extern const Field *not_found_field;
-Field *find_field_in_tables(THD *thd, Item_field *item, TABLE_LIST *tables,
+Field *find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
 			    bool report_error);
 Field *find_field_in_table(THD *thd,TABLE *table,const char *name,uint length,
 			   bool check_grant,bool allow_rowid);
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index b6f81ab07eb..cee5502fb32 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -932,7 +932,7 @@ get_mm_leaf(PARAM *param, Field *field, KEY_PART *key_part,
     String tmp(buff1,sizeof(buff1),default_charset_info),*res;
     uint length,offset,min_length,max_length;
 
-    if (!field->optimize_range((uint) key_part->key))
+    if (!field->optimize_range(param->real_keynr[key_part->key]))
       DBUG_RETURN(0);				// Can't optimize this
     if (!(res= value->val_str(&tmp)))
       DBUG_RETURN(&null_element);
@@ -1002,7 +1002,7 @@ get_mm_leaf(PARAM *param, Field *field, KEY_PART *key_part,
     DBUG_RETURN(tree);
   }
 
-  if (!field->optimize_range((uint) key_part->key) &&
+  if (!field->optimize_range(param->real_keynr[key_part->key]) &&
       type != Item_func::EQ_FUNC &&
       type != Item_func::EQUAL_FUNC)
     DBUG_RETURN(0);				// Can't optimize this
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 7b434eb0bfb..08a17ed1197 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1861,7 +1861,7 @@ const Field *not_found_field= (Field*) 0x1;
 */
 
 Field *
-find_field_in_tables(THD *thd, Item_field *item, TABLE_LIST *tables,
+find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
 		     bool report_error)
 {
   Field *found=0;
@@ -2061,9 +2061,9 @@ int setup_fields(THD *thd, TABLE_LIST *tables, List<Item> &fields,
     if (item->type() == Item::FIELD_ITEM &&
 	((Item_field*) item)->field_name[0] == '*')
     {
-      uint elem=fields.elements;
+      uint elem= fields.elements;
       if (insert_fields(thd,tables,((Item_field*) item)->db_name,
-			((Item_field*) item)->table_name,&it))
+			((Item_field*) item)->table_name, &it))
 	DBUG_RETURN(-1); /* purecov: inspected */
       if (sum_func_list)
       {
@@ -2079,6 +2079,7 @@ int setup_fields(THD *thd, TABLE_LIST *tables, List<Item> &fields,
     {
       if (item->fix_fields(thd, tables, it.ref()))
 	DBUG_RETURN(-1); /* purecov: inspected */
+      item= *(it.ref()); //Item can be chenged in fix fields
       if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM &&
 	  sum_func_list)
 	item->split_sum_func(*sum_func_list);
@@ -2227,8 +2228,6 @@ int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds)
   thd->set_query_id=1;
   
   thd->cond_count= 0;
-  bool save_allow_sum_func= thd->allow_sum_func;
-  thd->allow_sum_func= 0;
   if (*conds)
   {
     thd->where="where clause";
@@ -2301,7 +2300,6 @@ int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds)
 	table->on_expr=and_conds(table->on_expr,cond_and);
     }
   }
-  thd->allow_sum_func= save_allow_sum_func;
   DBUG_RETURN(test(thd->fatal_error));
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index dd41af4b250..53bd0c0489a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -294,6 +294,7 @@ public:
   int cleanup();
   
   friend void mysql_init_query(THD *thd);
+  friend int subselect_union_engine::exec();
 private:
   bool create_total_list_n_last_return(THD *thd, st_lex *lex,
 				       TABLE_LIST ***result);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 20b000392df..db0219ae23c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -199,6 +199,26 @@ void fix_tables_pointers(SELECT_LEX *select_lex)
   }
 }
 
+/*
+  Inline function to setup clauses without sum functions
+*/
+inline int setup_without_group(THD *thd, TABLE_LIST *tables,
+			       List<Item> &fields,
+			       List<Item> &all_fields,
+			       COND **conds,
+			       ORDER *order,
+			       ORDER *group, bool *hidden_group_fields)
+{
+  bool save_allow_sum_func= thd->allow_sum_func;
+  thd->allow_sum_func= 0;
+  int res= (setup_conds(thd,tables, conds) ||
+	    setup_order(thd,tables, fields, all_fields, order) ||
+	    setup_group(thd,tables, fields, all_fields, group,
+			hidden_group_fields));
+  thd->allow_sum_func= save_allow_sum_func;
+  return res;
+}
+
 /*****************************************************************************
   Check fields, find best join, do the select and output fields.
   mysql_select assumes that all tables are already opened
@@ -233,10 +253,8 @@ JOIN::prepare(TABLE_LIST *tables_init,
 
   if (setup_tables(tables_list) ||
       setup_fields(thd,tables_list,fields_list,1,&all_fields,1) ||
-      setup_conds(thd,tables_list,&conds) ||
-      setup_order(thd,tables_list,fields_list,all_fields,order) ||
-      setup_group(thd,tables_list,fields_list,all_fields,group_list,
-                &hidden_group_fields))
+      setup_without_group(thd, tables_list, fields_list, all_fields,
+			  &conds, order, group_list, &hidden_group_fields))
     DBUG_RETURN(-1);				/* purecov: inspected */
 
   if (having)