From 10cc5a46df746dd3478c536c7e420d6b523c9897 Mon Sep 17 00:00:00 2001 From: "igor@rurik.mysql.com" <> Date: Mon, 30 May 2005 03:01:51 -0700 Subject: olap.result, olap.test: Added test cases for bug #7894. sql_select.cc: Fixed bug #7894: GROUP BY queries with ROLLUP returned wrong results for expressions containing group by columns. The fix ensured correct results by replacement of all occurrences of group by fields in non-aggregate expressions for corresponding ref objects and preventing creation of fields in temporary tables for expression containing group by fields. --- mysql-test/r/olap.result | 68 +++++++++++++++++++++++++++++++++- mysql-test/t/olap.test | 30 +++++++++++++++ sql/sql_select.cc | 95 +++++++++++++++++++++++++++++++++++++++++++++--- 3 files changed, 187 insertions(+), 6 deletions(-) diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 013952403d7..ab84fa5739a 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -248,7 +248,7 @@ concat(':',product,':') sum(profit) avg(profit) :Computer: 6900 1380.0000 :Phone: 10 10.0000 :TV: 600 120.0000 -:TV: 7785 519.0000 +NULL 7785 519.0000 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; @@ -438,3 +438,69 @@ a SUM(a) SUM(a)+1 CONCAT(SUM(a),'x') SUM(a)+SUM(a) SUM(a) 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; diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 09ba537bf3b..b9387d15320 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -208,3 +208,33 @@ SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a) 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; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7b27879ae28..4f192b9228e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9164,6 +9164,79 @@ void free_underlaid_joins(THD *thd, SELECT_LEX *select) 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 *expr, ORDER *group_list, + bool *changed) +{ + if (expr->type() != Item::FUNC_ITEM) + return 0; + Item_func *func_item= (Item_func *) expr; + if (func_item->arg_count) + { + Item **arg,**arg_end; + for (arg= func_item->arguments(), + arg_end= func_item->arguments()+func_item->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, group_list, changed)) + return 1; + } + } + } + return 0; +} + + /* Allocate memory needed for other rollup functions */ bool JOIN::rollup_init() @@ -9208,19 +9281,31 @@ bool JOIN::rollup_init() for (j=0 ; j < fields_list.elements ; j++) rollup.fields[i].push_back(rollup.null_items[i]); } - List_iterator_fast it(fields_list); + List_iterator_fast 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, 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; - } @@ -9318,14 +9403,14 @@ bool JOIN::rollup_make_fields(List &fields_arg, List &sel_fields, *(*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)) { /* This is an element that is used by the GROUP BY and should be -- cgit v1.2.1 From e8a6fa42783a484a6bfef802feb987513d0ff0db Mon Sep 17 00:00:00 2001 From: "igor@rurik.mysql.com" <> Date: Mon, 30 May 2005 05:25:12 -0700 Subject: sql_select.cc: Post-review modifications for the fix of bug #7894. --- sql/sql_select.cc | 15 ++++++--------- 1 file changed, 6 insertions(+), 9 deletions(-) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4f192b9228e..0362f097cba 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9197,17 +9197,14 @@ void free_underlaid_joins(THD *thd, SELECT_LEX *select) 1 on error */ -static bool change_group_ref(THD *thd, Item *expr, ORDER *group_list, +static bool change_group_ref(THD *thd, Item_func *expr, ORDER *group_list, bool *changed) { - if (expr->type() != Item::FUNC_ITEM) - return 0; - Item_func *func_item= (Item_func *) expr; - if (func_item->arg_count) + if (expr->arg_count) { Item **arg,**arg_end; - for (arg= func_item->arguments(), - arg_end= func_item->arguments()+func_item->arg_count; + for (arg= expr->arguments(), + arg_end= expr->arguments()+expr->arg_count; arg != arg_end; arg++) { Item *item= *arg; @@ -9228,7 +9225,7 @@ static bool change_group_ref(THD *thd, Item *expr, ORDER *group_list, } else if (item->type() == Item::FUNC_ITEM) { - if (change_group_ref(thd, item, group_list, changed)) + if (change_group_ref(thd, (Item_func *) item, group_list, changed)) return 1; } } @@ -9294,7 +9291,7 @@ bool JOIN::rollup_init() if (item->type() == Item::FUNC_ITEM) { bool changed= 0; - if (change_group_ref(thd, item, group_list, &changed)) + 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 -- cgit v1.2.1 From 13351aae48444e77961632ab5f3ab3c9aad63a3c Mon Sep 17 00:00:00 2001 From: "igor@rurik.mysql.com" <> Date: Tue, 31 May 2005 04:49:52 -0700 Subject: olap.result: Post merge corrections. --- mysql-test/r/olap.result | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 78326511f00..f3324838ef7 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -249,11 +249,11 @@ select product, country_id , year, sum(profit) from t1 group by product, country product country_id year sum(profit) select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup; concat(':',product,':') sum(profit) avg(profit) -:Calculator: 275 68.7500 -:Computer: 6900 1380.0000 -:Phone: 10 10.0000 -:TV: 600 120.0000 -:TV: 7785 519.0000 +:Calculator: 275 68.75000 +:Computer: 6900 1380.00000 +:Phone: 10 10.00000 +:TV: 600 120.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; @@ -503,7 +503,7 @@ a+1 NULL SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP; a+SUM(a) -2 +3 4 NULL SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2; -- cgit v1.2.1 From a7d28e20aaaf86a6f4007aa1c3586ac9777a2b45 Mon Sep 17 00:00:00 2001 From: "igor@rurik.mysql.com" <> Date: Tue, 31 May 2005 05:56:22 -0700 Subject: olap.result: Fixed bug #10982. item.cc: Fixed bug #10982. In the function Item_ref::val_decimal by mistake the method Item_ref::val_decimal was used instead of Item_ref::val_decimal_result. --- mysql-test/r/olap.result | 2 +- sql/item.cc | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index f3324838ef7..5ba2bc80484 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -503,7 +503,7 @@ a+1 NULL SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP; a+SUM(a) -3 +2 4 NULL SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2; diff --git a/sql/item.cc b/sql/item.cc index bf62aa1ddad..ef358d084f9 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4358,7 +4358,7 @@ bool Item_ref::get_date(TIME *ltime,uint fuzzydate) my_decimal *Item_ref::val_decimal(my_decimal *decimal_value) { - my_decimal *val= (*ref)->val_decimal(decimal_value); + my_decimal *val= (*ref)->val_decimal_result(decimal_value); null_value= (*ref)->null_value; return val; } -- cgit v1.2.1