diff options
Diffstat (limited to 'mysql-test/r/group_min_max.result')
| -rw-r--r-- | mysql-test/r/group_min_max.result | 124 |
1 files changed, 124 insertions, 0 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 0304919baf6..7583aa14db8 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2162,3 +2162,127 @@ SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; MIN(c) 2 DROP TABLE t1,t2; +CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by +FLUSH STATUS; +SELECT max(b), a FROM t1 GROUP BY a; +max(b) a +5 1 +3 2 +1 3 +6 4 +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 0 +EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by +FLUSH STATUS; +CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 0 +FLUSH STATUS; +SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; +max(b) a +5 1 +3 2 +1 3 +6 4 +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 0 +FLUSH STATUS; +(SELECT max(b), a FROM t1 GROUP BY a) UNION +(SELECT max(b), a FROM t1 GROUP BY a); +max(b) a +5 1 +3 2 +1 3 +6 4 +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 16 +Handler_read_next 0 +EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION +(SELECT max(b), a FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range NULL a 5 NULL 8 Using index for group-by +2 UNION t1 range NULL a 5 NULL 8 Using index for group-by +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x +FROM t1 AS t1_outer; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS +(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 index NULL a 10 NULL 8 Using index +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE +(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE +a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index +2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 8 Using index +EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING +a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer range NULL a 5 NULL 8 Using index for group-by +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 +ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) +AND t1_outer1.b = t1_outer2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x +FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index +3 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +CREATE TABLE t3 LIKE t1; +FLUSH STATUS; +INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 0 +DELETE FROM t3; +FLUSH STATUS; +INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) +FROM t1 LIMIT 1; +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 0 +FLUSH STATUS; +DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 0 +FLUSH STATUS; +DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x +FROM t1) > 10000; +Warnings: +Error 1242 Subquery returns more than 1 row +SHOW STATUS LIKE 'handler_read__e%'; +Variable_name Value +Handler_read_key 8 +Handler_read_next 1 +DROP TABLE t1,t2,t3; |