diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 24 |
1 files changed, 24 insertions, 0 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index aaeb998d0c7..12e2e4f1f25 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2882,6 +2882,30 @@ 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. # |