summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result19
-rw-r--r--mysql-test/t/subselect.test19
-rw-r--r--sql/item.cc9
-rw-r--r--sql/item_sum.cc33
-rw-r--r--sql/item_sum.h4
-rw-r--r--sql/sql_base.cc2
-rw-r--r--sql/sql_class.h3
-rw-r--r--sql/sql_insert.cc6
-rw-r--r--sql/sql_select.cc11
9 files changed, 76 insertions, 30 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 72bde001e87..81f087fe8fa 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3905,3 +3905,22 @@ COUNT(*) a
2 2
3 3
DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+SELECT COUNT(*) c, a,
+(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
+FROM t1 GROUP BY a;
+c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
+2 2 2
+3 3 3
+1 4 1,1
+SELECT COUNT(*) c, a,
+(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
+FROM t1 GROUP BY a;
+c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
+2 2 3
+3 3 4
+1 4 2,2
+DROP table t1,t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index a238c8f070b..efd54ca95d1 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2763,3 +2763,22 @@ SELECT COUNT(*), a
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
DROP TABLE t1,t2;
+
+#
+# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
+#
+
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+
+SELECT COUNT(*) c, a,
+ (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
+ FROM t1 GROUP BY a;
+
+SELECT COUNT(*) c, a,
+ (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
+ FROM t1 GROUP BY a;
+
+DROP table t1,t2;
diff --git a/sql/item.cc b/sql/item.cc
index 11a5039ca19..255a756b6d1 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1261,15 +1261,18 @@ void Item::split_sum_func2(THD *thd, Item **ref_pointer_array,
Exception is Item_direct_view_ref which we need to convert to
Item_ref to allow fields from view being stored in tmp table.
*/
+ Item_aggregate_ref *item_ref;
uint el= fields.elements;
- Item *new_item, *real_itm= real_item();
+ Item *real_itm= real_item();
ref_pointer_array[el]= real_itm;
- if (!(new_item= new Item_aggregate_ref(&thd->lex->current_select->context,
+ if (!(item_ref= new Item_aggregate_ref(&thd->lex->current_select->context,
ref_pointer_array + el, 0, name)))
return; // fatal_error is set
+ if (type() == SUM_FUNC_ITEM)
+ item_ref->depended_from= ((Item_sum *) this)->depended_from();
fields.push_front(real_itm);
- thd->change_item_tree(ref, new_item);
+ thd->change_item_tree(ref, item_ref);
}
}
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index fcebc89c6e9..03a1c32456a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -61,9 +61,9 @@ bool Item_sum::init_sum_func_check(THD *thd)
/* Save a pointer to object to be used in items for nested set functions */
thd->lex->in_sum_func= this;
nest_level= thd->lex->current_select->nest_level;
- nest_level_tables_count= thd->lex->current_select->join->tables;
ref_by= 0;
aggr_level= -1;
+ aggr_sel= NULL;
max_arg_level= -1;
max_sum_func_level= -1;
return FALSE;
@@ -151,7 +151,10 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
invalid= aggr_level < 0 && !(allow_sum_func & (1 << nest_level));
}
if (!invalid && aggr_level < 0)
+ {
aggr_level= nest_level;
+ aggr_sel= thd->lex->current_select;
+ }
/*
By this moment we either found a subquery where the set function is
to be aggregated and assigned a value that is >= 0 to aggr_level,
@@ -212,7 +215,6 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
bool Item_sum::register_sum_func(THD *thd, Item **ref)
{
SELECT_LEX *sl;
- SELECT_LEX *aggr_sl= NULL;
nesting_map allow_sum_func= thd->lex->allow_sum_func;
for (sl= thd->lex->current_select->master_unit()->outer_select() ;
sl && sl->nest_level > max_arg_level;
@@ -222,7 +224,7 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
{
/* Found the most nested subquery where the function can be aggregated */
aggr_level= sl->nest_level;
- aggr_sl= sl;
+ aggr_sel= sl;
}
}
if (sl && (allow_sum_func & (1 << sl->nest_level)))
@@ -233,21 +235,22 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
The set function will be aggregated in this subquery.
*/
aggr_level= sl->nest_level;
- aggr_sl= sl;
+ aggr_sel= sl;
+
}
if (aggr_level >= 0)
{
ref_by= ref;
- /* Add the object to the list of registered objects assigned to aggr_sl */
- if (!aggr_sl->inner_sum_func_list)
+ /* Add the object to the list of registered objects assigned to aggr_sel */
+ if (!aggr_sel->inner_sum_func_list)
next= this;
else
{
- next= aggr_sl->inner_sum_func_list->next;
- aggr_sl->inner_sum_func_list->next= this;
+ next= aggr_sel->inner_sum_func_list->next;
+ aggr_sel->inner_sum_func_list->next= this;
}
- aggr_sl->inner_sum_func_list= this;
- aggr_sl->with_sum_func= 1;
+ aggr_sel->inner_sum_func_list= this;
+ aggr_sel->with_sum_func= 1;
/*
Mark Item_subselect(s) as containing aggregate function all the way up
@@ -265,11 +268,11 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
has aggregate functions directly referenced (i.e. not through a sub-select).
*/
for (sl= thd->lex->current_select;
- sl && sl != aggr_sl && sl->master_unit()->item;
+ sl && sl != aggr_sel && sl->master_unit()->item;
sl= sl->master_unit()->outer_select() )
sl->master_unit()->item->with_sum_func= 1;
}
- thd->lex->current_select->mark_as_dependent(aggr_sl);
+ thd->lex->current_select->mark_as_dependent(aggr_sel);
return FALSE;
}
@@ -299,10 +302,10 @@ Item_sum::Item_sum(List<Item> &list) :arg_count(list.elements),
Item_sum::Item_sum(THD *thd, Item_sum *item):
Item_result_field(thd, item), arg_count(item->arg_count),
+ aggr_sel(item->aggr_sel),
nest_level(item->nest_level), aggr_level(item->aggr_level),
quick_group(item->quick_group), used_tables_cache(item->used_tables_cache),
- forced_const(item->forced_const),
- nest_level_tables_count(item->nest_level_tables_count)
+ forced_const(item->forced_const)
{
if (arg_count <= 2)
args=tmp_args;
@@ -447,7 +450,7 @@ void Item_sum::update_used_tables ()
/* the aggregate function is aggregated into its local context */
if (aggr_level == nest_level)
- used_tables_cache |= (1 << nest_level_tables_count) - 1;
+ used_tables_cache |= (1 << aggr_sel->join->tables) - 1;
}
}
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 9ddda94a8ee..66c73e1d416 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -233,6 +233,7 @@ public:
Item_sum *next; /* next in the circular chain of registered objects */
uint arg_count;
Item_sum *in_sum_func; /* embedding set function if any */
+ st_select_lex * aggr_sel; /* select where the function is aggregated */
int8 nest_level; /* number of the nesting level of the set function */
int8 aggr_level; /* nesting level of the aggregating subquery */
int8 max_arg_level; /* max level of unbound column references */
@@ -242,7 +243,6 @@ public:
protected:
table_map used_tables_cache;
bool forced_const;
- byte nest_level_tables_count;
public:
@@ -365,6 +365,8 @@ public:
bool init_sum_func_check(THD *thd);
bool check_sum_func(THD *thd, Item **ref);
bool register_sum_func(THD *thd, Item **ref);
+ st_select_lex *depended_from()
+ { return (nest_level == aggr_level ? 0 : aggr_sel); }
};
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 13279abb1c8..77bb1d9642b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4864,7 +4864,6 @@ bool setup_tables_and_check_access(THD *thd,
TABLE_LIST *leaves_tmp = NULL;
bool first_table= true;
- thd->leaf_count= 0;
if (setup_tables (thd, context, from_clause, tables, conds,
&leaves_tmp, select_insert))
return TRUE;
@@ -4882,7 +4881,6 @@ bool setup_tables_and_check_access(THD *thd,
return TRUE;
}
first_table= false;
- thd->leaf_count++;
}
return FALSE;
}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 048da718dfd..99803802001 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1492,9 +1492,6 @@ public:
query_id_t first_query_id;
} binlog_evt_union;
- /* pass up the count of "leaf" tables in a JOIN out of setup_tables() */
- byte leaf_count;
-
THD();
~THD();
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 3c8f9fd5fc2..d9d32d14321 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -2349,14 +2349,12 @@ bool mysql_insert_select_prepare(THD *thd)
DBUG_ASSERT(select_lex->leaf_tables != 0);
lex->leaf_tables_insert= select_lex->leaf_tables;
/* skip all leaf tables belonged to view where we are insert */
- for (first_select_leaf_table= select_lex->leaf_tables->next_leaf,
- thd->leaf_count --;
+ for (first_select_leaf_table= select_lex->leaf_tables->next_leaf;
first_select_leaf_table &&
first_select_leaf_table->belong_to_view &&
first_select_leaf_table->belong_to_view ==
lex->leaf_tables_insert->belong_to_view;
- first_select_leaf_table= first_select_leaf_table->next_leaf,
- thd->leaf_count --)
+ first_select_leaf_table= first_select_leaf_table->next_leaf)
{}
select_lex->leaf_tables= first_select_leaf_table;
DBUG_RETURN(FALSE);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f46a212e7fb..c96ae153cf2 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -412,7 +412,12 @@ JOIN::prepare(Item ***rref_pointer_array,
&select_lex->leaf_tables, FALSE,
SELECT_ACL, SELECT_ACL))
DBUG_RETURN(-1);
- tables= thd->leaf_count;
+
+ TABLE_LIST *table_ptr;
+ for (table_ptr= select_lex->leaf_tables;
+ table_ptr;
+ table_ptr= table_ptr->next_leaf)
+ tables++;
if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
select_lex->setup_ref_array(thd, og_num) ||
@@ -9186,7 +9191,9 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
Item::Type type=item->type();
if (not_all_columns)
{
- if (item->with_sum_func && type != Item::SUM_FUNC_ITEM)
+ if (item->with_sum_func && type != Item::SUM_FUNC_ITEM &&
+ (type == Item::SUBSELECT_ITEM ||
+ (item->used_tables() & ~PSEUDO_TABLE_BITS)))
{
/*
Mark that the we have ignored an item that refers to a summary
View Lorry Controller Status