Merge

mysql-test/r/subselect.result:
  Auto merged
mysql-test/t/subselect.test:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_subselect.h:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_select.h:
  Auto merged
sql/item_subselect.cc:
  SCCS merged

20 files changed, 349 insertions, 160 deletions

diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok
index 9df789c0340..9fa17da17a1 100644
--- a/BitKeeper/etc/logging_ok
+++ b/BitKeeper/etc/logging_ok
@@ -75,6 +75,7 @@ monty@hundin.mysql.fi
 monty@mashka.(none)
 monty@mashka.mysql.fi
 monty@mishka.mysql.fi
+monty@mysql.com
 monty@narttu.
 monty@narttu.mysql.fi
 monty@rescue.
diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am
index 2babb6fba66..d23a9919b48 100644
--- a/mysql-test/Makefile.am
+++ b/mysql-test/Makefile.am
@@ -73,6 +73,7 @@ SUFFIXES = .sh
 	  -e 's!@''PERL''@!@PERL@!' \
 	  -e 's!@''VERSION''@!@VERSION@!' \
 	  -e 's!@''MYSQL_BASE_VERSION''@!@MYSQL_BASE_VERSION@!' \
+	  -e 's!@''MYSQL_UNIX_ADDR''@!@MYSQL_UNIX_ADDR@!' \
 	  -e 's!@''MYSQL_NO_DASH_VERSION''@!@MYSQL_NO_DASH_VERSION@!' \
 	  -e 's!@''MYSQL_SERVER_SUFFIX''@!@MYSQL_SERVER_SUFFIX@!' \
 	  $< > $@-t
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index b7e39549411..94affe6b2f7 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -15,6 +15,7 @@ VERBOSE=""
 USE_MANAGER=0
 MY_TZ=GMT-3
 TZ=$MY_TZ; export TZ # for UNIX_TIMESTAMP tests to work
+LOCAL_SOCKET=@MYSQL_UNIX_ADDR@
 
 # For query_cache test
 ulimit -n 1024
@@ -226,7 +227,7 @@ while test $# -gt 0; do
     --slave-binary=*)
       SLAVE_MYSQLD=`$ECHO "$1" | $SED -e "s;--slave-binary=;;"` ;;
     --local)   USE_RUNNING_SERVER="" ;;
-    --extern)   USE_RUNNING_SERVER="1" ;;
+    --extern)  USE_RUNNING_SERVER="1" ;;
     --tmpdir=*) MYSQL_TMP_DIR=`$ECHO "$1" | $SED -e "s;--tmpdir=;;"` ;;
     --local-master)
       MASTER_MYPORT=3306;
@@ -256,6 +257,7 @@ while test $# -gt 0; do
     --start-and-exit)
      START_AND_EXIT=1
      ;;
+    --socket=*) LOCAL_SOCKET=`$ECHO "$1" | $SED -e "s;--socket=;;"` ;;
     --skip-rpl) NO_SLAVE=1 ;;
     --skip-test=*) SKIP_TEST=`$ECHO "$1" | $SED -e "s;--skip-test=;;"`;;
     --do-test=*) DO_TEST=`$ECHO "$1" | $SED -e "s;--do-test=;;"`;;
@@ -501,7 +503,7 @@ then
 fi
 if [ -n "$USE_RUNNING_SERVER" ]
 then
-   MASTER_MYSOCK="/tmp/mysql.sock"
+   MASTER_MYSOCK=$LOCAL_SOCKET;
    DBUSER=${DBUSER:-test}
 else
    DBUSER=${DBUSER:-root}		# We want to do FLUSH xxx commands
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 048464e6b84..31b5ff84365 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1267,31 +1267,6 @@ drop table if exists t1;
 (SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
 a
 1
-CREATE TABLE t1
-(
-FOLDERID VARCHAR(32)BINARY NOT NULL
-, FOLDERNAME VARCHAR(255)BINARY NOT NULL
-, CREATOR VARCHAR(255)BINARY
-, CREATED TIMESTAMP NOT NULL
-, DESCRIPTION VARCHAR(255)BINARY
-, FOLDERTYPE INTEGER NOT NULL
-, MODIFIED TIMESTAMP
-, MODIFIER VARCHAR(255)BINARY
-, FOLDERSIZE INTEGER NOT NULL
-, PARENTID VARCHAR(32)BINARY
-, REPID VARCHAR(32)BINARY
-, ORIGINATOR INTEGER
-, PRIMARY KEY ( FOLDERID )
-) TYPE=InnoDB;
-CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID);
-CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID);
-INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");
-INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");
-INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
-SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');
-'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1')
-0
-drop table t1;
 create table t1 (a int not null, b int, primary key (a));
 create table t2 (a int not null, primary key (a));
 create table t3 (a int not null, b int, primary key (a));
