From: Date: May 17 2007 8:42am
Subject: bk commit into 5.0 tree (igor:1.2490) BUG#28337
List-Archive: http://lists.mysql.com/commits/26885
X-Bug: 28337
Message-Id: <20070517064216.D7F6841366@olga.mysql.com>

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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, 2007-05-16 23:42:10-07:00, igor@stripped +3 -0
  Fixed bug #28337: wrong results for grouping queries with correlated
  subqueries in WHERE conditions.
  This bug was introduced by the patch for bug 27321.

  mysql-test/r/subselect.result@stripped, 2007-05-16 23:42:08-07:00, igor@stripped +30 -0
    Added a test case for bug #28337.

  mysql-test/t/subselect.test@stripped, 2007-05-16 23:42:08-07:00, igor@stripped +24 -0
    Added a test case for bug #28337.

  sql/item.cc@stripped, 2007-05-16 23:42:09-07:00, igor@stripped +2 -1
    Fixed bug #28337: wrong results for grouping queries with correlated
    subqueries in WHERE conditions.
    This bug was introduced by the patch for bug 27321.
    
    Now in the Item_field::fix_outer_field function we create an Item_outer_ref
    object for an outer reference only if it is used in the SELECT list or
    in the HAVING clause of the subquery against which the reference is resolved.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug28337

--- 1.266/sql/item.cc	2007-05-16 23:42:16 -07:00
+++ 1.267/sql/item.cc	2007-05-16 23:42:16 -07:00
@@ -3503,7 +3503,8 @@
           prev_subselect_item->const_item_cache= 0;
           set_field(*from_field);
           if (!last_checked_context->select_lex->having_fix_field &&
-              select->group_list.elements)
+              select->group_list.elements &&
+              (place == SELECT_LIST || place == IN_HAVING))
           {
             Item_outer_ref *rf;
             /*

--- 1.189/mysql-test/r/subselect.result	2007-05-16 23:42:16 -07:00
+++ 1.190/mysql-test/r/subselect.result	2007-05-16 23:42:16 -07:00
@@ -4041,4 +4041,34 @@
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id	st
+3	FL
+1	GA
+7	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id	st
+1	GA
+3	FL
+7	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id	st
+2	GA
+4	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id	st
+2	GA
+4	FL
+DROP TABLE t1,t2;
 End of 5.0 tests.

--- 1.151/mysql-test/t/subselect.test	2007-05-16 23:42:16 -07:00
+++ 1.152/mysql-test/t/subselect.test	2007-05-16 23:42:16 -07:00
@@ -2882,4 +2882,28 @@
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 DROP TABLE t1;
 
+#
+# Bug #28377: grouping query with a correlated subquery in WHERE condition
+#  
+
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+  (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+    GROUP BY id;
+
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+    GROUP BY id;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.