MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:igor Date:March 16 2005 6:51am
Subject:bk commit into 4.1 tree (igor:1.2121) BUG#8616
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
  1.2121 05/03/15 22:50:54 igor@stripped +5 -0
  olap.result, olap.test:
    Added a test case for bug #8616.
  item.h:
    Fixed bug #8616.
    Added class Item_null_result used in rollup processing.
  sql_select.h, sql_select.cc:
    Fixed bug #8616.
    Added JOIN::rollup_write_data to cover rollup queries
    with DISTINCT. Modified other rollup methods.

  mysql-test/r/olap.result
    1.13 05/03/15 22:49:41 igor@stripped +54 -0
    Added a test case for bug #8616.

  mysql-test/t/olap.test
    1.10 05/03/15 22:49:21 igor@stripped +27 -0
    Added a test case for bug #8616.

  sql/item.h
    1.173 05/03/15 22:47:26 igor@stripped +11 -0
    Fixed bug #8616.
    Added class Item_null_result used in rollup processing.

  sql/sql_select.h
    1.72 05/03/15 22:46:11 igor@stripped +2 -1
    Fixed bug #8616.
    Added JOIN::rollup_write_data to cover rollup queries
    with DISTINCT. Modified other rollup methods.

  sql/sql_select.cc
    1.386 05/03/15 22:44:45 igor@stripped +92 -26
    Fixed bug #8616.
    Added JOIN::rollup_write_data to cover rollup queries
    with DISTINCT. Modified other rollup methods.

# 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:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-4.1-0

--- 1.172/sql/item.h	Fri Mar  4 02:16:24 2005
+++ 1.173/sql/item.h	Tue Mar 15 22:47:26 2005
@@ -470,6 +470,17 @@
   Item *safe_charset_converter(CHARSET_INFO *tocs);
 };
 
+class Item_null_result :public Item_null
+{
+public:
+  Field *result_field;
+  Item_null_result() : Item_null(), result_field(0) {}
+  bool is_result_field() { return result_field != 0; }
+  void save_in_result_field(bool no_conversions)
+  {
+    save_in_field(result_field, no_conversions);
+  }
+};  
 
 /* Item represents one placeholder ('?') of prepared statement */
 

--- 1.385/sql/sql_select.cc	Tue Mar  8 10:45:49 2005
+++ 1.386/sql/sql_select.cc	Tue Mar 15 22:44:45 2005
@@ -157,7 +157,7 @@
 				      uint elements, List<Item> &items);
 static void init_tmptable_sum_functions(Item_sum **func);
 static void update_tmptable_sum_func(Item_sum **func,TABLE *tmp_table);
-static void copy_sum_funcs(Item_sum **func_ptr);
+static void copy_sum_funcs(Item_sum **func_ptr, Item_sum **end);
 static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab);
 static bool init_sum_functions(Item_sum **func, Item_sum **end);
 static bool update_sum_func(Item_sum **func);
@@ -1328,7 +1328,7 @@
       if (curr_join->tmp_having)
 	curr_join->tmp_having->update_used_tables();
       if (remove_duplicates(curr_join, curr_tmp_table,
-			    curr_join->fields_list, curr_join->tmp_having))
+			    *curr_fields_list, curr_join->tmp_having))
 	DBUG_VOID_RETURN;
       curr_join->tmp_having=0;
       curr_join->select_distinct=0;
