diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 30 |
1 files changed, 30 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e90f43b9870..3dc1795a65a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4041,6 +4041,36 @@ 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. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); |