diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 27 |
1 files changed, 27 insertions, 0 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index eb4b1f33b14..5bff28dca77 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1222,3 +1222,30 @@ 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; + +# +# resolving fields of grouped outer SELECT +# +CREATE TABLE t1 (howmanyvalues bigint, avalue int); +INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4); +SELECT howmanyvalues, count(*) from t1 group by howmanyvalues; +SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues; +CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues); +SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues; +drop table t1; |