Merge magare.gmz:/home/kgeorge/mysql/work/B32400-5.0-opt

into  magare.gmz:/home/kgeorge/mysql/work/B32400-5.1-opt


BitKeeper/deleted/.del-ps_6bdb.result:
  Auto merged
mysql-test/r/ps_2myisam.result:
  Auto merged
mysql-test/r/ps_3innodb.result:
  Auto merged
mysql-test/r/ps_4heap.result:
  Auto merged
mysql-test/r/ps_5merge.result:
  Auto merged
mysql-test/suite/ndb/r/ps_7ndb.result:
  Auto merged
sql/item.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/t/subselect.test:
  merge of bug 32400 to 5.1-opt

7 files changed, 82 insertions, 44 deletions

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index e96d666eaec..ae6027a0e07 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -434,8 +434,8 @@ execute stmt1 ;
 let $1= 3 ;
 while ($1)
 {
-  prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+  prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
   execute stmt1 ;
   deallocate prepare stmt1 ;
   dec $1 ;
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index eb8ef7a85c3..fbc6781e5e7 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -783,20 +783,20 @@ a	b
 2	two
 3	three
 4	four
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 15650efa41d..fcd0b5de9a0 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -783,20 +783,20 @@ a	b
 2	two
 3	three
 4	four
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 486f770220e..862c0ff75c1 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -784,20 +784,20 @@ a	b
 2	two
 3	three
 4	four
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 0229b0ece08..51393cc8bc3 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -826,20 +826,20 @@ a	b
 2	two
 3	three
 4	four
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
@@ -3848,20 +3848,20 @@ a	b
 2	two
 3	three
 4	four
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
diff --git a/mysql-test/suite/ndb/r/ps_7ndb.result b/mysql-test/suite/ndb/r/ps_7ndb.result
index fe694520f7e..6e2e61bbc5e 100644
--- a/mysql-test/suite/ndb/r/ps_7ndb.result
+++ b/mysql-test/suite/ndb/r/ps_7ndb.result
@@ -783,20 +783,20 @@ a	b
 2	two
 3	three
 4	four
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
 deallocate prepare stmt1 ;
-prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
-                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
+prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
+                           (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
 execute stmt1 ;
 ccc
 1
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index ee524cda994..442123b9a80 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -32,7 +32,9 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
 -- error 1108
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
+-- error ER_BAD_FIELD_ERROR
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
+-- error ER_BAD_FIELD_ERROR
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
 SELECT (SELECT 1,2,3) = ROW(1,2,3);
 SELECT (SELECT 1,2,3) = ROW(1,2,1);
@@ -1346,17 +1348,20 @@ drop table t1,t2;
 CREATE TABLE t1 ( a int, b int );
 CREATE TABLE t2 ( c int, d int );
 INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
-SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
-INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+SELECT a AS abc, b FROM t1 outr WHERE b = 
+  (SELECT MIN(b) FROM t1 WHERE a=outr.a);
+INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = 
+  (SELECT MIN(b) FROM t1 WHERE a=outr.a);
 select * from t2;
-CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = 
+  (SELECT MIN(b) FROM t1 WHERE a=outr.a);
 select * from t3;
-prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
 execute stmt1;
 deallocate prepare stmt1;
 select * from t2;
 drop table t3;
-prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
 execute stmt1;
 select * from t3;
 deallocate prepare stmt1;
@@ -1529,7 +1534,9 @@ INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,680
 INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
 INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
 /*!40000 ALTER TABLE t1 ENABLE KEYS */;
-SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
+SELECT DISTINCT Continent AS c FROM t1 outr WHERE 
+  Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND 
+  Population < 200);
 drop table t1;
 
 #
@@ -3163,6 +3170,37 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
 
 DROP TABLE t1,t2;
 
+#
+# Bug #32400: Complex SELECT query returns correct result only on some
+# occasions
+#
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
+
+--error ER_BAD_FIELD_ERROR
+EXPLAIN 
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+
+--error ER_BAD_FIELD_ERROR
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+
+EXPLAIN 
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests.
 
 #