← Back to branch summary

~ubuntu-branches/ubuntu/saucy/drizzle/saucy-proposed

« back to all changes in this revision

Viewing changes to tests/r/derived.result

  • Committer: Bazaar Package Importer
  • Author(s): Monty Taylor
  • Date: 2010-03-18 12:12:31 UTC
  • Revision ID: james.westby@ubuntu.com-20100318121231-k6g1xe6cshbwa0f8
Tags: upstream-2010.03.1347
Import upstream version 2010.03.1347

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/derived.result
 
1
drop table if exists t1,t2,t3;
 
2
select * from (select 2) b;
 
3
2
 
4
2
 
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;
 
14
a       y
 
15
3       3
 
16
3       3
 
17
select t1.a,t3.a from t1,(select * from t2  where b='c') as t3  where t1.a = t3.a;
 
18
a       a
 
19
3       3
 
20
3       3
 
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;
 
24
a       y
 
25
3       3
 
26
3       3
 
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;
 
32
a       a
 
33
1       2
 
34
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
 
35
a       a
 
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;
 
41
a       b       a       b
 
42
1       a       1       a
 
43
2       b       1       a
 
44
3       c       1       a
 
45
3       c       1       a
 
46
1       a       2       b
 
47
2       b       2       b
 
48
3       c       2       b
 
49
3       c       2       b
 
50
1       a       3       c
 
51
2       b       3       c
 
52
3       c       3       c
 
53
3       c       3       c
 
54
1       a       3       c
 
55
2       b       3       c
 
56
3       c       3       c
 
57
3       c       3       c
 
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;
 
65
1
 
66
1
 
67
select a from (select 1 as a) as b;
 
68
a
 
69
1
 
70
select 1 from (select 1) as a;
 
71
1
 
72
1
 
73
select * from (select * from t1 union select * from t1) a;
 
74
a       b
 
75
1       a
 
76
2       b
 
77
3       c
 
78
select * from (select * from t1 union all select * from t1) a;
 
79
a       b
 
80
1       a
 
81
2       b
 
82
3       c
 
83
3       c
 
84
1       a
 
85
2       b
 
86
3       c
 
87
3       c
 
88
select * from (select * from t1 union all select * from t1 limit 2) a;
 
89
a       b
 
90
1       a
 
91
2       b
 
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;
 
107
a       b
 
108
1       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;
 
110
a       b
 
111
1       a
 
112
2       b
 
113
3       c
 
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      ALL     NULL    NULL    NULL    NULL    1       
 
118
2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    4       Using where; Using join buffer
 
119
drop table t1, t2;
 
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;
 
122
a       t
 
123
1       1
 
124
2       2
 
125
3       3
 
126
4       4
 
127
5       5
 
128
6       6
 
129
7       7
 
130
8       8
 
131
9       9
 
132
10      10
 
133
11      11
 
134
12      12
 
135
13      13
 
136
14      14
 
137
15      15
 
138
16      16
 
139
17      17
 
140
18      18
 
141
19      19
 
142
20      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 tt1     index   NULL    a       4       NULL    X       Using index
 
146
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    X       Using join buffer
 
147
2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    X       
 
148
drop table t1;
 
149
SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
 
150
(SELECT * FROM (SELECT 1 as a) as a )
 
151
1
 
152
select * from (select 1 as a) b  left join (select 2 as a) c using(a);
 
153
a
 
154
1
 
155
SELECT * FROM (SELECT 1 UNION SELECT a) b;
 
156
ERROR 42S22: Unknown column 'a' in 'field list'
 
157
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
 
158
ERROR 42S22: Unknown column 'a' in 'field list'
 
159
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
 
160
ERROR 42S22: Unknown column 'a' in 'field list'
 
161
select 1 from  (select 2) a order by 0;
 
162
ERROR 42S22: Unknown column '0' in 'order clause'
 
163
create table t1 (id int);
 
164
insert into t1 values (1),(2),(3);
 
