From 8c34ae268cc10d57ae592b286a83550d08ad221c Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Wed, 16 May 2007 23:42:10 -0700
Subject: 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/t/subselect.test | 24 ++++++++++++++++++++++++
 1 file changed, 24 insertions(+)

(limited to 'mysql-test/t/subselect.test')

diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 64de2ada93c..5840e434b64 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2882,4 +2882,28 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 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.
-- 
cgit v1.2.1