@@ -1411,20 +1386,8 @@ create table t2 (s1 int);
 insert into t1 values (1);
 insert into t2 values (1);
 update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
-ERROR 42S02: Unknown table 'x' in field list
+ERROR 42S22: Unknown column 'x.s1' in 'field list'
 DROP TABLE t1, t2;
-create table t1 (a int) type=innodb;
-create table t2 (a int) type=innodb;
-create table t3 (a int) type=innodb;
-insert into t1 values (1),(2),(3),(4);
-insert into t2 values (10),(20),(30),(40);
-insert into t3 values (1),(2),(10),(50);
-select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30);
-a
-1
-2
-10
-drop table t1,t2,t3;
 CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
 s2 CHAR(5) COLLATE latin1_swedish_ci);
 INSERT INTO t1 VALUES ('z','?');
@@ -1579,3 +1542,30 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
 3	UNION	t1	system	NULL	NULL	NULL	NULL	1	
 drop table t1;
+CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
+CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
+select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;
+phone	code
+69294728265	6
+18621828126	1862
+89356874041	NULL
+95895001874	NULL
+drop table t1, t2;
+create table t1 (s1 int);
+create table t2 (s1 int);
+select * from t1 where (select count(*) from t2 where t1.s2) = 1;
+ERROR 42S22: Unknown column 't1.s2' in 'where clause'
+select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
+ERROR 42S22: Unknown column 't1.s2' in 'group statement'
+select count(*) from t2 group by t1.s2;
+ERROR 42S02: Unknown table 't1' in group statement
+drop table t1, t2;
+CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
+CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
+INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
+INSERT INTO t2 VALUES (100, 200, 'C');
+SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
+COLC
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
new file mode 100644
index 00000000000..2f5b92fd05d
--- /dev/null
+++ b/mysql-test/r/subselect_innodb.result
@@ -0,0 +1,65 @@
+drop table if exists t1,t2,t3;
+CREATE TABLE t1
+(
+FOLDERID VARCHAR(32)BINARY NOT NULL
+, FOLDERNAME VARCHAR(255)BINARY NOT NULL
+, CREATOR VARCHAR(255)BINARY
+, CREATED TIMESTAMP NOT NULL
+, DESCRIPTION VARCHAR(255)BINARY
+, FOLDERTYPE INTEGER NOT NULL
+, MODIFIED TIMESTAMP
+, MODIFIER VARCHAR(255)BINARY
+, FOLDERSIZE INTEGER NOT NULL
+, PARENTID VARCHAR(32)BINARY
+, REPID VARCHAR(32)BINARY
+, ORIGINATOR INTEGER
+, PRIMARY KEY ( FOLDERID )
+) TYPE=InnoDB;
+CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID);
+CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID);
+INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");
+INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");
+INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');
+'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1')
+0
+drop table t1;
+create table t1 (a int) type=innodb;
+create table t2 (a int) type=innodb;
+create table t3 (a int) type=innodb;
+insert into t1 values (1),(2),(3),(4);
+insert into t2 values (10),(20),(30),(40);
+insert into t3 values (1),(2),(10),(50);
+select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30);
+a
+1
+2
+10
+drop table t1,t2,t3;
+CREATE TABLE t1 (
+processor_id INTEGER NOT NULL,
+PRIMARY KEY (processor_id)
+) TYPE=InnoDB;
+CREATE TABLE t3 (
+yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
+login_processor INTEGER UNSIGNED ,
+PRIMARY KEY (yod_id)
+) TYPE=InnoDB;
+CREATE TABLE t2 (
+processor_id INTEGER NOT NULL,
+yod_id BIGINT UNSIGNED NOT NULL,
+PRIMARY KEY (processor_id, yod_id),
+INDEX (processor_id),
+INDEX (yod_id),
+FOREIGN KEY (processor_id) REFERENCES t1(processor_id),
+FOREIGN KEY (yod_id) REFERENCES t3(yod_id) 
+) TYPE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1,1),(2,2),(3,3);
+INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
+SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1;
+processor_id	(SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id)
+1	1
+2	1
+3	1
+drop table t1,t2,t3;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index bc49f0c28cd..f356e7931c8 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -785,36 +785,6 @@ drop table if exists t1;
 (SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
 
 #
-# key field overflow test
-#
---disable_warnings
-CREATE TABLE t1
-(
-FOLDERID VARCHAR(32)BINARY NOT NULL
-, FOLDERNAME VARCHAR(255)BINARY NOT NULL
-, CREATOR VARCHAR(255)BINARY
-, CREATED TIMESTAMP NOT NULL
-, DESCRIPTION VARCHAR(255)BINARY
-, FOLDERTYPE INTEGER NOT NULL
-, MODIFIED TIMESTAMP
-, MODIFIER VARCHAR(255)BINARY
-, FOLDERSIZE INTEGER NOT NULL
-, PARENTID VARCHAR(32)BINARY
-, REPID VARCHAR(32)BINARY
-, ORIGINATOR INTEGER
-
-, PRIMARY KEY ( FOLDERID )
-) TYPE=InnoDB;
---enable_warnings
-CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID);
-CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID);
-INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");
-INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");
-INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
-SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');
-drop table t1;
-
-#
 # IN subselect optimization test
 #
 create table t1 (a int not null, b int, primary key (a));
