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
|