diff options
| author | unknown <bell@sanja.is.com.ua> | 2004-08-12 18:38:29 +0300 |
|---|---|---|
| committer | unknown <bell@sanja.is.com.ua> | 2004-08-12 18:38:29 +0300 |
| commit | 05b67e7844f48222120ed119de326e6573b74cf3 (patch) | |
| tree | 21b72373734f158c8af452d0bb88165d945ff5ab /mysql-test | |
| parent | a953bb4b70ea20d806136772214d522b7cfb31f5 (diff) | |
| parent | 694103472b92b91e5d3f35b565f9d64b290fa8e9 (diff) | |
| download | mariadb-git-05b67e7844f48222120ed119de326e6573b74cf3.tar.gz | |
Merge sanja.is.com.ua:/home/bell/mysql/bk/mysql-4.1
into sanja.is.com.ua:/home/bell/mysql/bk/work-4.1
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect.result | 21 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 16 |
2 files changed, 37 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 77339473142..ffcff534219 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1899,3 +1899,24 @@ select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL ( a C 1 1 drop table t1,t2; +CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)); +CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)); +insert into t1 values (1,1),(1,2),(2,1),(2,2); +insert into t2 values (1,2),(2,2); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +aid bid +1 1 +2 1 +alter table t2 drop primary key; +alter table t2 add key KEY1 (aid, bid); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +aid bid +1 1 +2 1 +alter table t2 drop key KEY1; +alter table t2 add primary key (bid, aid); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +aid bid +1 1 +2 1 +drop table t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index eb4b1f33b14..55ec001ba26 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1222,3 +1222,19 @@ CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=M insert into t2 values (1,2); select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1); drop table t1,t2; + +# +# Optimized IN with compound index +# +CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)); +CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)); +insert into t1 values (1,1),(1,2),(2,1),(2,2); +insert into t2 values (1,2),(2,2); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +alter table t2 drop primary key; +alter table t2 add key KEY1 (aid, bid); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +alter table t2 drop key KEY1; +alter table t2 add primary key (bid, aid); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +drop table t1,t2; |