@@ -887,25 +857,11 @@ create table t1 (s1 int);
 create table t2 (s1 int);
 insert into t1 values (1);
 insert into t2 values (1);
--- error 1109
+-- error 1054
 update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
 DROP TABLE t1, t2;
 
 #
-# UNION unlocking test
-#
---disable_warnings
-create table t1 (a int) type=innodb;
-create table t2 (a int) type=innodb;
-create table t3 (a int) type=innodb;
---enable_warnings
-insert into t1 values (1),(2),(3),(4);
-insert into t2 values (10),(20),(30),(40);
-insert into t3 values (1),(2),(10),(50);
-select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30);
-drop table t1,t2,t3;
-
-#
 # collation test
 #
 CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
@@ -1020,3 +976,36 @@ select * from t1 where 'f' > any (select s1 from t1);
 select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
 explain select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
 drop table t1;
+
+#
+# filesort in subquery (restoring join_tab)
+#
+CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
+CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
+select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;
+drop table t1, t2;
+
+#
+# unresolved field error
+#
+create table t1 (s1 int); 
+create table t2 (s1 int);
+-- error 1054
+select * from t1 where (select count(*) from t2 where t1.s2) = 1;
+-- error 1054
+select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
+-- error 1109
+select count(*) from t2 group by t1.s2;
+drop table t1, t2;
+
+#
+# fix_fields() in add_ref_to_table_cond()
+#
+CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
+CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
+INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
+INSERT INTO t2 VALUES (100, 200, 'C');
+SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
+DROP TABLE t1, t2;s
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
new file mode 100644
index 00000000000..5e274ecbd5c
--- /dev/null
+++ b/mysql-test/t/subselect_innodb.test
@@ -0,0 +1,70 @@
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+
+#
+# key field overflow test
+#
+CREATE TABLE t1
+(
+FOLDERID VARCHAR(32)BINARY NOT NULL
+, FOLDERNAME VARCHAR(255)BINARY NOT NULL
+, CREATOR VARCHAR(255)BINARY
+, CREATED TIMESTAMP NOT NULL
+, DESCRIPTION VARCHAR(255)BINARY
+, FOLDERTYPE INTEGER NOT NULL
+, MODIFIED TIMESTAMP
+, MODIFIER VARCHAR(255)BINARY
+, FOLDERSIZE INTEGER NOT NULL
+, PARENTID VARCHAR(32)BINARY
+, REPID VARCHAR(32)BINARY
+, ORIGINATOR INTEGER
+
+, PRIMARY KEY ( FOLDERID )
+) TYPE=InnoDB;
+CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID);
+CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID);
+INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");
+INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");
+INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');
+drop table t1;
+
+#
+# UNION unlocking test
+#
+create table t1 (a int) type=innodb;
+create table t2 (a int) type=innodb;
+create table t3 (a int) type=innodb;
+insert into t1 values (1),(2),(3),(4);
+insert into t2 values (10),(20),(30),(40);
+insert into t3 values (1),(2),(10),(50);
+select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30);
+drop table t1,t2,t3;
+
+
+CREATE TABLE t1 (
+   processor_id INTEGER NOT NULL,
+   PRIMARY KEY (processor_id)
+) TYPE=InnoDB;
+CREATE TABLE t3 (
+   yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
+   login_processor INTEGER UNSIGNED ,
+   PRIMARY KEY (yod_id)
+) TYPE=InnoDB;
+CREATE TABLE t2 (
+   processor_id INTEGER NOT NULL,
+   yod_id BIGINT UNSIGNED NOT NULL,
+   PRIMARY KEY (processor_id, yod_id),
+   INDEX (processor_id),
+   INDEX (yod_id),
+   FOREIGN KEY (processor_id) REFERENCES t1(processor_id),
+   FOREIGN KEY (yod_id) REFERENCES t3(yod_id) 
+) TYPE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1,1),(2,2),(3,3);
+INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
+SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1;
+drop table t1,t2,t3;
\ No newline at end of file
diff --git a/sql/derror.cc b/sql/derror.cc
index 7f4068c487e..f5fe92da238 100644
--- a/sql/derror.cc
+++ b/sql/derror.cc
@@ -136,6 +136,7 @@ err1:
   if (file != FERR)
     VOID(my_close(file,MYF(MY_WME)));
   unireg_abort(1);
