| 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 |
|