MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:igor Date:April 29 2007 11:04pm
Subject:bk commit into 4.1 tree (igor:1.2650) BUG#24856
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor 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@stripped, 2007-04-29 16:04:43-07:00, igor@stripped +4 -0
  Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
  some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
  list contained constant expressions.
  
  This happened because the results of constant expressions were not put
  in the temporary table used for duplicate elimination. In fact a constant
  item from the GROUP BY list of a ROLLUP query can be replaced for an
  Item_null_result object when a rollup row is produced . 
  
  Now the JOIN::rollup_init function wraps any constant item referenced in
  the GROYP BY list of a ROLLUP query into an Item_func object of a special
  class that is never detected as constant item. This ensures creation of
  fields for such  constant items in temporary tables and guarantees right
  results when the result of the rollup operation first has to be written
  into a temporary table, e.g. in the cases when duplicate elimination is
  required.  

  mysql-test/r/olap.result@stripped, 2007-04-29 16:04:42-07:00, igor@stripped +61 -0
    Added a test case for bug #24856.

  mysql-test/t/olap.test@stripped, 2007-04-29 16:04:42-07:00, igor@stripped +19 -0
    Added a test case for bug #24856.

  sql/item_func.h@stripped, 2007-04-29 16:04:42-07:00, igor@stripped +25 -0
    Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
    some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
    list contained constant expressions.
    
    Itroduced class Item_func_rollup_const derived from Item_func. The object of
    this class are never detected as constant items.
    We use them for wrapping constant items from the GROUP BY list of any ROLLUP
    query. This wrapping allows us to ensure writing constant items into temporary
    tables whenever the result of the ROLLUP operation has to be written into a
    temporary table, e.g. when ROLLUP is used together with DISTINCT in the SELECT
    list.

  sql/sql_select.cc@stripped, 2007-04-29 16:04:42-07:00, igor@stripped +27 -1
    Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
    some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
    list contained constant expressions.
    
    Now the JOIN::rollup_init function wraps any constant item referenced in
    the GROYP BY list of a ROLLUP query into an Item_func object of a special
    class that is never detected as constant item. This ensures creation of
    fields for such  constant items in temporary tables and guarantees right
    results when the result of the rollup operation first has to be written
    into a temporary table, e.g. in the cases when duplicate elimination is
    required.  

# 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:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-4.1-opt-bug24856

--- 1.134/sql/item_func.h	2007-04-29 16:04:46 -07:00
+++ 1.135/sql/item_func.h	2007-04-29 16:04:46 -07:00
@@ -590,6 +590,31 @@
 };
 
 
+/* 
+  Objects of this class are used for ROLLUP queries to wrap up 
+  each constant item referred to in GROUP BY list. 
+*/
+
+class Item_func_rollup_const :public Item_func
+{
+public:
+  Item_func_rollup_const(Item *a) :Item_func(a)
+    { name= a->name; }
+  double val() { return args[0]->val(); }
+  longlong val_int() { return args[0]->val_int(); }
+  String *val_str(String *str) { return args[0]->val_str(str); }
+  const char *func_name() const { return "rollup_const"; }
+  bool const_item() const { return 0; }
+  Item_result result_type() const { return args[0]->result_type(); }
+  void fix_length_and_dec()
+  {
+    collation= args[0]->collation;
+    max_length= args[0]->max_length;
+    decimals=args[0]->decimals; 
+  }
+};
+
+
 class Item_func_length :public Item_int_func
 {
   String value;

--- 1.469/sql/sql_select.cc	2007-04-29 16:04:46 -07:00
+++ 1.470/sql/sql_select.cc	2007-04-29 16:04:46 -07:00
@@ -9754,7 +9754,7 @@
     for (j=0 ; j < fields_list.elements ; j++)
       rollup.fields[i].push_back(rollup.null_items[i]);
   }
-  List_iterator_fast<Item> it(all_fields);
+  List_iterator<Item> it(all_fields);
   Item *item;
   while ((item= it++))
   {
@@ -9767,6 +9767,32 @@
       {
         item->maybe_null= 1;
         found_in_group= 1;
+        if (item->const_item())
+        {
+          /*
+            For ROLLUP queries each constant item referenced in GROUP BY list
+            is wrapped up into an Item_func object yielding the same value
+            as the constant item. The objects of the wrapper class are never
+            considered as constant items and besides they inherit all
+            properties of the Item_result_field class.
+            This wrapping allows us to ensure writing constant items
+            into temporary tables whenever the result of the ROLLUP
+            operation has to be written into a temporary table, e.g. when
+            ROLLUP is used together with DISTINCT in the SELECT list.
+            Usually when creating temporary tables for a intermidiate
+            result we do not include fields for constant expressions.
+	  */           
+          Item* new_item= new Item_func_rollup_const(item);
+          if (!new_item)
+            return 1;
+          new_item->fix_fields(thd,0, (Item **) 0);
+          thd->change_item_tree(it.ref(), new_item);
+          for (ORDER *tmp= group_tmp; tmp; tmp= tmp->next)
+          { 
+            if (*tmp->item == item)
+              thd->change_item_tree(tmp->item, new_item);
+          }
+        }
       }
     }
     if (item->type() == Item::FUNC_ITEM && !found_in_group)

--- 1.23/mysql-test/r/olap.result	2007-04-29 16:04:46 -07:00
+++ 1.24/mysql-test/r/olap.result	2007-04-29 16:04:46 -07:00
@@ -556,3 +556,64 @@
 2006-07-01	NULL	11
 NULL	NULL	11
 drop table t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 
+VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
+SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+a	SUM(b)
+1	30
+2	90
+3	30
+NULL	150
+SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+a	SUM(b)
+1	30
+2	90
+3	30
+NULL	150
+SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
+a	b	COUNT(*)
+1	10	1
+1	20	1
+1	NULL	2
+2	10	2
+2	30	1
+2	40	1
+2	NULL	4
+3	30	1
+3	NULL	1
+NULL	NULL	7
+SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
+a	b	COUNT(*)
+1	10	1
+1	20	1
+1	NULL	2
+2	10	2
+2	30	1
+2	40	1
+2	NULL	4
+3	30	1
+3	NULL	1
+NULL	NULL	7
+SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+x	a	SUM(b)
+x	1	30
+x	2	90
+x	3	30
+x	NULL	150
+NULL	NULL	150
+SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+x	a	SUM(b)
+x	1	30
+x	2	90
+x	3	30
+x	NULL	150
+NULL	NULL	150
+SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+x	a	SUM(b)
+x	1	30
+x	2	90
+x	3	30
+x	NULL	150
+NULL	NULL	150
+DROP TABLE t1;

--- 1.20/mysql-test/t/olap.test	2007-04-29 16:04:46 -07:00
+++ 1.21/mysql-test/t/olap.test	2007-04-29 16:04:46 -07:00
@@ -281,4 +281,23 @@
 select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
 drop table t1;
 
+#
+# Bug #20825: ROLLUP by const item in a query with DISTINCT
+#
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 
+  VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
+
+SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
+SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
+
+SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+
+DROP TABLE t1;
+
 # End of 4.1 tests
Thread
bk commit into 4.1 tree (igor:1.2650) BUG#24856igor30 Apr