diff options
| author | igor@olga.mysql.com <> | 2007-05-17 16:06:57 -0700 |
|---|---|---|
| committer | igor@olga.mysql.com <> | 2007-05-17 16:06:57 -0700 |
| commit | 2189c571b2ffe24a41c97f961e2853d68b240348 (patch) | |
| tree | c54b7de614207f435201a5ed08706472afa44e08 | |
| parent | 4ac1e98630759c9b86bb73035b4f95cd6909a2a9 (diff) | |
| parent | 8c34ae268cc10d57ae592b286a83550d08ad221c (diff) | |
| download | mariadb-git-2189c571b2ffe24a41c97f961e2853d68b240348.tar.gz | |
Merge olga.mysql.com:/home/igor/mysql-5.0-opt
into olga.mysql.com:/home/igor/dev-opt/mysql-5.0-opt-bug28337
| -rw-r--r-- | mysql-test/r/subselect.result | 30 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 24 | ||||
| -rw-r--r-- | sql/item.cc | 3 |
3 files changed, 56 insertions, 1 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 2e82d948edb..92a4a6f3f5a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4041,4 +4041,34 @@ id select_type table type possible_keys key key_len ref rows Extra 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. 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. diff --git a/sql/item.cc b/sql/item.cc index 30486e7559a..60c5b3b4a8c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3503,7 +3503,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) 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; /* |