Debian Codegraph @

mysql-test/suite/storage_engine/select.test

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