diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 294 |
1 files changed, 276 insertions, 18 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e2a5494eaf7..03308ca7dc4 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1,13 +1,33 @@ +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); (select 2) 2 +explain select (select 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1247 Select 2 was reduced during optimisation SELECT (SELECT 1) UNION SELECT (SELECT 2); (SELECT 1) 1 2 +explain SELECT (SELECT 1) UNION SELECT (SELECT 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1247 Select 2 was reduced during optimisation +Note 1247 Select 4 was reduced during optimisation SELECT (SELECT (SELECT 0 UNION SELECT 0)); (SELECT (SELECT 0 UNION SELECT 0)) 0 +explain SELECT (SELECT (SELECT 0 UNION SELECT 0)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1247 Select 2 was reduced during optimisation SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; Reference 'a' not supported (forward reference in item list) SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b; @@ -20,8 +40,8 @@ Reference 'a' not supported (forward reference in item list) EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -3 DEPENDENT SUBSELECT No tables used -2 DERIVED No tables used +3 DEPENDENT SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -32,7 +52,7 @@ a 1 SELECT 1 FROM (SELECT (SELECT a) b) c; Unknown column 'a' in 'field list' -SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id LIMIT 1); +SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id); id 1 SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); @@ -47,7 +67,61 @@ select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); Wrong usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); Incorrect parameters to procedure 'ANALYSE' -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; +SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; +a +SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; +a +1 +SELECT (SELECT 1,2,3) = ROW(1,2,3); +(SELECT 1,2,3) = ROW(1,2,3) +1 +SELECT (SELECT 1,2,3) = ROW(1,2,1); +(SELECT 1,2,3) = ROW(1,2,1) +0 +SELECT (SELECT 1,2,3) < ROW(1,2,1); +(SELECT 1,2,3) < ROW(1,2,1) +0 +SELECT (SELECT 1,2,3) > ROW(1,2,1); +(SELECT 1,2,3) > ROW(1,2,1) +1 +SELECT (SELECT 1,2,3) = ROW(1,2,NULL); +(SELECT 1,2,3) = ROW(1,2,NULL) +NULL +SELECT ROW(1,2,3) = (SELECT 1,2,3); +ROW(1,2,3) = (SELECT 1,2,3) +1 +SELECT ROW(1,2,3) = (SELECT 1,2,1); +ROW(1,2,3) = (SELECT 1,2,1) +0 +SELECT ROW(1,2,3) < (SELECT 1,2,1); +ROW(1,2,3) < (SELECT 1,2,1) +0 +SELECT ROW(1,2,3) > (SELECT 1,2,1); +ROW(1,2,3) > (SELECT 1,2,1) +1 +SELECT ROW(1,2,3) = (SELECT 1,2,NULL); +ROW(1,2,3) = (SELECT 1,2,NULL) +NULL +SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a'); +(SELECT 1.5,2,'a') = ROW(1.5,2,'a') +1 +SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b'); +(SELECT 1.5,2,'a') = ROW(1.5,2,'b') +0 +SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b'); +(SELECT 1.5,2,'a') = ROW('b',2,'b') +0 +SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a'); +(SELECT 'b',2,'a') = ROW(1.5,2,'a') +0 +SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a'); +(SELECT 1.5,2,'a') = ROW(1.5,'c','a') +0 +SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); +(SELECT 1.5,'c','a') = ROW(1.5,2,'a') +0 +SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); +Cardinality error (more/less than 1 columns) create table t1 (a int); create table t2 (a int, b int); create table t3 (a int); @@ -81,18 +155,18 @@ select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); a b 1 7 2 7 -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 order by a limit 2) limit 3; a b 1 7 2 7 3 8 -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 * 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); a b 1 7 2 7 3 8 4 8 -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 (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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using filesort @@ -235,7 +309,6 @@ W 1 SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3); a b W a -drop table if exists t8; CREATE TABLE `t8` ( `pseudo` varchar(35) character set latin1 NOT NULL default '', `email` varchar(60) character set latin1 NOT NULL default '', @@ -277,7 +350,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 41 NULL 2 Using where; Using index EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY No tables used +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBSELECT t1 index NULL PRIMARY 41 NULL 2 Using where; Using index SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; date @@ -295,8 +368,8 @@ Subselect returns more than 1 record EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL topic 3 NULL 2 Using index -2 SUBSELECT No tables used -3 UNION No tables used +2 SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, @@ -388,11 +461,11 @@ EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 Subselect returns more than 1 record EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE Select tables optimized away +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 -2 SUBSELECT Select tables optimized away +2 SUBSELECT NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -442,7 +515,6 @@ a b 0 10 1 11 drop table t1, t2; -drop table if exists t11, t12, t2; create table t11 (a int NOT NULL, b int, primary key (a)); create table t12 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); @@ -548,7 +620,6 @@ x y drop table t1, t2, t3; SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); No tables used -drop table if exists t; CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; INSERT INTO t VALUES (1),(2); SELECT * FROM t WHERE id IN (SELECT 1); @@ -568,14 +639,15 @@ id EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ref id id 5 const 1 Using where; Using index -3 SUBSELECT No tables used +3 SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: +Note 1247 Select 3 was reduced during optimisation Note 1247 Select 2 was reduced during optimisation EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index NULL id 5 NULL 2 Using where; Using index -2 DEPENDENT SUBSELECT No tables used -3 UNION No tables used +2 DEPENDENT SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2); @@ -591,4 +663,190 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin INSERT INTO t1 values (1),(1); UPDATE t SET id=(SELECT * FROM t1); Subselect returns more than 1 record +drop table t, t1; +create table t (a int); +insert into t values (1),(2),(3); +select 1 IN (SELECT * from t); +1 IN (SELECT * from t) +1 +select 10 IN (SELECT * from t); +10 IN (SELECT * from t) +0 +select NULL IN (SELECT * from t); +NULL IN (SELECT * from t) +NULL +update t set a=NULL where a=2; +select 1 IN (SELECT * from t); +1 IN (SELECT * from t) +1 +select 3 IN (SELECT * from t); +3 IN (SELECT * from t) +1 +select 10 IN (SELECT * from t); +10 IN (SELECT * from t) +NULL +select 1 > ALL (SELECT * from t); +1 > ALL (SELECT * from t) +0 +select 10 > ALL (SELECT * from t); +10 > ALL (SELECT * from t) +NULL +select 1 > ANY (SELECT * from t); +1 > ANY (SELECT * from t) +NULL +select 10 > ANY (SELECT * from t); +10 > ANY (SELECT * from t) +1 drop table t; +create table t (a varchar(20)); +insert into t values ('A'),('BC'),('DEF'); +select 'A' IN (SELECT * from t); +'A' IN (SELECT * from t) +1 +select 'XYZS' IN (SELECT * from t); +'XYZS' IN (SELECT * from t) +0 +select NULL IN (SELECT * from t); +NULL IN (SELECT * from t) +NULL +update t set a=NULL where a='BC'; +select 'A' IN (SELECT * from t); +'A' IN (SELECT * from t) +1 +select 'DEF' IN (SELECT * from t); +'DEF' IN (SELECT * from t) +1 +select 'XYZS' IN (SELECT * from t); +'XYZS' IN (SELECT * from t) +NULL +select 'A' > ALL (SELECT * from t); +'A' > ALL (SELECT * from t) +0 +select 'XYZS' > ALL (SELECT * from t); +'XYZS' > ALL (SELECT * from t) +NULL +select 'A' > ANY (SELECT * from t); +'A' > ANY (SELECT * from t) +NULL +select 'XYZS' > ANY (SELECT * from t); +'XYZS' > ANY (SELECT * from t) +1 +drop table t; +create table t (a float); +insert into t values (1.5),(2.5),(3.5); +select 1.5 IN (SELECT * from t); +1.5 IN (SELECT * from t) +1 +select 10.5 IN (SELECT * from t); +10.5 IN (SELECT * from t) +0 +select NULL IN (SELECT * from t); +NULL IN (SELECT * from t) +NULL +update t set a=NULL where a=2.5; +select 1.5 IN (SELECT * from t); +1.5 IN (SELECT * from t) +1 +select 3.5 IN (SELECT * from t); +3.5 IN (SELECT * from t) +1 +select 10.5 IN (SELECT * from t); +10.5 IN (SELECT * from t) +NULL +select 1.5 > ALL (SELECT * from t); +1.5 > ALL (SELECT * from t) +0 +select 10.5 > ALL (SELECT * from t); +10.5 > ALL (SELECT * from t) +NULL +select 1.5 > ANY (SELECT * from t); +1.5 > ANY (SELECT * from t) +NULL +select 10.5 > ANY (SELECT * from t); +10.5 > ANY (SELECT * from t) +1 +explain select (select a+1) from t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 3 +Warnings: +Note 1247 Select 2 was reduced during optimisation +select (select a+1) from t; +(select a+1) +2.5 +NULL +4.5 +drop table t; +create table t (a float); +select 10.5 IN (SELECT * from t LIMIT 1); +This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5); +This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +drop table t; +create table t1 (a int, b int, c varchar(10)); +create table t2 (a int); +insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c'); +insert into t2 values (1),(2),(NULL); +select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2; +a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a) +1 1 a +2 0 b +NULL NULL NULL +select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2; +a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a) +1 0 a +2 1 b +NULL NULL NULL +select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2; +a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a) +1 0 a +2 0 b +NULL NULL NULL +drop table t1,t2; +create table t1 (a int, b real, c varchar(10)); +insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b'); +select ROW(1, 1, 'a') IN (select a,b,c from t1); +ROW(1, 1, 'a') IN (select a,b,c from t1) +1 +select ROW(1, 2, 'a') IN (select a,b,c from t1); +ROW(1, 2, 'a') IN (select a,b,c from t1) +NULL +select ROW(1, 1, 'a') IN (select b,a,c from t1); +ROW(1, 1, 'a') IN (select b,a,c from t1) +1 +select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null); +ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null) +1 +select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null); +ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null) +0 +select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null); +ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null) +1 +select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a'); +ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a') +1 +select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a'); +ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a') +NULL +select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a'); +ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a') +1 +select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2); +This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +drop table t1; +create table t1 (a int); +insert into t1 values (1); +do @a:=(SELECT a from t1); +select @a; +@a +1 +set @a:=2; +set @a:=(SELECT a from t1); +select @a; +@a +1 +drop table t1; +do (SELECT a from t1); +Table 'test.t1' doesn't exist +set @a:=(SELECT a from t1); +Table 'test.t1' doesn't exist |