+  return 1;
 } /* read_texts */
 
 
diff --git a/sql/item.cc b/sql/item.cc
index 0fbf3ae587b..29cde9375ba 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -844,6 +844,7 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
   if (!field)					// If field is not checked
   {
     TABLE_LIST *where= 0;
+    bool upward_lookup= 0;
     Field *tmp= (Field *)not_found_field;
     if ((tmp= find_field_in_tables(thd, this, tables, &where, 0)) ==
 	not_found_field)
@@ -873,6 +874,7 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 	     sl;
 	     sl= (prev_unit= sl->master_unit())->outer_select())
 	{
+	  upward_lookup= 1;
 	  table_list= (last= sl)->get_table_list();
 	  if (sl->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
 	  {
@@ -917,8 +919,17 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 	return 1;
       else if (tmp == not_found_field && refer == (Item **)not_found_item)
       {
-	// call to return error code
-	find_field_in_tables(thd, this, tables, &where, 1);
+	if (upward_lookup)
+	{
+	  // We can't say exactly what absend table or field
+	  my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
+			  full_name(), thd->where);
+	}
+	else
+	{
+	  // Call to report error
+	  find_field_in_tables(thd, this, tables, &where, 1);
+	}
 	return -1;
       }
       else if (refer != (Item **)not_found_item)
@@ -1429,6 +1440,7 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
   if (!ref)
   {
     TABLE_LIST *where= 0, *table_list;
+    bool upward_lookup= 0;
     SELECT_LEX_UNIT *prev_unit= thd->lex.current_select->master_unit();
     SELECT_LEX *sl= prev_unit->outer_select();
     /*
@@ -1447,6 +1459,7 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
 				  REPORT_ALL_ERRORS))) ==
 	(Item **)not_found_item)
     {
+      upward_lookup= 1;
       Field *tmp= (Field*) not_found_field;
       /*
 	We can't find table field in table list of current select,
@@ -1505,11 +1518,20 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
 	return -1;
       else if (ref == (Item **)not_found_item && tmp == not_found_field)
       {
-	// Call to report error
-	find_item_in_list(this,
-			  *(thd->lex.current_select->get_item_list()),
-			  &counter,
-			  REPORT_ALL_ERRORS);
+	if (upward_lookup)
+	{
+	  // We can't say exactly what absend (table or field)
+	  my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
+			  full_name(), thd->where);
+	}
+	else
+	{
+	  // Call to report error
+	  find_item_in_list(this,
+			    *(thd->lex.current_select->get_item_list()),
+			    &counter,
+			    REPORT_ALL_ERRORS);
+	}
         ref= 0;
 	return 1;
       }
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index ae44647b11c..d43978db0d5 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -170,7 +170,7 @@ void Item_subselect::update_used_tables()
   if (!engine->uncacheable())
   {
     // did all used tables become ststic?
-    if ((used_tables_cache & ~engine->upper_select_const_tables()))
+    if (!(used_tables_cache & ~engine->upper_select_const_tables()))
       const_item_cache= 1;
   }
 }
@@ -542,7 +542,6 @@ String *Item_in_subselect::val_str(String *str)
 
 Item_subselect::trans_res
 Item_in_subselect::single_value_transformer(JOIN *join,
-					    Item *left_expr,
 					    compare_func_creator func)
 {
   DBUG_ENTER("Item_in_subselect::single_value_transformer");
@@ -617,7 +616,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     // left expression belong to outer select
     SELECT_LEX *current= thd->lex.current_select, *up;
     thd->lex.current_select= up= current->return_after_parsing();
-    if (left_expr->fix_fields(thd, up->get_table_list(), 0))
+    if (left_expr->fix_fields(thd, up->get_table_list(), &left_expr))
     {
       thd->lex.current_select= current;
       DBUG_RETURN(RES_ERROR);
@@ -652,10 +651,10 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 		       (char *)"<no matter>",
 		       (char *)in_left_expr_name);
 
-    unit->dependent= 1;
+    unit->dependent= unit->uncacheable= 1;
   }
 
-  select_lex->dependent= 1;
+  select_lex->dependent= select_lex->uncacheable= 1;
   Item *item;
 
   item= (Item*) select_lex->item_list.head();
@@ -746,8 +745,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 }
 
 Item_subselect::trans_res
-Item_in_subselect::row_value_transformer(JOIN *join,
-					 Item *left_expr)
+Item_in_subselect::row_value_transformer(JOIN *join)
 {
   DBUG_ENTER("Item_in_subselect::row_value_transformer");
 
@@ -777,13 +775,12 @@ Item_in_subselect::row_value_transformer(JOIN *join,
       DBUG_RETURN(RES_ERROR);
     }
     thd->lex.current_select= current;
-
-    unit->dependent= 1;
+    unit->dependent= unit->uncacheable= 1;
   }
 
   uint n= left_expr->cols();
 
-  select_lex->dependent= 1;
+  select_lex->dependent= select_lex->uncacheable= 1;
   select_lex->setup_ref_array(thd,
 			      select_lex->order_list.elements +
 			      select_lex->group_list.elements);
@@ -832,9 +829,9 @@ Item_in_subselect::select_transformer(JOIN *join)
 {
   transformed= 1;
   if (left_expr->cols() == 1)
-    return single_value_transformer(join, left_expr,
+    return single_value_transformer(join,
 				    &Item_bool_func2::eq_creator);
-  return row_value_transformer(join, left_expr);
+  return row_value_transformer(join);
 }
 
 
@@ -857,7 +854,7 @@ Item_allany_subselect::select_transformer(JOIN *join)
   transformed= 1;
   if (upper_not)
     upper_not->show= 1;
-  return single_value_transformer(join, left_expr, func);
+  return single_value_transformer(join, func);
 }
 
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 8d438d0e72f..26e165df3d3 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -210,10 +210,8 @@ public:
   }
   trans_res select_transformer(JOIN *join);
   trans_res single_value_transformer(JOIN *join,
-				     Item *left_expr,
 				     compare_func_creator func);
-  trans_res row_value_transformer(JOIN * join,
-				  Item *left_expr);
+  trans_res row_value_transformer(JOIN * join);
   longlong val_int();
   double val();
   String *val_str(String*);
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 88061354b68..72cfd6ee56e 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -15,6 +15,7 @@
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
 #include <my_global.h>
+#include <assert.h>
 #include <mysql_version.h>
 #include <mysql_embed.h>
 #include <my_sys.h>
@@ -24,7 +25,6 @@
 #include <thr_lock.h>
 #include <my_base.h>			/* Needed by field.h */
 #include <sql_bitmap.h>
-#include <assert.h>
 
 #ifdef __EMX__
 #undef write  /* remove pthread.h macro definition for EMX */
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index fafc67e059d..75e2fc957e4 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -1884,7 +1884,7 @@ extern "C" int my_message_sql(uint error, const char *str,
   if ((thd= current_thd))
   {
     /*
-      thd->lex.current_select equel to zero if lex structure is not inited
+      thd->lex.current_select == 0 if lex structure is not inited
       (not query command (COM_QUERY))
     */
     if (thd->lex.current_select &&
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 57076bec603..66c8ef87d5a 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -2279,7 +2279,11 @@ int set_var::update(THD *thd)
 
 int set_var_user::check(THD *thd)
 {
-  return (user_var_item->fix_fields(thd,0, (Item**) 0) ||
+  /*
+    Item_func_set_user_var can't substitute something else on its place =>
+    0 can be passed as last argument
+  */
+  return (user_var_item->fix_fields(thd, 0, (Item**) 0) ||
 	  user_var_item->check()) ? -1 : 0;
 }
 
diff --git a/sql/sql_bitmap.h b/sql/sql_bitmap.h
index 53293e39591..e5e50c180bf 100644
--- a/sql/sql_bitmap.h
+++ b/sql/sql_bitmap.h
@@ -1,14 +1,26 @@
-#include <my_global.h>
-//#include <mysql_version.h>
-//#include <mysql_embed.h>
-//#include <my_sys.h>
-//#include <m_string.h>
-//#include <hash.h>
-//#include <signal.h>
-//#include <thr_lock.h>
-//#include <my_base.h>
+/* Copyright (C) 2003 MySQL AB
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; either version 2 of the License, or
+   (at your option) any later version.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+/*
+  Implementation of a bitmap type.
+  The idea with this is to be able to handle any constant number of bits but
+  also be able to use 32 or 64 bits bitmaps very efficiently
+*/
+
 #include <my_bitmap.h>
-#include <assert.h>
 
 template <uint default_width> class Bitmap
 {
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index e5760dda8ae..ed8eaba9128 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -102,6 +102,8 @@ THD::THD():user_time(0), is_fatal_error(0),
   lock=locked_tables=0;
   used_tables=0;
   cuted_fields= sent_row_count= current_stmt_id= 0L;
+  // Must be reset to handle error with THD's created for init of mysqld
+  lex.current_select= 0;
   start_time=(time_t) 0;
   current_linfo =  0;
   slave_thread = 0;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 5aaeef57669..93c4658d38c 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1195,16 +1195,16 @@ void st_select_lex::mark_as_dependent(SELECT_LEX *last)
     found table as depended (of select where was found table)
   */
   for (SELECT_LEX *s= this;
-       s &&s != last;
+       s && s != last;
        s= s->outer_select())
     if ( !s->dependent )
     {
       // Select is dependent of outer select
-      s->dependent= 1;
-      s->master_unit()->dependent= 1;
+      s->dependent= s->uncacheable= 1;
+      SELECT_LEX_UNIT *munit= s->master_unit();
+      munit->dependent= munit->uncacheable= 1;
       //Tables will be reopened many times
-      for (TABLE_LIST *tbl=
-	     s->get_table_list();
+      for (TABLE_LIST *tbl= s->get_table_list();
 	   tbl;
 	   tbl= tbl->next)
 	tbl->shared= 1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 00d8ea4c114..23f66b10a5b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -62,7 +62,6 @@ static store_key *get_store_key(THD *thd,
 static bool make_simple_join(JOIN *join,TABLE *tmp_table);
 static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *item);
 static void make_join_readinfo(JOIN *join,uint options);
-static void join_free(JOIN *join, bool full);
 static bool only_eq_ref_tables(JOIN *join, ORDER *order, table_map tables);
 static void update_depend_map(JOIN *join);
 static void update_depend_map(JOIN *join, ORDER *order);
@@ -997,8 +996,7 @@ JOIN::optimize()
       }
     }
     
-    if (select_lex != &thd->lex.select_lex &&
-	select_lex->linkage != DERIVED_TABLE_TYPE)
+    if (select_lex->master_unit()->dependent)
     {
       if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN))))
 	DBUG_RETURN(-1);
@@ -1011,10 +1009,10 @@ JOIN::optimize()
   DBUG_RETURN(0);
 }
 
+
 /*
   Restore values in temporary join
 */
-
 void JOIN::restore_tmp()
 {
   memcpy(tmp_join, this, (size_t) sizeof(JOIN));
@@ -1056,12 +1054,29 @@ JOIN::reinit()
   if (items0)
     set_items_ref_array(items0);
 
+  if (join_tab_save)
+    memcpy(join_tab, join_tab_save, sizeof(JOIN_TAB) * tables);
+
   if (tmp_join)
     restore_tmp();
 
   DBUG_RETURN(0);
 }
 
+
+bool
+JOIN::save_join_tab()
+{
+  if (!join_tab_save && select_lex->master_unit()->dependent)
+  {
+    if (!(join_tab_save= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB) * tables)))
+      return 1;
+    memcpy(join_tab_save, join_tab, sizeof(JOIN_TAB) * tables);
+  }
+  return 0;
+}
+
+
 /*
   Exec select
 */
@@ -1230,7 +1245,7 @@ JOIN::exec()
       DBUG_PRINT("info",("Creating group table"));
       
       /* Free first data from old join */
-      join_free(curr_join, 0);
+      curr_join->join_free(0);
       if (make_simple_join(curr_join, curr_tmp_table))
 	DBUG_VOID_RETURN;
       calc_group_buffer(curr_join, group_list);
@@ -1263,6 +1278,10 @@ JOIN::exec()
       if (curr_join->group_list)
       {
 	thd->proc_info= "Creating sort index";
+	if (curr_join->join_tab == join_tab && save_join_tab())
+	{
+	  DBUG_VOID_RETURN;
+	}
 	if (create_sort_index(thd, curr_join, curr_join->group_list,
 			      HA_POS_ERROR, HA_POS_ERROR) ||
 	    make_group_fields(this, curr_join))
@@ -1321,7 +1340,7 @@ JOIN::exec()
     if (curr_tmp_table->distinct)
       curr_join->select_distinct=0;		/* Each row is unique */
     
-    join_free(curr_join, 0);			/* Free quick selects */
+    curr_join->join_free(0);			/* Free quick selects */
     if (select_distinct && ! group_list)
     {
       thd->proc_info="Removing duplicates";
@@ -1444,6 +1463,10 @@ JOIN::exec()
 	  }
 	}
       }
+      if (curr_join->join_tab == join_tab && save_join_tab())
+      {
+	DBUG_VOID_RETURN;
+      }
       if (create_sort_index(thd, curr_join,
 			    curr_join->group_list ? 
 			    curr_join->group_list : curr_join->order,
@@ -1491,7 +1514,7 @@ JOIN::cleanup()
   }
 
   lock=0;                                     // It's faster to unlock later
-  join_free(this, 1);
+  join_free(1);
    if (exec_tmp_table1)
      free_tmp_table(thd, exec_tmp_table1);
    if (exec_tmp_table2)
@@ -3669,26 +3692,37 @@ bool error_if_full_join(JOIN *join)
 }
 
 
-static void
-join_free(JOIN *join, bool full)
+/*
+  Free resources of given join
+
+  SYNOPSIS
+    JOIN::join_free()
+    fill - true if we should free all resources, call with full==1 should be
+           last, before it this function can be called with full==0
+
+  NOTE: with subquery this function definitely will be called several times,
+    but even for simple query it can be called several times.
+*/
+void
+JOIN::join_free(bool full)
 {
   JOIN_TAB *tab,*end;
   DBUG_ENTER("join_free");
 
-  if (join->table)
+  if (table)
   {
     /*
       Only a sorted table may be cached.  This sorted table is always the
       first non const table in join->table
     */
-    if (join->tables > join->const_tables) // Test for not-const tables
+    if (tables > const_tables) // Test for not-const tables
     {
-      free_io_cache(join->table[join->const_tables]);
-      filesort_free_buffers(join->table[join->const_tables]);
+      free_io_cache(table[const_tables]);
+      filesort_free_buffers(table[const_tables]);
     }
-    if (join->select_lex->dependent && !full)
+    if (!full && select_lex->uncacheable)
     {
-      for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
+      for (tab= join_tab, end= tab+tables; tab != end; tab++)
       {
 	if (tab->table)
 	{
@@ -3705,7 +3739,7 @@ join_free(JOIN *join, bool full)
     }
     else
     {
-      for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
+      for (tab= join_tab, end= tab+tables; tab != end; tab++)
       {
 	delete tab->select;
 	delete tab->quick;
@@ -3731,24 +3765,25 @@ join_free(JOIN *join, bool full)
 	}
 	end_read_record(&tab->read_record);
       }
-      join->table= 0;
+      table= 0;
     }
   }
   /*
     We are not using tables anymore
     Unlock all tables. We may be in an INSERT .... SELECT statement.
   */
-  if (join->lock && join->thd->lock &&
-      !(join->select_options & SELECT_NO_UNLOCK))
+  if ((full || !select_lex->uncacheable) &&
+      lock && thd->lock &&
+      !(select_options & SELECT_NO_UNLOCK))
   {
-    mysql_unlock_read_tables(join->thd, join->lock);// Don't free join->lock
-    join->lock=0;
+    mysql_unlock_read_tables(thd, lock);// Don't free join->lock
+    lock=0;
   }
   if (full)
   {
-    join->group_fields.delete_elements();
-    join->tmp_table_param.copy_funcs.delete_elements();
-    join->tmp_table_param.cleanup();
+    group_fields.delete_elements();
+    tmp_table_param.copy_funcs.delete_elements();
+    tmp_table_param.cleanup();
   }
   DBUG_VOID_RETURN;
 }
@@ -5460,7 +5495,7 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure)
 	The following will unlock all cursors if the command wasn't an
 	update command
       */
-      join_free(join, 0);				// Unlock all cursors
+      join->join_free(0);				// Unlock all cursors
       if (join->result->send_eof())
 	error= 1;				// Don't send error
     }
