diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 88 |
1 files changed, 44 insertions, 44 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3648210b943..bc49f0c28cd 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4,11 +4,11 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; --enable_warnings select (select 2); -explain select (select 2); +explain extended select (select 2); SELECT (SELECT 1) UNION SELECT (SELECT 2); -explain SELECT (SELECT 1) UNION SELECT (SELECT 2); +explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2); SELECT (SELECT (SELECT 0 UNION SELECT 0)); -explain SELECT (SELECT (SELECT 0 UNION SELECT 0)); +explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0)); -- error 1246 SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; -- error 1246 @@ -16,7 +16,7 @@ SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a; -- error 1246 SELECT (SELECT a) as a; -EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; +EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; -- error 1054 SELECT (SELECT 1), a; @@ -74,17 +74,17 @@ insert into t3 values (6),(7),(3); select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3; (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); -explain (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); +explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; -explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; -explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; select * from t3 where exists (select * from t2 where t2.b=t3.a); select * from t3 where not exists (select * from t2 where t2.b=t3.a); select * from t3 where a in (select b from t2); @@ -100,7 +100,7 @@ insert into t2 values (100, 5); select * from t3 where a < any (select b from t2); select * from t3 where a < all (select b from t2); select * from t3 where a >= any (select b from t2); -explain select * from t3 where a >= any (select b from t2); +explain extended select * from t3 where a >= any (select b from t2); select * from t3 where a >= all (select b from t2); delete from t2 where a=100; -- error 1240 @@ -120,7 +120,7 @@ insert into t5 values (5); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; insert into t5 values (2); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; -explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; -- error 1241 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -128,7 +128,7 @@ create table t7( uq int primary key, name char(25)); insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); insert into t6 values (1,1),(1,2),(2,2),(1,3); select * from t6 where exists (select * from t7 where uq = clinic_uq); -explain select * from t6 where exists (select * from t7 where uq = clinic_uq); +explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); # not unique fields -- error 1052 @@ -157,7 +157,7 @@ CREATE TABLE `t8` ( INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); -EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); +EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); -- error 1240 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); @@ -180,14 +180,14 @@ CREATE TABLE `t1` ( ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); -EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; -EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); +EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; +EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; -- error 1241 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; -EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); drop table t1; #forumconthardwarefr7 searchconthardwarefr7 @@ -276,9 +276,9 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); -- error 1241 -EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; -EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); +EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; +EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; CREATE TABLE t1 (a int(1)); @@ -395,11 +395,11 @@ SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (1),(2); SELECT * FROM t2 WHERE id IN (SELECT 1); -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); -EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); +EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); -- error 1093 @@ -455,7 +455,7 @@ select 1.5 > ALL (SELECT * from t1); select 10.5 > ALL (SELECT * from t1); select 1.5 > ANY (SELECT * from t1); select 10.5 > ANY (SELECT * from t1); -explain select (select a+1) from t1; +explain extended select (select a+1) from t1; select (select a+1) from t1; drop table t1; @@ -468,11 +468,11 @@ CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t2 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; -explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; +explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; CREATE TABLE t3 (a int(11) default '0'); INSERT INTO t3 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; -explain SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; +explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; drop table t1,t2,t3; #LIMIT is not supported now @@ -549,9 +549,9 @@ drop table t1; #test of uncacheable subqueries CREATE TABLE t1 (a int(1)); -EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1; -EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; -EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; +EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; drop table t1; @@ -613,7 +613,7 @@ drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3); -explain select a,(select (select rand() from t1 limit 1) from t1 limit 1) +explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1) from t1; drop table t1; @@ -677,10 +677,10 @@ CREATE TABLE `t1` ( ) TYPE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (pseudo) VALUES ('test'); SELECT 0 IN (SELECT 1 FROM t1 a); -EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a); +EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); -EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a); +EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); drop table t1; CREATE TABLE `t1` ( @@ -727,7 +727,7 @@ drop table t1; # create table t1 (id int not null auto_increment primary key, salary int, key(salary)); insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); -explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); +explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); drop table t1; CREATE TABLE t1 ( @@ -824,11 +824,11 @@ insert into t1 values (1,10), (2,20), (3,30), (4,40); insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); select * from t2 where t2.a in (select a from t1); -explain select * from t2 where t2.a in (select a from t1); +explain extended select * from t2 where t2.a in (select a from t1); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); -explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); +explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -846,15 +846,15 @@ enable_query_log; insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); select * from t2 where t2.a in (select a from t1); -explain select * from t2 where t2.a in (select a from t1); +explain extended select * from t2 where t2.a in (select a from t1); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); -explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); +explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31); -explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); drop table t1, t2, t3; # @@ -935,7 +935,7 @@ create table t1 (s1 char(5)); select (select 'a','b' from t1 union select 'a','b' from t1) from t1; insert into t1 values ('tttt'); select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1); -explain (select * from t1); +explain extended (select * from t1); (select * from t1); drop table t1; @@ -950,10 +950,10 @@ select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; select s1, s1 = ANY (SELECT s1 FROM t2) from t1; select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; -explain select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; -explain select s1, s1 = ANY (SELECT s1 FROM t2) from t1; -explain select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; -explain select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; +explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; +explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; +explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; +explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; drop table t1,t2; # @@ -963,14 +963,14 @@ create table t2 (a int, b int); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); -explain select * from t3 where a >= all (select b from t2); +explain extended select * from t3 where a >= all (select b from t2); # # optimized static ALL/ANY with grouping # insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); -explain select * from t3 where a > all (select max(b) from t2 group by a); +explain extended select * from t3 where a > all (select max(b) from t2 group by a); drop table t2, t3; # |