Below is the list of changes that have just been committed into a local
5.0 repository of ram. When ram does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.1940 05/05/31 15:02:22 ramil@stripped +6 -0
merging
sql/sql_select.cc
1.330 05/05/31 15:02:17 ramil@stripped +0 -0
merging
sql/item_sum.cc
1.145 05/05/31 15:02:17 ramil@stripped +2 -6
merging
mysql-test/t/olap.test
1.17 05/05/31 15:02:17 ramil@stripped +0 -0
merging
mysql-test/t/func_gconcat.test
1.27 05/05/31 15:02:17 ramil@stripped +0 -1
merging
mysql-test/r/olap.result
1.21 05/05/31 15:02:17 ramil@stripped +1 -2
merging
mysql-test/r/func_gconcat.result
1.37 05/05/31 15:02:17 ramil@stripped +14 -14
merging
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: ramil
# Host: gw.mysql.r18.ru
# Root: /usr/home/ram/work/mysql-5.0/RESYNC
--- 1.144/sql/item_sum.cc 2005-05-26 22:54:25 +05:00
+++ 1.145/sql/item_sum.cc 2005-05-31 15:02:17 +05:00
@@ -2611,7 +2611,6 @@
Item_func_group_concat* grp_item= (Item_func_group_concat*)arg;
TABLE *table= grp_item->table;
Item **field_item, **end;
- char *record= (char*) table->record[0] + table->s->null_bytes;
for (field_item= grp_item->args, end= field_item + grp_item->arg_count_field;
field_item < end;
@@ -2626,7 +2625,7 @@
if (field)
{
int res;
- uint offset= (uint) (field->ptr - record);
+ uint offset= field->offset();
if ((res= field->cmp((char *) key1 + offset, (char *) key2 + offset)))
return res;
}
@@ -2644,8 +2643,6 @@
{
Item_func_group_concat* grp_item= (Item_func_group_concat*) arg;
ORDER **order_item, **end;
- TABLE *table= grp_item->table;
- char *record= (char*) table->record[0] + table->s->null_bytes;
for (order_item= grp_item->order, end=order_item+ grp_item->arg_count_order;
order_item < end;
@@ -2662,7 +2659,7 @@
if (field)
{
int res;
- uint offset= (uint) (field->ptr - record);
+ uint offset= field->offset();
if ((res= field->cmp((char *) key1 + offset, (char *) key2 + offset)))
return (*order_item)->asc ? res : -res;
}
@@ -2703,7 +2700,6 @@
Item_func_group_concat *item)
{
TABLE *table= item->table;
- char *record= (char*) table->record[0] + table->s->null_bytes;
String tmp((char *)table->record[1], table->s->reclength, default_charset_info), tmp2;
String *result= &item->result;
Item **arg= item->args, **arg_end= item->args + item->arg_count_field;
@@ -2727,9 +2723,8 @@
*/
Field *field= (*arg)->get_tmp_table_field();
char *save_ptr= field->ptr;
- uint offset= (uint) (save_ptr - record);
- DBUG_ASSERT(offset < table->s->reclength);
- field->ptr= (char *) key + offset;
+ DBUG_ASSERT(field->offset() < item->table->reclength);
+ field->ptr= (char *) key + field->offset();
res= field->val_str(&tmp,&tmp2);
field->ptr= save_ptr;
}
@@ -2908,19 +2903,13 @@
copy_fields(tmp_table_param);
copy_funcs(tmp_table_param->items_to_copy);
- for (uint i= 0; i < arg_count_field; i++)
+ for (Item **arg= args, **arg_end= args + arg_count_field;
+ arg < arg_end; arg++)
{
- Item *show_item= args[i];
- if (!show_item->const_item())
- {
- /*
- Here we use real_item as we want the original field data that should
- be written to table->record[0]
- */
- Field *f= show_item->real_item()->get_tmp_table_field();
- if (f->is_null())
- return 0; // Skip row if it contains null
- }
+ if (!(*arg)->const_item() &&
+ (*arg)->get_tmp_table_field()->is_null_in_record(
+ (const uchar*) table->record[0]))
+ return 0; // Skip row if it contains null
}
null_value= FALSE;
@@ -2989,6 +2978,11 @@
SELECT_LEX *select_lex= thd->lex->current_select;
qsort_cmp2 compare_key;
DBUG_ENTER("Item_func_group_concat::setup");
+
+ if (!(tmp_table_param= new TMP_TABLE_PARAM))
+ return 1;
+ /* We'll convert all blobs to varchar fields in the temporary table */
+ tmp_table_param->convert_blob_length= group_concat_max_len;
/*
Currently setup() can be called twice. Please add
--- 1.329/sql/sql_select.cc 2005-05-30 21:56:08 +05:00
+++ 1.330/sql/sql_select.cc 2005-05-31 15:02:17 +05:00
@@ -12807,6 +12807,76 @@
ROLLUP handling
****************************************************************************/
+/*
+ Replace occurences of group by fields in an expression by ref items
+
+ SYNOPSIS
+ change_group_ref()
+ thd reference to the context
+ expr expression to make replacement
+ group_list list of references to group by items
+ changed out: returns 1 if item contains a replaced field item
+
+ DESCRIPTION
+ The function replaces occurrences of group by fields in expr
+ by ref objects for these fields unless they are under aggregate
+ functions.
+
+ IMPLEMENTATION
+ The function recursively traverses the tree of the expr expression,
+ looks for occurrences of the group by fields that are not under
+ aggregate functions and replaces them for the corresponding ref items.
+
+ NOTES
+ This substitution is needed GROUP BY queries with ROLLUP if
+ SELECT list contains expressions over group by attributes.
+
+ EXAMPLES
+ SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP
+ SELECT SUM(a)+a FROM t1 GROUP BY a WITH ROLLUP
+
+ RETURN
+ 0 if ok
+ 1 on error
+*/
+
+static bool change_group_ref(THD *thd, Item_func *expr, ORDER *group_list,
+ bool *changed)
+{
+ if (expr->arg_count)
+ {
+ Item **arg,**arg_end;
+ for (arg= expr->arguments(),
+ arg_end= expr->arguments()+expr->arg_count;
+ arg != arg_end; arg++)
+ {
+ Item *item= *arg;
+ if (item->type() == Item::FIELD_ITEM || item->type() == Item::REF_ITEM)
+ {
+ ORDER *group_tmp;
+ for (group_tmp= group_list; group_tmp; group_tmp= group_tmp->next)
+ {
+ if (item->eq(*group_tmp->item,0))
+ {
+ Item *new_item;
+ if(!(new_item= new Item_ref(group_tmp->item, 0, item->name)))
+ return 1; // fatal_error is set
+ thd->change_item_tree(arg, new_item);
+ *changed= TRUE;
+ }
+ }
+ }
+ else if (item->type() == Item::FUNC_ITEM)
+ {
+ if (change_group_ref(thd, (Item_func *) item, group_list, changed))
+ return 1;
+ }
+ }
+ }
+ return 0;
+}
+
+
/* Allocate memory needed for other rollup functions */
bool JOIN::rollup_init()
@@ -12851,19 +12921,31 @@
for (j=0 ; j < fields_list.elements ; j++)
rollup.fields[i].push_back(rollup.null_items[i]);
}
- List_iterator_fast<Item> it(fields_list);
+ List_iterator_fast<Item> it(all_fields);
Item *item;
while ((item= it++))
{
ORDER *group_tmp;
for (group_tmp= group_list; group_tmp; group_tmp= group_tmp->next)
{
- if (*group_tmp->item == item)
+ if (item->eq(*group_tmp->item,0))
item->maybe_null= 1;
}
+ if (item->type() == Item::FUNC_ITEM)
+ {
+ bool changed= 0;
+ if (change_group_ref(thd, (Item_func *) item, group_list, &changed))
+ return 1;
+ /*
+ We have to prevent creation of a field in a temporary table for
+ an expression that contains GROUP BY attributes.
+ Marking the expression item as 'with_sum_func' will ensure this.
+ */
+ if (changed)
+ item->with_sum_func= 1;
+ }
}
return 0;
-
}
@@ -12959,14 +13041,14 @@
*(*func)= (Item_sum*) item;
(*func)++;
}
- else if (real_fields)
+ else
{
/* Check if this is something that is part of this group by */
ORDER *group_tmp;
for (group_tmp= start_group, i= pos ;
group_tmp ; group_tmp= group_tmp->next, i++)
{
- if (*group_tmp->item == item)
+ if (item->eq(*group_tmp->item,0))
{
Item_null_result *null_item;
/*
--- 1.20/mysql-test/r/olap.result 2005-05-18 23:06:31 +05:00
+++ 1.21/mysql-test/r/olap.result 2005-05-31 15:02:17 +05:00
@@ -253,7 +253,7 @@
:Computer: 6900 1380.00000
:Phone: 10 10.00000
:TV: 600 120.00000
-:TV: 7785 519.00000
+NULL 7785 519.00000
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
ERROR 42000: This version of MySQL doesn't yet support 'CUBE'
explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
@@ -489,3 +489,69 @@
5 5 6 5x 10 5
NULL 8 9 8x 16 8
DROP TABLE t1;
+CREATE TABLE t1 (a int(11));
+INSERT INTO t1 VALUES (1),(2);
+SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
+a a+1 SUM(a)
+1 2 1
+2 3 2
+NULL NULL 3
+SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
+a+1
+2
+3
+NULL
+SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
+a+SUM(a)
+2
+4
+NULL
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
+a b
+2 3
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
+a b
+NULL NULL
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
+a b
+NULL NULL
+SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
+IFNULL(a, 'TEST')
+1
+2
+TEST
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+(1,4),
+(2,2), (2,2),
+(4,1), (4,1), (4,1), (4,1),
+(2,1), (2,1);
+SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP;
+a b SUM(b)
+1 4 4
+1 NULL 4
+2 1 2
+2 2 4
+2 NULL 6
+4 1 4
+4 NULL 4
+NULL NULL 14
+SELECT a,b,SUM(b), a+b as c FROM t2
+GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
+a b SUM(b) c
+1 NULL 4 NULL
+2 NULL 6 NULL
+4 NULL 4 NULL
+NULL NULL 14 NULL
+SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2
+GROUP BY a, b WITH ROLLUP;
+IFNULL(a, 'TEST') COALESCE(b, 'TEST')
+1 4
+1 TEST
+2 1
+2 2
+2 TEST
+4 1
+4 TEST
+TEST TEST
+DROP TABLE t1,t2;
--- 1.16/mysql-test/t/olap.test 2005-05-18 23:06:31 +05:00
+++ 1.17/mysql-test/t/olap.test 2005-05-31 15:02:17 +05:00
@@ -220,3 +220,33 @@
DROP TABLE t1;
+#
+# Tests for bug #7894: ROLLUP over expressions on group by attributes
+#
+
+CREATE TABLE t1 (a int(11));
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
+SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+ (1,4),
+ (2,2), (2,2),
+ (4,1), (4,1), (4,1), (4,1),
+ (2,1), (2,1);
+
+SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP;
+SELECT a,b,SUM(b), a+b as c FROM t2
+ GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
+SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2
+ GROUP BY a, b WITH ROLLUP;
+
+DROP TABLE t1,t2;
+
--- 1.36/mysql-test/r/func_gconcat.result 2005-03-18 01:40:56 +04:00
+++ 1.37/mysql-test/r/func_gconcat.result 2005-05-31 15:02:17 +05:00
@@ -476,3 +476,26 @@
1 1,1
2 2,2
drop table r2;
+create table t1 (d int, a int, b int, c int);
+insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3);
+select a, group_concat(b) from t1 group by a with rollup;
+a group_concat(b)
+1 3,4,2,1,2
+2 7,3,3
+NULL 3,4,2,1,2,7,3,3
+select a, group_concat(distinct b) from t1 group by a with rollup;
+a group_concat(distinct b)
+1 3,4,2,1
+2 7,3
+NULL 3,4,2,1,7
+select a, group_concat(b order by b) from t1 group by a with rollup;
+a group_concat(b order by b)
+1 1,2,2,3,4
+2 3,3,7
+NULL 1,2,2,3,3,3,4,7
+select a, group_concat(distinct b order by b) from t1 group by a with rollup;
+a group_concat(distinct b order by b)
+1 1,2,3,4
+2 3,7
+NULL 1,2,3,4,7
+drop table t1;
--- 1.26/mysql-test/t/func_gconcat.test 2005-03-17 17:51:02 +04:00
+++ 1.27/mysql-test/t/func_gconcat.test 2005-05-31 15:02:17 +05:00
@@ -301,3 +301,14 @@
select b x, (select group_concat(x) from r2) from r2;
drop table r2;
+#
+# Bug #7405: problems with rollup
+#
+
+create table t1 (d int, a int, b int, c int);
+insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3);
+select a, group_concat(b) from t1 group by a with rollup;
+select a, group_concat(distinct b) from t1 group by a with rollup;
+select a, group_concat(b order by b) from t1 group by a with rollup;
+select a, group_concat(distinct b order by b) from t1 group by a with rollup;
+drop table t1;
| Thread |
|---|
| • bk commit into 5.0 tree (ramil:1.1940) | ramil | 31 May |
