1
drop table if exists t1,t2,t3;
2
select * from (select 2 from DUAL) b;
5
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
6
ERROR 42S22: Unknown column 'a' in 'field list'
7
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
8
ERROR 42S22: Unknown column 'a' in 'field list'
9
CREATE TABLE t1 (a int not null, b char (10) not null);
10
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
11
CREATE TABLE t2 (a int not null, b char (10) not null);
12
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
13
select t1.a,t3.y from t1,(select a as y from t2 where b='c') as t3 where t1.a = t3.y;
17
select t1.a,t3.a from t1,(select * from t2 where b='c') as t3 where t1.a = t3.a;
21
CREATE TABLE t3 (a int not null, b char (10) not null);
22
insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
23
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;
27
SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b;
28
ERROR 42S22: Unknown column 'a' in 'having clause'
29
SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b HAVING a=1;
30
ERROR 23000: Column 'a' in having clause is ambiguous
31
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2;
34
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
36
SELECT 1 FROM (SELECT 1) a WHERE a=2;
37
ERROR 42S22: Unknown column 'a' in 'where clause'
38
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a;
39
ERROR 42S22: Unknown column 'a' in 'having clause'
40
select * from t1 as x1, (select * from t1) as x2;
58
explain select * from t1 as x1, (select * from t1) as x2;
59
id select_type table type possible_keys key key_len ref rows Extra
60
1 PRIMARY x1 ALL NULL NULL NULL NULL 4
61
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer
62
2 DERIVED t1 ALL NULL NULL NULL NULL 4
63
drop table if exists t2,t3;
64
select * from (select 1) as a;
67
select a from (select 1 as a) as b;
70
select 1 from (select 1) as a;
73
select * from (select * from t1 union select * from t1) a;
78
select * from (select * from t1 union all select * from t1) a;
88
select * from (select * from t1 union all select * from t1 limit 2) a;
92
explain select * from (select * from t1 union select * from t1) a;
93
id select_type table type possible_keys key key_len ref rows Extra
94
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
95
2 DERIVED t1 ALL NULL NULL NULL NULL 4
96
3 UNION t1 ALL NULL NULL NULL NULL 4
97
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
98
explain select * from (select * from t1 union all select * from t1) a;
99
id select_type table type possible_keys key key_len ref rows Extra
100
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8
101
2 DERIVED t1 ALL NULL NULL NULL NULL 4
102
3 UNION t1 ALL NULL NULL NULL NULL 4
103
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
104
CREATE TABLE t2 (a int not null);
105
insert into t2 values(1);
106
select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a;
109
select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a;
114
explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
115
id select_type table type possible_keys key key_len ref rows Extra
116
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
117
2 DERIVED t2 system NULL NULL NULL NULL 1
118
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where
120
create table t1(a int not null, t char(8), index(a));
121
SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20;
143
explain select count(*) from t1 as tt1, (select * from t1) as tt2;
144
id select_type table type possible_keys key key_len ref rows Extra
145
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
146
2 DERIVED t1 ALL NULL NULL NULL NULL 10000
148
SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
149
(SELECT * FROM (SELECT 1 as a) as a )
151
select * from (select 1 as a) b left join (select 2 as a) c using(a);
154
SELECT * FROM (SELECT 1 UNION SELECT a) b;
155
ERROR 42S22: Unknown column 'a' in 'field list'
156
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
157
ERROR 42S22: Unknown column 'a' in 'field list'
158
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
159
ERROR 42S22: Unknown column 'a' in 'field list'
160
select 1 from (select 2) a order by 0;
161
ERROR 42S22: Unknown column '0' in 'order clause'
162
create table t1 (id int);
163
insert into t1 values (1),(2),(3);
164
describe select * from (select * from t1 group by id) bar;
165
id select_type table type possible_keys key key_len ref rows Extra
166
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
167
2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
169
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
170
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
171
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
172
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
173
SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
181
SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
189
explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
190
id select_type table type possible_keys key key_len ref rows Extra
191
1 PRIMARY m2 ALL NULL NULL NULL NULL 9
192
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer
193
2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
194
2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1
195
explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
196
id select_type table type possible_keys key key_len ref rows Extra
197
1 PRIMARY m2 ALL NULL NULL NULL NULL 9
198
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer
199
2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
200
2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1
202
SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;
205
create user mysqltest_1;
206
create table t1 select 1 as a;
207
select 2 as a from (select * from t1) b;
208
ERROR 3D000: No database selected
210
select 2 as a from (select * from t1) b;
214
select mail_id, if(folder.f_description!='', folder.f_description, folder.f_name) as folder_name, date, address_id, phrase, address, subject from folder, (select mail.mail_id as mail_id, date_format(mail.h_date, '%b %e, %Y %h:%i') as date, mail.folder_id, sender.address_id as address_id, sender.phrase as phrase, sender.address as address, mail.h_subject as subject from mail left join mxa as mxa_sender on mail.mail_id=mxa_sender.mail_id and mxa_sender.type='from' left join address as sender on mxa_sender.address_id=sender.address_id mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_recipient.mail_id and mxa_recipient.address_id=recipient.address_id and mxa_recipient.type='to' and match(sender.phrase, sender.address, sender.comment) against ('jeremy' in boolean mode) and match(recipient.phrase, recipient.address, recipient.comment) against ('monty' in boolean mode) order by mail.h_date desc limit 0, 25 ) as query where query.folder_id=folder.folder_id;
215
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_r' at line 1
216
create table t1 (a int);
217
insert into t1 values (1),(2),(3);
218
update (select * from t1) as t1 set a = 5;
219
ERROR HY000: The target table t1 of the UPDATE is not updatable
220
delete from (select * from t1);
221
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1)' at line 1
222
insert into (select * from t1) values (5);
223
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1) values (5)' at line 1
225
create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1)
227
insert into t1 VALUES(1,1,1), (2,2,1);
228
select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
231
explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
232
id select_type table type possible_keys key key_len ref rows Extra
233
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
234
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 THEMAX.E2 1 Using where
235
2 DERIVED A ALL NULL NULL NULL NULL 2 Using where
236
3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where
238
create table t1 (a int);
239
insert into t1 values (1),(2);
240
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
246
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
247
id select_type table type possible_keys key key_len ref rows Extra
248
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
249
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 2 Using join buffer
250
4 DERIVED t1 ALL NULL NULL NULL NULL 2
251
5 UNION t1 ALL NULL NULL NULL NULL 2
252
NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL
253
2 DERIVED t1 ALL NULL NULL NULL NULL 2
254
3 UNION t1 ALL NULL NULL NULL NULL 2
255
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
258
`N` int(11) unsigned NOT NULL default '0',
259
`M` tinyint(1) default '0'
260
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
261
INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0);
262
UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
271
UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;
272
ERROR HY000: The target table P2 of the UPDATE is not updatable
273
UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
274
ERROR 42S22: Unknown column 'aaaa' in 'field list'
275
delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
279
delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N;
280
ERROR HY000: The target table p2 of the DELETE is not updatable
281
delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
282
ERROR 42S22: Unknown column 'aaa' in 'field list'
285
OBJECTID int(11) NOT NULL default '0',
286
SORTORDER int(11) NOT NULL auto_increment,
287
KEY t1_SortIndex (SORTORDER),
288
KEY t1_IdIndex (OBJECTID)
289
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
291
ID int(11) default NULL,
292
PARID int(11) default NULL,
293
UNIQUE KEY t2_ID_IDX (ID),
294
KEY t2_PARID_IDX (PARID)
295
) engine=MyISAM DEFAULT CHARSET=latin1;
296
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
298
ID int(11) default NULL,
299
DATA decimal(10,2) default NULL,
300
UNIQUE KEY t3_ID_IDX (ID)
301
) engine=MyISAM DEFAULT CHARSET=latin1;
302
INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
303
select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
305
drop table t1, t2, t3;
306
CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
307
INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
308
SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
312
explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
313
id select_type table type possible_keys key key_len ref rows Extra
314
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
315
2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort
316
2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer
318
create table t2 (a int, b int, primary key (a));
319
insert into t2 values (1,7),(2,7);
320
explain select a from t2 where a>1;
321
id select_type table type possible_keys key key_len ref rows Extra
322
1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
323
explain select a from (select a from t2 where a>1) tt;
324
id select_type table type possible_keys key key_len ref rows Extra
325
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
326
2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
328
CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
329
insert into t1 values (128, 'rozn', 2, curdate(), 10),
330
(128, 'rozn', 1, curdate(), 10);
331
SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices;
335
create table t1 (a integer, b integer);
336
insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
337
select distinct sum(b) from t1 group by a;
340
select distinct sum(b) from (select a,b from t1) y group by a;
344
CREATE TABLE t1 (a char(10), b char(10));
345
INSERT INTO t1 VALUES ('root','localhost'), ('root','%');
346
SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c;
347
ERROR 21000: Subquery returns more than 1 row
349
create table t1(a int);
350
create table t2(a int);
351
create table t3(a int);
352
insert into t1 values(1),(1);
353
insert into t2 values(2),(2);
354
insert into t3 values(3),(3);
355
select * from t1 union distinct select * from t2 union all select * from t3;
361
select * from (select * from t1 union distinct select * from t2 union all select * from t3) X;
367
drop table t1, t2, t3;
368
create table t1 (a int);
369
create table t2 (a int);
370
select * from (select * from t1,t2) foo;
371
ERROR 42S21: Duplicate column name 'a'
373
create table t1 (ID int unsigned not null auto_increment,
374
DATA varchar(5) not null, primary key (ID));
375
create table t2 (ID int unsigned not null auto_increment,
376
DATA varchar(5) not null, FID int unsigned not null,
378
select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID);
380
select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID);
382
select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID;
385
drop user mysqltest_1;
added
removed
mysql-test/r/derived.result
