diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 32 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 22 | ||||
| -rw-r--r-- | sql/sql_select.cc | 29 |
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); |