summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect.result')
-rw-r--r--mysql-test/r/subselect.result100
1 files changed, 80 insertions, 20 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cf02eda9aba..d29609f5f85 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
@@ -286,8 +291,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 record
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");
@@ -301,6 +309,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;
@@ -344,7 +354,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 record
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
CREATE TABLE `t1` (
`topic` mediumint(8) unsigned NOT NULL default '0',
@@ -374,7 +384,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 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
@@ -461,9 +471,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 record
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 record
drop table t1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
@@ -476,7 +486,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 record
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
@@ -494,7 +504,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 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 NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
@@ -521,7 +531,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 record
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
a b
@@ -544,7 +554,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 record
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
a b
@@ -570,7 +580,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 record
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
@@ -589,7 +599,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 record
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
x
@@ -631,7 +641,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 record
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x y
@@ -702,7 +712,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 record
drop table t2, t1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
@@ -809,6 +819,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)
@@ -829,7 +840,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;
@@ -1035,6 +1046,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` (
@@ -1172,7 +1186,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 record
select * from t1;
a b
1 1
@@ -1240,7 +1254,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
@@ -1248,7 +1262,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
@@ -1273,7 +1287,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
@@ -1281,7 +1295,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
@@ -1304,7 +1318,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);
@@ -1352,3 +1366,49 @@ ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_
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;
View Lorry Controller Status