summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_min_max.result124
-rw-r--r--mysql-test/t/group_min_max.test60
-rw-r--r--sql/opt_range.cc6
3 files changed, 187 insertions, 3 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;
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 08f0f54df60..5ed082f7583 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -810,3 +810,63 @@ explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
DROP TABLE t1,t2;
+
+#
+# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
+#
+
+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;
+FLUSH STATUS;
+SELECT max(b), a FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
+FLUSH STATUS;
+CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+(SELECT max(b), a FROM t1 GROUP BY a) UNION
+ (SELECT max(b), a FROM t1 GROUP BY a);
+SHOW STATUS LIKE 'handler_read__e%';
+EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION
+ (SELECT max(b), a FROM t1 GROUP BY a);
+
+EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
+ FROM t1 AS t1_outer;
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
+ (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
+ (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
+ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
+ a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+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;
+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;
+
+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%';
+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%';
+FLUSH STATUS;
+DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
+ FROM t1) > 10000;
+SHOW STATUS LIKE 'handler_read__e%';
+
+DROP TABLE t1,t2,t3;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 96239315026..596640fdf95 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7445,7 +7445,7 @@ static TRP_GROUP_MIN_MAX *
get_best_group_min_max(PARAM *param, SEL_TREE *tree)
{
THD *thd= param->thd;
- JOIN *join= thd->lex->select_lex.join;
+ JOIN *join= thd->lex->current_select->join;
TABLE *table= param->table;
bool have_min= FALSE; /* TRUE if there is a MIN function. */
bool have_max= FALSE; /* TRUE if there is a MAX function. */
@@ -7466,7 +7466,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree)
DBUG_ENTER("get_best_group_min_max");
/* Perform few 'cheap' tests whether this access method is applicable. */
- if (!join || (thd->lex->sql_command != SQLCOM_SELECT))
+ if (!join)
DBUG_RETURN(NULL); /* This is not a select statement. */
if ((join->tables != 1) || /* The query must reference one table. */
((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */
@@ -8316,7 +8316,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows,
DBUG_ENTER("TRP_GROUP_MIN_MAX::make_quick");
quick= new QUICK_GROUP_MIN_MAX_SELECT(param->table,
- param->thd->lex->select_lex.join,
+ param->thd->lex->current_select->join,
have_min, have_max, min_max_arg_part,
group_prefix_len, used_key_parts,
index_info, index, read_cost, records,
View Lorry Controller Status