summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--BitKeeper/etc/logging_ok1
-rw-r--r--include/mysqld_error.h3
-rw-r--r--mysql-test/r/func_gconcat.result155
-rw-r--r--mysql-test/t/func_gconcat.test77
-rw-r--r--sql/field.h1
-rw-r--r--sql/item_sum.cc450
-rw-r--r--sql/item_sum.h86
-rw-r--r--sql/lex.h2
-rw-r--r--sql/mysql_priv.h4
-rw-r--r--sql/mysqld.cc8
-rw-r--r--sql/set_var.cc3
-rw-r--r--sql/share/english/errmsg.txt3
-rw-r--r--sql/sql_class.h9
-rw-r--r--sql/sql_error.cc12
-rw-r--r--sql/sql_lex.h1
-rw-r--r--sql/sql_yacc.yy36
16 files changed, 837 insertions, 14 deletions
diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok
index 71388fba9c3..5741f0e0756 100644
--- a/BitKeeper/etc/logging_ok
+++ b/BitKeeper/etc/logging_ok
@@ -104,6 +104,7 @@ vva@eagle.mysql.r18.ru
vva@genie.(none)
walrus@kishkin.ru
walrus@mysql.com
+wax@kishkin.ru
wax@mysql.com
worm@altair.is.lan
zak@balfor.local
diff --git a/include/mysqld_error.h b/include/mysqld_error.h
index 377f714bfff..d1453c86f2a 100644
--- a/include/mysqld_error.h
+++ b/include/mysqld_error.h
@@ -266,4 +266,5 @@
#define ER_SELECT_REDUCED 1247
#define ER_TABLENAME_NOT_ALLOWED_HERE 1248
#define ER_NOT_SUPPORTED_AUTH_MODE 1249
-#define ER_ERROR_MESSAGES 250
+#define ER_CUT_VALUE_GROUP_CONCAT 1250
+#define ER_ERROR_MESSAGES 251
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
new file mode 100644
index 00000000000..b17ad8370ce
--- /dev/null
+++ b/mysql-test/r/func_gconcat.result
@@ -0,0 +1,155 @@
+drop table if exists t1;
+Warnings:
+Note 1051 Unknown table 't1'
+create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null);
+insert into t1 values (1,1,"a","a");
+insert into t1 values (2,2,"b","a");
+insert into t1 values (2,3,"c","b");
+insert into t1 values (3,4,"E","a");
+insert into t1 values (3,5,"C","b");
+insert into t1 values (3,6,"D","b");
+insert into t1 values (3,7,"d","d");
+insert into t1 values (3,8,"d","d");
+insert into t1 values (3,9,"D","c");
+select grp,group_concat(c) from t1 group by grp;
+grp group_concat(c)
+1 a
+2 b c
+3 E C D d d D
+select grp,group_concat(a,c) from t1 group by grp;
+grp group_concat(a,c)
+1 1a
+2 2b 3c
+3 4E 5C 6D 7d 8d 9D
+select grp,group_concat("(",a,":",c,")") from t1 group by grp;
+grp group_concat("(",a,":",c,")")
+1 (1:a)
+2 (2:b) (3:c)
+3 (4:E) (5:C) (6:D) (7:d) (8:d) (9:D)
+select grp,group_concat(c separator ",") from t1 group by grp;
+grp group_concat(c separator ",")
+1 a
+2 b,c
+3 E,C,D,d,d,D
+select grp,group_concat(c separator "---->") from t1 group by grp;
+grp group_concat(c separator "---->")
+1 a
+2 b---->c
+3 E---->C---->D---->d---->d---->D
+select grp,group_concat(c order by c) from t1 group by grp;
+grp group_concat(c order by c)
+1 a
+2 b c
+3 C D d d D E
+select grp,group_concat(c order by c desc) from t1 group by grp;
+grp group_concat(c order by c desc)
+1 a
+2 c b
+3 E D d d D C
+select grp,group_concat(d order by a) from t1 group by grp;
+grp group_concat(d order by a)
+1 a
+2 a b
+3 a b b d d c
+select grp,group_concat(d order by a desc) from t1 group by grp;
+grp group_concat(d order by a desc)
+1 a
+2 b a
+3 c d d b b a
+select grp,group_concat(a order by a,d+c) from t1 group by grp;
+grp group_concat(a order by a,d+c)
+1 1
+2 2 3
+3 4 5 6 7 8 9
+select grp,group_concat(c order by 1) from t1 group by grp;
+grp group_concat(c order by 1)
+1 a
+2 b c
+3 C D d d D E
+select grp,group_concat(c order by "c") from t1 group by grp;
+grp group_concat(c order by "c")
+1 a
+2 b c
+3 C D d d D E
+select grp,group_concat(distinct c order by c) from t1 group by grp;
+grp group_concat(distinct c order by c)
+1 a
+2 b c
+3 C D E
+select grp,group_concat(distinct c order by c desc) from t1 group by grp;
+grp group_concat(distinct c order by c desc)
+1 a
+2 c b
+3 E D C
+select grp,group_concat(c order by c separator ",") from t1 group by grp;
+grp group_concat(c order by c separator ",")
+1 a
+2 b,c
+3 C,D,d,d,D,E
+select grp,group_concat(c order by c desc separator ",") from t1 group by grp;
+grp group_concat(c order by c desc separator ",")
+1 a
+2 c,b
+3 E,D,d,d,D,C
+select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
+grp group_concat(distinct c order by c separator ",")
+1 a
+2 b,c
+3 C,D,E
+select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;
+grp group_concat(distinct c order by c desc separator ",")
+1 a
+2 c,b
+3 E,D,C
+select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
+grp group_concat(c order by grp desc)
+1 a
+2 c b
+3 D d d D C E
+select grp, group_concat(a separator "")+0 from t1 group by grp;
+grp group_concat(a separator "")+0
+1 1
+2 23
+3 456789
+select grp, group_concat(a separator "")+0.0 from t1 group by grp;
+grp group_concat(a separator "")+0.0
+1 1.0
+2 23.0
+3 456789.0
+select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
+grp ROUND(group_concat(a separator ""))
+1 1
+2 23
+3 456789
+drop table if exists t1;
+create table t1 (grp int, c char(10));
+insert into t1 values (1,NULL);
+insert into t1 values (2,"b");
+insert into t1 values (2,NULL);
+insert into t1 values (3,"E");
+insert into t1 values (3,NULL);
+insert into t1 values (3,"D");
+insert into t1 values (3,NULL);
+insert into t1 values (3,NULL);
+insert into t1 values (3,"D");
+select grp,group_concat(c order by c) from t1 group by grp;
+grp group_concat(c order by c)
+1
+2 b
+3 D D E
+set group_concat_max_len = 5;
+select grp,group_concat(c) from t1 group by grp;
+grp group_concat(c)
+1
+2 b
+3 E D
+Warnings:
+Warning 1250 1 line(s) was(were) cut by group_concat()
+show warnings;
+Level Code Message
+Warning 1250 1 line(s) was(were) cut by group_concat()
+select group_concat(sum(a)) from t1 group by grp;
+Invalid use of group function
+select grp,group_concat(c order by 2) from t1 group by grp;
+Unknown column '2' in 'group statement'
+drop table if exists t1;
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
new file mode 100644
index 00000000000..a122f612bfc
--- /dev/null
+++ b/mysql-test/t/func_gconcat.test
@@ -0,0 +1,77 @@
+#
+# simple test of group_concat function
+#
+
+drop table if exists t1;
+create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null);
+insert into t1 values (1,1,"a","a");
+insert into t1 values (2,2,"b","a");
+insert into t1 values (2,3,"c","b");
+insert into t1 values (3,4,"E","a");
+insert into t1 values (3,5,"C","b");
+insert into t1 values (3,6,"D","b");
+insert into t1 values (3,7,"d","d");
+insert into t1 values (3,8,"d","d");
+insert into t1 values (3,9,"D","c");
+
+# Test of MySQL simple request
+select grp,group_concat(c) from t1 group by grp;
+select grp,group_concat(a,c) from t1 group by grp;
+select grp,group_concat("(",a,":",c,")") from t1 group by grp;
+
+# Test of MySQL with options
+select grp,group_concat(c separator ",") from t1 group by grp;
+select grp,group_concat(c separator "---->") from t1 group by grp;
+select grp,group_concat(c order by c) from t1 group by grp;
+select grp,group_concat(c order by c desc) from t1 group by grp;
+select grp,group_concat(d order by a) from t1 group by grp;
+select grp,group_concat(d order by a desc) from t1 group by grp;
+select grp,group_concat(a order by a,d+c) from t1 group by grp;
+select grp,group_concat(c order by 1) from t1 group by grp;
+select grp,group_concat(c order by "c") from t1 group by grp;
+select grp,group_concat(distinct c order by c) from t1 group by grp;
+select grp,group_concat(distinct c order by c desc) from t1 group by grp;
+select grp,group_concat(c order by c separator ",") from t1 group by grp;
+select grp,group_concat(c order by c desc separator ",") from t1 group by grp;
+select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
+select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;
+
+# Test of SQL_LIST objects
+select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
+
+
+# Test transfer to real values
+
+select grp, group_concat(a separator "")+0 from t1 group by grp;
+select grp, group_concat(a separator "")+0.0 from t1 group by grp;
+select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
+
+# Test NULL values
+
+drop table if exists t1;
+create table t1 (grp int, c char(10));
+insert into t1 values (1,NULL);
+insert into t1 values (2,"b");
+insert into t1 values (2,NULL);
+insert into t1 values (3,"E");
+insert into t1 values (3,NULL);
+insert into t1 values (3,"D");
+insert into t1 values (3,NULL);
+insert into t1 values (3,NULL);
+insert into t1 values (3,"D");
+select grp,group_concat(c order by c) from t1 group by grp;
+
+# Test warnings
+
+set group_concat_max_len = 5;
+select grp,group_concat(c) from t1 group by grp;
+show warnings;
+
+# Test errors
+
+--error 1111
+select group_concat(sum(a)) from t1 group by grp;
+--error 1054
+select grp,group_concat(c order by 2) from t1 group by grp;
+
+drop table if exists t1;
diff --git a/sql/field.h b/sql/field.h
index 04225158270..d8ceb66fc57 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -221,6 +221,7 @@ public:
friend class Item_sum_std;
friend class Item_sum_min;
friend class Item_sum_max;
+ friend class Item_func_group_concat;
};
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index c2db50345d1..29294d8304d 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1320,3 +1320,453 @@ String *Item_sum_udf_str::val_str(String *str)
}
#endif /* HAVE_DLOPEN */
+
+
+/*****************************************************************************
+ GROUP_CONCAT function
+ Syntax:
+ GROUP_CONCAT([DISTINCT] expr,... [ORDER BY col [ASC|DESC],...]
+ [SEPARATOR str_const])
+ concat of values from "group by" operation
+*****************************************************************************/
+
+/*
+ function of sort for syntax:
+ GROUP_CONCAT(DISTINCT expr,...)
+*/
+
+static int group_concat_key_cmp_with_distinct(void* arg, byte* key1, byte* key2)
+{
+ Item_func_group_concat* item = (Item_func_group_concat*)arg;
+/*
+ DISTINCT
+*/
+ for (int i=0; i<item->arg_count_field; i++)
+ {
+ Item *field_item=item->expr[i];
+ Field *field = field_item->tmp_table_field();
+ if (field)
+ {
+ uint offset = field->offset();
+
+ int res = field->key_cmp(key1 + offset, key2 + offset);
+ if (res)
+ return 1;
+ }
+ }
+ return 0;
+}
+
+/*
+ function of sort for syntax:
+ GROUP_CONCAT(expr,... ORDER BY col,... )
+*/
+
+static int group_concat_key_cmp_with_order(void* arg, byte* key1, byte* key2)
+{
+ Item_func_group_concat* item = (Item_func_group_concat*)arg;
+/*
+ ORDER
+*/
+
+ for (int i=0; i<item->arg_count_order; i++)
+ {
+ ORDER *order_item = item->order[i];
+ Item *item=*order_item->item;
+ Field *field = item->tmp_table_field();
+ if (field)
+ {
+ uint offset = field->offset();
+
+ bool dir = order_item->asc;
+ int res = field->key_cmp(key1 + offset, key2 + offset);
+ if (res)
+ return dir ? res : -res;
+ }
+ }
+ return 1;
+}
+
+/*
+ function of sort for syntax:
+ GROUP_CONCAT(DISTINCT expr,... ORDER BY col,... )
+*/
+static int group_concat_key_cmp_with_distinct_and_order(void* arg, byte* key1, byte* key2)
+{
+ Item_func_group_concat* item = (Item_func_group_concat*)arg;
+/*
+ DISTINCT
+*/
+ if (!group_concat_key_cmp_with_distinct(arg,key1,key2))
+ return 0;
+/*
+ ORDER
+*/
+
+ return(group_concat_key_cmp_with_order(arg,key1,key2));
+}
+
+/*
+ create result
+ item is pointer to Item_func_group_concat
+*/
+static int dump_leaf_key(byte* key, uint32 count __attribute__((unused)),
+ Item_func_group_concat *item)
+{
+ char buff[MAX_FIELD_WIDTH];
+ String tmp((char *)&buff,sizeof(buff),default_charset_info);
+ String tmp2((char *)&buff,sizeof(buff),default_charset_info);
+
+ tmp.length(0);
+
+ for (int i=0; i < item->arg_show_fields; i++)
+ {
+ Item *show_item = item->expr[i];
+ if (!show_item->const_item())
+ {
+ Field *f = show_item->tmp_table_field();
+ uint offset = f->offset();
+ char *sv = f->ptr;
+ f->ptr = (char *)key + offset;
+/*
+ We can't check this field on NULL, becouse if f->is_null() return that the
+ first field is NULL than it return and that all fields are NULL too. Maybe
+ is it bag?
+*/
+ String *res = f->val_str(&tmp,&tmp2);
+ if (res)
+ item->result.append(*res);
+ f->ptr = sv;
+ }
+ else
+ {
+ String *res = show_item->val_str(&tmp);
+ if (res)
+ item->result.append(*res);
+ }
+ }
+ item->show_elements++;
+ if (item->tree_mode)
+ {
+/*
+ Last item of tree
+*/
+ if (item->show_elements < item->tree->elements_in_tree)
+ item->result.append(*item->separator);
+ }
+ else
+ {
+ item->result.append(*item->separator);
+ }
+/*
+ if length of result more than group_concat_max_len - stop !
+*/
+ if (item->result.length() > item->group_concat_max_len)
+ {
+ item->count_cut_values++;
+ item->result.length(item->group_concat_max_len);
+ item->warning_for_row = TRUE;
+ return 1;
+ }
+ return 0;
+}
+
+/*
+ Constructor of Item_func_group_concat
+ is_distinct - distinct
+ is_select - list of expression for show values
+ is_order - list of sort columns
+ is_separator - string value of separator
+*/
+Item_func_group_concat::Item_func_group_concat(int is_distinct,List<Item> *is_select,
+ SQL_LIST *is_order,String *is_separator):
+ Item_sum(),
+ tmp_table_param(0),
+ warning_available(false),
+ separator(is_separator),
+ tree(&tree_base),
+ table(0),
+ distinct(is_distinct),
+ tree_mode(0),
+ count_cut_values(0)
+{
+ original = 0;
+ quick_group = 0;
+ mark_as_sum_func();
+ SELECT_LEX *select_lex= current_lex->current_select->select_lex();
+
+ arg_show_fields = arg_count_field = is_select->elements;
+ arg_count_order = is_order ? is_order->elements : 0;
+ arg_count = arg_count_field;
+
+/*
+ fill args items of show and sort
+*/
+ int i = 0;
+ if ((args=(Item**) sql_alloc(sizeof(Item*)*arg_count))&&
+ (expr=(Item**)sql_alloc(sizeof(Item*)*arg_count_field)))
+ {
+ List_iterator_fast<Item> li(*is_select);
+ Item *item_select;
+
+ while ((item_select=li++))
+ {
+ args[i] = item_select;
+ expr[i] = item_select;
+ i++;
+ }
+
+ if (arg_count_order)
+ {
+ order=(ORDER**)sql_alloc(sizeof(ORDER*)*arg_count_order);
+ if (order)
+ {
+ uint j = 0;
+ for (ORDER *order_item = (ORDER*)is_order->first;
+ order_item != NULL;
+ order_item = order_item->next)
+ {
+ order[j++] = order_item;
+ }
+ }
+ else
+ {
+ my_error(ER_OUTOFMEMORY,MYF(0));
+ }
+ }
+ else
+ {
+ order = 0;
+ }
+ }
+ else
+ {
+ my_error(ER_OUTOFMEMORY,MYF(0));
+ }
+}
+
+Item_func_group_concat::~Item_func_group_concat()
+{
+ if (!original)
+ {
+ if (warning_available)
+ {
+ char warn_buff[MYSQL_ERRMSG_SIZE];
+ sprintf(warn_buff, ER(ER_CUT_VALUE_GROUP_CONCAT), count_cut_values);
+ ((MYSQL_ERROR *)warning)->set_msg((char *)&warn_buff);
+ }
+ if (table)
+ free_tmp_table(current_thd, table);
+ if (tmp_table_param)
+ delete tmp_table_param;
+ if (tree_mode)
+ delete_tree(tree);
+ }
+}
+
+
+void Item_func_group_concat::reset()
+{
+ result.length(0);
+ result.copy();
+ warning_for_row = false;
+ if (table)
+ {
+ table->file->extra(HA_EXTRA_NO_CACHE);
+ table->file->delete_all_rows();
+ table->file->extra(HA_EXTRA_WRITE_CACHE);
+ }
+ if (tree_mode)
+ reset_tree(tree);
+ add();
+}
+
+bool Item_func_group_concat::add()
+{
+ copy_fields(tmp_table_param);
+ copy_funcs(tmp_table_param->items_to_copy);
+
+ if (tree_mode)
+ {
+ if (tree->elements_in_tree > max_elements_in_tree)
+ return 1;
+ else
+ {
+ if (!tree_insert(tree, table->record[0], 0,tree->custom_arg))
+ return 1;
+ }
+ }
+ else
+ {
+ if (result.length() <= group_concat_max_len && !warning_for_row)
+ dump_leaf_key(table->record[0],1,
+ (Item_func_group_concat*)this);
+ }
+ return 0;
+}
+
+void Item_func_group_concat::reset_field()
+{
+ if (tree_mode)
+ reset_tree(tree);
+ (void) add();
+}
+
+bool
+Item_func_group_concat::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
+{
+ if (!thd->allow_sum_func)
+ {
+ my_error(ER_INVALID_GROUP_FUNC_USE,MYF(0));
+ return 1;
+ }
+
+ thd->allow_sum_func=0;
+ maybe_null=0;
+ for (uint i=0 ; i < arg_count ; i++)
+ {
+ if (args[i]->fix_fields(thd, tables, args + i) || args[i]->check_cols(1))
+ return 1;
+ maybe_null |= args[i]->maybe_null;
+ }
+ for (int i=0 ; i < arg_count_field ; i++)
+ {
+ if (expr[i]->fix_fields(thd, tables, expr + i) || expr[i]->check_cols(1))
+ return 1;
+ maybe_null |= expr[i]->maybe_null;
+ }
+ for (int i=0 ; i < arg_count_order ; i++)
+ {
+ ORDER *order_item = order[i];
+ Item *item=*order_item->item;
+ if (item->fix_fields(thd, tables, &item) || item->check_cols(1))
+ return 1;
+ }
+ result_field=0;
+ null_value=1;
+ fix_length_and_dec();
+ thd->allow_sum_func=1;
+ if (!(tmp_table_param= new TMP_TABLE_PARAM))
+ return 1;
+ tables_list = tables;
+ fixed= 1;
+ return 0;
+}
+
+bool Item_func_group_concat::setup(THD *thd)
+{
+ List<Item> list;
+ SELECT_LEX *select_lex= current_lex->current_select->select_lex();
+
+ if (select_lex->linkage == GLOBAL_OPTIONS_TYPE)
+ return 1;
+/*
+ all not constant fields are push to list and create temp table
+*/
+ for (uint i=0; i < arg_count; i++)
+ {
+ Item *item=args[i];
+ if (list.push_back(item))
+ return 1;
+ if (item->const_item())
+ {
+ (void) item->val_int();
+ if (item->null_value)
+ always_null=1;
+ }
+ }
+
+ List<Item> all_fields(list);
+ if (arg_count_order)
+ {
+ bool hidden_group_fields;
+ setup_group(thd, args, tables_list, list, all_fields, *order,
+ &hidden_group_fields);
+/*
+ check wrong cols in order list (incorrect number of coloum or value of name)
+*/
+ for (int i=0; i<arg_count_order; i++)
+ {
+ ORDER *order_item = order[i];
+ Item *item=*order_item->item;
+ if (item->const_item())
+ {
+ my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),
+ MYF(0),item->full_name(),thd->where);
+ return 1;
+ }
+ }
+ }
+
+ count_field_types(tmp_table_param,all_fields,0);
+ if (!(table=create_tmp_table(thd, tmp_table_param, all_fields, order?*order:0,
+ 0, 0, 0,select_lex->options | thd->options/*, select_lex->master_unit()*/)))
+ return 1;
+ table->file->extra(HA_EXTRA_NO_ROWS);
+ table->no_rows=1;
+ qsort_cmp2 compare_key;
+
+ tree_mode = distinct || arg_count_order;
+/*
+ choise function of sort
+*/
+ if (tree_mode)
+ {
+ if (arg_count_order)
+ {
+ if (distinct)
+ compare_key = (qsort_cmp2) group_concat_key_cmp_with_distinct_and_order;
+ else
+ compare_key = (qsort_cmp2) group_concat_key_cmp_with_order;
+ }
+ else
+ {
+ if (distinct)
+ compare_key = (qsort_cmp2) group_concat_key_cmp_with_distinct;
+ else
+ compare_key = NULL;
+ }
+/*
+ Create tree of sort
+*/
+ init_tree(tree, min(thd->variables.max_heap_table_size,
+ thd->variables.sortbuff_size/16), 0,
+ table->reclength, compare_key, 0, NULL, (void*) this);
+ max_elements_in_tree = ((table->reclength) ?
+ thd->variables.max_heap_table_size/table->reclength : 1);
+ };
+ item_thd = thd;
+
+ group_concat_max_len = thd->variables.group_concat_max_len;
+
+ if (original)
+ {
+ original->table= table;
+ original->tree_mode= tree_mode;
+ }
+ return 0;
+}
+
+String* Item_func_group_concat::val_str(String* str)
+{
+ if (tree_mode)
+ {
+ show_elements = 0;
+ tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
+ left_root_right);
+ }
+ else
+ {
+ if (!warning_for_row)
+ result.length(result.length()-separator->length());
+ }
+
+ null_value = result.length() == 0;
+ if (count_cut_values && !warning_available)
+ {
+ warning_available=TRUE;
+ warning = push_warning(item_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_CUT_VALUE_GROUP_CONCAT, NULL);
+ }
+ return &result;
+}
diff --git a/sql/item_sum.h b/sql/item_sum.h
index db8bded9946..1608819bbb2 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -28,7 +28,7 @@ class Item_sum :public Item_result_field
public:
enum Sumfunctype {COUNT_FUNC,COUNT_DISTINCT_FUNC,SUM_FUNC,AVG_FUNC,MIN_FUNC,
MAX_FUNC, UNIQUE_USERS_FUNC,STD_FUNC,VARIANCE_FUNC,SUM_BIT_FUNC,
- UDF_SUM_FUNC };
+ UDF_SUM_FUNC, GROUP_CONCAT_FUNC };
Item **args,*tmp_args[2];
uint arg_count;
@@ -628,3 +628,87 @@ public:
};
#endif /* HAVE_DLOPEN */
+
+class Item_func_group_concat : public Item_sum
+{
+ THD *item_thd;
+ TMP_TABLE_PARAM *tmp_table_param;
+ uint max_elements_in_tree;
+ void *warning;
+ bool warning_available;
+ public:
+ String result;
+ String *separator;
+ uint show_elements;
+ TREE tree_base;
+ TREE *tree;
+ TABLE *table;
+ int arg_count_order;
+ int arg_count_field;
+ int arg_show_fields;
+ int distinct;
+ Item **expr;
+ ORDER **order;
+ bool tree_mode;
+ int count_cut_values;
+ ulong group_concat_max_len;
+ bool warning_for_row;
+ TABLE_LIST *tables_list;
+ bool always_null;
+ /*
+ Following is 0 normal object and pointer to original one for copy
+ (to correctly free resources)
+ */
+ Item_func_group_concat *original;
+
+ Item_func_group_concat(int is_distinct,List<Item> *is_select,
+ SQL_LIST *is_order,String *is_separator);
+
+ Item_func_group_concat(THD *thd, Item_func_group_concat &item)
+ :Item_sum(thd, item),item_thd(thd),
+ tmp_table_param(item.tmp_table_param),
+ max_elements_in_tree(item.max_elements_in_tree),
+ warning(item.warning),
+ warning_available(item.warning_available),
+ separator(item.separator),
+ show_elements(item.show_elements),
+ tree(item.tree),
+ table(item.table),
+ arg_count_order(item.arg_count_order),
+ arg_count_field(item.arg_count_field),
+ arg_show_fields(item.arg_show_fields),
+ distinct(item.distinct),
+ expr(item.expr),
+ order(item.order),
+ tree_mode(0),
+ count_cut_values(item.count_cut_values),
+ group_concat_max_len(item.group_concat_max_len),
+ warning_for_row(item.warning_for_row),
+ tables_list(item.tables_list),
+ original(&item)
+ {
+ quick_group = 0;
+ };
+ ~Item_func_group_concat();
+ enum Sumfunctype sum_func () const {return GROUP_CONCAT_FUNC;}
+ const char *func_name() const { return "group_concat"; }
+ enum Type type() const { return SUM_FUNC_ITEM; }
+ virtual Item_result result_type () const { return STRING_RESULT; }
+ void reset();
+ bool add();
+ void reset_field();
+ bool fix_fields(THD *, TABLE_LIST *, Item **);
+ bool setup(THD *thd);
+ virtual void update_field(int offset) {};
+ double val()
+ {
+ String *res; res=val_str(&str_value);
+ return res ? atof(res->c_ptr()) : 0.0;
+ }
+ longlong val_int()
+ {
+ String *res; res=val_str(&str_value);
+ return res ? strtoll(res->c_ptr(),(char**) 0,10) : (longlong) 0;
+ }
+ String* val_str(String* str);
+};
diff --git a/sql/lex.h b/sql/lex.h
index d4ae8c32828..d08c031e822 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -330,6 +330,7 @@ static SYMBOL symbols[] = {
{ "ROWS", SYM(ROWS_SYM),0,0},
{ "RTREE", SYM(RTREE_SYM),0,0},
{ "SECOND", SYM(SECOND_SYM),0,0},
+ { "SEPARATOR", SYM(SEPARATOR_SYM),0,0},
{ "SELECT", SYM(SELECT_SYM),0,0},
{ "SERIAL", SYM(SERIAL_SYM),0,0},
{ "SERIALIZABLE", SYM(SERIALIZABLE_SYM),0,0},
@@ -490,6 +491,7 @@ static SYMBOL sql_functions[] = {
{ "GEOMFROMTEXT", SYM(GEOMFROMTEXT),0,0},
{ "GLENGTH", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_glength)},
{ "GREATEST", SYM(GREATEST_SYM),0,0},
+ { "GROUP_CONCAT", SYM(GROUP_CONCAT_SYM),0,0},
{ "GROUP_UNIQUE_USERS", SYM(GROUP_UNIQUE_USERS),0,0},
{ "HEX", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_hex)},
{ "IFNULL", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_ifnull)},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 5bf7e6e6951..f722f9bce2c 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -544,8 +544,8 @@ int check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
List<Item> &values, ulong counter);
/* sql_error.cc */
-void push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,
- const char *msg);
+MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,
+ const char *msg);
void push_warning_printf(THD *thd, MYSQL_ERROR::enum_warning_level level,
uint code, const char *format, ...);
void mysql_reset_errors(THD *thd);
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 1be832964b9..6a13a292abb 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3465,7 +3465,8 @@ enum options
OPT_ERROR_LOG_FILE,
OPT_ENABLE_SHARED_MEMORY,
OPT_SHARED_MEMORY_BASE_NAME,
- OPT_OLD_PASSWORDS
+ OPT_OLD_PASSWORDS,
+ OPT_GROUP_CONCAT_MAX_LEN
};
@@ -3581,6 +3582,11 @@ struct my_option my_long_options[] =
GET_LONG, OPT_ARG, 0, 0, 0, 0, 0, 0},
{"flush", OPT_FLUSH, "Flush tables to disk between SQL commands", 0, 0, 0,
GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
+ { "group_concat_max_len", OPT_GROUP_CONCAT_MAX_LEN,
+ "The maximum length of the result of function group_concat.",
+ (gptr*) &global_system_variables.group_concat_max_len,
+ (gptr*) &max_system_variables.group_concat_max_len, 0, GET_ULONG,
+ REQUIRED_ARG, 1024, 4, (long) ~0, 0, 1, 0},
/* We must always support the next option to make scripts like mysqltest
easier to do */
{"init-rpl-role", OPT_INIT_RPL_ROLE, "Set the replication role", 0, 0, 0,
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 5686adb765e..9d74b560cb3 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -311,6 +311,8 @@ static sys_var_slave_skip_counter sys_slave_skip_counter("sql_slave_skip_counter
static sys_var_rand_seed1 sys_rand_seed1("rand_seed1");
static sys_var_rand_seed2 sys_rand_seed2("rand_seed2");
+sys_var_thd_ulong sys_group_concat_max_len("group_concat_max_len",
+ &SV::group_concat_max_len);
/*
List of all variables for initialisation and storage in hash
@@ -340,6 +342,7 @@ sys_var *sys_variables[]=
&sys_flush,
&sys_flush_time,
&sys_foreign_key_checks,
+ &sys_group_concat_max_len,
&sys_identity,
&sys_insert_id,
&sys_interactive_timeout,
diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt
index 954b7cd2fb3..1b5d4a23601 100644
--- a/sql/share/english/errmsg.txt
+++ b/sql/share/english/errmsg.txt
@@ -250,4 +250,5 @@
"Every derived table must have it's own alias",
"Select %u was reduced during optimisation",
"Table '%-.64s' from one of SELECT's can not be used in %-.32s"
-"Client does not support authentication protocol requested by server. Consider upgrading MySQL client" \ No newline at end of file
+"Client does not support authentication protocol requested by server. Consider upgrading MySQL client",
+"%d line(s) was(were) cut by group_concat()"
diff --git a/sql/sql_class.h b/sql/sql_class.h
index ccbd7a194f2..02e163d1e5d 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -327,7 +327,12 @@ public:
const char *msg_arg)
:code(code_arg), level(level_arg)
{
- msg=sql_strdup(msg_arg);
+ set_msg(msg_arg);
+ }
+ inline void set_msg(const char *msg_arg)
+ {
+ if (msg_arg)
+ msg=sql_strdup(msg_arg);
}
};
@@ -391,7 +396,7 @@ struct system_variables
ulong tmp_table_size;
ulong tx_isolation;
ulong sql_mode;
-
+ ulong group_concat_max_len;
/*
In slave thread we need to know in behalf of which
thread the query is being run to replicate temp tables properly
diff --git a/sql/sql_error.cc b/sql/sql_error.cc
index 5508be5167a..9f28d33bc50 100644
--- a/sql/sql_error.cc
+++ b/sql/sql_error.cc
@@ -79,14 +79,19 @@ void mysql_reset_errors(THD *thd)
level Severity of warning (note, warning, error ...)
code Error number
msg Clear error message
+
+ RETURN
+ pointer on MYSQL_ERROR object
*/
-void push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,
- const char *msg)
+MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level,
+ uint code, const char *msg)
{
if (thd->query_id != thd->warn_id)
mysql_reset_errors(thd);
+ MYSQL_ERROR *err = NULL;
+
if (thd->warn_list.elements < thd->variables.max_error_count)
{
/*
@@ -95,13 +100,14 @@ void push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,
*/
MEM_ROOT *old_root=my_pthread_getspecific_ptr(MEM_ROOT*,THR_MALLOC);
my_pthread_setspecific_ptr(THR_MALLOC, &thd->warn_root);
- MYSQL_ERROR *err= new MYSQL_ERROR(code, level, msg);
+ err = new MYSQL_ERROR(code, level, msg);
if (err)
thd->warn_list.push_back(err);
my_pthread_setspecific_ptr(THR_MALLOC, old_root);
}
thd->warn_count[(uint) level]++;
thd->total_warn_count++;
+ return err;
}
/*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 60f48f293e4..b73488f5ef9 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -477,6 +477,7 @@ typedef struct st_lex
uint slave_thd_opt;
CHARSET_INFO *charset;
char *help_arg;
+ SQL_LIST *gorder_list;
inline void uncacheable()
{
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index b9c0167bc05..92cc2643073 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -332,6 +332,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token ROW_SYM
%token RTREE_SYM
%token SET
+%token SEPARATOR_SYM
%token SERIAL_SYM
%token SERIALIZABLE_SYM
%token SESSION_SYM
@@ -457,6 +458,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token GEOMCOLLFROMTEXT
%token GEOMFROMTEXT
%token GEOMETRYCOLLECTION
+%token GROUP_CONCAT_SYM
%token GROUP_UNIQUE_USERS
%token HOUR_MINUTE_SYM
%token HOUR_SECOND_SYM
@@ -567,13 +569,13 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
opt_escape
%type <string>
- text_string
+ text_string opt_gconcat_separator
%type <num>
type int_type real_type order_dir opt_field_spec lock_option
udf_type if_exists opt_local opt_table_options table_options
table_option opt_if_not_exists opt_var_type opt_var_ident_type
- delete_option opt_temporary all_or_any
+ delete_option opt_temporary all_or_any opt_distinct
%type <ulong_num>
ULONG_NUM raid_types merge_insert_types
@@ -2466,7 +2468,35 @@ sum_expr:
| VARIANCE_SYM '(' in_sum_expr ')'
{ $$=new Item_sum_variance($3); }
| SUM_SYM '(' in_sum_expr ')'
- { $$=new Item_sum_sum($3); };
+ { $$=new Item_sum_sum($3); }
+ | GROUP_CONCAT_SYM '(' opt_distinct expr_list opt_gorder_clause opt_gconcat_separator ')'
+ {
+ $$=new Item_func_group_concat($3,$4,Lex->gorder_list,$6);
+ $4->empty();
+ };
+
+opt_distinct:
+ /* empty */ { $$ = 0; }
+ |DISTINCT { $$ = 1; };
+
+opt_gconcat_separator:
+ /* empty */ { $$ = new String(" ",1,default_charset_info); }
+ |SEPARATOR_SYM text_string { $$ = $2; };
+
+
+opt_gorder_clause:
+ /* empty */
+ {
+ LEX *lex=Lex;
+ lex->gorder_list = NULL;
+ }
+ | order_clause
+ {
+ LEX *lex=Lex;
+ lex->gorder_list= (SQL_LIST*) sql_memdup((char*) &lex->current_select->order_list,sizeof(st_sql_list));
+ lex->current_select->order_list.empty();
+ };
+
in_sum_expr:
opt_all
View Lorry Controller Status