@@ -6740,26 +6740,32 @@
     {
       if (join->procedure)
 	join->procedure->end_group();
-      if (idx < (int) join->send_group_parts)
+      int send_group_parts= join->send_group_parts;
+      if (idx < send_group_parts)
       {
 	if (!join->first_record)
 	{
 	  /* No matching rows for group function */
 	  join->clear();
 	}
-	copy_sum_funcs(join->sum_funcs);
-	if (!join->having || join->having->val_int())
+        copy_sum_funcs(join->sum_funcs,
+                       join->sum_funcs_end[send_group_parts]);
+	if (join->having && join->having->val_int() == 0)
+          error= -1;
+        else if ((error=table->file->write_row(table->record[0])))
+	{
+	  if (create_myisam_from_heap(join->thd, table,
+				      &join->tmp_table_param,
+				      error, 0))
+	    DBUG_RETURN(-1);		       
+        }
+        if (join->rollup.state != ROLLUP::STATE_NONE && error <= 0)
 	{
-	  if ((error=table->file->write_row(table->record[0])))
-	  {
-	    if (create_myisam_from_heap(join->thd, table,
-					&join->tmp_table_param,
-					error, 0))
-	      DBUG_RETURN(-1);			// Not a table_is_full error
-	  }
-	  else
-	    join->send_records++;
+	  if (join->rollup_write_data((uint) (idx+1), table))
+	    error= 1;
 	}
+	if (error > 0)
+	  DBUG_RETURN(-1);	  
 	if (end_of_records)
 	  DBUG_RETURN(0);
       }
@@ -8888,11 +8894,10 @@
 	/* Copy result of sum functions to record in tmp_table */
 
 static void
-copy_sum_funcs(Item_sum **func_ptr)
+copy_sum_funcs(Item_sum **func_ptr, Item_sum **end_ptr)
 {
-  Item_sum *func;
-  for (; (func = *func_ptr) ; func_ptr++)
-    (void) func->save_in_result_field(1);
+  for (; func_ptr != end_ptr ; func_ptr++)
+    (void) (*func_ptr)->save_in_result_field(1);
   return;
 }
 
@@ -9013,14 +9018,16 @@
   */
   tmp_table_param.group_parts= send_group_parts;
 
-  if (!(rollup.fields= (List<Item>*) thd->alloc((sizeof(Item*) +
-						 sizeof(List<Item>) +
-						 ref_pointer_array_size)
-						* send_group_parts)))
+  if (!(rollup.null_items= (Item_null_result**) thd->alloc((sizeof(Item*) +
+                                                sizeof(Item**) +
+                                                sizeof(List<Item>) +
+				                ref_pointer_array_size)
+				                * send_group_parts )))
     return 1;
+  
+  rollup.fields= (List<Item>*) (rollup.null_items + send_group_parts);
   rollup.ref_pointer_arrays= (Item***) (rollup.fields + send_group_parts);
   ref_array= (Item**) (rollup.ref_pointer_arrays+send_group_parts);
-  rollup.item_null= new (thd->mem_root) Item_null();
 
   /*
     Prepare space for field list for the different levels
@@ -9028,12 +9035,16 @@
   */
   for (i= 0 ; i < send_group_parts ; i++)
   {
+    rollup.null_items[i]= new (thd->mem_root) Item_null_result();
     List<Item> *rollup_fields= &rollup.fields[i];
     rollup_fields->empty();
     rollup.ref_pointer_arrays[i]= ref_array;
     ref_array+= all_fields.elements;
+  }
+  for (i= 0 ; i < send_group_parts; i++)
+  {
     for (j=0 ; j < fields_list.elements ; j++)
-      rollup_fields->push_back(rollup.item_null);
+      rollup.fields[i].push_back(rollup.null_items[i]);
   }
   return 0;
 }
@@ -9137,7 +9148,8 @@
       {
 	/* Check if this is something that is part of this group by */
 	ORDER *group_tmp;
-	for (group_tmp= start_group ; group_tmp ; group_tmp= group_tmp->next)
+	for (group_tmp= start_group, i-- ;
+             group_tmp ; group_tmp= group_tmp->next, i++)
 	{
 	  if (*group_tmp->item == item)
 	  {
@@ -9146,7 +9158,9 @@
 	      set to NULL in this level
 	    */
 	    item->maybe_null= 1;		// Value will be null sometimes
-	    item= rollup.item_null;
+            Item_null_result *null_item= rollup.null_items[i];
+            null_item->result_field= ((Item_field *) item)->result_field;
+            item= null_item;
 	    break;
 	  }
 	}
@@ -9199,6 +9213,58 @@
 	  result->send_data(rollup.fields[i]))
 	return 1;
       send_records++;
