summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_by.result45
-rw-r--r--mysql-test/r/user_var.result4
-rw-r--r--mysql-test/t/group_by.test28
-rw-r--r--sql/item_cmpfunc.cc45
-rw-r--r--sql/item_func.cc8
-rw-r--r--sql/item_row.cc11
-rw-r--r--sql/item_strfunc.cc7
-rw-r--r--sql/unireg.h2
8 files changed, 123 insertions, 27 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 18e4cb7a434..f7b4d302a52 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -629,15 +629,6 @@ explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
DROP TABLE t1;
-create table t1 ( col1 int, col2 int );
-insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
-select group_concat( distinct col1 ) as alias from t1
-group by col2 having alias like '%';
-alias
-1,2
-1,2
-1
-drop table t1;
create table t1 (a int);
insert into t1 values(null);
select min(a) is null from t1;
@@ -650,3 +641,39 @@ select 1 and min(a) is null from t1;
1 and min(a) is null
1
drop table t1;
+create table t1 ( col1 int, col2 int );
+insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
+select group_concat( distinct col1 ) as alias from t1
+group by col2 having alias like '%';
+alias
+1,2
+1,2
+1
+drop table t1;
+create table t1 (a integer, b integer, c integer);
+insert into t1 (a,b) values (1,2),(1,3),(2,5);
+select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
+a r2 r1
+1 1.0 2
+select a, rand()*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
+a r2 r1
+1 1 2
+select a,sum(b) from t1 where a=1 group by c;
+a sum(b)
+1 5
+select a*sum(b) from t1 where a=1 group by c;
+a*sum(b)
+5
+select sum(a)*sum(b) from t1 where a=1 group by c;
+sum(a)*sum(b)
+10
+select a,sum(b) from t1 where a=1 group by c having a=1;
+a sum(b)
+1 5
+select a as d,sum(b) from t1 where a=1 group by c having d=1;
+d sum(b)
+1 5
+select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
+d
+10
+drop table t1;
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index bacd8311a1e..ba5cb264013 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -113,8 +113,8 @@ select @a:=0;
select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
@a @a:=@a+count(*) count(*) @a
0 1 1 0
-0 2 2 0
-0 3 3 0
+0 3 2 0
+0 6 3 0
select @a:=0;
@a:=0
0
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index c0447b06303..379f668df1a 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -457,6 +457,14 @@ SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
DROP TABLE t1;
+#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
+create table t1 (a int);
+insert into t1 values(null);
+select min(a) is null from t1;
+select min(a) is null or null from t1;
+select 1 and min(a) is null from t1;
+drop table t1;
+
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
create table t1 ( col1 int, col2 int );
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
@@ -465,12 +473,18 @@ select group_concat( distinct col1 ) as alias from t1
drop table t1;
+#
+# Test BUG#8216 when referring in HAVING to n alias which is rand() function
+#
-#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
-create table t1 (a int);
-insert into t1 values(null);
-select min(a) is null from t1;
-select min(a) is null or null from t1;
-select 1 and min(a) is null from t1;
+create table t1 (a integer, b integer, c integer);
+insert into t1 (a,b) values (1,2),(1,3),(2,5);
+select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
+select a, rand()*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
+select a,sum(b) from t1 where a=1 group by c;
+select a*sum(b) from t1 where a=1 group by c;
+select sum(a)*sum(b) from t1 where a=1 group by c;
+select a,sum(b) from t1 where a=1 group by c having a=1;
+select a as d,sum(b) from t1 where a=1 group by c having d=1;
+select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
drop table t1;
-
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e63bafe4811..1079e31e23a 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2029,6 +2029,35 @@ Item *Item_cond::transform(Item_transformer transformer, byte *arg)
}
+/*
+ Move SUM items out from item tree and replace with reference
+
+ SYNOPSIS
+ split_sum_func()
+ thd Thread handler
+ ref_pointer_array Pointer to array of reference fields
+ fields All fields in select
+
+ NOTES
+ This function is run on all expression (SELECT list, WHERE, HAVING etc)
+ that have or refer (HAVING) to a SUM expression.
+
+ The split is done to get an unique item for each SUM function
+ so that we can easily find and calculate them.
+ (Calculation done by update_sum_func() and copy_sum_funcs() in
+ sql_select.cc)
+
+ All found SUM items are added FIRST in the fields list and
+ we replace the item with a reference.
+
+ We also replace all functions without side effects (like RAND() or UDF's)
+ that uses columns as arguments.
+ For functions with side effects, we just remember any fields referred
+ by the function to ensure that we get a copy of the field value for the
+ first accepted row. This ensures that we can do things like
+ SELECT a*SUM(b) FROM t1 WHERE a=1
+*/
+
void Item_cond::split_sum_func(THD *thd, Item **ref_pointer_array,
List<Item> &fields)
{
@@ -2038,10 +2067,22 @@ void Item_cond::split_sum_func(THD *thd, Item **ref_pointer_array,
const_item_cache=1;
while ((item=li++))
{
- if (item->with_sum_func && item->type() != SUM_FUNC_ITEM)
+ /* with_sum_func is set for items that contains a SUM expression */
+ if (item->type() != SUM_FUNC_ITEM &&
+ (item->with_sum_func ||
+ (item->used_tables() & PSEUDO_TABLE_BITS)))
item->split_sum_func(thd, ref_pointer_array, fields);
- else if (item->used_tables() || item->type() == SUM_FUNC_ITEM)
+ else if (item->type() == SUM_FUNC_ITEM ||
+ (item->used_tables() && item->type() != REF_ITEM))
{
+ /*
+ Replace item with a reference so that we can easily calculate
+ it (in case of sum functions) or copy it (in case of fields)
+
+ The test above is to ensure we don't do a reference for things
+ that are constants or are not yet calculated as in:
+ SELECT RAND() as r1, SUM(a) as r2 FROM t1 HAVING r1 > 1 AND r2 > 0
+ */
Item **ref= li.ref();
uint el= fields.elements;
ref_pointer_array[el]= item;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 7731ccee773..e1d81b2a37d 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -356,6 +356,7 @@ bool Item_func::walk (Item_processor processor, byte *argument)
}
+
/*
Transform an Item_func object with a transformer callback function
@@ -402,9 +403,12 @@ void Item_func::split_sum_func(THD *thd, Item **ref_pointer_array,
for (arg= args, arg_end= args+arg_count; arg != arg_end ; arg++)
{
Item *item=* arg;
- if (item->with_sum_func && item->type() != SUM_FUNC_ITEM)
+ if (item->type() != SUM_FUNC_ITEM &&
+ (item->with_sum_func ||
+ (item->used_tables() & PSEUDO_TABLE_BITS)))
item->split_sum_func(thd, ref_pointer_array, fields);
- else if (item->used_tables() || item->type() == SUM_FUNC_ITEM)
+ else if (item->type() == SUM_FUNC_ITEM ||
+ (item->used_tables() && item->type() != REF_ITEM))
{
uint el= fields.elements;
ref_pointer_array[el]= item;
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 62f31186b09..a5a430785d6 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -84,15 +84,20 @@ bool Item_row::fix_fields(THD *thd, TABLE_LIST *tabl, Item **ref)
return FALSE;
}
+
void Item_row::split_sum_func(THD *thd, Item **ref_pointer_array,
List<Item> &fields)
{
Item **arg, **arg_end;
for (arg= items, arg_end= items+arg_count; arg != arg_end ; arg++)
{
- if ((*arg)->with_sum_func && (*arg)->type() != SUM_FUNC_ITEM)
- (*arg)->split_sum_func(thd, ref_pointer_array, fields);
- else if ((*arg)->used_tables() || (*arg)->type() == SUM_FUNC_ITEM)
+ Item *item= *arg;
+ if (item->type() != SUM_FUNC_ITEM &&
+ (item->with_sum_func ||
+ (item->used_tables() & PSEUDO_TABLE_BITS)))
+ item->split_sum_func(thd, ref_pointer_array, fields);
+ else if (item->type() == SUM_FUNC_ITEM ||
+ (item->used_tables() && item->type() != REF_ITEM))
{
uint el= fields.elements;
ref_pointer_array[el]=*arg;
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index c39caabeacf..e3f1053dc73 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1776,9 +1776,12 @@ String *Item_func_elt::val_str(String *str)
void Item_func_make_set::split_sum_func(THD *thd, Item **ref_pointer_array,
List<Item> &fields)
{
- if (item->with_sum_func && item->type() != SUM_FUNC_ITEM)
+ if (item->type() != SUM_FUNC_ITEM &&
+ (item->with_sum_func ||
+ (item->used_tables() & PSEUDO_TABLE_BITS)))
item->split_sum_func(thd, ref_pointer_array, fields);
- else if (item->used_tables() || item->type() == SUM_FUNC_ITEM)
+ else if (item->type() == SUM_FUNC_ITEM ||
+ (item->used_tables() && item->type() != REF_ITEM))
{
uint el= fields.elements;
ref_pointer_array[el]=item;
diff --git a/sql/unireg.h b/sql/unireg.h
index eca540b61b9..8d88683241b 100644
--- a/sql/unireg.h
+++ b/sql/unireg.h
@@ -77,6 +77,8 @@
#define PARAM_TABLE_BIT (((table_map) 1) << (sizeof(table_map)*8-3))
#define OUTER_REF_TABLE_BIT (((table_map) 1) << (sizeof(table_map)*8-2))
#define RAND_TABLE_BIT (((table_map) 1) << (sizeof(table_map)*8-1))
+#define PSEUDO_TABLE_BITS (PARAM_TABLE_BIT | OUTER_REF_TABLE_BIT | \
+ RAND_TABLE_BIT)
#define MAX_FIELDS 4096 /* Limit in the .frm file */
#define MAX_SORT_MEMORY (2048*1024-MALLOC_OVERHEAD)
View Lorry Controller Status