1
by brian
clean slate |
1 |
drop table if exists t1,t2,t3;
|
722.2.27
by Monty Taylor
Enabled derived test. |
2 |
select * from (select 2) b;
|
1
by brian
clean slate |
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
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
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
|
|
1
by brian
clean slate |
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
|
|
722.2.32
by Monty Taylor
Fixed non-deterministic innodb explain output. |
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
|
|
1
by brian
clean slate |
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;
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
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);
|
|
1
by brian
clean slate |
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
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
195 |
2 DERIVED m1 eq_ref PRIMARY PRIMARY 4 test.mp.mat_id 1
|
1
by brian
clean slate |
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
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
201 |
2 DERIVED m1 eq_ref PRIMARY PRIMARY 4 test.mp.mat_id 1
|
1
by brian
clean slate |
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);
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
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
|
1
by brian
clean slate |
210 |
insert into (select * from t1) values (5);
|
722.2.27
by Monty Taylor
Enabled derived test. |
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
|
1
by brian
clean slate |
212 |
drop table t1;
|
722.2.27
by Monty Taylor
Enabled derived test. |
213 |
create table t1 (E1 INTEGER NOT NULL, E2 INTEGER NOT NULL, E3 INTEGER NOT NULL, PRIMARY KEY(E1)
|
1
by brian
clean slate |
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
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
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
|
|
1
by brian
clean slate |
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;
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
245 |
CREATE TEMPORARY TABLE t1 (
|
722.2.27
by Monty Taylor
Enabled derived test. |
246 |
OBJECTID int NOT NULL default 0,
|
247 |
SORTORDER int NOT NULL auto_increment,
|
|
1
by brian
clean slate |
248 |
KEY t1_SortIndex (SORTORDER),
|
249 |
KEY t1_IdIndex (OBJECTID)
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
250 |
) ENGINE=MyISAM;
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
251 |
CREATE TEMPORARY TABLE t2 (
|
722.2.27
by Monty Taylor
Enabled derived test. |
252 |
ID int default NULL,
|
253 |
PARID int default NULL,
|
|
1
by brian
clean slate |
254 |
UNIQUE KEY t2_ID_IDX (ID),
|
255 |
KEY t2_PARID_IDX (PARID)
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
256 |
) engine=MyISAM;
|
1
by brian
clean slate |
257 |
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
258 |
CREATE TEMPORARY TABLE t3 (
|
722.2.27
by Monty Taylor
Enabled derived test. |
259 |
ID int default NULL,
|
1
by brian
clean slate |
260 |
DATA decimal(10,2) default NULL,
|
261 |
UNIQUE KEY t3_ID_IDX (ID)
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
262 |
) engine=MyISAM;
|
1
by brian
clean slate |
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;
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
267 |
CREATE TABLE t1 (name char(1) default NULL, val int default NULL);
|
1
by brian
clean slate |
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
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
283 |
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index
|
1
by brian
clean slate |
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
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
287 |
2 DERIVED t2 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index
|
1
by brian
clean slate |
288 |
drop table t2;
|
722.2.27
by Monty Taylor
Enabled derived test. |
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_`));
|
1
by brian
clean slate |
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;
|
|
722.2.27
by Monty Taylor
Enabled derived test. |
332 |
a a
|
1
by brian
clean slate |
333 |
drop table t1,t2;
|
722.2.27
by Monty Taylor
Enabled derived test. |
334 |
create table t1 (ID int not null auto_increment,
|
1
by brian
clean slate |
335 |
DATA varchar(5) not null, primary key (ID));
|
722.2.27
by Monty Taylor
Enabled derived test. |
336 |
create table t2 (ID int not null auto_increment,
|
337 |
DATA varchar(5) not null, FID int not null,
|
|
1
by brian
clean slate |
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;
|