165
describe select * from (select * from t1 group by id) bar;
 
166
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
167
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    3       
 
168
2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    3       Using temporary; Using filesort
 
169
drop table t1;
 
170
create table t1 (mat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test INT NULL);
 
171
create table t2 (mat_id INT NOT NULL, pla_id INT NOT NULL);
 
172
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);
 
173
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
 
174
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;
 
175
pla_id  mat_id
 
176
100     1
 
177
101     1
 
178
102     1
 
179
103     2
 
180
104     2
 
181
105     3
 
182
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;
 
183
pla_id  test
 
184
100     1
 
185
101     1
 
186
102     1
 
187
103     2
 
188
104     2
 
189
105     3
 
190
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;
 
191
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
192
1       PRIMARY m2      ALL     NULL    NULL    NULL    NULL    9       
 
193
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    6       Using where; Using join buffer
 
194
2       DERIVED mp      ALL     NULL    NULL    NULL    NULL    9       Using temporary; Using filesort
 
195
2       DERIVED m1      eq_ref  PRIMARY PRIMARY 4       test.mp.mat_id  1       
 
196
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;
 
197
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
198
1       PRIMARY m2      ALL     NULL    NULL    NULL    NULL    9       
 
199
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    6       Using where; Using join buffer
 
200
2       DERIVED mp      ALL     NULL    NULL    NULL    NULL    9       Using temporary; Using filesort
 
201
2       DERIVED m1      eq_ref  PRIMARY PRIMARY 4       test.mp.mat_id  1       
 
202
drop table t1,t2;
 
203
SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;
 
204
x
 
205
1
 
206
create table t1 (a int);
 
207
insert into t1 values (1),(2),(3);
 
208
delete from (select * from t1);
 
209
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '(select * from t1)' at line 1
 
210
insert into  (select * from t1) values (5);
 
211
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '(select * from t1) values (5)' at line 1
 
212
drop table t1;
 
213
create table t1 (E1 INTEGER NOT NULL, E2 INTEGER NOT NULL, E3 INTEGER NOT NULL, PRIMARY KEY(E1)
 
214
);
 
215
insert into t1 VALUES(1,1,1), (2,2,1);
 
216
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;
 
217
count(*)
 
218
2
 
219
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;
 
220
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
221
1       PRIMARY t1      ALL     PRIMARY NULL    NULL    NULL    2       Using where
 
222
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    2       Using where; Using join buffer
 
223
2       DERIVED A       ALL     NULL    NULL    NULL    NULL    2       Using where
 
224
3       DEPENDENT SUBQUERY      B       ALL     NULL    NULL    NULL    NULL    2       Using where
 
225
drop table t1;
 
226
create table t1 (a int);
 
227
insert into t1 values (1),(2);
 
228
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 
229
a       a
 
230
1       1
 
231
2       1
 
232
1       2
 
233
2       2
 
234
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 
235
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
236
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    2       
 
237
1       PRIMARY <derived4>      ALL     NULL    NULL    NULL    NULL    2       Using join buffer
 
238
4       DERIVED t1      ALL     NULL    NULL    NULL    NULL    2       
 
239
5       UNION   t1      ALL     NULL    NULL    NULL    NULL    2       
 
240
NULL    UNION RESULT    <union4,5>      ALL     NULL    NULL    NULL    NULL    NULL    
 
241
2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    2       
 
242
3       UNION   t1      ALL     NULL    NULL    NULL    NULL    2       
 
243
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
 
244
drop table t1;
 
245
CREATE TEMPORARY TABLE t1 (
 
246
OBJECTID int NOT NULL default 0,
 
247
SORTORDER int NOT NULL auto_increment,
 
248
KEY t1_SortIndex (SORTORDER),
 
249
KEY t1_IdIndex (OBJECTID)
 
250
) ENGINE=MyISAM;
 
251
CREATE TEMPORARY TABLE t2 (
 
252
ID int default NULL,
 
253
PARID int default NULL,
 
254
UNIQUE KEY t2_ID_IDX (ID),
 
255
KEY t2_PARID_IDX (PARID)
 
256
) engine=MyISAM;
 
