MySQL Bugs: #9681: rollup in from-subquery returns error "collumn cannot be null"

Bug #9681	rollup in from-subquery returns error "collumn cannot be null"
Submitted:	6 Apr 2005 15:28	Modified:	28 Apr 2005 15:41
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Any (*)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
If the column that is used in the subquery's "group by" is declared or assumed "not null", the select will fail, because the rollup produces a NULL value.

This is similiar to bug 9360.

How to repeat:
select * from ( select a,  sum(a) m from (select 1 a union select 2  ) t1 group by a with rollup ) t2;

ERROR 1048 (23000): Column 'a' cannot be null

# with a table

create table t1 (a int(11) not null);
insert into t1 values (1),(2);
select * from ( select a,  sum(a) m from  t1 group by a with rollup ) t2;

ERROR 1048 (23000): Column 'a' cannot be null

Suggested fix:
-

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24154

ChangeSet
  1.2199 05/04/19 16:54:30 igor@rurik.mysql.com +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.

The fix will appear in version 4.1.12 and 5.0.5

Noted in 4.1.12, 5.0.5 changelogs.