diff options
Diffstat (limited to 'mysql-test/suite/storage_engine/select.result')
| -rw-r--r-- | mysql-test/suite/storage_engine/select.result | 391 |
1 files changed, 391 insertions, 0 deletions
diff --git a/mysql-test/suite/storage_engine/select.result b/mysql-test/suite/storage_engine/select.result new file mode 100644 index 00000000..9bd36cff --- /dev/null +++ b/mysql-test/suite/storage_engine/select.result @@ -0,0 +1,391 @@ +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; +INSERT INTO t1 (a,b) VALUES (100,'foobar'),(1,'z'),(200,'bar'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; +INSERT INTO t2 (a,b) SELECT a, b FROM t1; +INSERT INTO t1 (a,b) SELECT a, b FROM t2; +SELECT * FROM t1; +a b +1 z +1 z +100 foobar +100 foobar +200 bar +200 bar +SELECT DISTINCT a FROM t1; +a +1 +100 +200 +SELECT ALL b, a FROM t1; +b a +bar 200 +bar 200 +foobar 100 +foobar 100 +z 1 +z 1 +SELECT STRAIGHT_JOIN SQL_CACHE t1.* FROM t2, t1 WHERE t1.a <> t2.a; +a b +1 z +1 z +1 z +1 z +100 foobar +100 foobar +100 foobar +100 foobar +200 bar +200 bar +200 bar +200 bar +SELECT SQL_SMALL_RESULT SQL_NO_CACHE t1.a FROM t1, t2; +a +1 +1 +1 +1 +1 +1 +100 +100 +100 +100 +100 +100 +200 +200 +200 +200 +200 +200 +SELECT SQL_BIG_RESULT SQL_CALC_FOUND_ROWS DISTINCT(t2.a) +FROM t1 t1_1, t2, t1 t1_2; +a +1 +100 +200 +SELECT FOUND_ROWS(); +FOUND_ROWS() +3 +SET GLOBAL query_cache_type = ON; +SET query_cache_type = ON; +SELECT SQL_CACHE * FROM t1, t2; +a b a b +1 z 1 z +1 z 1 z +1 z 100 foobar +1 z 100 foobar +1 z 200 bar +1 z 200 bar +100 foobar 1 z +100 foobar 1 z +100 foobar 100 foobar +100 foobar 100 foobar +100 foobar 200 bar +100 foobar 200 bar +200 bar 1 z +200 bar 1 z +200 bar 100 foobar +200 bar 100 foobar +200 bar 200 bar +200 bar 200 bar +SET GLOBAL query_cache_type = DEFAULT; +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; +field1 field2 +11 z:z +110 foobar:foobar +SELECT SUM(a), MAX(a), b FROM t1 GROUP BY b WITH ROLLUP; +SUM(a) MAX(a) b +2 1 z +200 100 foobar +400 200 bar +602 200 NULL +SELECT * FROM t2 WHERE a>0 PROCEDURE ANALYSE(); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t2.a 1 200 1 3 0 0 100.3333 81.2418 ENUM('1','100','200') NOT NULL +test.t2.b bar z 1 6 0 0 3.3333 NULL ENUM('bar','foobar','z') NOT NULL +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 ''''; +200,'bar' +200,'bar' +100,'foobar' +100,'foobar' +1,'z' +1,'z' +SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a +INTO DUMPFILE '<DATADIR>/select.dump'; +ERROR 42000: Result consisted of more than one row +SELECT t1.*, t2.* FROM t1, t2 ORDER BY t2.b, t1.a, t2.a, t1.b LIMIT 1 +INTO DUMPFILE '<DATADIR>/select.dump'; +1z200bar +SELECT MIN(a), MAX(a) FROM t1 INTO @min, @max; +SELECT @min, @max; +@min @max +1 200 +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; +a b a b +1 z 1 z +1 z 1 z +1 z 1 z +1 z 1 z +100 foobar 100 foobar +100 foobar 100 foobar +100 foobar 100 foobar +100 foobar 100 foobar +200 bar 200 bar +200 bar 200 bar +200 bar 200 bar +200 bar 200 bar +SELECT alias1.* FROM ( SELECT a,b FROM t1 ) alias1, t2 WHERE t2.a IN (100,200); +a b +1 z +1 z +1 z +1 z +100 foobar +100 foobar +100 foobar +100 foobar +200 bar +200 bar +200 bar +200 bar +SELECT t1.a FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a+10 }; +a +1 +1 +100 +100 +200 +200 +SELECT t1.* FROM t2 INNER JOIN t1; +a b +1 z +1 z +1 z +1 z +1 z +1 z +100 foobar +100 foobar +100 foobar +100 foobar +100 foobar +100 foobar +200 bar +200 bar +200 bar +200 bar +200 bar +200 bar +SELECT t1_2.* FROM t1 t1_1 CROSS JOIN t1 t1_2 ON t1_1.b = t1_2.b; +a b +1 z +1 z +1 z +1 z +100 foobar +100 foobar +100 foobar +100 foobar +200 bar +200 bar +200 bar +200 bar +SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 WHERE t1.b > t2.b; +a b +1 bar +1 bar +1 foobar +1 foobar +100 bar +100 bar +SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 ON t1.b > t2.b ORDER BY t1.a, t2.b; +a b +1 bar +1 bar +1 foobar +1 foobar +100 bar +100 bar +SELECT t2.* FROM t1 LEFT JOIN t2 USING (a) ORDER BY t2.a, t2.b LIMIT 1; +a b +1 z +SELECT t2.* FROM t2 LEFT OUTER JOIN t1 ON t1.a = t2.a WHERE t1.a IS NOT NULL; +a b +1 z +1 z +100 foobar +100 foobar +200 bar +200 bar +SELECT SUM(t2.a) FROM t1 RIGHT JOIN t2 ON t2.b = t1.b; +SUM(t2.a) +602 +SELECT MIN(t2.a) FROM t1 RIGHT OUTER JOIN t2 USING (b,a); +MIN(t2.a) +1 +SELECT alias.b FROM t1 NATURAL JOIN ( SELECT a,b FROM t1 ) alias WHERE b > ''; +b +bar +bar +bar +bar +foobar +foobar +foobar +foobar +z +z +z +z +SELECT t2.b FROM ( SELECT a,b FROM t1 ) alias NATURAL LEFT JOIN t2 WHERE b IS NOT NULL; +b +bar +bar +foobar +foobar +z +z +SELECT t1.*, t2.* FROM t1 NATURAL LEFT OUTER JOIN t2; +a b a b +1 z 1 z +1 z 1 z +100 foobar 100 foobar +100 foobar 100 foobar +200 bar 200 bar +200 bar 200 bar +SELECT t2_2.* FROM t2 t2_1 NATURAL RIGHT JOIN t2 t2_2 WHERE t2_1.a IN ( SELECT a FROM t1 ); +a b +1 z +100 foobar +200 bar +SELECT t1_2.b FROM t1 t1_1 NATURAL RIGHT OUTER JOIN t1 t1_2 INNER JOIN t2; +b +bar +bar +bar +bar +bar +bar +bar +bar +bar +bar +bar +bar +foobar +foobar +foobar +foobar +foobar +foobar +foobar +foobar +foobar +foobar +foobar +foobar +z +z +z +z +z +z +z +z +z +z +z +z +SELECT ( SELECT MIN(a) FROM ( SELECT a,b FROM t1 ) alias1 ) AS min_a FROM t2; +min_a +1 +1 +1 +SELECT a,b FROM t2 WHERE a = ( SELECT MIN(a) FROM t1 ); +a b +1 z +SELECT a,b FROM t2 WHERE b LIKE ( SELECT b FROM t1 ORDER BY b LIMIT 1 ); +a b +200 bar +SELECT t2.* FROM t1 t1_outer, t2 WHERE ( t1_outer.a, t2.b ) IN ( SELECT a, b FROM t2 WHERE a = t1_outer.a ); +a b +1 z +1 z +100 foobar +100 foobar +200 bar +200 bar +SELECT a,b FROM t2 WHERE b = ANY ( SELECT b FROM t1 WHERE a > 1 ); +a b +100 foobar +200 bar +SELECT a,b FROM t2 WHERE b > ALL ( SELECT b FROM t1 WHERE b < 'foo' ); +a b +1 z +100 foobar +SELECT a,b FROM t1 WHERE ROW(a, b) = ( SELECT a, b FROM t2 ORDER BY a, b LIMIT 1 ); +a b +1 z +1 z +SELECT a,b FROM t1 WHERE EXISTS ( SELECT a,b FROM t2 WHERE t2.b > t1.b ); +a b +100 foobar +100 foobar +200 bar +200 bar +SELECT t1.* FROM t1, t2 ORDER BY ( SELECT b FROM t1 WHERE a IS NULL ORDER BY b LIMIT 1 ) DESC; +a b +1 z +1 z +1 z +1 z +1 z +1 z +100 foobar +100 foobar +100 foobar +100 foobar +100 foobar +100 foobar +200 bar +200 bar +200 bar +200 bar +200 bar +200 bar +SELECT a, b FROM t1 HAVING a IN ( SELECT a FROM t2 WHERE b = t1.b ); +a b +1 z +1 z +100 foobar +100 foobar +200 bar +200 bar +SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION DISTINCT SELECT a,b FROM t1; +a b +1 z +100 foobar +200 bar +SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION ALL SELECT a,b FROM t1; +a b +1 z +1 z +1 z +100 foobar +100 foobar +100 foobar +200 bar +200 bar +200 bar +DROP TABLE t1, t2; |