257
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
 
258
CREATE TEMPORARY TABLE t3 (
 
259
ID int default NULL,
 
260
DATA decimal(10,2) default NULL,
 
261
UNIQUE KEY t3_ID_IDX (ID)
 
262
) engine=MyISAM;
 
263
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);
 
264
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;
 
265
497     ID      NULL
 
266
drop table t1, t2, t3;
 
267
CREATE TABLE t1 (name char(1) default NULL, val int default NULL);
 
268
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);
 
269
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;
 
270
name    median
 
271
a       7.0000
 
272
b       3.5000
 
273
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;
 
274
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
275
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    3       Using temporary; Using filesort
 
276
2       DERIVED x       ALL     NULL    NULL    NULL    NULL    17      Using temporary; Using filesort
 
277
2       DERIVED y       ALL     NULL    NULL    NULL    NULL    17      Using where; Using join buffer
 
278
drop table t1;
 
279
create table t2 (a int, b int, primary key (a));
 
280
insert into t2 values (1,7),(2,7);
 
281
explain select a from t2 where a>1;
 
282
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
283
1       SIMPLE  t2      range   PRIMARY PRIMARY 4       NULL    1       Using where; Using index
 
284
explain select a from (select a from t2 where a>1) tt;
 
285
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
286
1       PRIMARY <derived2>      system  NULL    NULL    NULL    NULL    1       
 
287
2       DERIVED t2      range   PRIMARY PRIMARY 4       NULL    1       Using where; Using index
 
288
drop table t2;
 
289
CREATE TABLE `t1` ( `itemid` int NOT NULL default 0, `grpid` varchar(15) NOT NULL default '', `vendor` int NOT NULL default 0, `date_` date NOT NULL default '1900-01-01', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY  (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
 
290
insert into t1 values (128, 'rozn', 2, curdate(), 10),
 
291
(128, 'rozn', 1, curdate(), 10);
 
292
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;
 
293
min     max     avg
 
294
10.00   10.00   10
 
295
DROP TABLE t1;
 
296
create table t1 (a integer, b integer);
 
297
insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
 
298
select distinct sum(b) from t1 group by a;
 
299
sum(b)
 
300
4
 
301
select distinct sum(b) from (select a,b from t1) y group by a;
 
302
sum(b)
 
303
4
 
304
drop table t1;
 
305
CREATE TABLE t1 (a char(10), b char(10));
 
306
INSERT INTO t1 VALUES ('root','localhost'), ('root','%');
 
307
SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c;
 
308
ERROR 21000: Subquery returns more than 1 row
 
309
DROP TABLE t1;
 
310
create table t1(a int);
 
311
create table t2(a int);
 
312
create table t3(a int);
 
313
insert into t1 values(1),(1);
 
314
insert into t2 values(2),(2);
 
315
insert into t3 values(3),(3);
 
316
select * from t1 union distinct select * from t2 union all select * from t3;
 
317
a
 
318
1
 
319
2
 
320
3
 
321
3
 
322
select * from (select * from t1 union distinct select * from t2 union all select * from t3) X;
 
323
a
 
324
1
 
325
2
 
326
3
 
327
3
 
328
drop table t1, t2, t3;
 
329
create table t1 (a int);
 
330
create table t2 (a int);
 
331
select * from (select * from t1,t2) foo;
 
332
a       a
 
333
drop table t1,t2;
 
334
create table t1 (ID int not null auto_increment,
 
335
DATA varchar(5) not null, primary key (ID));
 
336
create table t2 (ID int not null auto_increment,
 
337
DATA varchar(5) not null, FID int not null,
 
338
primary key (ID));
 
339
select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID);
 
340
ID      DATA    FID
 
341
select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID);
 
342
ID      DATA    FID
 
343
select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID;
 
344
ID      DATA    FID
 
345
drop table t1, t2;