Merge mhansson@bk-internal.mysql.com:/home/bk/mysql-5.0-opt

into  lamia.dupka:/home/mhansson/my50-bug31797-pushee

3 files changed, 82 insertions, 1 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 1693fa646eb..7574aa916ae 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1177,4 +1177,40 @@ id	c1	c2
 4	2	3
 1	5	1
 DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT );
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+c	(SELECT a FROM t1 WHERE b = c)
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+c	(SELECT a FROM t1 WHERE b = c)
+SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+ERROR 42S22: Reference 'c' not supported (reference to group function)
+SET @old_sql_mode = @@sql_mode;
+SET @@sql_mode='ONLY_FULL_GROUP_BY';
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+c	(SELECT a FROM t1 WHERE b = c)
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+ERROR 42000: non-grouping field 'b' is used in HAVING clause
+SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+ERROR 42S22: Reference 'c' not supported (reference to group function)
+INSERT INTO t1 VALUES (1, 1);
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+c	(SELECT a FROM t1 WHERE b = c)
+1	1
+INSERT INTO t1 VALUES (2, 1);
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+DROP TABLE t1;
+SET @@sql_mode = @old_sql_mode;
 End of 5.0 tests
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index b150db6dafe..636544f7854 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -849,4 +849,48 @@ SELECT * FROM t1 GROUP BY c2  ORDER BY c2 DESC, c1 DESC;
 
 DROP TABLE t1;
 
+# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
+#
+CREATE TABLE t1 ( a INT, b INT );
+
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+
+--error ER_ILLEGAL_REFERENCE
+SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+
+SET @old_sql_mode = @@sql_mode;
+SET @@sql_mode='ONLY_FULL_GROUP_BY';
+
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+
+--error ER_ILLEGAL_REFERENCE
+SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
+FROM t1 
+HAVING b = 10;
+
+INSERT INTO t1 VALUES (1, 1);
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+
+INSERT INTO t1 VALUES (2, 1);
+--error ER_SUBQUERY_NO_1_ROW
+SELECT b c, (SELECT a FROM t1 WHERE b = c)
+FROM t1;
+
+DROP TABLE t1;
+SET @@sql_mode = @old_sql_mode;
+
 --echo End of 5.0 tests
diff --git a/sql/item.cc b/sql/item.cc
index cafa40ecbb0..713e7709bcb 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3366,7 +3366,7 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list)
     resolve_ref_in_select_and_group()
     thd     current thread
     ref     column reference being resolved
-    select  the sub-select that ref is resolved against
+    select  the select that ref is resolved against
 
   DESCRIPTION
     Resolve a column reference (usually inside a HAVING clause) against the
@@ -3437,6 +3437,7 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select)
   }
 
   if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+      select->having_fix_field  &&
       select_ref != not_found_item && !group_by_ref)
   {
     /*