+    }
+  }
+  /* Restore ref_pointer_array */
+  set_items_ref_array(current_ref_pointer_array);
+  return 0;
+}
+
+/*
+  Write all rollup levels higher than the current one to a temp table
+
+  SYNOPSIS:
+    rollup_write_data()
+    idx                 Level we are on:
+                        0 = Total sum level
+                        1 = First group changed  (a)
+                        2 = Second group changed (a,b)
+    table               reference to temp table
+
+  SAMPLE
+    SELECT a, b, SUM(c) FROM t1 GROUP BY a,b WITH ROLLUP
+
+  RETURN
+    0	ok
+    1   if write_data_failed()
+*/
+
+int JOIN::rollup_write_data(uint idx, TABLE *table)
+{
+  uint i;
+  for (i= send_group_parts ; i-- > idx ; )
+  {
+    /* Get reference pointers to sum functions in place */
+    memcpy((char*) ref_pointer_array,
+	   (char*) rollup.ref_pointer_arrays[i],
+	   ref_pointer_array_size);
+    if ((!having || having->val_int()))
+    {
+      int error;
+      Item *item;
+      List_iterator_fast<Item> it(rollup.fields[i]);
+      while ((item= it++))
+      {
+        if (item->type() == Item::NULL_ITEM && item->is_result_field())
+          item->save_in_result_field(1);
+      }
+      copy_sum_funcs(sum_funcs_end[i+1], sum_funcs_end[i]);
+      if ((error= table->file->write_row(table->record[0])))
+      {
+	if (create_myisam_from_heap(thd, table, &tmp_table_param,
+				      error, 0))
+	  return 1;		     
+      }
     }
   }
   /* Restore ref_pointer_array */

--- 1.71/sql/sql_select.h	Sun Nov  7 15:13:50 2004
+++ 1.72/sql/sql_select.h	Tue Mar 15 22:46:11 2005
@@ -124,7 +124,7 @@
 {
   enum State { STATE_NONE, STATE_INITED, STATE_READY };
   State state;
-  Item *item_null;
+  Item_null_result **null_items;
   Item ***ref_pointer_arrays;
   List<Item> *fields;
 } ROLLUP;
@@ -295,6 +295,7 @@
   bool rollup_make_fields(List<Item> &all_fields, List<Item> &fields,
 			  Item_sum ***func);
   int rollup_send_data(uint idx);
+  int rollup_write_data(uint idx, TABLE *table);
   bool test_in_subselect(Item **where);
   void join_free(bool full);
   void clear();

--- 1.12/mysql-test/r/olap.result	Wed Aug 11 23:54:02 2004
+++ 1.13/mysql-test/r/olap.result	Tue Mar 15 22:49:41 2005
@@ -307,3 +307,57 @@
 2004-06-07	1	0
 NULL	3	1
 DROP TABLE user_day;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES
+(1,4),
+(2,2), (2,2),
+(4,1), (4,1), (4,1), (4,1),
+(2,1), (2,1);
+SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)
+4
+6
+4
+14
+SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)
+4
+6
+14
+SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)	COUNT(DISTINCT b)
+4	1
+6	2
+4	1
+14	3
+SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)	COUNT(DISTINCT b)
+4	1
+6	2
+14	3
+SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)	COUNT(*)
+4	1
+6	4
+4	4
+14	9
+SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)	COUNT(*)
+4	1
+6	4
+4	4
+14	9
+SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SUM(b)	COUNT(DISTINCT b)	COUNT(*)
+4	1	1
+6	2	4
+4	1	4
+14	3	9
+SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
+GROUP BY a WITH ROLLUP;
+SUM(b)	COUNT(DISTINCT b)	COUNT(*)
+4	1	1
+6	2	4
+4	1	4
+14	3	9
+DROP TABLE t1;

--- 1.9/mysql-test/t/olap.test	Wed Aug 11 23:54:36 2004
+++ 1.10/mysql-test/t/olap.test	Tue Mar 15 22:49:21 2005
@@ -125,3 +125,30 @@
 
 DROP TABLE user_day;
 
+#
+# Test for bug #8616: distinct sum with rollup
+#
+
+CREATE TABLE t1 (a int, b int);
+
+INSERT INTO t1 VALUES
+  (1,4),
+  (2,2), (2,2),
+  (4,1), (4,1), (4,1), (4,1),
+  (2,1), (2,1);
+
+SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+
+SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
+
+SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+
+SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
+  GROUP BY a WITH ROLLUP;
+
+DROP TABLE t1;
+
Thread
bk commit into 4.1 tree (igor:1.2121) BUG#8616igor16 Mar