diff options
author | unknown <bell@laptop.sanja.is.com.ua> | 2003-10-06 20:55:06 +0300 |
---|---|---|
committer | unknown <bell@laptop.sanja.is.com.ua> | 2003-10-06 20:55:06 +0300 |
commit | ff8c8fa1efc4a622e571381c34c5a4575d0f3b5e (patch) | |
tree | f658418875ab7ed1c8c3198dc5135387bf23a7a6 /mysql-test/r/subselect.result | |
parent | 12b769418af82303239d43447def128ed933b440 (diff) | |
parent | 6fc8b48c4652d404b38146844d17ed2a01a3b327 (diff) | |
download | mariadb-git-ff8c8fa1efc4a622e571381c34c5a4575d0f3b5e.tar.gz |
Merge
mysql-test/r/union.result:
Auto merged
mysql-test/t/union.test:
Auto merged
sql/item.cc:
Auto merged
sql/item.h:
Auto merged
sql/item_cmpfunc.cc:
Auto merged
sql/item_cmpfunc.h:
Auto merged
sql/item_subselect.cc:
Auto merged
sql/item_subselect.h:
Auto merged
sql/item_sum.cc:
Auto merged
sql/item_sum.h:
Auto merged
sql/mysql_priv.h:
Auto merged
sql/sql_base.cc:
Auto merged
sql/sql_class.cc:
Auto merged
sql/sql_class.h:
Auto merged
sql/sql_derived.cc:
Auto merged
sql/sql_union.cc:
Auto merged
sql/sql_yacc.yy:
Auto merged
mysql-test/r/subselect.result:
SCCS merged
mysql-test/t/subselect.test:
SCCS merged
Diffstat (limited to 'mysql-test/r/subselect.result')
-rw-r--r-- | mysql-test/r/subselect.result | 108 |
1 files changed, 88 insertions, 20 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 81eae4a2efe..f8d7da5a92e 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -42,6 +42,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1275 Field or reference 'a' of SELECT #3 was resolved in SELECT #1 +Note 1275 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -207,6 +210,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where +Warnings: +Note 1275 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 @@ -290,8 +295,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Note 1275 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 +Note 1275 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); create table t7( uq int primary key, name char(25)); insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); @@ -305,6 +313,8 @@ explain select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 +Warnings: +Note 1275 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column: 'a' in field list is ambiguous drop table if exists t1,t2,t3; @@ -348,7 +358,7 @@ SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); pseudo joce SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%'); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; CREATE TABLE `t1` ( `topic` mediumint(8) unsigned NOT NULL default '0', @@ -378,7 +388,7 @@ SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; 1 1 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row 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 @@ -465,9 +475,9 @@ UNIQUE KEY `maxnumrep` (`maxnumrep`) ) TYPE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1); select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -480,7 +490,7 @@ drop table t1; CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b'); INSERT INTO t1 VALUES (); SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b'); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL default '0', @@ -498,7 +508,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away @@ -525,7 +535,7 @@ a b update t1 set b= (select b from t1); ERROR HY000: You can't specify target table 't1' for update in FROM clause update t1 set b= (select b from t2); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); select * from t1; a b @@ -548,7 +558,7 @@ a b delete from t1 where b = (select b from t1); ERROR HY000: You can't specify target table 't1' for update in FROM clause delete from t1 where b = (select b from t2); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); select * from t1; a b @@ -574,7 +584,7 @@ a b delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); ERROR HY000: You can't specify target table 't12' for update in FROM clause delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); select * from t11; a b @@ -593,7 +603,7 @@ insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); ERROR HY000: You can't specify target table 't1' for update in FROM clause INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); select * from t1; x @@ -635,7 +645,7 @@ x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); ERROR HY000: You can't specify target table 't1' for update in FROM clause replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); select * from t1; x y @@ -706,7 +716,7 @@ id CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; INSERT INTO t1 values (1),(1); UPDATE t2 SET id=(SELECT * FROM t1); -ERROR 21000: Subselect returns more than 1 record +ERROR 21000: Subquery returns more than 1 row drop table t2, t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -813,6 +823,7 @@ explain select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Warnings: +Note 1275 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 Note 1248 Select 2 was reduced during optimisation select (select a+1) from t1; (select a+1) @@ -833,7 +844,7 @@ a t1.a in (select t2.a from t2) explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index -2 DEPENDENT SUBQUERY t2 index_in a a 5 func 2 Using where; Using index +2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 Using index 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; @@ -1039,6 +1050,9 @@ t1 CREATE TABLE `t1` ( ) TYPE=MyISAM CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a; +select * from t1; +a +2 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1176,7 +1190,7 @@ insert into t1 values (1,0), (2,0), (3,0); insert into t2 values (1,1), (2,1), (3,1), (2,2); update ignore t1 set b=(select b from t2 where t1.a=t2.a); Warnings: -Error 1241 Subselect returns more than 1 record +Error 1241 Subquery returns more than 1 row select * from t1; a b 1 1 @@ -1244,7 +1258,7 @@ a explain select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 simple_in PRIMARY PRIMARY 4 func 1 Using index +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1252,7 +1266,7 @@ a explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 simple_in PRIMARY PRIMARY 4 func 1 Using index; Using where +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1277,7 +1291,7 @@ a explain select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_in a a 5 func 1001 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1285,7 +1299,7 @@ a explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_in a a 5 func 1001 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1308,7 +1322,7 @@ a explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_in a a 5 func 1001 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -1348,6 +1362,60 @@ a 2 10 drop table t1,t2,t3; +CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, +s2 CHAR(5) COLLATE latin1_swedish_ci); +INSERT INTO t1 VALUES ('z','?'); +select * from t1 where s1 > (select max(s2) from t1); +ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' +select * from t1 where s1 > any (select max(s2) from t1); +ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' +drop table t1; +create table t1(toid int,rd int); +create table t2(userid int,pmnew int,pmtotal int); +insert into t2 values(1,0,0),(2,0,0); +insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2); +select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1); +userid pmtotal pmnew calc_total calc_new +1 0 0 9 3 +2 0 0 4 2 +drop table t1, t2; +create table t1 (s1 char(5)); +select (select 'a','b' from t1 union select 'a','b' from t1) from t1; +ERROR 21000: Cardinality error (more/less than 1 columns) +insert into t1 values ('tttt'); +select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1); +s1 +tttt +explain (select * from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +(select * from t1); +s1 +tttt +drop table t1; +create table t1 (s1 char(5), index s1(s1)); +create table t2 (s1 char(5), index s1(s1)); +insert into t1 values ('a1'),('a2'),('a3'); +insert into t2 values ('a1'),('a2'); +select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; +s1 s1 NOT IN (SELECT s1 FROM t2) +a1 0 +a2 0 +a3 1 +select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; +s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') +a1 0 +a2 1 +a3 1 +explain select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index +explain select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 1 Using index; Using where +drop table t1,t2; create table t2 (a int, b int); create table t3 (a int); insert into t3 values (6),(7),(3); |