Debian Codegraph @

mysql-test/suite/storage_engine/select.result

raw
1 DROP TABLE IF EXISTS t1, t2;
2 CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
3 INSERT INTO t1 (a,b) VALUES (100,'foobar'),(1,'z'),(200,'bar');
4 CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
5 INSERT INTO t2 (a,b) SELECT a, b FROM t1;
6 INSERT INTO t1 (a,b) SELECT a, b FROM t2;
7 SELECT * FROM t1;
8 a b
9 1 z
10 1 z
11 100 foobar
12 100 foobar
13 200 bar
14 200 bar
15 SELECT DISTINCT a FROM t1;
16 a
17 1
18 100
19 200
20 SELECT ALL b, a FROM t1;
21 b a
22 bar 200
23 bar 200
24 foobar 100
25 foobar 100
26 z 1
27 z 1
28 SELECT STRAIGHT_JOIN SQL_CACHE t1.* FROM t2, t1 WHERE t1.a <> t2.a;
29 a b
30 1 z
31 1 z
32 1 z
33 1 z
34 100 foobar
35 100 foobar
36 100 foobar
37 100 foobar
38 200 bar
39 200 bar
40 200 bar
41 200 bar
42 SELECT SQL_SMALL_RESULT SQL_NO_CACHE t1.a FROM t1, t2;
43 a
44 1
45 1
46 1
47 1
48 1
49 1
50 100
51 100
52 100
53 100
54 100
55 100
56 200
57 200
58 200
59 200
60 200
61 200
62 SELECT SQL_BIG_RESULT SQL_CALC_FOUND_ROWS DISTINCT(t2.a)
63 FROM t1 t1_1, t2, t1 t1_2;
64 a
65 1
66 100
67 200
68 SELECT FOUND_ROWS();
69 FOUND_ROWS()
70 3
71 SET GLOBAL query_cache_type = ON;
72 SET query_cache_type = ON;
73 SELECT SQL_CACHE * FROM t1, t2;
74 a b a b
75 1 z 1 z
76 1 z 1 z
77 1 z 100 foobar
78 1 z 100 foobar
79 1 z 200 bar
80 1 z 200 bar
81 100 foobar 1 z
82 100 foobar 1 z
83 100 foobar 100 foobar
84 100 foobar 100 foobar
85 100 foobar 200 bar
86 100 foobar 200 bar
87 200 bar 1 z
88 200 bar 1 z
89 200 bar 100 foobar
90 200 bar 100 foobar
91 200 bar 200 bar
92 200 bar 200 bar
93 SET GLOBAL query_cache_type = DEFAULT;
94 SELECT a+10 AS field1, CONCAT(b,':',b) AS field2 FROM t1
95 WHERE b > 'b' AND a IS NOT NULL
96 GROUP BY 2 DESC, field1 ASC
97 HAVING field1 < 1000
98 ORDER BY field2, 1 DESC, field1*2
99 LIMIT 5 OFFSET 1 ROWS EXAMINED 100000;
100 field1 field2
101 11 z:z
102 110 foobar:foobar
103 SELECT SUM(a), MAX(a), b FROM t1 GROUP BY b WITH ROLLUP;
104 SUM(a) MAX(a) b
105 2 1 z
106 200 100 foobar
107 400 200 bar
108 602 200 NULL
109 SELECT * FROM t2 WHERE a>0 PROCEDURE ANALYSE();
110 Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
111 test.t2.a 1 200 1 3 0 0 100.3333 81.2418 ENUM('1','100','200') NOT NULL
112 test.t2.b bar z 1 6 0 0 3.3333 NULL ENUM('bar','foobar','z') NOT NULL
113 SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a
114 INTO OUTFILE '<DATADIR>/select.out'
115 CHARACTER SET utf8
116 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''';
117 200,'bar'
118 200,'bar'
119 100,'foobar'
120 100,'foobar'
121 1,'z'
122 1,'z'
123 SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a
124 INTO DUMPFILE '<DATADIR>/select.dump';
125 ERROR 42000: Result consisted of more than one row
126 SELECT t1.*, t2.* FROM t1, t2 ORDER BY t2.b, t1.a, t2.a, t1.b LIMIT 1
127 INTO DUMPFILE '<DATADIR>/select.dump';
128 1z200bar
129 SELECT MIN(a), MAX(a) FROM t1 INTO @min, @max;
130 SELECT @min, @max;
131 @min @max
132 1 200
133 SELECT t1_1.*, t2.* FROM t2, t1 AS t1_1, t1 AS t1_2
134 WHERE t1_1.a = t1_2.a AND t2.a = t1_1.a;
135 a b a b
136 1 z 1 z
137 1 z 1 z
138 1 z 1 z
139 1 z 1 z
140 100 foobar 100 foobar
141 100 foobar 100 foobar
142 100 foobar 100 foobar
143 100 foobar 100 foobar
144 200 bar 200 bar
145 200 bar 200 bar
146 200 bar 200 bar
147 200 bar 200 bar
148 SELECT alias1.* FROM ( SELECT a,b FROM t1 ) alias1, t2 WHERE t2.a IN (100,200);
149 a b
150 1 z
151 1 z
152 1 z
153 1 z
154 100 foobar
155 100 foobar
156 100 foobar
157 100 foobar
158 200 bar
159 200 bar
160 200 bar
161 200 bar
162 SELECT t1.a FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a+10 };
163 a
164 1
165 1
166 100
167 100
168 200
169 200
170 SELECT t1.* FROM t2 INNER JOIN t1;
171 a b
172 1 z
173 1 z
174 1 z
175 1 z
176 1 z
177 1 z
178 100 foobar
179 100 foobar
180 100 foobar
181 100 foobar
182 100 foobar
183 100 foobar
184 200 bar
185 200 bar
186 200 bar
187 200 bar
188 200 bar
189 200 bar
190 SELECT t1_2.* FROM t1 t1_1 CROSS JOIN t1 t1_2 ON t1_1.b = t1_2.b;
191 a b
192 1 z
193 1 z
194 1 z
195 1 z
196 100 foobar
197 100 foobar
198 100 foobar
199 100 foobar
200 200 bar
201 200 bar
202 200 bar
203 200 bar
204 SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 WHERE t1.b > t2.b;
205 a b
206 1 bar
207 1 bar
208 1 foobar
209 1 foobar
210 100 bar
211 100 bar
212 SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 ON t1.b > t2.b ORDER BY t1.a, t2.b;
213 a b
214 1 bar
215 1 bar
216 1 foobar
217 1 foobar
218 100 bar
219 100 bar
220 SELECT t2.* FROM t1 LEFT JOIN t2 USING (a) ORDER BY t2.a, t2.b LIMIT 1;
221 a b
222 1 z
223 SELECT t2.* FROM t2 LEFT OUTER JOIN t1 ON t1.a = t2.a WHERE t1.a IS NOT NULL;
224 a b
225 1 z
226 1 z
227 100 foobar
228 100 foobar
229 200 bar
230 200 bar
231 SELECT SUM(t2.a) FROM t1 RIGHT JOIN t2 ON t2.b = t1.b;
232 SUM(t2.a)
233 602
234 SELECT MIN(t2.a) FROM t1 RIGHT OUTER JOIN t2 USING (b,a);
235 MIN(t2.a)
236 1
237 SELECT alias.b FROM t1 NATURAL JOIN ( SELECT a,b FROM t1 ) alias WHERE b > '';
238 b
239 bar
240 bar
241 bar
242 bar
243 foobar
244 foobar
245 foobar
246 foobar
247 z
248 z
249 z
250 z
251 SELECT t2.b FROM ( SELECT a,b FROM t1 ) alias NATURAL LEFT JOIN t2 WHERE b IS NOT NULL;
252 b
253 bar
254 bar
255 foobar
256 foobar
257 z
258 z
259 SELECT t1.*, t2.* FROM t1 NATURAL LEFT OUTER JOIN t2;
260 a b a b
261 1 z 1 z
262 1 z 1 z
263 100 foobar 100 foobar
264 100 foobar 100 foobar
265 200 bar 200 bar
266 200 bar 200 bar
267 SELECT t2_2.* FROM t2 t2_1 NATURAL RIGHT JOIN t2 t2_2 WHERE t2_1.a IN ( SELECT a FROM t1 );
268 a b
269 1 z
270 100 foobar
271 200 bar
272 SELECT t1_2.b FROM t1 t1_1 NATURAL RIGHT OUTER JOIN t1 t1_2 INNER JOIN t2;
273 b
274 bar
275 bar
276 bar
277 bar
278 bar
279 bar
280 bar
281 bar
282 bar
283 bar
284 bar
285 bar
286 foobar
287 foobar
288 foobar
289 foobar
290 foobar
291 foobar
292 foobar
293 foobar
294 foobar
295 foobar
296 foobar
297 foobar
298 z
299 z
300 z
301 z
302 z
303 z
304 z
305 z
306 z
307 z
308 z
309 z
310 SELECT ( SELECT MIN(a) FROM ( SELECT a,b FROM t1 ) alias1 ) AS min_a FROM t2;
311 min_a
312 1
313 1
314 1
315 SELECT a,b FROM t2 WHERE a = ( SELECT MIN(a) FROM t1 );
316 a b
317 1 z
318 SELECT a,b FROM t2 WHERE b LIKE ( SELECT b FROM t1 ORDER BY b LIMIT 1 );
319 a b
320 200 bar
321 SELECT t2.* FROM t1 t1_outer, t2 WHERE ( t1_outer.a, t2.b ) IN ( SELECT a, b FROM t2 WHERE a = t1_outer.a );
322 a b
323 1 z
324 1 z
325 100 foobar
326 100 foobar
327 200 bar
328 200 bar
329 SELECT a,b FROM t2 WHERE b = ANY ( SELECT b FROM t1 WHERE a > 1 );
330 a b
331 100 foobar
332 200 bar
333 SELECT a,b FROM t2 WHERE b > ALL ( SELECT b FROM t1 WHERE b < 'foo' );
334 a b
335 1 z
336 100 foobar
337 SELECT a,b FROM t1 WHERE ROW(a, b) = ( SELECT a, b FROM t2 ORDER BY a, b LIMIT 1 );
338 a b
339 1 z
340 1 z
341 SELECT a,b FROM t1 WHERE EXISTS ( SELECT a,b FROM t2 WHERE t2.b > t1.b );
342 a b
343 100 foobar
344 100 foobar
345 200 bar
346 200 bar
347 SELECT t1.* FROM t1, t2 ORDER BY ( SELECT b FROM t1 WHERE a IS NULL ORDER BY b LIMIT 1 ) DESC;
348 a b
349 1 z
350 1 z
351 1 z
352 1 z
353 1 z
354 1 z
355 100 foobar
356 100 foobar
357 100 foobar
358 100 foobar
359 100 foobar
360 100 foobar
361 200 bar
362 200 bar
363 200 bar
364 200 bar
365 200 bar
366 200 bar
367 SELECT a, b FROM t1 HAVING a IN ( SELECT a FROM t2 WHERE b = t1.b );
368 a b
369 1 z
370 1 z
371 100 foobar
372 100 foobar
373 200 bar
374 200 bar
375 SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION DISTINCT SELECT a,b FROM t1;
376 a b
377 1 z
378 100 foobar
379 200 bar
380 SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION ALL SELECT a,b FROM t1;
381 a b
382 1 z
383 1 z
384 1 z
385 100 foobar
386 100 foobar
387 100 foobar
388 200 bar
389 200 bar
390 200 bar
391 DROP TABLE t1, t2;