diff options
| -rw-r--r-- | mysql-test/r/subselect_innodb.result | 8 | ||||
| -rw-r--r-- | mysql-test/t/subselect_innodb.test | 14 |
2 files changed, 22 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index bbbc607b6f8..e8f6426f51b 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -106,3 +106,11 @@ a b 2 12 4 105 drop table t1, t2; +CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; +CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES ('xx','yy'); +INSERT INTO `t2` VALUES ('yy','xx'); +SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit); +unit ingredient +xx yy +drop table t1, t2; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 8c13171d221..5f4badb3624 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -111,3 +111,17 @@ create table t2 (a int) engine=innodb; insert into t2 values (1),(2),(3),(4); select a, sum(b) as b from t1 group by a having b > (select max(a) from t2); drop table t1, t2; + +# +# bug #5220 test suite +# +CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO `t1` VALUES ('xx','yy'); +INSERT INTO `t2` VALUES ('yy','xx'); + +SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit); + +drop table t1, t2; |