summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/subselect.result32
-rw-r--r--mysql-test/t/subselect.test22
-rw-r--r--sql/sql_select.cc29
3 files changed, 66 insertions, 17 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 85976c211c5..33b12c05f98 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -215,9 +215,9 @@ select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from
a
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
-8 7.5
-8 4.5
-9 7.5
+8 7.5000
+8 4.5000
+9 7.5000
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 3
@@ -3131,3 +3131,29 @@ a sum
3 20
4 40
DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a varchar(5), b varchar(10));
+INSERT INTO t1 VALUES
+('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
+('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+a b
+BBB 4
+CCC 7
+AAA 8
+EXPLAIN
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+ALTER TABLE t1 ADD INDEX(a);
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+a b
+BBB 4
+CCC 7
+AAA 8
+EXPLAIN
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
+2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 9 Using filesort
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 762ff36ba63..9e09b215951 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2051,3 +2051,25 @@ SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
HAVING t2.c+sum > 20);
DROP TABLE t1,t2,t3;
+
+#
+# Test for bug #16603: GROUP BY in a row subquery with a quantifier
+# when an index is defined on the grouping field
+
+CREATE TABLE t1 (a varchar(5), b varchar(10));
+INSERT INTO t1 VALUES
+ ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
+ ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
+
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+EXPLAIN
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+
+ALTER TABLE t1 ADD INDEX(a);
+
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+EXPLAIN
+SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
+
+DROP TABLE t1;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9160c023576..777814adc5f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -364,22 +364,8 @@ JOIN::prepare(Item ***rref_pointer_array,
select_lex->having_fix_field= 0;
if (having_fix_rc || thd->net.report_error)
DBUG_RETURN(-1); /* purecov: inspected */
- if (having->with_sum_func)
- having->split_sum_func2(thd, ref_pointer_array, all_fields,
- &having, TRUE);
thd->lex->allow_sum_func= save_allow_sum_func;
}
- if (select_lex->inner_sum_func_list)
- {
- Item_sum *end=select_lex->inner_sum_func_list;
- Item_sum *item_sum= end;
- do
- {
- item_sum= item_sum->next;
- item_sum->split_sum_func2(thd, ref_pointer_array,
- all_fields, item_sum->ref_by, FALSE);
- } while (item_sum != end);
- }
if (!thd->lex->view_prepare_mode)
{
@@ -397,6 +383,21 @@ JOIN::prepare(Item ***rref_pointer_array,
}
}
+ if (having && having->with_sum_func)
+ having->split_sum_func2(thd, ref_pointer_array, all_fields,
+ &having, TRUE);
+ if (select_lex->inner_sum_func_list)
+ {
+ Item_sum *end=select_lex->inner_sum_func_list;
+ Item_sum *item_sum= end;
+ do
+ {
+ item_sum= item_sum->next;
+ item_sum->split_sum_func2(thd, ref_pointer_array,
+ all_fields, item_sum->ref_by, FALSE);
+ } while (item_sum != end);
+ }
+
if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
DBUG_RETURN(-1);