summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/derived.result54
-rw-r--r--mysql-test/r/group_by.result2
-rw-r--r--mysql-test/r/select_found.result2
-rw-r--r--mysql-test/r/subselect.result76
-rw-r--r--mysql-test/t/derived.test26
-rw-r--r--mysql-test/t/group_by.test2
-rw-r--r--mysql-test/t/select_found.test2
-rw-r--r--mysql-test/t/subselect.test69
8 files changed, 165 insertions, 68 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index b397c2f7635..95640e69d8b 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -17,32 +17,55 @@ select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3
a y
3 3
3 3
-SELECT a FROM (SELECT 1 FROM (SELECT 1) HAVING a=1);
+SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b;
Unknown column 'a' in 'having clause'
-SELECT a,b as a FROM (SELECT '1' as a,'2' as b) HAVING a=1;
+SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b HAVING a=1;
Column: 'a' in having clause is ambiguous
-SELECT a,2 as a FROM (SELECT '1' as a) HAVING a=2;
+SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2;
a a
1 2
-SELECT a,2 as a FROM (SELECT '1' as a) HAVING a=1;
+SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
a a
-SELECT 1 FROM (SELECT 1) WHERE a=2;
+SELECT 1 FROM (SELECT 1) a WHERE a=2;
Unknown column 'a' in 'where clause'
-SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
+SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a;
Unknown column 'a' in 'having clause'
+select * from t1 as x1, (select * from t1) as x2;
+a b a b
+1 a 1 a
+2 b 1 a
+3 c 1 a
+3 c 1 a
+1 a 2 b
+2 b 2 b
+3 c 2 b
+3 c 2 b
+1 a 3 c
+2 b 3 c
+3 c 3 c
+3 c 3 c
+1 a 3 c
+2 b 3 c
+3 c 3 c
+3 c 3 c
+explain select * from t1 as x1, (select * from t1) as x2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY x1 ALL NULL NULL NULL NULL 4
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+2 DERIVED x1 ALL NULL NULL NULL NULL 4
drop table if exists t1.t2,t3;
-select * from (select 1);
+select * from (select 1) as a;
1
1
-select a from (select 1 as a);
+select a from (select 1 as a) as b;
a
1
-select 1 from (select 1);
+select 1 from (select 1) as a;
1
1
drop table if exists t1;
create table t1(a int not null, t char(8), index(a));
-SELECT * FROM (SELECT * FROM t1) ORDER BY a ASC LIMIT 0,20;
+SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20;
a t
1 1
2 2
@@ -64,7 +87,14 @@ a t
18 18
19 19
20 20
+explain select count(*) from t1 as tt1, (select * from t1) as tt2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY Select tables optimized away
+2 DERIVED tt1 index NULL a 4 NULL 10000 Using index
drop table if exists t1;
-SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a)));
-(SELECT * FROM (SELECT 1 as a))
+SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
+(SELECT * FROM (SELECT 1 as a) as a )
1
+select * from (select 1 as a) b left join (select 2 as a) c using(a);
+a a
+1 NULL
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index ead9935f824..b5a35b981db 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1,4 +1,4 @@
-SELECT 1 FROM (SELECT 1) GROUP BY SUM(1);
+SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
Invalid use of group function
drop table if exists t1,t2,t3;
CREATE TABLE t1 (
diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 8500e244d08..5b6c490b928 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -168,7 +168,7 @@ test2 2 2
SELECT FOUND_ROWS();
FOUND_ROWS()
2
-SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) LIMIT 0;
+SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0;
1
SELECT FOUND_ROWS();
FOUND_ROWS()
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index bbf70150ee0..42e9ff7012f 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -8,39 +8,39 @@ SELECT (SELECT 1) UNION SELECT (SELECT 2);
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
-SELECT (SELECT 1 FROM (SELECT 1) HAVING a=1) as a;
+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) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVING a=1) as b;
+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;
Reference 'b' not supported (forward reference in item list)
-SELECT (SELECT 1),MAX(1) FROM (SELECT 1);
+SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
(SELECT 1) MAX(1)
1 1
SELECT (SELECT a) as a;
Reference 'a' not supported (forward reference in item list)
-EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+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
-SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
SELECT (SELECT 1), a;
Unknown column 'a' in 'field list'
-SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1;
+SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
a
1
-SELECT 1 FROM (SELECT (SELECT a));
+SELECT 1 FROM (SELECT (SELECT a) b) c;
Unknown column 'a' in 'field list'
-SELECT * FROM (SELECT 1 as id) WHERE id IN (SELECT * FROM (SELECT 1 as id) 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 LIMIT 1);
id
1
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT 1,1);
+SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
Cardinality error (more/less than 1 columns)
SELECT 1 IN (SELECT 1);
1 IN (SELECT 1)
1
-SELECT 1 FROM (SELECT 1 as a) WHERE 1 IN (SELECT (SELECT a));
+SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
1
1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
@@ -319,7 +319,7 @@ SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='j
numeropost maxnumrep
43506 2
40143 1
-SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
+SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
Unknown column 'a' in 'having clause'
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
Unknown column 'a' in 'having clause'
@@ -368,7 +368,7 @@ Subselect returns more than 1 record
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) HAVING field='b');
+SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
Subselect returns more than 1 record
drop table t1;
CREATE TABLE `t1` (
@@ -398,7 +398,7 @@ id select_type table type possible_keys key key_len ref rows Extra
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
-SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1;
+SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
1
1
drop table t1;
@@ -411,6 +411,10 @@ a b
0 10
1 11
2 12
+update t1 set b= (select b from t1);
+INSERT TABLE 't1' isn't allowed in FROM table list
+update t1 set b= (select b from t2);
+Subselect returns more than 1 record
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
a b
@@ -430,6 +434,10 @@ a b
select * from t1 where b = (select b from t2 where t1.a = t2.a);
a b
2 12
+delete from t1 where b = (select b from t1);
+INSERT TABLE 't1' isn't allowed in FROM table list
+delete from t1 where b = (select b from t2);
+Subselect returns more than 1 record
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
a b
@@ -453,6 +461,10 @@ a b
33 10
22 11
2 12
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+INSERT TABLE 't12' isn't allowed in FROM table list
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
+Subselect 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
@@ -465,7 +477,13 @@ a b
drop table t11, t12, t2;
CREATE TABLE t1 (x int);
create table t2 (a int);
+create table t3 (a int);
insert into t2 values (1);
+insert into t3 values (1),(2);
+INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
+INSERT TABLE 't1' isn't allowed in FROM table list
+INSERT INTO t1 (x) VALUES ((SELECT a FROM t3));
+Subselect returns more than 1 record
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
x
@@ -485,20 +503,26 @@ x
3
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
INSERT TABLE 't1' isn't allowed in FROM table list
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t1));
+INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
select * from t1;
x
1
2
3
3
-9
-drop table t1, t2;
+0
+drop table t1, t2, t3;
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
+create table t3 (a int);
insert into t2 values (1);
+insert into t3 values (1),(2);
select * from t1;
x y
+replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
+INSERT TABLE 't1' isn't allowed in FROM table list
+replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
+Subselect 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
@@ -523,8 +547,8 @@ x y
1 3
4 2
2 1
-drop table t1, t2;
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *);
+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;
@@ -536,7 +560,7 @@ EXPLAIN SELECT * FROM t WHERE id IN (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
Warnings:
-Note 1246 Select 2 was reduced during optimisation
+Note 1247 Select 2 was reduced during optimisation
SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
id
1
@@ -548,7 +572,7 @@ 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
Warnings:
-Note 1246 Select 2 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
@@ -559,4 +583,14 @@ id
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
id
2
-drop table if exists t;
+INSERT INTO t VALUES ((SELECT * FROM t));
+INSERT TABLE 't' isn't allowed in FROM table list
+SELECT * FROM t;
+id
+1
+2
+CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 values (1),(1);
+UPDATE t SET id=(SELECT * FROM t1);
+Subselect returns more than 1 record
+drop table t;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index c1d8af4074a..8f187dd46ba 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -9,19 +9,21 @@ CREATE TABLE t3 (a int not null, b char (10) not null);
insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5 where t2.b=t5.b) as t4 where t1.a = t4.y;
--error 1054
-SELECT a FROM (SELECT 1 FROM (SELECT 1) HAVING a=1);
+SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b;
--error 1052
-SELECT a,b as a FROM (SELECT '1' as a,'2' as b) HAVING a=1;
-SELECT a,2 as a FROM (SELECT '1' as a) HAVING a=2;
-SELECT a,2 as a FROM (SELECT '1' as a) HAVING a=1;
+SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b HAVING a=1;
+SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2;
+SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
--error 1054
-SELECT 1 FROM (SELECT 1) WHERE a=2;
+SELECT 1 FROM (SELECT 1) a WHERE a=2;
--error 1054
-SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
+SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a;
+select * from t1 as x1, (select * from t1) as x2;
+explain select * from t1 as x1, (select * from t1) as x2;
drop table if exists t1.t2,t3;
-select * from (select 1);
-select a from (select 1 as a);
-select 1 from (select 1);
+select * from (select 1) as a;
+select a from (select 1 as a) as b;
+select 1 from (select 1) as a;
drop table if exists t1;
create table t1(a int not null, t char(8), index(a));
disable_query_log;
@@ -32,6 +34,8 @@ while ($1)
dec $1;
}
enable_query_log;
-SELECT * FROM (SELECT * FROM t1) ORDER BY a ASC LIMIT 0,20;
+SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20;
+explain select count(*) from t1 as tt1, (select * from t1) as tt2;
drop table if exists t1;
-SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a)));
+SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
+select * from (select 1 as a) b left join (select 2 as a) c using(a);
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 0f30fbd4cc6..4670feca500 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1,5 +1,5 @@
-- error 1111
-SELECT 1 FROM (SELECT 1) GROUP BY SUM(1);
+SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
#
# Test of group (Failed for Lars Hoss <lh@pbm.de>)
#
diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test
index 316e7894344..fb57224dd19 100644
--- a/mysql-test/t/select_found.test
+++ b/mysql-test/t/select_found.test
@@ -84,7 +84,7 @@ INSERT INTO t1 (titre,maxnumrep) VALUES
('test1','1'),('test2','2'),('test3','3');
SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1;
SELECT FOUND_ROWS();
-SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) LIMIT 0;
+SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0;
SELECT FOUND_ROWS();
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE numeropost > 1 LIMIT 0;
SELECT FOUND_ROWS();
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index c875f8372b3..9ed4250976b 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2,25 +2,24 @@ select (select 2);
SELECT (SELECT 1) UNION SELECT (SELECT 2);
SELECT (SELECT (SELECT 0 UNION SELECT 0));
-- error 1245
-SELECT (SELECT 1 FROM (SELECT 1) HAVING a=1) as a;
+SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
-- error 1245
-SELECT (SELECT 1 FROM (SELECT 1) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVING a=1) as b;
-SELECT (SELECT 1),MAX(1) FROM (SELECT 1);
+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;
+SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
-- error 1245
SELECT (SELECT a) as a;
-EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
-SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
+EXPLAIN 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;
-SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1;
+SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
-- error 1054
-SELECT 1 FROM (SELECT (SELECT a));
-SELECT * FROM (SELECT 1 as id) WHERE id IN (SELECT * FROM (SELECT 1 as id) ORDER BY id LIMIT 1);
+SELECT 1 FROM (SELECT (SELECT a) b) c;
+SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id LIMIT 1);
-- error 1239
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT 1,1);
+SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
SELECT 1 IN (SELECT 1);
-SELECT 1 FROM (SELECT 1 as a) WHERE 1 IN (SELECT (SELECT a));
-
+SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
create table t1 (a int);
create table t2 (a int, b int);
@@ -176,7 +175,7 @@ INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
-- error 1054
-SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
+SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
-- error 1054
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date);
@@ -210,7 +209,7 @@ drop table t1;
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
-- error 1240
-SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
+SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
drop table t1;
# threadhardwarefr7
@@ -236,7 +235,7 @@ drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
-SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1;
+SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
drop table t1;
#update with subselects
@@ -245,6 +244,10 @@ create table t2 (a int NOT NULL, b int, primary key (a));
insert into t1 values (0, 10),(1, 11),(2, 12);
insert into t2 values (1, 21),(2, 22),(3, 23);
select * from t1;
+-- error 1093
+update t1 set b= (select b from t1);
+-- error 1240
+update t1 set b= (select b from t2);
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
drop table t1, t2;
@@ -256,6 +259,10 @@ insert into t1 values (0, 10),(1, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t1;
select * from t1 where b = (select b from t2 where t1.a = t2.a);
+-- error 1093
+delete from t1 where b = (select b from t1);
+-- error 1240
+delete from t1 where b = (select b from t2);
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
drop table t1, t2;
@@ -270,6 +277,10 @@ insert into t12 values (33, 10),(22, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t11;
select * from t12;
+-- error 1093
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+-- error 1240
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
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;
select * from t12;
@@ -278,7 +289,13 @@ drop table t11, t12, t2;
#insert with subselects
CREATE TABLE t1 (x int);
create table t2 (a int);
+create table t3 (a int);
insert into t2 values (1);
+insert into t3 values (1),(2);
+-- error 1093
+INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
+-- error 1240
+INSERT INTO t1 (x) VALUES ((SELECT a FROM t3));
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
insert into t2 values (1);
@@ -289,16 +306,22 @@ INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
select * from t1;
-- error 1093
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t1));
+INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-- sleep 1
select * from t1;
-drop table t1, t2;
+drop table t1, t2, t3;
#replace with subselects
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
+create table t3 (a int);
insert into t2 values (1);
+insert into t3 values (1),(2);
select * from t1;
+-- error 1093
+replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
+-- error 1240
+replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
@@ -311,10 +334,10 @@ replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2
select * from t1;
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
-drop table t1, t2;
+drop table t1, t2, t3;
-- error 1096
-SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *);
+SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
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);
@@ -326,5 +349,11 @@ EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
-drop table if exists t;
-
+-- error 1093
+INSERT INTO t VALUES ((SELECT * FROM t));
+SELECT * FROM t;
+CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 values (1),(1);
+-- error 1240
+UPDATE t SET id=(SELECT * FROM t1);
+drop table t;
View Lorry Controller Status