bk commit into 5.0 tree (mhansson:1.2546) BUG#31797

[mhansson]

Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-01-11 18:50:54+01:00, mhansson@stripped +3 -0
  Bug#31797: error while parsing subqueries -- WHERE is parsed as HAVING
  
  The name resolution for correlated subqueries and HAVING clauses
  failed to distinguish which of two was being performed when there 
  was a reference to an outer aliased field.
  Fixed by adding the condition that HAVING clause name resulotion
  is being performed.

  mysql-test/r/group_by.result@stripped, 2008-01-11 18:50:52+01:00, mhansson@stripped +36 -0
    Bug#31797: Test result

  mysql-test/t/group_by.test@stripped, 2008-01-11 18:50:52+01:00, mhansson@stripped +48 -0
    Bug#31797: Test case

  sql/item.cc@stripped, 2008-01-11 18:50:52+01:00, mhansson@stripped +2 -1
    Bug#31797: 
    Corrected function comment.
    The fix, raising the error is restricted to HAVING name resolution.

diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result	2007-08-27 17:33:38 +02:00
+++ b/mysql-test/r/group_by.result	2008-01-11 18:50:52 +01:00
@@ -1113,3 +1113,39 @@ c	b
 3	1
 3	2
 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;
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test	2007-08-27 17:33:38 +02:00
+++ b/mysql-test/t/group_by.test	2008-01-11 18:50:52 +01:00
@@ -815,3 +815,51 @@ EXPLAIN SELECT c,b   FROM t1 GROUP BY c,
 SELECT c,b   FROM t1 GROUP BY c,b;
 
 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;
+
+
+
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc	2007-10-16 10:17:35 +02:00
+++ b/sql/item.cc	2008-01-11 18:50:52 +01:00
@@ -3352,7 +3352,7 @@ static Item** find_field_in_group_list(I
     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
@@ -3423,6 +3423,7 @@ resolve_ref_in_select_and_group(THD *thd
   }
 
   if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+      select->having_fix_field  &&
       select_ref != not_found_item && !group_by_ref)
   {
     /*