@@ -8516,11 +8551,7 @@ static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab)
   if (thd->is_fatal_error)
     DBUG_RETURN(TRUE);
 
-  /*
-    Here we pass 0 as the first argument to fix_fields that don't need
-    to do any stack checking (This is already done in the initial fix_fields).
-  */
-  cond->fix_fields((THD *) 0,(TABLE_LIST *) 0, (Item**)&cond);
+  cond->fix_fields(thd,(TABLE_LIST *) 0, (Item**)&cond);
   if (join_tab->select)
   {
     error=(int) cond->add(join_tab->select->cond);
diff --git a/sql/sql_select.h b/sql/sql_select.h
index cdf528060f7..24854713a0e 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -131,6 +131,7 @@ class JOIN :public Sql_alloc
 {
  public:
   JOIN_TAB *join_tab,**best_ref,**map2table;
+  JOIN_TAB *join_tab_save; //saved join_tab for subquery reexecution
   TABLE    **table,**all_tables,*sort_by_table;
   uint	   tables,const_tables;
   uint	   send_group_parts;
@@ -204,7 +205,7 @@ class JOIN :public Sql_alloc
   void init(THD *thd_arg, List<Item> &fields, ulong select_options_arg,
        select_result *result_arg)
   {
-    join_tab= 0;
+    join_tab= join_tab_save= 0;
     table= 0;
     tables= 0;
     const_tables= 0;
@@ -243,7 +244,7 @@ class JOIN :public Sql_alloc
     zero_result_cause= 0;
     optimized= 0;
 
-    fields_list = fields;
+    fields_list= fields;
     bzero((char*) &keyuse,sizeof(keyuse));
     tmp_table_param.copy_field=0;
     tmp_table_param.end_write_records= HA_POS_ERROR;
@@ -280,7 +281,9 @@ class JOIN :public Sql_alloc
 			  Item_sum ***func);
   int rollup_send_data(uint idx);
   bool test_in_subselect(Item **where);
+  void join_free(bool full);
   void clear();
+  bool save_join_tab();
 };
 
 
diff --git a/sql/sql_test.cc b/sql/sql_test.cc
index 7e3513367fd..47f0932b221 100644
--- a/sql/sql_test.cc
+++ b/sql/sql_test.cc
@@ -164,10 +164,11 @@ TEST_join(JOIN *join)
   {
     JOIN_TAB *tab=join->join_tab+i;
     TABLE *form=tab->table;
-    fprintf(DBUG_FILE,"%-16.16s  type: %-7s  q_keys: %4d  refs: %d  key: %d  len: %d\n",
+    char key_map_buff[128];
+    fprintf(DBUG_FILE,"%-16.16s  type: %-7s  q_keys: %s  refs: %d  key: %d  len: %d\n",
 	    form->table_name,
 	    join_type_str[tab->type],
-	    tab->keys,
+	    tab->keys.print(key_map_buff),
 	    tab->ref.key_parts,
 	    tab->ref.key,
 	    tab->ref.key_length);