1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
|
#
# Basic SELECT statements
#
# HIGH_PRIORITY is covered in select_high_prio test
# FOR UPDATE - in trx/select_for_update test
# LOCK IN SHARE MODE - in trx/select_lock_in_share_mode test
# index hints - in type_*_indexes tests
#
--source have_engine.inc
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
--source create_table.inc
INSERT INTO t1 (a,b) VALUES (100,'foobar'),(1,'z'),(200,'bar');
--let $table_name = t2
--source create_table.inc
INSERT INTO t2 (a,b) SELECT a, b FROM t1;
INSERT INTO t1 (a,b) SELECT a, b FROM t2;
--sorted_result
SELECT * FROM t1;
# Modifiers
--sorted_result
SELECT DISTINCT a FROM t1;
--sorted_result
SELECT ALL b, a FROM t1;
# Optimizer and cache directives should not have any visible effect here,
# but we will add them for completness
--sorted_result
SELECT STRAIGHT_JOIN SQL_CACHE t1.* FROM t2, t1 WHERE t1.a <> t2.a;
--sorted_result
SELECT SQL_SMALL_RESULT SQL_NO_CACHE t1.a FROM t1, t2;
--sorted_result
SELECT SQL_BIG_RESULT SQL_CALC_FOUND_ROWS DISTINCT(t2.a)
FROM t1 t1_1, t2, t1 t1_2;
SELECT FOUND_ROWS();
SET GLOBAL query_cache_type = ON;
SET query_cache_type = ON;
--sorted_result
SELECT SQL_CACHE * FROM t1, t2;
SET GLOBAL query_cache_type = DEFAULT;
# Combination of main clauses
--sorted_result
SELECT a+10 AS field1, CONCAT(b,':',b) AS field2 FROM t1
WHERE b > 'b' AND a IS NOT NULL
GROUP BY 2 DESC, field1 ASC
HAVING field1 < 1000
ORDER BY field2, 1 DESC, field1*2
LIMIT 5 OFFSET 1 ROWS EXAMINED 100000;
# ROLLUP
--sorted_result
SELECT SUM(a), MAX(a), b FROM t1 GROUP BY b WITH ROLLUP;
# Procedure
--sorted_result
SELECT * FROM t2 WHERE a>0 PROCEDURE ANALYSE();
# SELECT INTO
let $datadir = `SELECT @@datadir`;
--replace_result $datadir <DATADIR>
eval
SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a
INTO OUTFILE '$datadir/select.out'
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''';
--cat_file $datadir/select.out
--remove_file $datadir/select.out
--replace_result $datadir <DATADIR>
--let $error_codes = ER_TOO_MANY_ROWS
eval
SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a
INTO DUMPFILE '$datadir/select.dump';
--source check_errors.inc
--remove_file $datadir/select.dump
--replace_result $datadir <DATADIR>
eval
SELECT t1.*, t2.* FROM t1, t2 ORDER BY t2.b, t1.a, t2.a, t1.b LIMIT 1
INTO DUMPFILE '$datadir/select.dump';
--cat_file $datadir/select.dump
--echo
--remove_file $datadir/select.dump
SELECT MIN(a), MAX(a) FROM t1 INTO @min, @max;
SELECT @min, @max;
# Joins
--sorted_result
SELECT t1_1.*, t2.* FROM t2, t1 AS t1_1, t1 AS t1_2
WHERE t1_1.a = t1_2.a AND t2.a = t1_1.a;
--sorted_result
SELECT alias1.* FROM ( SELECT a,b FROM t1 ) alias1, t2 WHERE t2.a IN (100,200);
--sorted_result
SELECT t1.a FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a+10 };
--sorted_result
SELECT t1.* FROM t2 INNER JOIN t1;
--sorted_result
SELECT t1_2.* FROM t1 t1_1 CROSS JOIN t1 t1_2 ON t1_1.b = t1_2.b;
--sorted_result
SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 WHERE t1.b > t2.b;
SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 ON t1.b > t2.b ORDER BY t1.a, t2.b;
SELECT t2.* FROM t1 LEFT JOIN t2 USING (a) ORDER BY t2.a, t2.b LIMIT 1;
--sorted_result
SELECT t2.* FROM t2 LEFT OUTER JOIN t1 ON t1.a = t2.a WHERE t1.a IS NOT NULL;
SELECT SUM(t2.a) FROM t1 RIGHT JOIN t2 ON t2.b = t1.b;
SELECT MIN(t2.a) FROM t1 RIGHT OUTER JOIN t2 USING (b,a);
--sorted_result
SELECT alias.b FROM t1 NATURAL JOIN ( SELECT a,b FROM t1 ) alias WHERE b > '';
--sorted_result
SELECT t2.b FROM ( SELECT a,b FROM t1 ) alias NATURAL LEFT JOIN t2 WHERE b IS NOT NULL;
--sorted_result
SELECT t1.*, t2.* FROM t1 NATURAL LEFT OUTER JOIN t2;
--sorted_result
SELECT t2_2.* FROM t2 t2_1 NATURAL RIGHT JOIN t2 t2_2 WHERE t2_1.a IN ( SELECT a FROM t1 );
--sorted_result
SELECT t1_2.b FROM t1 t1_1 NATURAL RIGHT OUTER JOIN t1 t1_2 INNER JOIN t2;
# Subquery as scalar operand, subquery in the FROM clause
--sorted_result
SELECT ( SELECT MIN(a) FROM ( SELECT a,b FROM t1 ) alias1 ) AS min_a FROM t2;
# Comparison using subqueries
--sorted_result
SELECT a,b FROM t2 WHERE a = ( SELECT MIN(a) FROM t1 );
--sorted_result
SELECT a,b FROM t2 WHERE b LIKE ( SELECT b FROM t1 ORDER BY b LIMIT 1 );
# Subquery with IN, correlated subquery
--sorted_result
SELECT t2.* FROM t1 t1_outer, t2 WHERE ( t1_outer.a, t2.b ) IN ( SELECT a, b FROM t2 WHERE a = t1_outer.a );
# Subquery with ANY, ALL
--sorted_result
SELECT a,b FROM t2 WHERE b = ANY ( SELECT b FROM t1 WHERE a > 1 );
--sorted_result
SELECT a,b FROM t2 WHERE b > ALL ( SELECT b FROM t1 WHERE b < 'foo' );
# Row subqueries
--sorted_result
SELECT a,b FROM t1 WHERE ROW(a, b) = ( SELECT a, b FROM t2 ORDER BY a, b LIMIT 1 );
# Subquery with EXISTS
--sorted_result
SELECT a,b FROM t1 WHERE EXISTS ( SELECT a,b FROM t2 WHERE t2.b > t1.b );
# Subquery in ORDER BY
--sorted_result
SELECT t1.* FROM t1, t2 ORDER BY ( SELECT b FROM t1 WHERE a IS NULL ORDER BY b LIMIT 1 ) DESC;
# Subquery in HAVING
--sorted_result
SELECT a, b FROM t1 HAVING a IN ( SELECT a FROM t2 WHERE b = t1.b );
# Union
--sorted_result
SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION DISTINCT SELECT a,b FROM t1;
--sorted_result
SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION ALL SELECT a,b FROM t1;
# Cleanup
DROP TABLE t1, t2;
--source cleanup_engine.inc
|
