From: Date: April 20 2005 1:54am
Subject: bk commit into 4.1 tree (igor:1.2199) BUG#9681
List-Archive: http://lists.mysql.com/internals/24154
X-Bug: 9681
Message-Id: <20050419235438.5380CFF96C@rurik.mysql.com>

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.2199 05/04/19 16:54:30 igor@stripped +3 -0
  sql_select.cc:
    Fixed bug #9681.
    The bug happened with queries using derived tables specified by
    a SELECT with ROLLUP, such as:
    SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) t2,
    if column a of table t1 is declared as NOT NULL.
    This was to the fact that the first column of the temporary table
    created to contain the derived table erroneously inherited the NOT NULL
    attribute from column a.
  olap.result, olap.test:
    Added a test case for bug #9681.

  sql/sql_select.cc
    1.396 05/04/19 16:53:02 igor@stripped +0 -7
    Fixed bug #9681.
    The bug happened with queries using derived tables specified by
    a SELECT with ROLLUP, such as:
    SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) t2,
    if column a of table t1 is declared as NOT NULL.
    This was to the fact that the first column of the temporary table
    created to contain the derived table erroneously inherited the NOT NULL
    attribute from column a.

  mysql-test/r/olap.result
    1.16 05/04/19 16:43:35 igor@stripped +13 -0
    Added a test case for bug #9681.

  mysql-test/t/olap.test
    1.13 05/04/19 16:43:03 igor@stripped +13 -0
    Added a test case for bug #9681.

  sql/sql_select.cc
    1.395 05/04/19 16:40:59 igor@stripped +19 -0
    Fixed bug #9681.
    The bug happened with queries using derived tables specified by
    a SELECT with ROLLUP, such as:
    SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) t2,
    if column a of table t1 is declared as NOT NULL.
    This was to the fact that the first column of the temporary table
    created to contain the derived table erroneously inherited the NOT NULL
    attribute from column a.

# 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.394/sql/sql_select.cc	Thu Apr  7 06:47:36 2005
+++ 1.396/sql/sql_select.cc	Tue Apr 19 16:53:02 2005
@@ -4779,7 +4779,7 @@
       item->result_field= new_field;
     else
       new_field->field_name= name;
-    if (org_field->maybe_null())
+    if (org_field->maybe_null() || (item && item->maybe_null))
       new_field->flags&= ~NOT_NULL_FLAG;	// Because of outer join
     if (org_field->type() == FIELD_TYPE_VAR_STRING)
       table->db_create_options|= HA_OPTION_PACK_RECORD;
@@ -9192,7 +9192,19 @@
     for (j=0 ; j < fields_list.elements ; j++)
       rollup.fields[i].push_back(rollup.null_items[i]);
   }
+  List_iterator_fast<Item> it(fields_list);
+  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)
+        item->maybe_null= 1;
+    }
+  }
   return 0;
+
 }
   
 

--- 1.15/mysql-test/r/olap.result	Thu Mar 17 20:16:30 2005
+++ 1.16/mysql-test/r/olap.result	Tue Apr 19 16:43:35 2005
@@ -392,3 +392,16 @@
 a	SUM(b)
 1	4
 DROP TABLE t1;
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
+a	m
+1	1
+2	2
+NULL	3
+SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
+a	m
+1	1
+2	2
+NULL	3
+DROP TABLE t1;

--- 1.12/mysql-test/t/olap.test	Thu Mar 17 20:16:01 2005
+++ 1.13/mysql-test/t/olap.test	Tue Apr 19 16:43:03 2005
@@ -171,3 +171,16 @@
 SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
 
 DROP TABLE t1;
+
+#
+# Tests for bug #9681: ROLLUP in subquery for derived table wiht 
+#                      a group by field declared as NOT NULL
+#
+
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
+SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
+
+DROP TABLE t1;